September 27, 2002 at 7:08 am
I am using SQL Server 7. I want to have a stored procedure on Server1 to
get data from a table on Server2 over the Network (i.e. both Server1 and
Server2 are on different machines).
On Server2, I have a table T_City which contains CityID, CityName, and CityPopulation
fields. The owner/user is dbo.
On Server1, I have a stored procedure named dbo.sp_sel_City as follow:
CREATE PROCEDURE dbo.sp_sel_City
AS
SELECT
dbo.T_City.CityID, dbo.T_City.CityName, dbo.T_City.CityPopulation
FROM dbo.T_City
Order by dbo.T_City.CityName
Can anyone tell me:
1.How to re-write the above stored procedure on Server1 to get data from
Server2?
2.Are there any necessary steps that I have to take in order to connect
both Server1 and Server2?
3.I am using ASP, is there any impact on it?
Your prompt reply is greatly appreciated!
Thank you!
WooGor
WooGor
September 27, 2002 at 7:15 am
On Server1, create a Linked Server to Server2.
This way you can directly query Server2 from Server1's SP.
To create a Linked Server, open EM and Security => Linked Servers.
In you SP you will need to qualify your statements...for example
CREATE PROC dbo.sp_sel_city
AS
SELECT CityID, CityName
FROM Server2.dbo.T_City
In the example above replace Server2 with whatever you name the Linked Server. You may need to qualify the SELECT Statement..Not sure.
Clive Strong
September 27, 2002 at 8:12 am
You do not have to qualify the SELECT statement.
Another alternative is to place the stored procedure on Server2 in the database where the table resides and then execute the following command from Server1:
EXEC Server2.databasename.dbo.sp_sel_City
I can't say this is any better or worse than the way Clive Strong suggested. However, this way allows you to keep the SP in the database where the data is. Sometimes I prefer to do this.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 27, 2002 at 11:13 am
From a performance standpoint, you may want to look at OPENQUERY() as it is a pass-through mechanism if you have a complex query or the table is fairly large. OPENQUERY will run the query on the linked server and return the results.
Here's an example:
Four-part naming convention:
SELECT i
FROM LinkedServer.LinkedDB.dbo.Pivot
WHERE i < 20
OPENQUERY:
SELECT i
FROM OPENQUERY(LinkedServer, 'SELECT i FROM LinkedDB.dbo.Pivot WHERE i < 20')
Pivot has 10,000 rows (1 integer column) in it. The first query has a total cost of 10.01 because all 10,000 rows have to brought across and then parsed down. The second query using OPENQUERY has a cost of 3.364, substancially smaller because the query is being run on the linked server and only the rows needed are returned. Incidently, the cost on the other server is 0.00643. So it's real small there, too.
Food for thought.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply