Link to home
Start Free TrialLog in
Avatar of aljubicic
aljubicic

asked on

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
Anthony
Avatar of Aycex
Aycex

You must create another variable and have the query insert the results into that variable.  Then either select it to look at it or just insert it where u need it to go.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aljubicic

ASKER

Perfect

Thanks for that, works great