We help IT Professionals succeed at work.

SQL 2000 - Access 2000 - SQL-DMO

ImraneA
ImraneA asked
on
Hi there

Problem - Erroe message "Runtime error '-2147165949 (8004d903) [Microsoft][ODBC SQL Driver]You must
upgrade your SQL Enterprise Manager and SQL-DMO(SQLOLE) to SQL Server 2000(SQLDMO) to connect to this
server."

Installed
Server - BackOffice 2000 (SQL 2000 Standard)
Client - Win 98 2nd edition

What am I doing wrong ?
Thanks

Code:

Sub call_list_tables()
Dim srvrname As String
Dim dbsname As String

'name of server and database
'call table lister
srvrname = "IMRANE"
dbsname = "Northwind"
Call list_tables(srvrname, dbsname)
End Sub

Sub list_tables(srvrname As String, dbsname As String)
Dim srv1 As New SQLDMO.SQLServer
Dim tab1 As New SQLDMO.Table

'connect to server

srv1.Connect srvrname, "sa", ""

'list table name and column count for user-defined tables
Debug.Print "User-defined tables with their column counts for the " & _
dbsname & " databases are : "
For Each tab1 In srv1.Databases(dbsname).Tables
   If tab1.TypeOf = SQLDMOObj_UserTable Then
       Debug.Print tab1.Name & String(25 - Len(tab1.Name), "") & _
       tab1.Columns.Count
   End If
Next
End Sub
Comment
Watch Question

CERTIFIED EXPERT

Commented:
install on your client MDAK 2.6 SP1. Download from
http://www.microsoft.com/data
This will alleviate the issue.

Cheers

Author

Commented:
Tried that - no success.  (I installed it, but I did nothing else.)
Ideas:
1.I have not installed service pack 1 for sql 2000 - could that make a difference ?
CERTIFIED EXPERT

Commented:
I am not sure, do you still have this same error message?

"
Problem - Erroe message "Runtime error '-2147165949 (8004d903) [Microsoft][ODBC SQL Driver]You must

upgrade your SQL Enterprise Manager and SQL-DMO(SQLOLE) to SQL Server 2000(SQLDMO) to connect to this
"
and did you have this error message on what side of connection - on server side or client side.
please clarify.

Also, where did you install the MDAK 2.6 SP1 and have you red the even vewer and installation log, whether the installation was successfull or not. If it is successfull your SQLDMO object as well as ODBC drivers on the client machine has been upgraded to the version fully compatible with SQL Server 2000. Either something has forced an old version of ODBC driver copy on the client machine, or network libraries compatibility issues. Verify the DLL(s) by looking up MDAK 2.6 component version list. Should be available from the same web site or Microsoft Knowledge Base hrrp://support.microsoft.com. To troubleshoot / correct network compatibility issues run cliconfg.exe, configure a distinct alias for SQL Server on named pipes and TCP/IP and try both. For TCP/IP you need also verify the port on which SQL Server is listening, to check on that one you need to look up SQL Server management log. One of the records will say, SQL Server listening on port xxxx, default port is 1433, but make sure that it is. By the same tocken check if SQL Server accepts SQL authentication. It can be found on the EM "security" tab.

If all failes, re -install MDAK 2.6 SP1 by downloading the installation package on the client machine locally, disconnect client machine from network physically, run the instllation, re - boot the client machine, re - connect machine to the physicall network. Try to connect via ODBC to the server and make sure you run "test connection"

Thanks

Author

Commented:
OK - do some troubleshooting.  Come back today or tomorrow.  Thanks.

Author

Commented:
Client side + same message on client side.
CERTIFIED EXPERT

Commented:
Could you please check where the DLL(s) for the client are loaded from. If my memory is correct the first one gets loaded from local directory. So, check the directory where the client lives. If you find any DLL that can be found in a system path - system path can be found by typing "COMMAND" into run dialog box under Start menu and issuing command "PATH" on the command line, temporaryli remove, or re - name those. Also compare their versions. There is also possibility that the old SQL DMO object code was statically compiled into the client - re - compile the client on a machine that has prevously undegone the upgrade to MDAC 2.6 SP1.

Author

Commented:
OK

Author

Commented:
Still troubleshooting, hopefully complete sometime this week.  Come back start next week or earlier.  

 

Author

Commented:
Sorry for delay in responding - gone threw half points you have mentioned - still obtain same error message.  Hoping to finish it today or tomorrow.
CERTIFIED EXPERT
Commented:
I forgot to mention that when re - compiling the client on a build station with correct version of MDAC installed, one need to check on the references menu located under Project pull down - this menu actually decides what version of SQL DMO is linked at run time. If client re - compiled without seting up the references to the newer version of AciveX Data Objects there is a chance that old  error will contintue.

Author

Commented:
I am having difficulty trouble shooting this problem - working on other stuff.  Time being, to be fair,
you have provided good pointers.  So grade as normal.

Later date, may have further queries, resubmit this again.

Thanks.
CERTIFIED EXPERT

Commented:
Thanks,

also, think that this question is confirming the pointers.

Best in your work.

http://devx.experts-exchange.com/jsp/qShow.jsp?qid=20208733