Prev: Reporting Services And Object Data Source
Next: Query and Extract
From: Anonymous User on 14 Jun 2010 08:29
Hi Everyone,

I am using a product which generates its own SQL and it worked fine under
SQL Server 2000, but after a recent upgrade to SQL Server 2008, I am getting
the above error on several queries.

Here is what this product generates:

SELECT dbo.v_dtf_SCOpenCallsTech.preffullname AS "preffullname"

which comes up with the error in the subject line above.

If I change it around and execute it in a query window:

SELECT preffullname AS "preffullname" from dbo.v_dtf_SCOpenCallsTech

This works fine. It seems like their method is an odd way to reference the
desired table and field, but it used to work fine. Is there a compatibility
option or setting change in SQL 2008 that will allow this to work the way it
did before?

Thanks,

Alan


From: Erland Sommarskog on 14 Jun 2010 18:18
Anonymous User (nospam38925(a)forme.com) writes:
> I am using a product which generates its own SQL and it worked fine
> under SQL Server 2000, but after a recent upgrade to SQL Server 2008, I
> am getting the above error on several queries.
>
> Here is what this product generates:
>
> SELECT dbo.v_dtf_SCOpenCallsTech.preffullname AS "preffullname"
>
> which comes up with the error in the subject line above.

And the full query that it generated was?

> If I change it around and execute it in a query window:
>
> SELECT preffullname AS "preffullname" from dbo.v_dtf_SCOpenCallsTech
>
> This works fine. It seems like their method is an odd way to reference
> the desired table and field, but it used to work fine. Is there a
> compatibility option or setting change in SQL 2008 that will allow this
> to work the way it did before?

Since I don't know what the full query was, it's difficult to tell.
But there were bugs in SQL 2000 where it was possible to use prefixes
where it should not be possible. You could use prefixes that not have
been defined. I don't remember on the top of my head whether these
errors not reported if you set the database compatibility level 80,
but you could try:

ALTER DATABASE db SET COMPATIBILITY_LEVEL = 80

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Reporting Services And Object Data Source
Next: Query and Extract