May 30, 2012 at 4:58 pm
I am executing a package in a stored proc using dynamic sql along with connection string as package variable and the password has a ampersand in it which is causing an error. Is there escape character that I can use for the ampersand.
ERROR MESSAGE:
The argument "\package.Variables[ConnString].Value;"Server=testserver;Database=testDATABASE;User ID=testUSER;Password=WrKe\ " has mismatched quotes.
'WEVzz' is not recognized as an internal or external command,
I am using something like this below.
----package name
SET @packagename = 'testpackage'
SET @ServerName = 'testserver'
SET @params = '/SET \package.Variables[ConnString].Value;"\"Server= ' + @ServerName + ';Database=testDATABASE;User ID=testUSER;Password=WrKe$WEVzz;Trusted_Connection = False\""'
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
----now making "dtexec" SQL from dynamic values
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @ServerName + ' '
SET @ssisstr = @ssisstr + @params
----now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode
May 31, 2012 at 12:13 pm
I figure it out putting double quotes around the whole password.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply