Linked Server not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server not working

Author

Topic

Kirun
Yak Posting Veteran

60 Posts

Posted - 2008-03-10 : 02:18:58

I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were fine but suddenly one day things stopped working when I have tested the connection for link server. It failed and gave me this error. What could be the possible error .

The source it is linked is a System DSN which is linking is a remote server running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it the driver that is the cause of error or what ???

"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale categories.". (.Net SqlClient Data Provider)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-10 : 22:07:27

Tried recreate linked server?

rajeshk
Starting Member

15 Posts

Posted - 2008-03-11 : 05:06:32

Which security context you are using for the connection?

Kirun
Yak Posting Veteran

60 Posts

Posted - 2008-03-11 : 05:15:56

I have tried to recreate the DSN and tried the Link Server with then new but to no use. Is there a way you can check wether the System DSN is configured correctly and is there test connection kind of thing for a DSN. May be a problmed DSN is cause of this .. i am mind boggled with this

rajeshk
Starting Member

15 Posts

Posted - 2008-03-11 : 05:40:50

Check the security context? Both the servers are on same domain or different?

durgapras
Starting Member

15 Posts

Posted - 2008-03-11 : 08:16:10

Create a UDL file and check whether your connection is fine.

Here are the Instructions.
http://msdn2.microsoft.com/en-us/library/e38h511e(VS.71).aspx

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-11 : 22:51:21

Did informix restart recently? May need reboot sql server if so.

Kirun
Yak Posting Veteran

60 Posts

Posted - 2008-03-14 : 06:49:50

i tried the UDL and it also failed. is there some thing wrong at my computer or its about any rights issue or what . this is just mind boggeling. Please help. it gave me error message box when i try to "enter the initial catalogue to use"
[INTERSOLV][ODBC Informix Driver][Informix]Unable to load locale categories
and then another msgbox

Login failed.Catalog information cannot be retrieved.

Kirun
Yak Posting Veteran

60 Posts

Posted - 2008-03-18 : 06:21:09

any luck --- its still mind boggling for me ....

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 19:54:59

Tried reboot sql server?

prateja
Starting Member

2 Posts

Posted - 2008-03-24 : 18:23:15

I recently came across same issue with Oracle database.
Upon investigation I found out If connecting user doesn't own the objects, login will fail for linked server. I created a one column table with one row within the user schema linked server connection was successfull.

amacopu
Starting Member

1 Post

Posted - 2008-10-31 : 10:03:44

Hello, I've the same problem connecting to Oracle database.
I test the connection using SQLplus and it is ok, but from sql server the linked server fails.
What do you refer with security context? I mean, do you refer to security conf in linked server (loca login sa, remote loging/pass) or the local account to run sql server?
Please give more details who did you fix the problem... :-)
best regards
Anali

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-31 : 10:43:50

1)Ping that server to make sure basic level configuration is set up right like allow Remote connections, TCP/IP etc.

Open Linked server properties and check for these things
2) on Security Tab correct security option is checked
3) On Server Options Tab make sure Data Access, RPC, RPC Out is true
4) Select providers under Linked server and make sure "ALLOW IN Process" is enabled and your linked server can be seen at the bottom of the provider options window

For Oracle servers if you are able to connect but queries are failing try to update Oracle statictics and retry that query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources