Solved

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

Posted on 2003-10-28
9
4,499 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:leoncornel
9 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 9633158
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
 

Author Comment

by:leoncornel
ID: 9633274
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9633392
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:leoncornel
ID: 9633640
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
 
LVL 6

Expert Comment

by:lausz
ID: 9633641
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 400 total points
ID: 9633753
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
 
LVL 2

Assisted Solution

by:sedmans
sedmans earned 100 total points
ID: 9634196
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
 

Author Comment

by:leoncornel
ID: 9640165
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
 

Author Comment

by:leoncornel
ID: 9641623
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 26
SQL SELECT query help 7 40
sql select record as one long string 21 24
SQL view 2 27
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question