0

When I run the following code to output an Sql query to a file, it works fine:

exec xp_cmdshell 'bcp "SELECT top 5 * from NK.[dbo].[eclubCustomerDelta]" queryout c:\nk\contacts.txt -c -T'

But I have saved my query in another variable earlier in the code:

SET @result = (SELECT @x,@y
FOR XML PATH('contacts'))

@x and @yare themselves variables defined earlier (the query gives the correct output when run reparately). This being said, a similar output of the query does not seem to be working:

exec xp_cmdshell 'bcp "SELECT @result" queryout c:\nk\contacts.txt -c -T'

Here is how my output looks:

Starting copy...

SQLState = 37000, NativeError = 137

Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Must declare the scalar variable "@result".

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

BCP copy out failed

disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • You are passing command line arguments to a command-line utility `bcp`, which is a separate exe. Why do you expect this separate exe would be able to see your `@result`? – GSerg Feb 01 '17 at 14:18
  • try escaping @ character – E LaRoche Feb 01 '17 at 14:18
  • 1
    Possible duplicate of [Passing variable into xp\_cmdshell](http://stackoverflow.com/questions/35691620/passing-variable-into-xp-cmdshell) – GSerg Feb 01 '17 at 14:22
  • @GSerg agree, but could you show me the way? in the code above my `@result` is an Xml variable. I don't think my question is a duplicate. The other question has another command and has nothing to do with Xml. – disasterkid Feb 01 '17 at 15:03
  • @Disasterkid It does not matter what your variable contains. The point is that you put the contents or the variable into the command string like you do with dynamic SQL. – GSerg Feb 01 '17 at 15:05
  • @GSerg the query `SELECT @result` is exactly I need. If I put my entire query in the `bcp` command my code becomes very hard to read. – disasterkid Feb 01 '17 at 15:06
  • @Disasterkid Then also put `SET @result = ...` in your command, before the `select @result`, preceded by everything else that is referenced in the `set`. If you don't like that either, create a stored procedure/function with that code and call it by name from the command. – GSerg Feb 01 '17 at 15:07
  • @GSerg my `@result` variable also contains references to other variables. Should I put the entire query in a bcp command? Isn't there a cleaner way of doing this? – disasterkid Feb 01 '17 at 15:10

0 Answers0