Run Stored Procedure on remote server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Run Stored Procedure on remote server

Run Stored Procedure on remote server

Rate Topic

Display Mode

Topic Options

Author

Message

skb 44459

Posted Tuesday, November 13, 2012 6:35 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 50, Visits: 108

Would like to Run Stored Procedure on remote server and store the results in temp table.
I am hoping Not to use Linked Server Feature.

CREATE TABLE #ProdItem (
FileName char(100),
DwgName char(30),
FilePath char(400) )

1. Make a remote connection to the server ( I dont want to use the linked server if possible )
Server Name : ServerDwg

2. Run a Stored Procedure on Remote Server (ServerDwg) and store the output of the stored Procedure
in the temp table #ProdItem

Post #1384056

sjimmo

Posted Tuesday, November 13, 2012 7:01 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:15 AM
Points: 2,865, Visits: 2,467

--Execute a SP on a remote machine w/o using linked server

insert into #temptable
exec('exec OPENDATASOURCE(''sqloledb'',''Data Source=servername; User ID=loginid;Password=password'').databasename.schema.storedprocedurename''' +@parameter1+''','+@parameter2+'')

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Post #1384070

skb 44459

Posted Tuesday, November 13, 2012 9:00 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 50, Visits: 108

exec('exec OPENDATASOURCE(''sqloledb'',''Data Source=sqlsrv1; User ID=user1;Password=Pwd1'').Orders.dbo.testreport''' + @parameter1 + ''','+@parameter2+'' )

Above statement gives me error
Incorrect syntax near ','.

Post #1384137

skb 44459

Posted Tuesday, November 13, 2012 9:41 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 50, Visits: 108

SELECT top 10 * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=Server1;User ID=user1;Password=password').orders.dbo.folder
this statement returned error

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" 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.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].

if i run the same statement against other database it works fine.

Post #1384169

« Prev Topic | Next Topic »

Permissions