CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10590

SQL Server 2008 connecting to instance is not working

I am trying to deploy my database on a customer server

I tried this:

select @@servername

and I got this

`INCONCERTSERVER`

Then I tried to create this stored procedure:

CREATE PROCEDURE [dbo].[getAgentStatues]
AS
BEGIN
    SET NOCOUNT ON
    EXECUTE sp_configure 'Show Advanced Options', 1
    RECONFIGURE
    EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE

    SELECT UserName, LoggedState, InteractionId, InteractionType    --<-- The columns required.
    FROM 
    OPENROWSET('SQLNCLI'
            ,'Server=INCONCERTSERVER;Trusted_Connection=yes;database=MMProDat'
            ,'EXECUTE dbo.[SupGetAgentsWithInteractions]')
END

I got this error:

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 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Could you help me please?

I am already using a windows admistrator accound and an administrator on the database

I am working remotely using remote desk top

Upvotes: 0

Views: 3328

Answers (1)

M.Ali
M.Ali

Reputation: 69594

Try something like this....

CREATE PROCEDURE [dbo].[getAgentStatues]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserName, LoggedState, InteractionId, InteractionType  
    FROM 
    OPENROWSET('SQLNCLI'
               ,'Server=INCONCERTSERVER;Trusted_Connection=yes;database=MMProDat'
               ,'SET FMTONLY OFF;SET NOCOUNT ON;EXECUTE dbo.[SupGetAgentsWithInteractions]')
END

Execute Proc

EXECUTE sp_configure 'Show Advanced Options', 1
GO

RECONFIGURE
GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1
GO

RECONFIGURE
GO

EXECUTE [dbo].[getAgentStatues]
GO

Upvotes: 1

Related Questions