BCP using tempdb in 2005

Question

Post reply

BCP using tempdb in 2005

Green Armchair

Hall of Fame

Points: 3561
More actions
November 29, 2006 at 9:42 am

#171477

Hi
Getting an error running bcp queryout in 2005 when executing a stored procedure that loads data into intermediate temporary tables in tempdb.
bcp "exec mis.dbo.MISR020_SELECT 0, 125000000000000" queryout "..\..\files\misr020a.dat" -c -t"|" -CACP -S%InstanceName% -T
returns:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
The sp declares a temporary table (e.g. @temp1) , inserts data into it and then joins to other permanent tables to produce final result set.
This worked fine in 2000
Anybody else seen this?
Thanks
Andy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Green Armchair Hall of Fame Points: 3561 More actions · Answer 1

Actually, its simply running the following that breaks bcp:

CREATE

PROCEDURE [dbo].[MISR020_SELECT_AC]

AS

declare

@MinFCPT smallint

select @MinFCPT = FIN_PERIOD from vmi_c001 where jobname = 'misr050'

bcp "exec mis.dbo.MISR020_SELECT_AC" queryout "..\..\files\misr020a.dat" -c -t"|" -CACP -S%InstanceName% -T

returns:

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column

This is an extract from the full sp, but its enough to demonstrate my problem. Also fails when using tempdb as in my previous post

Ric Francis SSC Veteran Points: 206 More actions · Answer 2

Hi Andy,

I have exactly the same problem right now. You didn't find any solution did you?

Thanks,

Ric.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

Ric

MS have confirmed that it is a bug with SQL 2005. If you have a support agreement, you should raise a call. We are currently in the process of getting a fix. I'm not too sure how this all works so don't know if it will be available publically.

There are workarounds however:

1. Use SQLCMD instead. With a variety of switches, you should be able to produce output in the same format. We didn't want to do this because we didn't want to rewrite our batch code. It does perform as well though.

2. Use SQL 2000 version of bcp (you will need both bcp.exe and bcp.rll - take a backup of 2005 versions first!). We didn't want to use this as it didn't perform as well.

Hope this helps

Andy

Ric Francis SSC Veteran Points: 206 More actions · Answer 4

Thanks Andy - that's useful.

I'm actually in the process of rewriting the SP in question to use a new view instead of the temp table, which will actually work just as well for me.

bcp's hard enough to get going in the first place & it's a real pain to debug.

Many thanks for your help,

Ric.