exec result of a stored procedure

Question

Post reply

exec result of a stored procedure

b38911

SSC Veteran

Points: 274
More actions
July 7, 2010 at 2:54 am

#223617

Hello.
I'm puzzled on how to get out from this....I have the follwing lines of code
set @execStmt = 'sp_stored_procedure'
set @ODS_cmd = 'execute OPENDATASOURCE(''SQLOLEDB'', ''' + @ODS_parms + ''').master..sp_executesql N''' + @execStmt + ''''
exec (@ODS_CMD)
In few words I'm running a stored procedure on a remote system. I'd like to store the result
of the exec command in a variable, table or something. I tried with the "insert into #errorlog exec (@ODS_cmd)"
but I get an error message saying the MSDTC is not available. Unfortunately I cannot activate MSDTC on
all systems (I have hundred of them), so I need to find another way....someone has some suggestion?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Have you tried with OPENROWSET?

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

SELECT * FROM OPENROWSET('SQLOLEDB', @ODS_parms, @execStmt)

-- Gianluca Sartori

b38911 SSC Veteran Points: 274 More actions · Answer 2

Thanks....

It looks like OPENROWSET does not accept parameters in the call: @ODS_parms and @execstmt are not accepted.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You could rewrite it this way:

DECLARE @sqlRowset nvarchar(4000)

DECLARE @results TABLE (comeColumn int)

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

set @sqlRowset = 'SELECT * FROM OPENROWSET(''SQLOLEDB'','''+ @ODS_parms + ''',''' + @execStmt + ''')'

INSERT @results EXEC(@sqlRowset)

-- Gianluca Sartori

b38911 SSC Veteran Points: 274 More actions · Answer 4

Thanks again...

Unfortunately with the insert I fall back with the problem of the missing MSDTC...

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Just a guess, if you have to run this script to connect to many servers, but always from the same server, you could tweak your DTC settings only for the server that runs the script.

Does this article contain something useful for you? http://support.microsoft.com/?scid=kb;en-us;817064&x=5&y=11

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

You could also try using SELECT/INTO instead of INSERT/EXEC:

DECLARE @sqlRowset nvarchar(4000)

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

set @sqlRowset = 'SELECT * INTO #results FROM OPENROWSET(''SQLOLEDB'','''+ @ODS_parms + ''',''' + @execStmt + ''')'

EXEC(@sqlRowset)

-- Gianluca Sartori

b38911 SSC Veteran Points: 274 More actions · Answer 7

Thanks a lot for your help.

The problem is that the MSDTC is required on the server from which I'm starting the job, but also on the target system, so I can not do it.

Unfortunately also the last method gave me the same error.