question

amira1344 avatar image
amira1344 asked

SQL ODBC Connectivity Problem

Hi Guys, i have a windows 2000 server (lets name it X) with SQL 2000 ODBC driver which am experiencing problem accessing SQL server database on another server (lets take it as Y). The test connection is successfull when i try it from my desktop to Y server but when i try it from that X to Y server no luck. What i noticed was the the SQL ODBC driver version differences. The SQL ODBC driver version on my PC is 2000.85.1117.00 where the version on X server is 3.70.11.46. Anybody has any idea how to upgrade this driver? By the way, is this some thing to do with MDAC? If i install MDAC 2.8 (Microsoft Data Access Component) will resolve my problem. X= Win 2k SP4 Y= SQL server 2000 Thanks for your input.
sqlodbc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
CAn you try this process described here [ http://jonathanallen69.blogspot.com/2010/04/testing-database-connection-or-who-hell.html][1] from the machine you are having problems with and let us know the results please? It could be MDAC and to my knowledge there is no reason not to have the most recent on all working computers so its worth doing anyway I guess. [1]: http://jonathanallen69.blogspot.com/2010/04/testing-database-connection-or-who-hell.html
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
What is very difficult to understand is the reason to use ODBC. On the top of the fact that ODBC is slow, it is also evil because it stores credentials in registry in clear text. Also, if you accidentally create a user DSN, it writes info into HKCU hive so it is not accessible to others. System DSN writes to HKLM, but depending on the group policy, the attempt to write might fail. Anyway you look at it, it is just plain difficult to find any justification to use it, specifically when connecting to SQL Server.
2 Likes 2 ·
amira1344 avatar image amira1344 commented ·
Thanks for your reply. I did try this which worked from my PC (win XP) but on my win 2k server. The result is the same as if I try to add DSN in ODBC and this is what I'm getting: Test connection failed because of an error in initializing provider. [Microsoft][ODBC Driver Manager] Data source name not fount and no default driver specified.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Oleg - very true. took the question at face value and simply tried to resolve issue rather than see if its necessary. Thanks for posing question ...
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Fatherjack @amira1344 Actually, it does look like the **server X** in question has very outdated version of MDAC installed. I believe that all MDAC 2.8 flavours should result in something like 2000.8X.YYYY.ZZ for SQL Server ODBC driver, the higher the X the better. The driver is 32 bit, the name of the binary is SQLSRV32.dll regardless whether OS is 32 or 64 bit. This means that installing MDAC 2.8 should resolve the issue.
0 Likes 0 ·
amira1344 avatar image
amira1344 answered
Thank you both. If fact, I am provideing application people to talk with SQL server throuth their program. I don't know any otherway for this kind of access and appreciate your suggestion. @Fatherjack, upgrading to mdac 2.8 was my initial though but don't the outcome. Hopefuly, will not toast me. Thanks again
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.