03-04-03, 08:54 #1Registered User
- Join Date
- Jan 2003
Unanswered: Obtaining a value from Openquery??
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.
03-04-03, 09:15 #2Registered User
- Join Date
- Feb 2002
- Houston, TX
you 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 endPaul Young
(Knowledge is power! Get some!)