SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Open SQL view from a different SQL server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xar8
Starting Member

USA
2 Posts

Posted - 08/27/2014 :  11:17:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/27/2014 :  11:25:25  Show Profile  Reply with Quote
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

Go to Top of Page

xar8
Starting Member

USA
2 Posts

Posted - 08/27/2014 :  12:19:25  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/27/2014 :  12:45:39  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000