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
|