CODEWITHSUNDEEP
t-sql
stackuser
stackuser

Reputation: 672

Issues witht the bcp command when using variables

The bcp commnad below gives NO errors

exec master..xp_cmdshell 'bcp "select RecordType + PaymentYear from   CentersApp.dbo.IRS_TRecord"     
queryout "\\w2k3solomon\c$\IRS1099B\IRS_VENDORS.TXT"  -T -c -S SOLOMON'

However, when I use variables to do the same thing (see below), I am getting the error 'bcp' is not recognized as an internal or external command

  declare @t nvarchar(200)
  declare @s nvarchar(1000)


   set @t = '"select RecordType + PaymentYear from CentersApp.dbo.IRS_TRecord"'
   set @s =  '''bcp ' + '"' +  @t + '"' +  '        queryout "\\w2k3solomon\c$\IRS1099B\IRS_VENDORS.TXT"  -T -c -S SOLOMON '''

   exec master..xp_cmdshell @s

Upvotes: 2

Views: 6090

Answers (1)

Matt Smucker
Matt Smucker

Reputation: 5234

Change 

set @s =  '''bcp ' + '"' +  @t + '"' +  '        queryout "\\w2k3solomon\c$\IRS1099B\IRS_VENDORS.TXT"  -T -c -S SOLOMON '''

to

set @s =  'bcp ' + '"' +  @t + '"' +  '        queryout "\\w2k3solomon\c$\IRS1099B\IRS_VENDORS.TXT"  -T -c -S SOLOMON '

it is stored in a string variable type so you don't need to use the quotes

Upvotes: 1

Related Questions