mflancour


There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.

help if you can.



Re: insert into exec distributedprocname

Chris Howarth


You first need to define a Linked Server to connect to the remote SQL Server instance, you can then use either four part naming or OPENQUERY to execute the stored procedure.

See here for more info on Linked Servers:

http://msdn2.microsoft.com/en-us/library/ms188279.aspx

Chris







Re: insert into exec distributedprocname

mflancour

Thanks for the reply, but the issue i'm running into is not from the linked server. Weather i use a linked server or dynamically link to the server via openrowset, I still get the error.

I found this snipet which sounds very close to what I need, but so far i have not been able to get it to fully work...(it works when I pass only the proc with no parameters, but if i supply parameters it boms..problem is i need those parameters because they determine what columns will show.) One thing I don't understand, is that the stored proc in question DOES NOT use temp tables nor does it have any inserts or select into's. it simply creates a select sql statement based on the paramters passed then executes it to return a record set.

Code Block

-- To do SELECT...INTO from SPs results do: SELECT * INTO SomeTbl FROM OPENQUERY(
localsrvr , '{call sp_who}')

-- If the SP uses temporary tables, -- you have to do something like the following
b'cos -- the above call will fail SELECT * INTO SomeTbl FROM OPENQUERY( localsrvr ,
'SET FMTONLY OFF {call sp_who}')

But you need to understand that this will result in the SP being executed twice
once when OPENQUERY tries to determine the metadata for the result set & again
when it actually executes the call.








Re: insert into exec distributedprocname

Manivannan.D.Sekaran

Can you try the following code. Note the connection string..,

Select * into #TT from

openrowset

(

'SQLOLEDB.1',

'SERVER=.\msss2000;UID=sa;PWD=sqladmin',

'Exec NEWMVP..Fetchdata ''name,id,xtype'''

)






Re: insert into exec distributedprocname

Chris Howarth

It would help greatly if you posted the error message. You made no mention of an error in your original post so I assumed that you were starting 'from scratch'.

Chris






Re: insert into exec distributedprocname

mflancour

The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.



Re: insert into exec distributedprocname

mflancour

I have tried openrowset...but not with those values...is there a reason why these should be used as apposed to:

openrowset('sqloledb','server=server.domain.com;trusted_connection=yes;',
'exec [dbname].dbo.proc @p1=''asdf'', @p2=23')




Re: insert into exec distributedprocname

mflancour

I did find a way to make it work. I'm a bit worried about possible issues arrizing with the method though. Does anyone know how doeing something like the following would effect a very high transaction database I read something about it locking tables while the query executes is that true Are there any other drawbacks to it

First I created a new proc that really only called the original proc with the params I wanted.

Next I used the following queries to:

1. gather the columns returned
SELECT * INTO #test3 FROM OPENQUERY( servername,
'SET FMTONLY on {call [databasename].dbo.procname}')

2. insert these values into the temp table created
insert #test3
exec servername.[databasename].dbo.procname