why does this fail - 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

SQL Server 2008 Forums

Transact-SQL (2008)

why does this fail

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-12 : 07:20:44

i have a cursor writing data to a text file. This works fine until
@DEST_ACCOUNTNAME contains '&' . I need the '&' to appear, how can i get around this problem

Set @WriteLine = 'echo ' + @DEST_SORTCODE + @DEST_ACCOUNTNUMBER + @TRANSACTIONCODE + @ORIG_SORTCODE
+ '0000' + @TRANSACTIONVALUE + @ORIG_ACCOUNTNAME + @DEST_BANKREFERENCE + @DEST_ACCOUNTNAME + ' >> ' + @FileName
exec @xpreurn_Value = master..xp_cmdshell @WriteLine
If @xpreurn_Value <> 0
Begin
return 1
End

Kristen
Test

22859 Posts

Posted - 2012-02-12 : 07:34:11

Escape it with ^&


Set @WriteLine = REPLACE(REPLACE(
                    'echo ' + @DEST_SORTCODE + @DEST_ACCOUNTNUMBER + @TRANSACTIONCODE + @ORIG_SORTCODE
                            + '0000' + @TRANSACTIONVALUE + @ORIG_ACCOUNTNAME + @DEST_BANKREFERENCE + @DEST_ACCOUNTNAME 
                            + ' >> ' + @FileName 
                    , '^', '^^')
                    , '&', '^&')

that will also take care of the unlikely situation that you have "^&" in your data stream too ...

Add more REPLACE for any other characters that need escaping

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-12 : 07:44:08

Brilliant, thank you that did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources