I've an issue while transferring the data from ServerA to ServerB. I've made the ServerB as "Linked Server" to Server A. Pre requisite like:
1) SQL Account is been created on ServerB.
2) Timeout settings for remote connections is set to "No Timeout".
When I execute the below script using Query Window, It executes successfully:
Insert into ServerB.DBName.dbo.TableName1
Select * from dbo.TableName1
But when I execute the same step by creating a SQL Job, it fails with the below error message:
Executed as user: DomainName\UserName. Named Pipes Provider: Could not open a connection to SQL Server . [SQLSTATE 42000] (Error 1450) OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". [SQLSTATE 01000] (Error 7412). The step failed.
Could you please help me in fixing the above error message and I can transfer the SQL Data between 2 Servers.
Sridhar D Naik
It seems that your Server2 is not reachable from Server1.
If is unreachable and remote connection is not enable, you need to follow steps in mentioned this blog post from Pinal.
You also need to check whether test connection from linked server is succeeding or not. (To check from ssms, Select the linked server - > Right click -> Test Connection).
One thing i am not sure if you are using the sql account to connect using the linked server, why it is showing the account as window account in error message. Can you please check you are using the sql account to connect to the linked server not the windows account.
Yes I am logging to ServerA using Windows Authentication and Can execute the statement from a query window to transfer the data from ServerA to ServerB(Linked Server).
But when I try to execute the same steps by creating a Job, it throws the above error message.
Sridhar D Naik
First, please map the login “DomainName\UserName” (in the error message) with a security account on a remote server who has permission to access dbo.TableName1 on the linked server. Additionally, you can refer to the steps on the following document.
SQL Server Agent job fails when the job uses a linked server and the job owner is not a system administrator
TechNet Community Support
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Wednesday, July 03, 2013 9:59 AM