SQL Server Performance

Link a view to another database

Discussion in 'Getting Started' started by laurap, Jul 11, 2007.

  1. laurap New Member

    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
  2. satya Moderator

    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.
  3. Adriaan New Member

    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.
  4. MohammedU New Member

    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.
  5. DilliGrg Member

    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)

  6. MohammedU New Member

    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.
  7. satya Moderator

    That is entirely ruled out in this post as the version if SQL 2000 [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  8. laurap New Member

    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
  9. Adriaan New Member

    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.
  10. Madhivanan Moderator

    I think the code you posted is ACCESS syntax. Follow Adriaan's advice

    Madhivanan

    Failing to plan is Planning to fail
  11. Adriaan New Member

    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=';)' />]<br /><br />The difference in Access/Jet join syntax is that when you have more than two tables, you have to use brackets, like this -<br /><br />FROM (table1 AS t1 INNER JOIN table2 AS t2 ON t1.key = t2.key) INNER JOIN table3 AS t3 ON t1.key = t3.key<br /><br />- other than that, the basic Jet SQL syntax is fairly compliant.<br /><br />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.
  12. Madhivanan Moderator

    Note that Update with INNER JOIN syntax differs in SQL Server and Access [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  13. Adriaan New Member

    I was talking about the JOIN syntax itself.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />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.<br /><br />Anyway - this thread is not about Access, is it?[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]

Share This Page