Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.