Solved

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

Posted on 2003-10-28
9
4,371 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 SP4 29 35
t-sql month question 8 43
Data to display differently-SQL Server 4 21
Slow SQL query 12 29
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now