CODEDROP CURSOR CODE
You can submit data and code confidentially to Microsoft on Connect.Įrland Sommarskog, SQL Server MVP, for SQL Server Books Online: In either cases, you would need to have to supply a repro that demonstrates the issue. but this is less likely to result in a hotfix. Nevertheless, Microsoft takes backwards compatibility seriously, and if you think this is a showstopper for you, you should open a case with Microsoft, and if you present strong business arguments they may manufacture a hotfix for you. So the fact it has worked for you over the year, might be due to plain luck. I maintain that this is a quite a questionable design. I don't think this is any direct limitaiton with cursors, but possibly the cursor I don't know what is going on, but I guess there is some buffering in SQL Server that causes OSQL see so many messages at ones that the argument to SQLGetDiagRec overflows. I assumed you that passed the data to an application.
The fact that you run this procedure through OSQL is a very important piece of information that was missing from your original post. If it works with SQLCMD, you have a solution. I'm not saying that your procedure will work with any of the other two - but the error message in such case should be different. The error message you get clearly comes from ODBC, and OSQL uses ODBC. Have you tried running the procedure in SQL Server Management Studio or through SQLCMD? Has anyone else seen this problem? I searched the forums but didn't find anything else like it. With the sproc I get output for every row that should be there, but after the first 32767 rows, I get the message "SQLGetDiagRec failed" for each line of output that equates to what should be a row of results.īecause of the signed 16-bit integer number of rows valid data rows in the output, I thought maybe it was due to an integer overflow from a smallint, but I checked the sproc and that is not the case. NET or C# program, I get theĬomplete set of results. If I do essentially the same primary query and processing with a VB. The problem is that now with SQL 2008 the cursor for the primary query doesn't seem to return more than 32,767 rows of valid data if I use transact SQL. Unfortunately the processing after the primary query is complicated enough that it's just about impossible to retrieve the results with a simple query, and also the data has to be formatted and converted to other data types before redirecting the results to The tables for the primary queries typically have anywhere from hundreds of thousands to many millions of rows, but only a small amount of data is retrieved for each row. It uses a cursor to do a primary query, and then iterates through the cursor results with fetch and runs other queriesĪnd calls other stored procedures based on the results of the initial query. There is a type of stored procedure that I have been using variations on for years, mostly with SQL Server 7 and SQL Server 2000.
0 kommentar(er)
