CODEWITHSUNDEEP
sql
MickJuice
MickJuice

Reputation: 539

Using Table Variables with xp_cmdshell

I am trying to create a flat text file using a query that concatenates multiple values from multiple tables. This result set is being inserted into a table variable which I would like to use in the xp_cmdshell call to create the text file. Here's a sample of my code.

DECLARE @tablevar table (string nvarchar(200))

INSERT INTO @tablevar
SELECT 'test' + column1 + column2
FROM SampleTable

EXEC xp_cmdshell 'bcp "SELECT * FROM @tablevar" queryout "C:\temp\output.txt" -T -c '

I get the following error when I make the xp_cmdshell call:

SQLState = 42000, NativeError = 1087 Error = [Microsoft][SQL Native Client][SQL Server]Must declare the table variable "@outputtable". SQLState = 42000, NativeError = 8180 Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared. NULL

When attempting to use a temp table use this code

EXEC xp_cmdshell 'bcp "Select * From #temp" queryout "C:\temp\outputtable.txt" -T -c '

I get this error message

SQLState = 42S02, NativeError = 208 Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'tempdb.temp'. SQLState = 42000, NativeError = 8180 Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

Is using a temp table or table variable with xp_cmdshell just not possible?

Upvotes: 2

Views: 4727

Answers (1)

Alexandre Danault
Alexandre Danault

Reputation: 8682

Your @table variable and #temp table are both out of scope to other connections, but global temp tables like ##my_global_temp should work.

Upvotes: 3

Related Questions