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"
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.