How to Linked MS SQL Server To SQL Server Express (Linking SQL Server To SQL Server Express)

Hi there,
Currently I am working with ERP system, using MS SQL Server 2008 R2 as database. I have install attendance server on other computer that using SQL Server Express 2008. Now I want to get data from SQL Server Express. Here are the steps I followed to set up a linked server from MS SQL Server to SQL Server Express

Step 1: Open SQ Management Studio > Connect to MS SQL Server

Step 2: Expand Server Objects > Linked Servers, right click on Linked Server > New Linked Server...

Step 3: On General tab

- Linked server: type LINKED (or any name)

- Select option "Other data source"

+ Provider: select SQL Server Native Client 10.0

+ Product name: SQLExpress

+ Data source: ATTENDANCE-SERVER (or IP Address of attendance server)

+ Catalog: TestDB (this is DB name)

Step 4: on Security tab > select "Be made using this security context" then type username and password to login to sql server express then click OK to complete setup

Step 5: Test connection by right click on LINKED > select Test Connection

Connection to linked server succeeded

Step 6: Test select data

Before run select command, you need to run this command bellow first:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

select * from [linked_name].[database_name].dbo.[table_name]

Ex: on SQL Server Express, I have a database TestDB and there is a table T1

select * from LINKED.TestDB.dbo.T1

Video demo:

----------END----------

P/S: If you are using difference version of MS SQL Server and SQL Server Express from the version in this article, you may encounter error. Please leave a comment, I will answer.

Thanks,

Redbean