Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I use Open Query Funcion

Posted on 2006-07-06
7
Medium Priority
?
271 Views
Last Modified: 2012-06-22
Experts,

How do I use the open query function.  An example would be great connecting to the existing SQL Server 2000.
0
Comment
Question by:jjrr007
  • 5
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17055732
1.Find your SQL server name , use the following

select @@SERVERNAME -- let the result be 'servername'


2. run the following to enable remote data access to your sql server

exec sp_serveroption 'servername','Data Access', 'True'  -- replace servername with the above value

3.

declare @zip int
select @zip = (SELECT top 1 Zip from OPENQUERY([servername],'SELECT * FROM pubs.dbo.authors ')) -- replace servername with the above value
print @zip
0
 
LVL 1

Author Comment

by:jjrr007
ID: 17074430
I tried this, a couplet things.  

1. There is more than one value being returned.

2. Also, I am using this Open query in the context of a function that has this syntax when getting these two values:

BEGIN
DECLARE field_cursor CURSOR FOR SELECT
Column1, Column2  from
Table
END

I have increased the points.  Thanks once again.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17074500
1.

SELECT * from OPENQUERY(servername,'SELECT * FROM pubs.dbo.authors ')

2.

I didn't understand this part
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:jjrr007
ID: 17074840
1. The following will only return one value...

declare @zip int
select @zip =SELECT * from OPENQUERY(servername,'SELECT * FROM pubs.dbo.authors ')
print @zip

- There is an equal sign that signifies having one value returned. How do I modify this?


2.  I am using an open query function to "paramaterize" the column names in a user defined function. Will the open query work for this purpose?  The syntax for the user define function that I will be using the open query function is:

Alter FunctionName
Returns
AS
Declare Variables Part...
Begin
DECLARE field_cursor CURSOR FOR
SELECT Column1, Column2  from Table
END
Then the function...

- I want to use this open query function in the part that says for "SELECT Column1, Column2  from Table" and parameterize the column names.  Will the open query function work for this- what syntax...  
0
 
LVL 1

Author Comment

by:jjrr007
ID: 17075061
I got the first question resolved.  Please answer the second question.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 17075200
Basically, I want to create a query on the fly.  Please let me know of a basic way to do this.  I will run with basic answer and award points.  Thanks a lot.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 17076246
Thanks aneeshattingal!!

After research, I found out that open query function will not take parameter values.  

I realize that this is now a different question then using open query (maybe more difficult too).  I have made a new question for this at

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21914264.html

Basically,  I now need to make a query on the fly.  
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

963 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