24000 Invalid cursor state. Prepared Statement

I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.

I am not using / importing any dlls.

I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.

I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.

First time when I bind paramaters using SQLBindParameter it works perfect.

When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.

If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.

I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.

Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?

Thanksyes you missed something. see brett's sticky at the top of the page.|||I traced the solution.

I thought that the mistake is in declaring scrollable cursors.

So I explained precisouly the problem in 7-8 lines.
Reading 50-60 lines with altogether different coding standards is difficult.

Its not coding problem but associated with scrollable cursor options.
So I did not include the code.

MSDN examples does not show such required step as the scope of example code gets over before such situation is reached!!.

2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.

2005 Cursor Looping Issue

I've just begun to work in 2005 and am trying to run a cursor without any modification, which has proven to work in SQL 2000 and it's not looping.

the cursor has a few declared variables, running a select statement and assigning the returned value to the variables, then executing a sp using the variables as input. It is really written out textbook, for example:

declare
@.var1 int

declare cuMyCursor
Cursor For
(Select etc...)
Open cuMyCursor
Fetch Next from cuMyCursor
into @.Var1

while @.@.fetch_status = 0

begin
execute myStoredProc
@.var1

Fetch next from cuMyCursor
into @.Var1

end
close cuMyCusor
deallocate cuMyCursor

1 record affected

It will only execute the sp once and is not looping the cursor. I've checked the source data from the select statement and there are 600+ records to loop through before fetch next will not return a record. I literally ran this on a SQL2000 db with no problem, when i copy and paste it to run it in the SQL2005 db it will not loop.

Any insight would be helpful.

Thanks,
j.r.

I don't see anything obvious in the code. Would it be possible for you to post a sample that demonstrates this issue?|||I'm in the process of researching the issue further but i have discovered it is not the cursor at all that is causing the issue. The select statement that is used in the cursor is being run on the SQL2005 db and crossing to a linked SQL2000 db, THIS is where the issue lies. If i run just the select statement from a SQL2005 db that we created only one record is returned. When i run the same select statement from the Adventureworks db, the expected 677 records return. I'm looking into the differences between the sys.databases settings on the Adventureworks and our db thinking that the answer will be somewhere in the settings. When i find more symptoms i'll close this thread and open a new one.

J.R.