SQL Server Performance Forum – Threads Archive
Link a view to another database
Hello, I’m new with SQL Server and I have a problem ,,, I have a view made in a database let’s say DB1 and I have this same view in DB2. I want that this view to work from DB2 as well, like a link between this two databases. I have Microsoft SQL Server 2000 and I think that using synonyms will help me, but my version of SQL Server doesn’t allow the use of synonyms. Thank you! lauraWelcome to the forum! Synonyms are introduced in SQL 2005 and not available in SQL 2000.
You could take help of linked server upto some extent in this case, stating same server as a linked server for that database –http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
If both databases are on the same server instance, you can simply add the database name before the table name: SELECT t1.col, t2.col
FROM DB1.dbo.table1 t1
INNER JOIN DB2.dbo.table2 t2
ON t1.key = t2.key The login must have access to both databases, and read permission on both tables.
Synonyms are used for single name reference instead of three or four part name…
In your case it is three part name which dbname.owner.objectname so following Adriaan’s suggestion…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Synonyms can be created to reference the three or four part name not just single name only. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=50381&DisplayTab=Article
quote:Originally posted by MohammedU
Synonyms are used for single name reference instead of three or four part name…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided ?AS IS? with no warranties for accuracy.
NameMohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided ?AS IS? with no warranties for accuracy.
———
Dilli Grg (1 row(s) affected)
What I was trying say was… Instead of using three or four part names in the code you can use sinlge name your tsql code… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
That is entirely ruled out in this post as the version if SQL 2000 [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Thank you for your response! I used Adriaan information. I do have another issue now. I want my view to show me just the rows that required some criteria.I have the following: create view MyView
as
select t1.date,
t1.Nomber
from DB1.dbo.table1 t1
where (t1.name=’a name’)
INNER JOIN
DB2.dbo.table2 t2
ON t1.Nomber=t2.Nomber And it doesn’t work… Thank you! laura
The WHERE part comes after the FROM part. The FROM part includes all the JOINs and ON clauses, so you put the WHERE part after the last ON clause.
I think the code you posted is ACCESS syntax. Follow Adriaan’s advice Madhivanan Failing to plan is Planning to fail
Madhivanan, some people think Access is a poor excuse for an RDBMS, but it really isn’t all that bad.[<img src=’/community/emoticons/emotion-5.gif’ alt=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
Note that Update with INNER JOIN syntax differs in SQL Server and Access [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
I was talking about the JOIN syntax itself.[<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
![Stick Out Tongue :p :p](styles/default/xenforo/smilies/tongue.png)
]]>