Link server error The provider did not give any information about the error

Hi!

We have a test server (win 2003) in which MSSQL server is installed.In my system (win XP)I installed MYSQL server ,MY sql odbc driver.Now all I want is to create a linked server on MSSQL server so that data on Both server can be in sync.When even a new record in inserted in MYSQL it should be replicated to MSSQL srvr tabl immeditely

I tried to create a linked server but every time I got this error

Error 7399:OLEDB PROVIDER'MSDASQL' reported an error.The provider did not give any information about the error.OLE DB error trace[OLE/DB provider 'MSDASQL' IDBInitilize ::Initialize returned 0x80004005: The provider did not give any information about the error.]

Here is what I have done
sp_addlinkedserver @.server = 'localhost', @.srvproduct = 'MYSQL', @.provider= 'MSDASQL', @.datasrc ='localhost',@.location ='ipadd of my sqlserver',@.provstr ='DriverDriver={MySQL ODBC 3.51 driver};Server=ip add of mysql server;Port=3306;Option=131072;Stmt=;Database=testDB;Uid=root;Pwd=1234asdf' ,@.catalog = 'testDB'
Pls suggest what to do.I am trying for past 2 week but nothing came fruitful..sigh!!

Hi,

1. sp_addlinkedserver should not be returning this error. This error should occur when you try to access the linked server.

2. Can you check the permissions on the odbc driver files and make sure that they have the right permissions?

3. Can it be that the connection information that you specified in the connection string is incorrect?

4. You can also try to create a system DSN ( test the connection ) and use it in the linked server definition using the @.datasource parameter of sp_addlinkedserver( @.datasrc = N'YOU_DATASOURCE_NAME' ).

Hope this helps.

Thanks,
Rakesh