obtain the result of dynamic query with openrowset

im running a dynamic query with open rowset in it

pseudocode:

@.CMD=declare @. RETURN SELECT @.RETURN =SUM(X) FROM OPENROWSET(....) SELECT @.RETURN

EXEC @.CMD

This pseudocode dipplay the result of @.return

the problem:

capture @.return into @.myvalue outside the dynamic sql scope

something like

Select @.myvalue=exec(@.cmd)

I don't wanna run on ditributed transaction like this

insert mytable

exec(@.cmd)

thanks,

joey

Well, in 2005, you can direct EXEC to execute on a different server, but I think what you want to do is to use sp_executeSQL:

You will have to configure linked servers, (as well as possibly MSDTC for the servers,) but that would be the direction I would head. If you just want a a single row of values, you can use the following type of syntax:

declare @.name sysname

exec [.\sqlexpress].master.dbo.sp_executesql N'select @.name = @.@.servername ',N'@.name sysname output',@.name output

select @.name

I did it with insert into tableName... and it wanted MSDTC to be on.

|||

hi louis,

i'm using sql server 2000

calling sql2k5

regards,

joey

|||sp_executeSQL existed in 2000 in the same manner.|||

thanks

how can i reuse the output parameter

it is within a loop.

got this error

The variable name '@.RESULT' has already been declared. Variable names must be unique within a query batch or stored procedure.

|||

Just declare it once and set it to NULL before you execute the command...

If I am missing the point, can you post the code?

|||

got it.

i have a declare within @.cmd

thanks