January 30, 2008 at 3:49 am
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.
January 31, 2008 at 2:24 pm
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.
February 1, 2008 at 4:16 am
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?!!!
February 1, 2008 at 9:26 am
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...............
February 1, 2008 at 9:28 am
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:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply