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
Solved

How do I use Open Query Funcion

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

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

2.

I didn't understand this part
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

860 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