Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get retrieve data from another database on another computer using stored procedures?

Posted on 2009-04-06
6
Medium Priority
?
1,606 Views
Last Modified: 2012-05-06
Let's say that the IP address of my computer is 192.168.3.109.

 In my Microsoft SQL Server Management Studio Express, I have 2 databases:
 - DB_ONE
 - DB_TWO

I have created the stored procedure below in the database called DB_ONE. It will retrieve all records from the table called "MY_TABLE" in the "DB_TWO" database. So far this works fine.

But now I have another computer with IP address of 192.168.3.110 and in its Microsoft SQL Server Management Studio Express there's a database called DB_THREE, and there's a table called HIS_TABLE.

How do I modify the stored procedure below (that's running on 192.168.3.109) to retrieve all records from "HIS_TABLE" in "DB_THREE" (which is running on 192.168.3.110)?

Is this even possible in the first place? Thanks.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE sp_test
AS
BEGIN
	SET NOCOUNT ON;
	select * from DB_TWO.dbo.MY_TABLE
END
GO

Open in new window

0
Comment
Question by:killdurst
[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
6 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1200 total points
ID: 24075752
you can set your new computer's database as linked database and use it.
0
 
LVL 12

Assisted Solution

by:udaya kumar laligondla
udaya kumar laligondla earned 400 total points
ID: 24075754
in the following code change the  ServerName.DBName.dbo.TableName as you require and the SP will be able to fetech data from any server
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE sp_test
AS
BEGIN
	SET NOCOUNT ON;
	select * from  ServerName.DBName.dbo.TableName
END

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075758
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075773
the way udayakumarlm said, it also ok but you have to have full rights over another sql server or have to have certificates of each other installed on each server.

in short, if you are trying to access database in 192.168.3.110 from 192.168.3.109 and you have logged in SQL Server with SA user in 192.168.3.109 than that user should be able to connect to 192.168.3.110 as well. this is true for both SQL authentication as well as windows authentication.
0
 
LVL 1

Author Comment

by:killdurst
ID: 24075778
Man, you guys are fast. I'm gonna try these out and update you soon.
0
 
LVL 3

Assisted Solution

by:sateeshcv
sateeshcv earned 400 total points
ID: 24077196
--GETTING ROWS FROM ANOTHER COMPUTER BY CREATING LINKED SERVER
--creating linked server for remoting accessing
EXEC SP_ADDLINKEDSERVER
      @SERVER='ANYNAME',
      @SRVPRODUCT='',      
      @DATASRC='SERVERNAME',      
      @PROVIDER='SQLNCLI';
      --OPTIONAL      @PROVSTR='Initial Catalog=DB_THREE;'

--CREATING REMOTE SERVERLOGIN FOR SERVER WITH USERNAME AND PASSWORD    
EXEC SP_ADDLINKEDSRVLOGIN 'ANYNAME', 'false', null, 'sa', 'PASSWORD'

CREATE PROCEDURE sp_test
AS
BEGIN
      SET NOCOUNT ON;
      select * from ANYNAME.DB_THREE.dbo.HIS_TABLE;
END
GO

NOTE: BEFORE DOING PLEASE GET THE SERVER NAME FOR THE COMPUTER: 192.168.3.110 AND REPLACE THE NAME IN THE @DATASRC VARIABLE.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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