Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_ Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?
Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_ Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.sql

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me],
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
as FeederName,
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2].[Program_Web]
as FeederWeb FROM
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me]
= 'Dental Assistant (Cert)' and
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
<> '' order by
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog_2
'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name],
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> as FeederName,
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2].[Program_Web]
> as FeederWeb FROM
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name]
> = 'Dental Assistant (Cert)' and
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> <> '' order by
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column pref
ix
> 'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog
_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or m
ore tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to
access it. Or the view may be in another
schema and you will have to preface that view name with that.

Query works - sproc fails

I have a query that works fine but fails as a sproc.

QUERY:

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = 'Brown'

SPROC:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GetProfileId]

@.UserName nvarchar

AS

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = @.UserName

The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0

Set a size to the parameter. That might help.

|||

kerchunk!

That did it. Thanks!

Query work in sql 200 fails in sql 2005

Hello all,
The query below, has been succesfully working in sql 2000 for months.
While I recongnize that the isnumeric attribute is reference twice
(this has since been corrected).
My concern is why did this generate an error in sql 2005 and not sql
2000.
Error = (Duplicate column names are not allowed in result sets obtained
through OPENQUERY and OPENROWSET.)
I am concerned that I've missed a server setting.
If this is just a case where it should of failed in 2000 I would feel
much better.
Any insight into this would be greatly appreciated.
Thanks,
Henry Lovera
--
insert into @.deal_properties (deal_property_id,
parent_deal_property_id, is_numeric, property_name, property_format,
display_order, string_value, numeric_value)
select
deal_property_id
,parent_deal_property_id
,is_numeric
,property_name
,property_format
,display_order
,string_value = case when is_numeric = 0 then value
end
,numeric_value = case when is_numeric = 1 then convert(float,
value) end
from
openxml(@.xml_doc, '//deal_property', 1)
with( deal_property_id int
,parent_deal_property_id int
,is_numeric int
,property_name varchar(32)
,property_format varchar(16)
,display_order int
,is_numeric bit
,value varchar(100))Henry
I was not able to test it because the script throws lots of errors.
Please post proper DDL+ sample data.
<hanklvr@.yahoo.com> wrote in message
news:1143684314.846726.121030@.t31g2000cwb.googlegroups.com...
> Hello all,
> The query below, has been succesfully working in sql 2000 for months.
> While I recongnize that the isnumeric attribute is reference twice
> (this has since been corrected).
> My concern is why did this generate an error in sql 2005 and not sql
> 2000.
> Error = (Duplicate column names are not allowed in result sets obtained
> through OPENQUERY and OPENROWSET.)
> I am concerned that I've missed a server setting.
> If this is just a case where it should of failed in 2000 I would feel
> much better.
> Any insight into this would be greatly appreciated.
> Thanks,
> Henry Lovera
> --
> insert into @.deal_properties (deal_property_id,
> parent_deal_property_id, is_numeric, property_name, property_format,
> display_order, string_value, numeric_value)
> select
> deal_property_id
> ,parent_deal_property_id
> ,is_numeric
> ,property_name
> ,property_format
> ,display_order
> ,string_value = case when is_numeric = 0 then value
> end
> ,numeric_value = case when is_numeric = 1 then convert(float,
> value) end
> from
> openxml(@.xml_doc, '//deal_property', 1)
> with( deal_property_id int
> ,parent_deal_property_id int
> ,is_numeric int
> ,property_name varchar(32)
> ,property_format varchar(16)
> ,display_order int
> ,is_numeric bit
> ,value varchar(100))
>

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

OK

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!