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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect
Thanks for that, works great
Thanks for that, works great