BCP call to SP with temp table not working

Question

BCP call to SP with temp table not working

petey20

SSCommitted

Points: 1741
More actions
February 3, 2005 at 1:21 pm

#65523

I have a SP with an inline temp table
Much like
select * into ##temp from table
Do some stuff and select from ##temp to return results. In the Query Analyzer this SP works perfectly.
When I invoke this from a bcp command like below:
bcp "exec syteline7_app..matric_ARPayment ' 106',' 106' " queryout C:\webscripts\arpayment\dump\jpeters_no_header.xls -F1 -c -S Bluto -U blahblah -P xxxxxx
I get the following error.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##FI
NAL_PAY'.
Do I need to turn some options on before calling this SP from bcp??

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Jakob SSCommitted Points: 1720 More actions · Answer 1

I wouldn't try to use a global temporary table--or a temporary table at all for that matter if you are going to be doing BCP. Use a permanent table.

You can name the table "TableName_BCP" to make it clear what it is used for.

If you will have multiple threads hitting the table, you can add a "SessionID" column or something like that to the table.

You could even "simulate" a temporary table with a permanent table, using logic like:

IF NOT EXISTS(MyTable)...

CREATE MyTable...

ELSE

TRUNCATE MyTable...

[BCP INTO MyTable...]

DROP MyTable

Have fun.

- john

mwelcome Right there with Babe Points: 769 More actions · Answer 2

There is a problem in BCP handling querries with temp tables correctly. I am not exacly sure what causes it. All I know if that you will have to redesign the query to not use a temp table. I ran into the same thing.

philcart SSC-Forever Points: 47796 More actions · Answer 3

Should you be using BCP at all? Why not use BULK INSERT?

--------------------
Colt 45 - the original point and click interface

petey20 SSCommitted Points: 1741 More actions · Answer 4

Actually I'm calling the BCP command from a VBscript that exports to an excel file. I typically will use the ISQL or OSQL call to do inserts or BULK INSERTS, but this stored procedure is doing more than just inserting some values, it's actually returning values that need exported to XLS format. SOmething I do quite often, except this SP uses Temp tables.

Now I could use the ISQL call with the -o option which works in this case, but exports to txt format. I'd then have to parse my values within the VB script which isn't the preferred way.

Basically I created work tables in the DB. Even when creating them inline I get the same error.

I thought there must be a bug in BCP since it works fine using ISQL or Query ANalyzer

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

See this thread in the MS newsgroups (apologies for the long Google URL):

http://www.google.ca/groups?hl=en&lr=&threadm=759DA088-AA28-4D4B-B420-690C28A8CE90%40microsoft.com&rnum=13&prev=/groups%3Fq%3Dbcp%2Btemp%2Btable%2Bgroup:microsoft.public.sqlserver.*%26hl%3Den%26lr%3D%26selm%3D759DA088-AA28-4D4B-B420-690C28A8CE90%2540microsoft.com%26rnum%3D13

To sum up, BCP with a Stored Proc executes the proc multiple times - first time with SET FMTONLY ON to determine the column format for output. Since SET FMTONLY ON doesn't actually execute the proc, the temp table doesn't get created and you get the error, even with a global temp table.

You may need to resort to a "permanent" temp table.

petey20 SSCommitted Points: 1741 More actions · Answer 6

I ended up just using a cursor to walk down through the values instead of an inline select into a temp table.

Thanks for the info! Nice thing to know