Sintax error, stopred procedure

Question

Post reply

Sintax error, stopred procedure

river1

SSC-Insane

Points: 24315
More actions
May 22, 2012 at 4:59 am

#392311

Hi,
I have the following code:
exec master.dbo.xp_cmdshell 'rmdir "c:\tempo_SGCTLOCAL\"', no_output
exec master.dbo.xp_cmdshell 'mkdir "c:\tempo_SGCTLOCAL\"', no_output
exec master.dbo.xp_cmdshell 'MOVE '+@caminho+'*.* c:\tempo_SGCTLOCAL', no_output
exec master.dbo.xp_cmdshell 'rd /s/q '+@caminho+'', no_output
exec master.dbo.xp_cmdshell 'mkdir "'+@caminho+'"', no_output
exec master.dbo.xp_cmdshell 'MOVE c:\tempo_SGCTLOCAL*.rar '+@caminho+'', no_output
exec master.dbo.xp_cmdshell 'rd /s/q c:\tempo_sgrs', no_output
When I try to parse it, i receive the following error:
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 13
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 14
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 15
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 16
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 25
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS, Line 27
Incorrect syntax near '+'.
How can I put the value of the variable @caminho inside the '' without receiving this error?
Casn someone help?
Thank you.
P.S - I know about the security risks of using the xp_cmdshell.

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

declare @sql nvarchar(4000)

set @sql = 'exec master.dbo.xp_cmdshell ''MOVE '+@caminho+'*.* c:\tempo_SGCTLOCAL'', no_output'

EXEC(@sql)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

river1 SSC-Insane Points: 24315 More actions · Answer 2

But how to do it for several at the same time, like:

exec master.dbo.xp_cmdshell 'rmdir "c:\tempo_SGCTLOCAL\"', no_output

exec master.dbo.xp_cmdshell 'mkdir "c:\tempo_SGCTLOCAL\"', no_output

exec master.dbo.xp_cmdshell 'MOVE '+@caminho+'*.* c:\tempo_SGCTLOCAL', no_output

exec master.dbo.xp_cmdshell 'rd /s/q '+@caminho+'', no_output

exec master.dbo.xp_cmdshell 'mkdir "'+@caminho+'"', no_output

exec master.dbo.xp_cmdshell 'MOVE c:\tempo_SGCTLOCAL*.rar '+@caminho+'', no_output

exec master.dbo.xp_cmdshell 'rd /s/q c:\tempo_sgrs', no_output

I don't have only

exec master.dbo.xp_cmdshell 'rmdir "c:\tempo_SGCTLOCAL\"', no_output

Ant-Green SSC Guru Points: 113563 More actions · Answer 3

easy, just set @sql = to what you want it to do then execute it like this

declare @sql nvarchar(max)

set @sql = 'exec master.dbo.xp_cmdshell ''rmdir "c:\tempo_SGCTLOCAL\"'', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''mkdir "c:\tempo_SGCTLOCAL\"'', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''MOVE ''+@caminho+''*.* c:\tempo_SGCTLOCAL'', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''rd /s/q ''+@caminho+'''', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''mkdir "''+@caminho+''"'', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''MOVE c:\tempo_SGCTLOCAL*.rar ''+@caminho+'''', no_output'

exec (@sql)

set @sql = 'exec master.dbo.xp_cmdshell ''rd /s/q c:\tempo_sgrs'', no_output'

exec (@sql)

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Or do it in one go -

DECLARE @sql NVARCHAR(4000);

SELECT @sql =

'exec master.dbo.xp_cmdshell ''rmdir "c:\tempo_SGCTLOCAL\"'', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''mkdir "c:\tempo_SGCTLOCAL\"'', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''MOVE '+@caminho+'*.* c:\tempo_SGCTLOCAL'', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''rd /s/q '+@caminho+''', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''mkdir "'+@caminho+'"'', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''MOVE c:\tempo_SGCTLOCAL*.rar '+@caminho+''', no_output;'+CHAR(13)+CHAR(10)+

'exec master.dbo.xp_cmdshell ''rd /s/q c:\tempo_sgrs'', no_output;';

EXECUTE sp_executesql @sql;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/