linked server on SQL 2005 to database on AS400
-
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider. when we are using the linked server, we are getting the following error: OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD". When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before, The script of the linked server is : EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=192.1.1.50;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
-
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider. when we are using the linked server, we are getting the following error: OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD". When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before, The script of the linked server is : EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=192.1.1.50;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
What have you tried so far? A quick search on Google brings up a number of possible explanations and solutions for this. Which of them have you tried?
-
What have you tried so far? A quick search on Google brings up a number of possible explanations and solutions for this. Which of them have you tried?
What iam sure of is that it is not an issue from SQL server. i have tried to ping the AS400 server and the reply is <1 ms then i wrote a query to check the linked server and it is giving an ok reply b4 it stops suddenly. i have seen soo many possible issues and read articles abt the same error Msg 7303 and i will be trying to update the server with hotfixes. Are there any fixes that came to your mind?