SQL Server Performance

linked server query help..

Discussion in 'SQL Server 2005 General Developer Questions' started by limey, Dec 3, 2008.

  1. limey New Member

    Hello,
    I have the follwoing query:
    select col1, col2, col3 from [linked_server].db1.dbo.tbl1
    where col1 not in (select col1 from tbl1)
    I get the columns that I need from the linked server, but I am not sure how to add to the results col1 from the local sql server?
    so the results I would get would be:
    linked.col1, linked.col2, linked.col3, local.col1
    Any help would be greatly appreciated!
    Cheers!
  2. MichaelB Member

    Welcome to the forum!Not sure I understand... but here it goes...select linked.col1, linked.col2, linked.col3, local.col1from [linked_server].db1.dbo.tbl1 as linkedINNER JOINdb1.dbo.tbl2 as local ONlocal.keyfield = linked.FKfieldwhere col1 not in (select col1 from tbl1)???
  3. limey New Member

    Hi MichaelB,
    thanks for the reply!
    not sure that the join works in this...there is nothing common to join on between the local and linked db.
    bascially, my query gives a list of col1 from linked and I compare this to the same col1 on local, and whatever doesn't match give me the results...the problem is I am not sure how to add local.col1 to my query results.
    hope that makes sense, let me know
    Cheers!
  4. Adriaan New Member

    "bascially, my query gives a list of col1 from linked and I compare this to the same col1 on local, and whatever doesn't match give me the results...the problem is I am not sure how to add local.col1 to my query results."
    That's not terribly clear - but it sounds like you need to read up on the different versions of JOIN.
    If you want to show results from the local table where there is no match on the linked table, use:

    SELECT local.col1
    FROM local LEFT JOIN linked ON local.col1 = linked.col1
    WHERE linked.col1 IS NULL
    If you want to show results from the linked table where there is no match on the local table, use:

    SELECT linked.col1
    FROM local RIGHT JOIN linked ON local.col1 = linked.col1
    WHERE local.col1 IS NULL
    If you want to show results from both the linked and the local table in case there is no match on the other side, use:
    SELECT local.col1, linked.col1
    FROM local FULL JOIN linked ON local.col1 = linked.col1
    WHERE local.col1 IS NULL OR linked.col1 IS NULL
    If you want the last results, but in a single column, use the first and second query, and add UNION between them.
  5. limey New Member

    Hi guys,

    Sorry for not making this clear. I only have basic knowledge of joins, so please forgive my ignorance on the subject. I believe I will have to use some kind of join as I am trying to include results from two different databases and need to maker a relationship.
    My query in my first post gives me the results I need:
    select linked.col1, linked.col2, linked.col3 from [linked_server].db1.dbo.tbl1
    where linked.col1 not in (select local.col1 from local.tbl1)
    But, I would like to add to the query results local.col1, so in my results list I would have:
    linked.col1 | linked.col2 | linked.col3 | local.col1
    I hope this helps to explain better. I have tried different joins, but I am not getting the results.
    Cheers!
  6. satya Moderator

    I have a doubt here that, whether all the servers & databases involved here are on same COLLATION compatibility?
    FYI http://blogs.msdn.com/psssql/archiv...nked-servers-and-collation-compatibility.aspx on the collation blurb.
    In any case when such joins are involved within LINKED server then its best to:
    Use a pass-through query instead of a 4-part name query. To do so, you can use either of the following Transact-SQL functions:
    • OPENQUERY
      • OPENROWSET
  7. limey New Member

    Hi satya,
    Thanks for the reply.
    Although not too familiar with COLLATION, I did read the link and I think this is not the problem.
    The two databases (local and linked) have the same schema, they just reside on two seperate sql servers (and different versions v7 vs 2005).
    to give an overview, there was a migration (not database migration but another type that is propietary to the software that uses the databases)
    I am trying to verify the data that has been migrated, but with the sql server 2005 being the new 'live' database (represented in the query by local), there are many entries in this database that have been recently added, that would not be on the linked server.
    I can use col1 as this has a unique identifier (used by the software) between the two databases as they are the the most common to verify the migrated data (but not common in all cases!). But in my query results I want to show both local.col1 and linked.col1 as part of the verification report.
    I hope this helps to clarify, let me know.
    Cheers!
  8. satya Moderator

    Why not the collation in this case?
    As you are trying to present the data and compare the same between thse 2 servers, you must check what is the server & database collation on these ones.
  9. limey New Member

    Thanks satya,
    I know how to check collation in sql 2k5 but not sql v7...tried a bunch with no luck:
    sql server properties
    select SERVERPROPERTY ('collation')
    select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
    ...and a whole bunch of others...any idea to find collation in sql server v7?
    Cheers!


  10. Adriaan New Member

    Perhaps sp_helpsort?
  11. limey New Member

    Thanks Adriaan, that worked.
    So for sql server v7:
    Character Set = 1, iso_1
    ISO 8859-1 (Latin-1) - Western European 8-bit character set.
    Sort Order = 52, nocase_iso
    Case-insensitive dictionary sort order for use with several We
    stern-European languages including English, French, and German
    . Uses the ISO 8859-1 character set.
    for sql server 2005 (the specific database being used in the query):
    SQL_Latin1_General_CP1_CI_AS
    Is this the same collation?
    Cheers!
  12. Adriaan New Member

    Try Books Online (in my installation, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/56483d24-add7-483d-9b96-c6fda460ddbc.htm) for a list of the Sort order IDs and the full names.
    In that list, 52 corresponds to SQL_Latin1_General_CP1_CI_AS.

Share This Page