CODEWITHSUNDEEP
sql-servert-sqllinked-server
Equalsk
Equalsk

Reputation: 8214

Use T-SQL to create a linked server between localhost and a cloud server

I have an SQL database that is hosted in the cloud on a server which I don't have any control over. I can connect to that server and view my database through Management Studio using a URL similar to reports.theremotesite.net and using SQL Authentication.

I want to query some of the Views provided by that database and copy the resulting rows to a local database held on my machine. I am using SQL Server 2008 R2 locally.
My research indicates that setting up a linked server is the first step to doing this but I have been struggling to get one set up using T-SQL.

(I know that you can set them up through Management Studio but my ultimate goal is to have this as a stored procedure in my local database which is called every night so I've been trying to make it entirely script based. Am I making a mistake?)

I have read the documentation on sp_addlinkedserver and sp_addlinkedsrvlogin but I didn't fully understand where to put the different parameters in the script.

Example server details:

Server name: reports.theremotesite.net
Authentication: SQL Server Authentication
Login: MyUsername
Password: MyPassword
Database: MyRemoteDatabase

Local details:

Server name: localhost
Authentication: Windows Authentication
Database: MyLocalDatabase

My script structure is like this:

USE [master];
GO

EXEC master.dbo.sp_addlinkedserver 
    @server = N'localhost', 
    @srvproduct = N'', 
    @provider = N'SQLNCLI', 
    @datasrc = N'tcp:reports.theremotesite.net', 
    @catalog = N'master';

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'reports.theremotesite.net',
    @useself = N'False',
    @locallogin = NULL,
    @rmtuser = 'MyUsername'
    @rmtpassword = 'MyPassword';
GO

When I run the script I get the following error:

The server 'reports.theremotesite.net' does not exist. Use sp_helpserver to show available servers.

Running sp_helpserver shows 2 rows, one with my machine name and one called localhost which I assume is the result of the script even though it didn't complete.

Any help or pointers would be marvellous as I am totally lost!

Upvotes: 1

Views: 4124

Answers (1)

S. Rojak
S. Rojak

Reputation: 454

sp_addlinkedserver creates a link to a remote server having the link you give it. So I would do:

EXEC sp_addlinkedserver 
    @server = N'reports', 
    @provider = N'SQLNCLI', 
    @datasrc = N'tcp:reports.theremotesite.net', 
    @catalog = N'master';

After doing so, you should have two rows in sys.servers, one for your local instance and one for reports. But you have yet to tell the link how to authenticate. To do that:

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = N'reports', -- has to match the server name in sp_addlinkedserver
    @useself = N'False',
    @rmtuser = 'MyUsername'
    @rmtpassword = 'MyPassword';

Even if authentication fails, if you have got past sp_addlinkedserver, you have to use sp_dropserver to get the link out of sys.servers:

EXEC sp_dropserver N'reports'; -- has to match the server name in sp_addlinkedserver

Upvotes: 2

Related Questions