?
Solved

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

Posted on 2003-10-28
9
Medium Priority
?
4,945 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 1200 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 300 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

765 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