x

SQL Server 2005 BCP Function sequence error

Hi,

I have few batch jobs that are running for years on SQL server 2000 successfully. Now we are migrating to SQL server 2005.

the batch file uses BCP query out. Some of the procedures have #temp table. thus in the batch file, we had to use "set fmtonly off". When running these files on SQL server 2005, we get following error:

SQLState = S1010, NativeError = 0 Error = [Microsoft][SQL Native Client]Function sequence error

If I remove "set fmtonly only" BCP does not recognize #temp table.

I cannot post my company code but I wrote this small proc for testing:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO

create procedure dbo.testbcp as select top 10 * into #temp from entity

select * from #temp

And bcp out as follows from batch file:

bcp "exec dbo.testbcp" queryout d:\data\a.txt -c -S Server -U LMUsername -P Password

We are in the urgent need. Any help that does not require change in the SP is greatly appreciated.

more ▼

asked Jun 11, 2010 at 12:29 PM in Default

avatar image

Manish 1
2 2 2 2

I have applied SP3 on MS SQL Server 2005, that did not resolve this problem but did resolve the same problem with all the procedures where I was not selecting data from #temp table.

Jun 11, 2010 at 12:30 PM Manish 1

Are there any parameters used in the query you are passing to BCP?

Jun 12, 2010 at 01:59 PM Fatherjack ♦♦

Hi Fatherjack, my procedure is too long, that uses serveral parameters, however, those parameters are giving any problem. Because the code that posted above does not contain any parameters, still gives error.

I have simply inserted few rows into a #temp table, and the selecting from that #temp table. BCP in 2005 version does not like select from #temp table.

Jun 13, 2010 at 11:03 PM Manish 1

create procedure dbo.testbcp as select top 10 * into #temp from entity

select * from #temp

bcp "exec dbo.testbcp" queryout d:\data\a.txt -c -S Server -U LMUsername -P Password

Just reposted the test code and the BCP statement again for reference.

Jun 13, 2010 at 11:04 PM Manish 1
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It might be that the BCP is being thrown by the first "SELECT" statement. Try changing your stored procedure so that the first line is

SET NOCOUNT ON

and then carry on with the rest of your SP as written, ie:

create procedure dbo.testbcp as 
SET NOCOUNT ON
select top 10 * into #temp from entity
select * from #temp
more ▼

answered Jun 11, 2010 at 12:34 PM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

Sorry, no luck, exactly the same result.

Jun 11, 2010 at 01:10 PM Manish 1
(comments are locked)
10|1200 characters needed characters left

Hi

Has there been a solution for this? I'm sitting with the same problem and havelooked all over, can't find anything.

Thanks Ruan

more ▼

answered May 04, 2016 at 01:30 PM

avatar image

ruancra
1.2k 31 38 46

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2036
x57

asked: Jun 11, 2010 at 12:29 PM

Seen: 2984 times

Last Updated: May 04, 2016 at 01:30 PM

Copyright 2018 Redgate Software. Privacy Policy