June 17, 2014 at 12:28 pm
Hi guys,
I am keep getting this error message when I am trying to connect to one SQL Instance which is clustered from SSIS ( SQL Server Data tools)...
Exception from HRESULT: 0xC0202009
Error at Data Flow Task [OLE DB Source [548]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".".
Any workaround for this message if anyone has encountered before?
Thank you
June 17, 2014 at 1:31 pm
I'm sure plenty of people have encountered such a message, but you haven't stated what, if anything, you've checked to see if that item might be the problem. Some of the basics:
1.) Network path: Is there a path available to the server across the network? Have you tried to PING the server? Can you connect to the server from the machine SSIS is installed on using SSMS ?
2.) Spelling: Is the server name spelled correctly? Is there an instance name that is other than the default and perhaps was left out of the connection string? Have you tried using the IP address instead of the name?
3.) Is the server being connected to up and running? Does it allow remote connections? Are there any blocked ports on routers that might exist between the SSIS box and the destination server?
We'd need considerably more detail to suggest a better solution...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 17, 2014 at 1:52 pm
1.) Network path: Is there a path available to the server across the network? Have you tried to PING the server? Can you connect to the server from the machine SSIS is installed on using SSMS ?
I am able to connect to server from my local SSMS . I can ping Server
2.) Spelling: Is the server name spelled correctly? Is there an instance name that is other than the default and perhaps was left out of the connection string? Have you tried using the IP address instead of the name?
Was able to test connection from Connection Manager in SSIS . I am using SQL Command in Data flow task which throws error
3.) Is the server being connected to up and running? Does it allow remote connections? Are there any blocked ports on routers that might exist between the SSIS box and the destination server?
Server is up and running , allow remote connections . no blocking ports
June 17, 2014 at 1:54 pm
This is Error when I am running SQL Command on destination Server
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
June 17, 2014 at 1:57 pm
You indicated you can connect from your "local SSMS", but what I asked is if that could be done on the machine where SSIS is running. It might be in a different place on the network, or might have it's own problem of a network nature. Is this the only package running against this specific server from the machine in question? Also, just for grins and giggles, check the XML in the .dtsx file and see if that has anything weird in the connection string. I think you need to be sure you can connect to the server from the machine where SSIS is running.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 17, 2014 at 2:31 pm
I am running this command .
SELECT * INTO #JobInfo FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes','set fmtonly off exec msdb.dbo.sp_help_job')
this runs for other connections but somehow its throwing error for one Instance which is clustered.
December 10, 2014 at 12:27 pm
Did you ever resolve this? I have a similar situation. I'm on a VM where the firewall is turned on so I put in an exception for the SQL Browser (port udp 1434). I also tried putting the service accounts into my own login as I know I have Administrator rights with both Audit and Debug capability (in case that matters). I am able to create a linked server in 2008 to an instance level where a specific user's security can access a single database. Where it goes wrong is when I open the instance up and connect under the context of a specific user security. I should get the entire gamut but I am told I cannot connect (same as your error). I've also tried changing between the SQL Client and the OLEDB SQL Server and none of this seems to matter.
Jamie
December 16, 2014 at 4:20 pm
I know this probably is a long shot but is everything lined up in the Sql Server Configuration Manager ? (I have shared memory, tcp/ip, and named pipes all enabled).
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply