?
Solved

Obtaining A value from Openquery??

Posted on 2003-03-04
3
Medium Priority
?
531 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:aljubicic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:Aycex
ID: 8064249
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 140 total points
ID: 8065248
See this question how this is done in general (using sp_executesql) :
http://www.experts-exchange.com/Databases/Q_20511493.html

CHeers
0
 

Author Comment

by:aljubicic
ID: 8068096
Perfect

Thanks for that, works great
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question