BCP on global ##temp table error

Question

I'm using the following query to write the data in a external file:

declare @sql varchar(8000);
select @sql = 'bcp tempdb.##hmscript out 
F:\HMS\test.txt -c -t, -T -S GIT2B-01\MON'
select @sql
exec master..xp_cmdshell @sql;

When testing the bcp in cmd window I'm getting the following error:

SQLState = 37000, NativeError = 11525 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]The metadata could not be determined because statement 'select * from ##hmscript' uses a temp table.

Somebody has a solution for this?

Thanks in advance,

David

Why are you using a global ##temp table? Since this effectively reduces concurrency to 1, just use a regular user table and drop it when you are done. — Aaron Bertrand
that was another option I was thinking about, but I guessed my curiosity took the upperhand and I wanted to try something :) But seeing it's quite a messy thing to do using global temp table, I'll switch to a normal table like you said — David Vandenbroucke
Does the ##hmsscript table exist when you run this script? The tempdb database qualifier should be omitted since the ## prefix denotes tempdb. — Dan Guzman

daviddeath daviddeath · Accepted Answer · 2015-05-29T19:46:47

If the table doesn't exist, you'll get this error.

Create the table and try again, and you should get what you want.

BCP on global ##temp table error

1 Answers