I'm missing something

Question

declare @servername varchar(2000)
set @EmriServerit=(select @@servername)
declare @dbname varchar(2000)
set @dbname ='Test1'
declare @Dir varchar(2000)
set @Dir='F:\dataclient.sql'

exec master.dbo.xp_cmdshell 'osql -E -S ' + @servername+ ' -d ' + 
    @dbname +' -i ' + @Dir

It gives me and error: "incorrect sysntax near +"

If i don't use variables it works ok. What am i missing! Thanks in advance

Could it be the missing space after servername? – Bobby Apr 07 '10 at 11:26 — Bobby, Apr 07 '10 at 11:26

score 3 · Accepted Answer · answered Apr 07 '10 at 11:28

3

Additions are not allowed in a parameter list. Move them to a separate line, like:

declare @cmd varchar(500)
set @cmd = 'osql -E -S ' + @servername+ ' -d ' + @dbname +' -i ' + @Dir
exec master.dbo.xp_cmdshell @cmd

answered Apr 07 '10 at 11:28

Andomar

232,371
49
380
404

score 2 · Answer 2 · answered Apr 07 '10 at 11:27

2

You don't assign a value to @servername

answered Apr 07 '10 at 11:27

Álvaro González

142,137
41
261
360

I'm sorry i wrote it wrong on the post! It is declare as servername – Redi Apr 07 '10 at 11:30

score 2 · Answer 3 · answered Apr 07 '10 at 11:28

I would try to concatenate together your command string first, and then check it before executing:

declare @servername varchar(2000)
set @servername = select @@servername

declare @dbname varchar(2000)
set @dbname ='Test1'

declare @Dir varchar(2000)
set @Dir='F:\dataclient.sql'

declare @execCmd varchar(max)
set @execCmd = 'osql -E -S ' + @servername+ ' -d ' + @dbname +' -i ' + @Dir

print @execCmd  -- what do you get as output here??

exec master.dbo.xp_cmdshell @execCmd

score 0 · Answer 4 · answered Apr 07 '10 at 11:26

0

Try putting the entire exec on one line (this is a bit of a stab in the dark, I confess).

answered Apr 07 '10 at 11:26

Marcelo Cantos

181,030
38
327
365

That's my fault... I made a line break when I reformtted the code. – Anders Abel Apr 07 '10 at 11:29

score 0 · Answer 5 · answered Apr 07 '10 at 11:35

try :

declare @EmriServerit varchar(2000)
set @EmriServerit=(select @@servername)
declare @dbname varchar(2000)
set @dbname ='Test1'
declare @Dir varchar(2000)
set @Dir='F:\dataclient.sql'
declare @Command varchar(500)

set @command='osql -E -S ' + @EmriServerit+ ' -d ' + 
    @dbname +' -i ' + @Dir
exec master.dbo.xp_cmdshell @command

you had the server name declared and used differently, and you are not permitted to have expressions as parameters to a stored procedure, so concatenate the command into a variable and then pass it to the master.dbo.xp_cmdshell stored procedure.

I'm missing something

5 Answers5