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 + ''''')'
How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.