Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

How do I use Open Query Funcion

Experts,

How do I use the open query function.  An example would be great connecting to the existing SQL Server 2000.
0
jjrr007
Asked:
jjrr007
  • 5
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
jjrr007Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
1.

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

2.

I didn't understand this part
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjrr007Author Commented:
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
 
jjrr007Author Commented:
I got the first question resolved.  Please answer the second question.
0
 
jjrr007Author Commented:
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
 
jjrr007Author Commented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now