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! laura

Welcome 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.

Name
———
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=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> @<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

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=’

‘ />] The difference in Access/Jet join syntax is that when you have more than two tables, you have to use brackets, like this - FROM (table1 AS t1 INNER JOIN table2 AS t2 ON t1.key = t2.key) INNER JOIN table3 AS t3 ON t1.key = t3.key - other than that, the basic Jet SQL syntax is fairly compliant. Then again, starting with Access 2002 you can change either the current MDB file, or your installation of the Access software, to accept only ANSI-92 compliant SQL syntax.

Note that Update with INNER JOIN syntax differs in SQL Server and Access [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

I was talking about the JOIN syntax itself.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] But you’re right: in an UPDATE query, Jet SQL syntax does not support a FROM clause. You have to put the tables and joins between UPDATE and SET (with brackets like indicated above). And most of the time you have to add DISTINCTROW after UPDATE. Anyway – this thread is not about Access, is it?[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]

]]>