Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run Stored Procedure on remote server Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 6:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
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
Posted Tuesday, November 13, 2012 7:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,917, Visits: 2,532
--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
Posted Tuesday, November 13, 2012 9:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
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
Posted Tuesday, November 13, 2012 9:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
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 »

Add to briefcase

Permissions Expand / Collapse