Linked Server not working

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 server which is linked is a remote server running
Informix Database.
"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)Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>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 server which is linked is a remote server
> running Informix Database.
>
>
> "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)
>
>|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>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 server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "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)
>>
>>
>|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>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 server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "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)
>>
>>
>>
>|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .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 ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>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 server which is linked is
>> a remote server running Informix Database.
>>
>>
>> "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)
>>
>>
>>
>>
>|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .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 ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>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 server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "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)
>>
>>
>>
>>
>>
>|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> Thanks for a prompt reply Uri.
>>
>> I also wanted to add the the linkied server is linked to a source .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 ?
>>
>> And the error is
>> Msg 102, Level 15, State 1, Line 9
>> Incorrect syntax near 'IF@.statusval'.
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>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 server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "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)
>>
>>
>>
>>
>>
>>
>