Unable to move forward with a openquery function because of the server connection

Transact-SQL
,
1

I am using an openquery function and I have been stuck with this error message :

Named Pipes Provider: Could not open a connection to SQL Server [53]

OLE DB provider "SQLNCLI11" for linked server "MSSQLSERVER01" returned message "Login timeout expired".

OLE DB provider "SQLNCLI11" for linked server "MSSQLSERVER01" 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.".

Here is the code:

    Select* Into #PTBV  

    From Openquery(MSSQLSERVER01,'exec tempdb.dbo.dynamicPTBV') 

    GO

2

@Robinhohocepied,
Can you ping MSSQLSERVER01 from the server you're running the query from? Does it work if you run it on the server?

3

I would run the query and time how long it takes. Maybe it really is taking long enough that it exceeded the "Login timeout" setting.

4

It takes 20 seconds till it sends me the "login timeout"

5

The time I meant was to time how long it takes to run the underlying query without using the openquery

Then check if THAT time exceeds the limit for the OpenQuery connection to that server - which looks like it is set to 20 seconds.

6

I will thanks !