May 22, 2012 at 6:35 am
I separated a Database into two Databases Staging and Data Warehouse.
In doing so I moved started moving the schemas to a new Physical Server, Windows Server 2008 R2 64 bit with SQL Server 2008 R2.
The server is fast however I'm accessing an AS400 over a B2B.
When I create a Stored Procedure that references the Linked Server via an Open Query it takes several attempts and I get the following error.
OLE DB provider "MSDASQL" for linked server "LinkedServerDef" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "LinkedServerDef" returned message "[IBM][System i Access ODBC Driver]Communication link failure. comm rc=10060 - CWBCO1048 - A firewall blockage or time-out occurred trying to connect to the System i".
Msg 7303, Level 16, State 1, Procedure usp_merge_into_Unit_Detail_Name_Description, Line 3
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerDef".
I know that this is an IBM Error but is it Cause or Effect.
This did not happen on another Server, a Virtual Server with a lot less resources.
I started getting this error when running SSIS Packages. I was running tasks contained in Several Containers in Parallel. When I reduced the number of task that ran in parallel and tweaked it I was eventually able to avoid the error.
I'm not sure what could be causing this problem and what steps to minimize the probability of this from reoccurring?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 22, 2012 at 7:32 am
This is where I would start my investigation: CWBCO1048 - A firewall blockage or time-out occurred trying to connect to the System i.
May 22, 2012 at 8:02 am
Yes Lynn. I looked up that quite some time ago but sometimes you can get that error for various reasons.
I have had to increase the Timeouts, Record Blocking size etc.
I determined that it was the AS400 this time. It is hosted by someone else and they are not happy that we are going in a new direction. I checked with them yesterday and they we quiet.
Post closed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 7, 2012 at 10:48 am
Actually the problem went away for almost a week and started up again.
I determined that we are not being blocked by a firewall.
I was able to obtain a dedicated NAT from our DW server to the AS400 so that we could better isolate the problem because everyone else is using the same IP.
I test using the sys.sp_testlinkedserver and sometimes I get:
LINKED SERVER 'MyLinkedServer' IS CONNECTED.
DECLARE @ServerName SYSNAME
DECLARE @a INT
SET @ServerName = 'MyLinkedServer'
IF EXISTS(SELECT 1
FROM master.dbo.sysservers
WHERE srvname LIKE @ServerName)
BEGIN
EXEC @a = sys.sp_testlinkedserver
@servername = @ServerName
IF @a = 0
PRINT 'LINKED SERVER ''' + Isnull(@ServerName, '')
+ ''' IS CONNECTED.'
ELSE
PRINT 'LINKED SERVER ''' + Isnull(@ServerName, '')
+ ''' IS NOT CONNECTED!'
END
ELSE
BEGIN
PRINT 'LINKED SERVER ''' + Isnull(@ServerName, '')
+ ''' DOES NOT EXIST!'
END
GO
However I often get the following:
OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[IBM][System i Access ODBC Driver]Communication link failure. comm rc=10060 - CWBCO1048 - A firewall blockage or time-out occurred trying to connect to the System i".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MyLinkedServer".
When using an OLE DB AS400 Source I oftern get:
CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Error CWBCO48 -- Firewall Blockage
Any constructive thoughts or ideas would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 18, 2016 at 11:46 pm
I'm facing the same problem for 3 months. Have you solved it?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply