Error 207 " Invalid column name suid" when application makes a ODBC Connnection to MSSQL2000

Hi

We have a VB Application, that, on some computers, when we try to make a connection to MSSQL2000, it comes up with Error 207: Invalid column name suid.
Now, I'm aware that the suid column is not being used in MSSQL2000 any more, so I'm thinking that when the app try to make the odbc connection, it pics up some old (Maybe SQL7 or SQL6.5) driver, that's looking for the suid.
I've UnInstalled and ReInstalled MDAC 2.7
I've UnInstalled and ReInstalled the MSSQL2000 ClientConnectivity, with SP3.
Where do I start troubleshooting this error?

Many Thanks.
Leon
leoncornelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
If the ODBC data source is defined locally on the client machine,
I'm afraid you'll have to change the Definition of the ODBC source on each and every client machine.

run ODBCad32  (ODBC ADministrator IHM)
and go to the USER DSN Tab

- see if there's a data source for your MS SQL DB
- delete it (after copying)
- re-create it with the same name but using the new driver that comes with the MDAC version you recently installed

Cheers

Hilaire


0
leoncornelAuthor Commented:
Hilaire

Many thanks for your quick response.
I've just put this to the test, and it works fine.
However, when I try and make an OLE DB connection, it falls over with the same error 207: invalid suid column.
Any Ideas.

Many Thanks.
Leon
0
HilaireCommented:
Is it still for the same vb App ?

Maybe the connectString is hard-coded ?
Can you open the vb Project and see how the connectString is defined ?
Programmers sometimes use a special object in VB that defines the properties of a connexion. (I never use it because you have to recompile the project each time the connectstring changes)
I don't remember how they call it.
If it is used, you should see it in the project.

It has a different icon from usual modules, classes and forms.
If you have one, you should be able to right-clic on it and edit properties, to change the DB client layer from OLE DB SQL 7 to OLE DB SQL 2000.

If you don't have such a file in the project, you 'll have to find where in the code the connectstring is defined.
A search for "database" in the code should be enough to find it

Tell me if I can help more

Hilaire

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

leoncornelAuthor Commented:
Hilaire

Yet again, thanks for the response.

Yes, it is the same vb App.
And, what makes it so difficult, it's an off-the-shelf package, and we do not have the vb project source.

Cheers.
Leon
0
lauszCommented:
The column suid doesn't exists any more in ms sql 2000.

where
  suid     - server user ID


read this link

http://www.databasejournal.com/features/mssql/print.php/1438491
0
HilaireCommented:
If the OLEDB properties are hard-coded,
it will be very hard to solve.

You could also try to have a glance in the registry.
If you find something in, say,
HKEY\Local Machine\Software\YourAPPName ...
just let me know

Maybe you could also look for a YourAppName.ini file ?
in %windir%\system32 o rin the app's work directory

The setup.exe should come with a .cab file. You should be able to open it with a winzip/power archiver - like tool and see if there's a .reg or .ini file in it to get a clue

Cheers

Hilaire
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sedmansCommented:
I get this message when I am using ERWin version 3.5.2

The reason the error message appears is that it runs a query to determine the user name of the currently logged in user and the link between the syslogins and sysusers table has changed in SQL 2000.

If your application is doing the same thing then there is no ODBC or OLEDB changes you can make as the query is part of the application.  As you mention that it is an off the shelf application you will probably need to upgrade to the latest version of the application.
0
leoncornelAuthor Commented:
Hi Hilaire, sedmands

This is what happens:
1) I open the VB App
2) I can then Open a Database:
    SQL Server - OLEDB Connection (this is the one gives the error)
    ODBC DSN - This one works.
    MicroSoft Access - This one works.
3) When I select the "SQL Server - OLEDB Connection" option, the VB app calls the
    "SQL Server Login" Dialog Box.
     I put in the ServerName, Login, Password, and when I click on the Options button
     to select the SQL Database, I get the 207 error.
     Now, this "SQL Server Login" Dialog Box, is the VB app maybe calling the wrong  
     version of some or other VB Common Component (that still references the suid
     column)?

Cheers
0
leoncornelAuthor Commented:
Hi Hilaire, sedmands

I think I found something:

I downloaded the Microsoft MDAC Utility: Component Checker, and diagnosed the MDAC installation the the client. I checked all the .dll and stumbled across the following one: "C:\Program Files\Common Files\System\Ole DB\SQLOLEDB.dll"
This .dll is actually used when a OLEDB SQL Server Login Dialog box are displayed. I replaced it with a MDAC2.7 version. The App worked.

Thanks for your help.
Cheers.
Leon
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.