Solved

How do I use Open Query Funcion

Posted on 2006-07-06
7
267 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
[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
  • 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 125 total points
ID: 17074500
1.

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

2.

I didn't understand this part
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

624 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