How to Linked MS SQL Server To SQL Server Express

0
61

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 SQL Server Management Studio (SSMS) > Connect to MS SQL Server

5-18-2016%2B10-49-30%2BAM

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

5-18-2016%2B10-54-21%2BAM

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)

5-18-2016%2B11-18-50%2BAM

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

5-18-2016%2B11-24-05%2BAM

Step 5: Test connection by right click on LINKED > select Test ConnectionConnection to linked server succeeded

5-18-2016%2B11-28-56%2BAM

Step 6: Test select dataBefore 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]

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

select * from LINKED.TestDB.dbo.T1

LEAVE A REPLY

Please enter your comment!
Please enter your name here