FREE hit counter and Internet traffic statistics from freestats.com

Tuesday, April 27, 2004

Long running procedures and ADO.NET

Ran into an interesting problem this morning while at a client site. The client has a stored procedure in SQL Server that uses cursors to load data from one table into another. When the procedure executes sucessfully it processes over 1,000,000 rows in around 60 hours. However, when the stored procedure is kicked off in a .NET executable using the SqlClient .NET Data Provider a command timeout exception is fired after 18 hours, 12 minutes or thereabouts (even when the CommandTimeout property is set to 0 which should wait indefinitely). Tests were also run setting the CommandTimeout value to a large number (say 216,000) to force the command to wait 60 hours to no avail. When using the OleDb provider the timeout is not enountered.

Well, when we calculated the number of seconds in 18 hours and 12 minutes it comes to roughly 65,520 which immediately raised eyebrows. Although the data type of the CommandTimeout property of the SqlCommand object is System.Int32 which has an upper limit of 2,147,483,648, limit of System.UInt16 is 65,535. Apparently, internally the SqlClient provider is using an unsigned integer in its calculations for the command timeout. Has anyone else run into this problem?

2 comments:

Anonymous said...

Hi Dan,

Yes, I have the same problem. See this link: http://www.dotnet247.com/247reference//msgs/43/216470.aspx

I still can't see any documentation from microsoft on this behaviour, and where the Int32 is sliced to an UInt16.

Kind Regards,

Thomas Ebling

Anonymous said...

Do want to know the magic of online games, and here you can get more maple mesos. Do you want to have a try? Come on and mesos can make you happy.You can change a lot cheap mesos for play games. I often go and play online game. Playing online games can make much maplestory mesos. And you will feel well. And you can use the maple story mesos do what you want to do in the online game.