SQL Server - 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.

It looks like deadlock problem. Does anybody using/modifying the simultaniously?

This is the problem with Microsoft SQL Server 2005 Service Pack 2 Cumulative Update 5, This issue does not occur before you install SQL Server 2005 Service Pack 2 Cumulative Update 5. To determine whether you have SQL Server 2005 Service Pack 2 Cumulative Update 5 installed on the server, run the following SQL Server statement.

SELECT @@version
there will be hotfix available for this, refer this link for more information
http://support.microsoft.com/kb/952723

Sorry Sara, no one is using the proc or any other program at this time. I get this error even when I created the temp proc for my testing.

Thanks. We did have SP2, and looking at the same KB we installed SP3 that includes this hotfix.

This resolved our all the problems other than when the procedure includes select from #temp table.