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.
| Author |
Topic |
|
spc2011uk
Starting Member
3 Posts |
 Posted - 2014-01-27 : 10:53:36
|
| I'm trying to create a view that pulls in two tables, each from a different database (not SQL server). I could always do this before but on this particular server instance I get a 'NO ENTRY' sign when I try and drag the table onto the view design area in SQL Studio Manager. On another instance on the same box I do not have this issue.This is driving me mad.....any ideas? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-27 : 14:06:36
|
| Are you accessing them via linked servers?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
spc2011uk
Starting Member
3 Posts |
Posted - 2014-01-28 : 05:33:01
|
| HI, no I'm not, the databases are on the same instance of SQL server so linked servers are not required. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-28 : 07:18:23
|
quote:
Originally posted by spc2011uk
HI, no I'm not, the databases are on the same instance of SQL server so linked servers are not required. Thanks
sorry then i didnt understand what you meant by thiscreate a view that pulls in two tables, each from a different database (not SQL server). ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
spc2011uk
Starting Member
3 Posts |
Posted - 2014-01-29 : 12:25:55
|
| I have two databases on one sql instance. In one of these databases I am creating a view that uses tables in both database i.e. they are link through common fields. Normally you can drag and drop the table into the view designer and then link the fields; it will not allow me to drag a table from the 'other' database into the view designer.Hope this is a little clearer. Thanks |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-01-29 : 18:53:26
|
| What rights do you have in each database? Are you a User in each? Do you have read rights in each? Do you have DDLADMIN rights?===============================================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-30 : 06:59:19
|
quote:
Originally posted by spc2011uk
I have two databases on one sql instance. In one of these databases I am creating a view that uses tables in both database i.e. they are link through common fields. Normally you can drag and drop the table into the view designer and then link the fields; it will not allow me to drag a table from the 'other' database into the view designer.Hope this is a little clearer. Thanks
why do you need to use view designer?Why not use query editor in SSMS and create view using CREATE VIEW statement?I've seen cases where view editor throws error while using some new operators in T-SQL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Cross Database Views