wppiexperts
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!
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!
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\myinstancenam e'.
exec master..xp_cmdshell 'bcp "select * from [#TempItems]" queryout c:\Rev_codes.txt -T -c'