Open SQL view from a different SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Open SQL view from a different SQL server

Author

Topic

xar8
Starting Member

2 Posts

Posted - 2014-08-27 : 11:17:41

I want to open a view from a different SQL Server and database. I have gotten this to work fine with a different database, I believe the problem I am having now is tied to the name of both the SQL Server and the name of the database. The SQL Server has a '\' in the title and the database has a '-' in the title. This is the code I have used and it does not work.

SELECT *
FROM REACTSCHICO\SQLEXPRESS.REACTS - CNC.dbo.vwB30SalesContractProduct

I have tried putting in {} and [] and "" nothing seems to do the trick. Any help would be greatly appreciated. Thank You.

Jason E Evans

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 11:25:25

There's really only two ways to do this:

1. set up a linked server and use a four-part select

http://msdn.microsoft.com/en-ca/library/ms188279.aspx

2. Use OpenRowSet or OpenDataSource

http://msdn.microsoft.com/en-ca/library/ms190312.aspx
http://msdn.microsoft.com/en-us/library/ms179856.aspx

xar8
Starting Member

2 Posts

Posted - 2014-08-27 : 12:19:25

But I can use the same code I showed earlier with a different database, example

SELECT *
FROM REACTSSQLSERVER.REACTS.dbo.tblContact

This works fine. Is there no way I can get it to work with the other database?

Thanks for you comments

Jason E Evans

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 12:45:39

This works because REACTSSQLSERVER is in your linked servers (you can see it in SSMS under Server Objects/Linked Servers). However, to get to the other database, it needs to be added to the Catalogs node for REACTSSQLSERVER.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources