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
|