Link to home
Start Free TrialLog in
Avatar of wppiexperts
wppiexpertsFlag for United States of America

asked on

SQL Server: trouble referencing temp table through bcp

through a stored procedure I'm populating a temp table (#TempItems) from a series of queries.
the last step is the export the data in the #TempItems table to a text file. I'm using the following code:
exec master..xp_cmdshell 'bcp "select * from #TempItems" queryout c:\Rev_codes.txt -T -c'
which results in the follwing error messages:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TempItems'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

I'm assuming I'm not referencing the temp table properly in the sql statement....how should I do that?

Thanks!


Avatar of lahousden
lahousden
Flag of United States of America image

Not sure if this will work, but you could try using square brackets:

exec master..xp_cmdshell 'bcp "select * from [#TempItems]" queryout c:\Rev_codes.txt -T -c'
Avatar of wppiexperts

ASKER

nope, no luck with the brackets.

I'm assuming I need to identify the owner of the temp table (I tried tempdb..TempItems), but had the same results.

ASKER CERTIFIED SOLUTION
Avatar of lahousden
lahousden
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of earthdust
earthdust

I just want to add that we had the same experience. But even with a global (##) table it could not find the table. Upon closer examination, we knew that it had something to do with the instance of the SQL service running on the server. We had multiple instances, for instance [severname\instancename]. You sometimes have to specify the instance you want the BCP command to go look into when exporting data from a table to a file. Use the -S parameter to specify the specific instance of the SQL service on the server, for instance if you have two instances running on the same server you can use the following command "exec master..xp_cmdshell 'bcp "select * from master..##mytable" queryout c:\myfile.txt -T -c -S myservername\myinstancename'.