Exec not working in SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exec not working in SQL 2000

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2003-05-28 : 05:59:54

Hi,
can somebody help me with the new syntax in SQL 2000 with regards to execute statements. the sql below compiles in sqL 7.0 query analyzer but not in Sql 2000 query Analyzer.

exec (" insert into #debt
select'chshowcd' = chExhibitioncd , 'TotalBalance_Greater_120' = sum(Balance_Greater_120), '
Totalsunallocated' = sum(mTotalUnallocatedSite)
from
rptageddebtorbycompany ,
omshow
WHERE rptageddebtorbycompany.chshowcd = omshow.chshowcd and rptageddebtorbycompany.ishowyear = omshow.ishowyear and
rptageddebtorbycompany. ishowyear IN (" + @ishowyear + " ) and omshow.chStatusCd in (" + @ShowStatus + ")and omshow.vchGLJointVentCd in (" + @Site + ")GROUP BY chExhibitioncd")

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-28 : 07:41:07

SQL 2000 doesn't like " you must use apostrophes. Where you need to put an apostrophe inside your dynamic SQL double it up to escape it.

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-28 : 07:41:24

Don't use double quotes (") to delimit strings, only use single quotes. You can also use the AS syntax to alias columns:

exec ('insert into #debt
select chExhibitioncd AS chshowcd,
sum(Balance_Greater_120) AS TotalBalance_Greater_120,
sum(mTotalUnallocatedSite) AS Totalsunallocated
from rptageddebtorbycompany INNER JOIN omshow
ON rptageddebtorbycompany.chshowcd = omshow.chshowcd
and rptageddebtorbycompany.ishowyear = omshow.ishowyear
WHERE rptageddebtorbycompany.ishowyear IN (' + @ishowyear + ')
and omshow.chStatusCd in (' + @ShowStatus + ')
and omshow.vchGLJointVentCd in (' + @Site + ')
GROUP BY chExhibitioncd')

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 10:35:08

Wow 17 seconds...but I still think Graz beating me by 7 seconds is still the record...can you query the database to find the 2 closest post?

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-28 : 21:10:55

I checked

There are a bunch of 1 second posts. The earliest I can find is Justin sniping me here :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6898

Damian

Lavos
Posting Yak Master

200 Posts

Posted - 2003-05-28 : 23:44:11

Ahh yes, but how fast do each of you type? Those posts had far different lengths :)

I'd be interested in seeing how many are 1 sec with nearly identical word cuonts, and when far wordier posts lost/won. But I'm strange so.....

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources