Cursor issue ?
-
Hi everybody
I start the new year facing a problem that I don't understand.
I have a stored procedure in SQL Server 2000 doing the following:
1/ select ... from myview where ...
2/ DECLARE CURSOR FOR myselection
3/ While @@FETCH_STATUS <> 0
concatenate data
FETCH NEXT
if next record has a different ID
insert into a table
Looks basic - should work easily...
Run the stored procedure in SQL Query Analyser. Works fine.
For the bug I'm trying to fix, about 250 rows fetched with the cursor and about 30 records inserted properly in my table.
But I actually have to call this stored procedure from an external application. This application uses ODBC.
...
myquery = 'EXECUTE my_sp 123456'
myresult = SQL(myquery, 'void')
...
myresult is true. So everything looks to run smoothly
Back to my database: instead of 30 records inserted, only a few (5)
DB has not been updated or changed. Nobody accessed this table.
Can't find out what goes wrong.
The cursor takes about 20 seconds to be created in SQL Query Analyzer. Could it be a sort of timeout? Something to do with my cursor declaration (using all default parameters)? Issue with asynchronous population of the data set... I don't know !
Is there anyway to trace the execution of a stored procedure in SQL Server when a procedure is called from a external application? SOmething in the execution context must change between running this procedure within SQL Query Anallyzer and running it from an external call... don't understand what
Thanks for your help
Nicolas
-
DO you have any commits in your SP as everything should work or fail.
WHat is the application written in and can you change to ADO easily (or with help).
-
I have no commit. I thought an implicit commit was done after each query by default. have to try.
Can't changed to ADO (imply too many changes).
-
Your code has the snippet:
3/ While @@FETCH_STATUS <> 0
Don't you mean WHILE @@FETCH_STATUS = 0? A zero means that the fetch worked.
While I'm here, I will mention that I usually favor the syntax WHILE @@FETCH_STATUS <> -1, since a fetch can return -2 with dynamic cursors, which would lead to prematurely exiting the fetch loop. But in your case, this wouldn't be causing the problem ... but please double-check that you didn't just fat-brain the line of code mentioned above.
Good luck!
Chris
-
-
It could be due to the fact that the default SET OPTIONS that are set when you connect via query analyzer
and via ODBC.
It looks like in your case the specific setting CONCAT_NULL_YIELDS_NULL is NOT SET when you are executing
via query analyzer and is SET when executing via ODBC.
Include following statement as first line in stored procedure
SET CONCAT_NULL_YIELDS_NULL OFF
and see whether this makes any difference
-
Rajesh's idea about CONCAT_NULL_YIELDS_NULL is an excellent one, but IIRC, this setting is SET by default in the Query Analyzer and NOT SET via ODBC. However, Rajesh still could be correct, if you've changed the default connection settings at all.
If CONCAT_NULL_YIELDS_NULL is not the problem, you might want to run DBCC USEROPTIONS in Query Analyzer and then also through your ODBC connection and compare the results. That way you will know what connection options are set in each environment. It is a best practice of sorts to pick a set of connection options and to try to use them in all your connections, by the way, and this task is complicated by the fact the Rajesh points out: different client apps have different default connection settings. For example, Query Analyzer generally sets all the ANSI/SQL-92 options ON.
Good luck!
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply