master..xp_cmdshell error - 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

General SQL Server Forums

New to SQL Server Programming

master..xp_cmdshell error

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-28 : 10:54:43

I keep getting a "Incorrect syntax error near ' -Q''' " which is at the end of my xp_cmdshell line. What am I doing wrong?

CREATE Procedure dbo.sp_ImportRawData
(@ClientAbbrev nchar(4), @FileDate nvarchar(8), @SessionId nvarchar(50) )
AS
DECLARE @Msg varchar(255)
DECLARE @DataUpdates nvarchar(255)
DECLARE @Common nvarchar(255)
DECLARE @UServer nvarchar(255)
DECLARE @FullFileName nvarchar(68)
DECLARE @FileName nvarchar(64)
DECLARE @FileExt nvarchar(3)
DECLARE @SQL varchar(3500)
DECLARE @triFileName nvarchar(255)

Declare @Ip_FileName nvarchar(255)
Declare @Ip_Active char(1)

select @UServer = sd_value from Msysdata Where SD_Property = 'UpDateServer'
select @Common = sd_value from Msysdata WHERE SD_Property = 'CommonDir'
select @DataUpdates = sd_Value from Msysdata WHERE SD_Property = 'DataUpdate'

Set @UServer = @UServer + '\D$'

SET @Msg = 'Truncate tri_ active files'
EXEC MARS_SYS.dbo.sp_WriteLog @ClientAbbrev, 'UpdateMgr', @Msg, @FileDate, @SessionId

Declare ActiveFile Cursor For
Select Ip_FileName, Ip_Active from tbl_InputFiles
where Ip_Active = 'Y'

Open ActiveFile
Fetch Next From ActiveFile
Into @Ip_FileName, @Ip_Active

While @@Fetch_Status = 0
Begin

Exec('truncate table tri_'+ @Ip_FileName)
Set @FullFileName = @Ip_Filename + '.csv'

set @UServer = ''
set @Common = ''
set @triFileName = 'tri_' + @Ip_FileName

set @sql = 'master..xp_cmdshell ''' + @UServer + @Common + 'd:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\' + @FullFileName + ' -Q''' + @triFileName
exec(@SQL)

Fetch Next From ActiveFile
Into @Ip_FileName, @Ip_Active

End

CLOSE ActiveFile
DEALLOCATE ActiveFile

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:01:26

What happens when you PRINT @SQL before EXEC (@SQL)
and the open a CMD window and exeucte the statement [generated with the PRINT] manually?

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-28 : 11:15:42

I get an incorrect syntax near the keyword 'null'. error, here is what print(@sql) showed

master..xp_cmdshell 'd:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\actfile.csv -Q'tri_actfile

It has to be the extra ' after Q, but what is causing it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:24:09

I think DOS prefers " (ASCII 34, 1 double quote) instead of '' (ASCII 39, 2 single quotes).

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-28 : 11:28:18

so this line works in dos but using it in sql I get the error "Unclosed quotation mark before the character string 'd:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\actfile.csv -Q tri_actfile'."

set @sql = 'master..xp_cmdshell ''' + @UServer + @Common + 'd:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\' + @FullFileName + ' -Q ' + @triFileName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:30:51

quote:
Originally posted by smorty44

here is what print(@sql) showed

master..xp_cmdshell 'd:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\actfile.csv -Q'tri_actfile

How can that be?
'd$' is added to @userver.
Also @UServer is added to the path, which means you have the path twice...

Do it again, and do it right.

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-28 : 11:43:50

I'm not using @UServer or @Common, I have them set to ' '. I have hard coded the path in there to try and break down why this isn't working. I have removed them and tried again to see if that was the problem. Now I get, "Incorrect syntax near '\'" and "The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure."

set @sql = 'master..xp_cmdshell d:\mars_sys\common\scripts\odbcbcp -D CopyMedMgr10_Testing -i D:\mars_sys\dataupdates\ccbm\' + @FullFileName + ' -Q ' + @triFileName

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-28 : 13:32:39

It is working now for any of those that might run into this problem. I need to add + '''' to the end of the xp_cmdshell statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 13:45:11

Thats why you always need to PRINT the statement when using dynamic sql.
Then you will see what is wrong.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources