When getting Dynamic SQL to work I find the key is

1. Print the statement that is generated.
2. Manually run it - you can troubleshoot it and determine if the
syntax is correct.
** As the service account - ofcourse.

You will quickly spot errors with delimiters, quotes, etc. this way.
And "Getting an error " - well - what is the error ?



-----Original Message-----
From: Mirabella Derek
[mailto:mssqldba-ezmlmshield-x98720575.[Email address protected]
Sent: Wednesday, February 28, 2007 10:40 AM
To: LazyDBA Discussion
Subject: " quotes to appear in t-sql
Importance: Low

I am trying to shell out to dos and execute a simple ftp command, the
problem is, when I run this statement through dos it works fine, when I
try to execute through a job, or t-sql I am getting an error

"C:\Program Files\WS_FTP Pro\wsftppro.exe" -s "Shared Sites!User
Sites!MYFTPSERVER:/e:/e:/backup_mp" -d "local:F:\prod_backups\"



Here is my code



SET NOCOUNT ON



declare @DOSCommand varchar(150)

set @DOSCommand = '"C:\Program Files\WS_FTP Pro\wsftppro.exe" -s "Shared
Sites!User Sites!MYFTPSERVER:/e:/e:/backup_mp" -d
"local:F:\prod_backups\"'

print @DOSCommand

exec master..xp_cmdshell @DOSCommand





I have a feeling it has something to do with the "s



Because the results showing in QA are as follows





'C:\Program' is not recognized as an internal or external command,

operable program or batch file.

NULL



There should be a double quote in front of the C:\ not a single



Any help would greatly be appreciated



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page