Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Question Unanswered: 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

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you would need to fill in the data type for nar_num but give this a try:

    Code:
    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •