Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HELP - Weird ADO problem Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 8:26 AM
Points: 4, Visits: 44
Hi, I hope someone can help me with a weird problem I'm having. Towards the end of last week a number of users started reporting problems with one of our legacy applications. The application
is written in VB6 connecting via ADO to a SQL 7 database. I managed to trace the error to a piece of inline SQL (shock!horror!) in a DLL that hasn't been changed since 2002, it occurs on the
adodb recordset open and generates the error "could not complete cursor operation because the table schema changed after the cursor was declared" although nothing has changed on the
database. It does not always happen, it depends on the contents of the strOrganisationIDs and the order of the numbers, although the same combination of numbers will generate the error every
time! Initially there were certain users who were not having the error, but they have all gradually 'caught up'! There were some windows updates rolled out about the same time the errors started occuring, but un-installing them makes no difference. Here is a snippet of the code:-

Dim rsRecordSet As ADODB.Recordset
Dim strOrganisationIDs As String

strOrganisationIDs = "(111974,21986,0)"

strSQL = "SELECT o.organisation_id, o.name, o.town, o.county," & _
" o.country, o.key_customer, i.industry_sector," & _
"(SELECT Name FROM SODA_User WHERE User_Id = o.User_ID) as Name1," & _
"(SELECT Name FROM SODA_User WHERE User_ID = o.Assignee) AS Name2," & _
"o.registered_on_web " & _
" FROM Organisation o, Industry_Sector i WHERE o.Sector_ID = i.Sector_ID" & _
" AND Organisation_Id IN " & strOrganisationIDs & _
"ORDER BY o.Search_Name"
rsRecordset.open strSQL

I have managed to find a way around the error - I removed the spaces in front of the o.country, don't know why they were there, and they shouldn't cause the error - in fact they are there on the
occasions it works, it seems to be the combination of spaces and certain numbers in the strOrganisationIDs . It sounds stupid and i've been pulling my hair out looking for a reason, as have
several colleagues. I don't want to have to roll out the fix as it will be quite messy and affects a lot of users, although i may have to - but what I really want to know is why it has happened. I hope
someone can shed some light on this, if any other info is required, let me know.
Post #449250
Posted Thursday, January 31, 2008 2:24 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
I cannot remember exactly. But here are some points you may check.
1. Open your connection or recordset in client side;
2. After your strSQL in the OPEN statement, add another switch. You will have options after hitting the key ",".

Hepefully, they help.
Post #450182
Posted Friday, February 1, 2008 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 8:26 AM
Points: 4, Visits: 44
Hi, thanks for your reply.
I have tried both your suggestions. 2nd one made no difference, but the first one worked in the same way removing the spaces did, although making more sense it still means a change to an existing, legacy DLL! What it doesn't explain is why this started occuring in the first place and the randomness of it!
The first example I mentioned now does not cause it to fail but the following does, although not on everyones PC:-

strOrganisationIDs = "(5611,5612,0)" - fails with the same error message - if I remove the aforementioned spaces (or change the cursor to client side) it works.

strOrganisationIDs = "(5612,5611,0)" - works (with spaces and server side cursor).

Confused?!!!

Post #450387
Posted Friday, February 1, 2008 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 8:26 AM
Points: 4, Visits: 44
OK, I've just noticed that the SQL string i posted got altered during the cut and paste. The 2nd line of the string should read:-

" o.country, o.key_customer, i.industry_sector," & _

That's 7 spaces between the quotes and o.country. "That is irrelevant" you may say! That is what I thought originally.
Someone, somewhere else suggested i should try "o." in front of the Organisation_Id on the ADD clause, and this caused it to work, but then I tried experimenting and took the "o." out and increased the 7 spaces to 9 (equivalent to "o.") and it works!! Tried 6 and 8 spaces (without the "o.") and they both work, change it back to 7 and it fails. Tried various numbers of spaces with the "o." and they all work. I've just about had enough of this, I've got work to do! Looks like I am going to have to put a fix to the .DLL in, just need to choose which one...............
Post #450586
Posted Friday, February 1, 2008 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 8:26 AM
Points: 4, Visits: 44
Ok, it's done it again, posting the reply has removed the spaces in the example - believe me, there are 7 spaces between the " and the o.country and it makes all the difference:)
Post #450588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse