question

Manish 1 avatar image
Manish 1 asked

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 have SQL Server 2005 SP 3 installed on our server, that includes all the hot fixes as suggested by Microsoft Knowledge bases.

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

sql-server-2005bcp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.