SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HELP - Weird ADO problem


HELP - Weird ADO problem

Author
Message
mgallagher
mgallagher
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6997 Visits: 1314
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.
mgallagher
mgallagher
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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?!!!
mgallagher
mgallagher
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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...............
mgallagher
mgallagher
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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 differenceSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search