November 13, 2012 at 6:35 am
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
November 13, 2012 at 7:01 am
--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
November 13, 2012 at 9:00 am
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 ','.
November 13, 2012 at 9:41 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply