Home   Archive   Permalink



ODBC error: Invalid Cursor State

I've faced a very strange ODBC problem, might save some hours of others: Connect to your SQL server database via ODBC
    
     db: first open odbc://myDB
    
     insert db "select 3 * 5"
     copy db
     >> [[15]] ;this is ok
    
if you put some variable declarations in your SQL, then
    
     insert db "declare @i int; set @i = 3; select 3 * 5"
     copy db
     >> Invalid Cursor State Error
    
And the solution of this stupid problem is to put "set nocount on"
    
     insert db "set nocount on; declare @i int; set @i = 3; select 3 * 5"
     copy db
     >> [[15]]
    
This is because of, SQL server tries to return more than one result set if you use more than one select and/or set statement in your query.

posted by:   Endo       24-Jan-2011/8:12:23-8:00



Thank you! All glory to the internet and Google search. This worked like a charm for my situation - saving me untold hours of work and pestering co-workers.

posted by:   Dave       3-Aug-2011/15:57:17-7:00



I'm glad that it is helpful for someone :)
I spent 2-3 hours to find what the problem is..
I think sending a "SET NOCOUNT" SQL command just after connecting to db server is a good practice to prevent these kind of stupid problems.

posted by:   Endo       10-Aug-2011/12:51:02-7:00