Creating Linked Azure Server in Visual Studio SQL Server Express

I use Visual Studio to load data onto a SQL Server Express instance on my local PC. I believe that this SQL server installed with Visual Studio.

First, I used Microsoft SQL Server Management Studio to log in to my local instance with the following credentials. You should be able to run this SQL inside Visual Studio too, however.

Server name: (localdb)\Projects
Credentials: Windows authentication

Then execute the next two statements:

EXEC sp_addlinkedserver
@server=’Azure’,
@srvproduct=”,
@provider=’sqlncli’,
@datasrc='<remote db url>’,
@location=”,
@provstr=”,
@catalog='<a good name here, say: bob>’

and:

EXEC sp_addlinkedsrvlogin
@rmtsrvname=’Azure’,
@useself=’false’,
@rmtuser='<remote db username>@<remote db url>’,
@rmtpassword='<password>’
EXEC sp_serveroption ‘Azure’, ‘Collation Compatible’, true;

Now, if you have the isnap database selected on your local pc, you can issue this query:

select * from localtablename;

to query the table on the local pc.

and this query:

select * from azure.bob.dbo.remotetablename;

(where “bob” is the good name you assigned in the first statement you executed)  to query the table on azure!

Leave a Reply

Your email address will not be published. Required fields are marked *