February 11, 2008 at 4:01 pm
John Beggs (2/11/2008)
I don't know why I didn't think of this last week, but the problem (in my case) was in the database compatability level. I was working in an old testing DB that I had moved over from a 2000 server some time back and never changed it to 9.0 compatability level.@cgrigolini -- only you will know if it's safe to change the compatability level in your DB. It's my guess that is the cause of the Syntax Error.
Perfect, John... Dunno why I didn't think of that... maybe it's 'cause I quit smokin' and can't see past the thumb I'm sucking now
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 5:02 am
For earlier versions of sql that don't have row_number or cte's you can use the following :-
SET STATISTICS TIME ON
DECLARE @DUMMYVAR INT
SELECT @DUMMYVAR = seq
from
(select f.col * 100000 + e.col * 10000 + d.col * 1000 + c.col * 100 + b.col * 10 + a.col as Seq
from
(
select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as a
cross join
( select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as b
cross join
( select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as c
cross join
( select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as d
cross join
( select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as e
cross join
( select colid -1 as col from master.dbo.syscolumns
where id = 1 and colid < 11 ) as f
) as nums
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1871 ms, elapsed time = 1871 ms.
its not as fast but it works
Conan
Viewing 2 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy