Incorrect Syntax near ''@Variable''.????

Question

Post reply

Incorrect Syntax near ''@Variable''.????

James Stevens-465623

Old Hand

Points: 314
More actions
August 14, 2007 at 12:00 am

#180305

Hi,
Hope someone can help me. Having trouble translating a script that works fine on SQL 2000 alone that fires the xp_cmdshell procedure to delete some files used when upgrading our database. I'm trying to 'upgrade' the script to make it generic enough to run on either SQL 2000 or 2005, first by finding out the version, then if 2005 by including a handler to enable xp_cmdshell if disabled:
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
END
Then by including the same sections as were in the SQL 2000 script but modified to include the 2005 file path.
BEGIN
-- for SQL 2005
DECLARE @DOSCommand VarChar(200)
SELECT @DOSCommand = 'DEL "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_DataHold.mdf"'
PRINT ' Removing file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_DataHold.mdf'
PRINT @DOSCommand
EXECUTE xp_cmdshell @DOSCommand -- *ERROR HERE*
GO
DECLARE @DOSCommand VarChar(200)
SELECT @DOSCommand = 'DEL "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_LogHold.ldf"'
PRINT ' Removing file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_LogHold.ldf'
PRINT @DOSCommand
EXECUTE xp_cmdshell @DOSCommand
GO
END
When i run the parser or try to run the script it bombs with the following error on the line that says --*ERROR HERE* above:
Incorrect syntax near '@DOSCommand'
Anyone have any ideas as to what I'm doing wrong? I can't modify the script in any way to get rid of the error.
Thanks!
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Cook Say Hey Kid Points: 678 More actions · Answer 1

Hi,

Take the GO command out and it works perfectly (well on mine anyway).

GO Commands don't belong inside a begin - end loop.

Regards,

Ian.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

Hi James,

SQL Server 2005's parser does not like the GO statements interrupting its BEGIN-END blocks.

Removing the GOs could solve the problem. In this case your new batch will share the declared variable @DOSCommand. You could either reuse this variable by removing the second declaration, or rename it in the old second batch.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Well, Ian has already answered this question while I was typing my answer Sorry for the redundancy.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

James Stevens-465623 Old Hand Points: 314 More actions · Answer 4

Thanks guys, Seems to have done the job, though I'm sure I tried that yesterday! As far redundancy Andras, that's no problem since you repeated what Ian had said it further stressed that this was likely the problem.

Thanks again,

James