• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

View SQL Server Express table from SQL Server 2005

My client runs SQL Server 2005, multiple desktop workstations are networked into this domain server.  One of the desktop PCs is running an out of the box "TimeSheet" program that stores its data locally in SQL Server Express.
My question is I want to create a VIEW from SQL 2005 to the source table(s) in this local SQL Server 2005.  What is the best way to do this without having to go thru SSIS?  I just want a simple solution like a SELECT statement that can recognize this other server, can I do this?
I have full admin rights also.

thanks in advance.
  • 2
1 Solution
Saqib KhanSenior DeveloperCommented:
You should Add Other Server as a "Linked" Server, and then you can query other tables in that server like this

Select * From [IP of SErver].[Database name].[dbo].[TableName]

Link Server Option is under Server Objects in Managment Studio.
bretbelAuthor Commented:
Just learning about these Server Option Objects makes my day.
Please I've encountered the following authentication errors, I've followed the link for assistance but got more confused, can you assist.

TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The OLE DB provider "SQLNCLI" for linked server "MSSQL$EXAKTIME" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MSSQL$EXAKTIME".
OLE DB provider "SQLNCLI" for linked server "MSSQL$EXAKTIME" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

Open in new window

Saqib KhanSenior DeveloperCommented:
now the server that you adding as a linked server must have a admin login for you that you can to add that server to your local server.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now