Concurrenty Issue from 2000 to 2005

[jimoo]

I submitted a post before that had not replies. I believe I was to vauge.

Problem. Just upgraded from SQL 2000 to 2005 and now endorsements do not print licenses when printing certificates.

declare @P1 int set @P1=180150004 declare @P2 int set @P2=8 declare @P3 int set @P3=2 exec sp_cursoropen @cursor = @P1 output, @stmt = 'SELECT DISTINCT item_text, '' '' FROM lcps.t_license,lcps.t_assigned_authorities,lcps.c_s_itemval WHERE lcps.T_LICENSE.license_id= 123456 AND lcps.t_license.license_id=lcps.t_assigned_authorities.license_id AND lcps.t_assigned_authorities.authority_code=lcps.c_s_itemval.item_str_code AND lcps.c_s_itemval.language=''E'' AND lcps.c_s_itemval.profession_id in (0,1) AND item_type=''specialty code'' ORDER BY item_text, '' ''', @scrollopt = @P2 output, @ccopt = 4, @rows = @P3 output select @P1, @P2, @P3

Results. The data is sent to a word template and printed, but the endorsements (authorities) are not printed on the license in 2005. Works fine in 2000.

When I run the command interactively in Management Studio (obtained by a trace) in 2005 the following messages shows the following message: Again - no error message in 2000.

Msg 16966, Level 16, State 1, Procedure sp_cursoropen, Line 1
sp_cursoropen: Specified concurrency control option 4 (OPTIMISTIC) is incompatible with static or fast forward only cursors. Only read-only is compatible with static or fast forward only cursors.

Any ideas on how to solve this?

Jim

 

[gmmastros]

Just out of curiosity.... does your code work if you set the compatibility level to 80 (SQL 2000)?

Open SQL Server Management Studio
Right click your database
Click Properties
Click Options

I have no idea if this will work for you, but it's certainly worth a try (at least as a troubleshooting step).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

May be it's time to re-write this SP and get rid of cursors altogether.


PluralSight Learning Library

 

[jimoo]

The problem is it is a COTS, WIN32, Power Builder application that we do not have the source code for.

The app is in the process of being rewritten, but for now we must deal with it for another 6 months and it is crticial.

The DBA assures me the compatabiltiy mode has been lowered to 2000, but it still fails. The command I copied in came from the the 2000, and I used in management studio on both 2000 and 2005, but I have since done a trace on 2005 and posted that command. The results appear to be different - appears to bark at the DISTINCT clause. I would not have expcected this error if compatabilty mode was set to 2000....


I will post more after a few more tests...



Jim

 

[Crowley16]

what other statements are captures, specifically before the cursor open...

reading the error message, it seems the cursor is using a invalid read mode, this is defined in the declaration of the cursor...

Is this a sql statement fired directly from the application or as part of a stored procedure? If the latter, then definitely re-write the sproc...

--------------------
Procrastinate Now!

 

[jimoo]

The problem is resolved. I do not have all the answers. The app reads a series of INI files that have cryptic character strings that it translates into SQL code. We were able to modify the codes and got it to work. I will trace the result to see what it built out of curiosity, but for now we appear to be in good shape.

What it basically came down to is it built the following query that works in 2000, but no 2005 even with compatability mode to 2000 on the database.

SELECT DISTINCT item_text, ' ' 
 FROM lcps.t_license,lcps.t_assigned_authorities,
      lcps.c_s_itemval 
 WHERE lcps.T_LICENSE.license_id= 123456
 AND lcps.t_license.license_id=lcps.t_assigned_authorities.license_id 
 AND lcps.t_assigned_authorities.authority_code=lcps.c_s_itemval.item_str_code
 AND lcps.c_s_itemval.language='E' 
 AND lcps.c_s_itemval.profession_id in (0,1) 
 AND item_type='specialty code' 
 ORDER BY item_text, ' '

If you are curious, I suspect you could duplcate this query easily using another table.

This app is going to be unplugged in December - thank goodness!



Jim

 

[jimoo]

Just to follow-up. This code emulates the problem.

It worked on a 2000 database but failed on a 2005 database. The query is not querying a real table so it is a nice test if you have a database in 2000 and one in 2005 and would like to test it out.



use myDatabase
select DISTINCT 'a' as a, ' ' order by a, ' '

Jim