Solved

How do I use Open Query Funcion

Posted on 2006-07-06
7
266 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

710 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