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

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

LVL 1
killdurstAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RiteshShahConnect With a Mentor Commented:
you can set your new computer's database as linked database and use it.
0
 
udaya kumar laligondlaConnect With a Mentor Technical LeadCommented:
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
 
RiteshShahCommented:
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RiteshShahCommented:
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
 
killdurstAuthor Commented:
Man, you guys are fast. I'm gonna try these out and update you soon.
0
 
sateeshcvConnect With a Mentor Commented:
--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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.