Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    34

    Angry Unanswered: Join 2 Tables Exist In Two Databases

    Hi,
    I want to get the results of a query between 2 tables that exist in the same server but in 2 different databases. I want this query to be executed in a stored procedure (use the reults in a cursor), or if it's possible to be used in an ado dataset or a simple dataset in Delphi. Can this be done also if the two datases (MSSQL) exist in different servers??
    Can anyone show an example of how to accomplish these tasks??

    Best Regards,
    Manolis Perrakis

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You should be able to do this by using a qualified object name:

    select *
    from MyDB.dbo.MyTable a
    join YourDB.dbo.YourTable b
    on a.KeyColumn = b.KeyColumn
    where blah, blah, blah
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Dec 2003
    Posts
    39

    Linked Server

    U can query tables of databases (sql database) on different servers. But add the other server as linked server first (Security - Linked Servers).

    ================================================== =======
    select [<column>, ...]
    from <current server dbname>.<ownername>.<tablename> a
    inner join
    <other server name>.<other server dbname>.<ownername>.<tablename> b
    on a.<column> = b.<column>
    ================================================== =======

    current server => the server where query runs
    <other server name> => the other server name, which has been added as linked server at current server

  4. #4
    Join Date
    May 2004
    Posts
    34

    Angry JOIN works only with numeric fields??

    I tried to connect 2 tables exist in 2 different databases by using 2 varchar(25) fields. When doing this I get a message: Cannot resolve collation conflict for equal to operation.
    However when I join numeric fields I don't have a problem.
    In my case I need to join the varchar fields.
    Any thoughts??

    Regards,
    Manolis

  5. #5
    Join Date
    Dec 2003
    Posts
    39

    Collation

    The collation type of the fields are different, though both of them with varchar type. Check the collation types of both fields from table design view. If they use <database default> collation, check collation name from database property.

    eg :
    table a use Latin1_General_CI_AS collation
    table b use SQL_Latin1_General_CP1_CI_AS collation

    select *
    from a inner join b
    on a.field1 COLLATE SQL_Latin1_General_CP1_CI_AS = b.field2

    Use <servername>.<dbname>.<ownername>.<objectname> as required

  6. #6
    Join Date
    May 2004
    Posts
    34

    Angry Collation Syntax Problem

    You are right.
    The first table-first datase(db1) uses the SQL_Latin1_General_CP1253_CI_AS collation.
    The second table-second(db2) database uses the Greek_CI_AI collation.

    Numeric fields work
    select *
    from db1.dbo.Fin_Codes a
    join b2.dbo.Material b
    on a.FCode_ID=b.ID

    Varchar fields --> collation problem
    select *
    from db1.dbo.Fin_Codes a
    join b2.dbo.Material b
    on a.Fin_Code =b.Code


    When I tried to use the collations I get an syntax error:
    (Line 4: Incorrect syntax near 'COLLATE'.)

    select *
    from db1.dbo.Fin_Codes a
    join db2.dbo.Material b
    on a.Fin_Code COLLATE Greek_CI_AI=b.Code


    Or

    select *
    from db1.dbo.Fin_Codes a
    join db2.dbo.Material b
    on a.Fin_Code =b.Code COLLATE SQL_Latin1_General_CP1253_CI_AS

    Where is my error??

    Best Regards,
    Manolis

  7. #7
    Join Date
    Dec 2003
    Posts
    39
    can u post the real query u run, that raises that error? and if necessary post the table creation script, as i think the query is already correct

    FYI : The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
    Link Link

  8. #8
    Join Date
    May 2004
    Posts
    34

    Tablea Structure

    I try to join Fin_Codes.Fin_Code to Material.Code

    db1: Table-> Fin_Codes
    CREATE TABLE [dbo].[Fin_Codes] (
    [Fin_Code] [varchar] (25) COLLATE SQL_Latin1_General_CP1253_CI_AS NOT NULL ,
    [SourecFin_Code] [varchar] (25) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [Fin_Cat] [varchar] (10) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [FCode_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Cat_ID] [smallint] NULL ,
    [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [Full_TitleStr] [varchar] (250) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [GLPCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [GLSCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [ALICode] [varchar] (15) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [ALOCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [Cost_Account] [varchar] (15) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [Comments] [varchar] (250) COLLATE SQL_Latin1_General_CP1253_CI_AS NULL ,
    [ChPrime] [bit] NOT NULL
    ) ON [PRIMARY]
    GO


    db2: Table -> Material
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MATERIAL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[MATERIAL]
    GO

    CREATE TABLE [dbo].[MATERIAL] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [COMID] [int] NOT NULL ,
    [GLMID] [int] NULL ,
    [ICTID] [int] NULL ,
    [IGPID] [int] NULL ,
    [IQCID] [int] NULL ,
    [SUBCODE1] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [SUBCODE2] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [REPLACEVALUE] [float] NULL ,
    [MU1] [int] NOT NULL ,
    [MPCID] [int] NULL ,
    [RELITEID] [int] NULL ,
    [VTCID] [int] NOT NULL ,
    [MTRMODE] [smallint] NULL ,
    [MU2] [int] NULL ,
    [MU3] [int] NULL ,
    [MU4] [int] NULL ,
    [MU2_1] [float] NULL ,
    [MU3_1] [float] NULL ,
    [MU4_1] [float] NULL ,
    [MU1_2MODE] [smallint] NULL ,
    [MU1_3MODE] [smallint] NULL ,
    [MU1_4MODE] [smallint] NULL ,
    [DIM1] [float] NULL ,
    [DIM2] [float] NULL ,
    [DIM3] [float] NULL ,
    [DIMMODE] [smallint] NULL ,
    [DIMMULTMODE] [int] NULL ,
    [MUMODE] [smallint] NULL ,
    [CMPQTY] [float] NULL ,
    [RECOMQTY] [float] NULL ,
    [QTYFROMVALUE] [smallint] NULL ,
    [REORDERLIMIT] [float] NULL ,
    [ORDERDAYS] [int] NULL ,
    [GLSALESCODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [GLPURCHASECODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [ANALSALESCODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [ANALPURCHASECODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [PRODCODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [WHSPRICE] [float] NULL ,
    [RTLPRICE] [float] NULL ,
    [WHSMARKUP] [float] NULL ,
    [RTLMARKUP] [float] NULL ,
    [STDCOST] [float] NULL ,
    [CURID] [int] NULL ,
    [FWHSPRICE] [float] NULL ,
    [FRTLPRICE] [float] NULL ,
    [INTID] [int] NULL ,
    [WEIGHT] [float] NULL ,
    [VOLUME] [float] NULL ,
    [IDMID] [int] NULL ,
    [COMPOSITION] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [KEPYO] [smallint] NULL ,
    [ISGIFT] [smallint] NULL ,
    [THIRDPART] [smallint] NULL ,
    [ONORDER] [smallint] NULL ,
    [ADDONPRC] [float] NULL ,
    [EXCLUSIV] [smallint] NULL ,
    [SETSAUTOSELL] [smallint] NULL ,
    [SETSAUTOBUY] [smallint] NULL ,
    [SETSSELLMODE] [smallint] NULL ,
    [SETSBUYMODE] [smallint] NULL ,
    [SETSPRSELLMODE] [smallint] NULL ,
    [SETSPRBUYMODE] [smallint] NULL ,
    ---> [CODE] [varchar] (25) COLLATE Greek_CI_AI NOT NULL ,
    [DEFAULTDISCOUNT] [float] NULL ,
    [DESCR2] [varchar] (80) COLLATE Greek_CI_AI NULL ,
    [DESCRIPTION] [varchar] (80) COLLATE Greek_CI_AI NOT NULL ,
    [FACTORYCODE] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [NOTACTIVE] [smallint] NULL ,
    [ITEMTYPE] [smallint] NULL ,
    [MAXDISCOUNT] [float] NULL ,
    [REMARKS] [text] COLLATE Greek_CI_AI NULL ,
    [SURID1] [int] NULL ,
    [SURID2] [int] NULL ,
    [SURID3] [int] NULL ,
    [SURID4] [int] NULL ,
    [SURID5] [int] NULL ,
    [EXPVALUE1] [float] NULL ,
    [EXPVALUE2] [float] NULL ,
    [EXPVALUE3] [float] NULL ,
    [EXPVALUE4] [float] NULL ,
    [EXPVALUE5] [float] NULL ,
    [SECLIMITMIN] [float] NULL ,
    [SECLIMITMAX] [float] NULL ,
    [CLEVEL] [int] NULL ,
    [HASSPEC] [smallint] NULL ,
    [GURITEID] [int] NULL ,
    [ISACTIVE] [smallint] NULL ,
    [MNFID] [int] NULL ,
    [IGSID] [int] NULL ,
    [IGTID] [int] NULL ,
    [BUSID] [int] NULL ,
    [PURPRICE] [float] NULL ,
    [PURCURID] [int] NULL ,
    [MUINT_1] [float] NULL ,
    [ORDERDATES] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [MINLOTQTY] [float] NULL ,
    [STEPLOTQTY] [float] NULL ,
    [LOTPERIOD] [int] NULL ,
    [SETSSTOREMODE] [smallint] NULL ,
    [SETSAUTOSTORE] [smallint] NULL ,
    [SETSPRSTOREMODE] [smallint] NULL ,
    [LASTBUYPRICE] [float] NULL ,
    [MAINSZLID] [int] NULL ,
    [ALT1SZLID] [int] NULL ,
    [ALT2SZLID] [int] NULL ,
    [MRPFLAG] [smallint] NOT NULL ,
    [STARTSTOCK] [float] NULL ,
    [ENDSTOCK] [float] NULL ,
    [FLDSTRING1] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDSTRING2] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDSTRING3] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDSTRING4] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDSTRING5] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDFLOAT1] [float] NULL ,
    [FLDFLOAT2] [float] NULL ,
    [FLDFLOAT3] [float] NULL ,
    [FLDFLOAT4] [float] NULL ,
    [FLDFLOAT5] [float] NULL ,
    [FLDDATE1] [datetime] NULL ,
    [FLDDATE2] [datetime] NULL ,
    [FLTID1] [int] NULL ,
    [FLTID2] [int] NULL ,
    [ECOMAVAILABLE] [smallint] NULL ,
    [ABCMASK] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [ABCDDID] [int] NULL ,
    [LASTBUYPRICEUPDATE] [datetime] NULL ,
    [SNCODEMASK] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [NOTNATIVE] [smallint] NOT NULL ,
    [STOCKREMAINMODE] [smallint] NOT NULL ,
    [REPLACED] [smallint] NULL ,
    [REPLACECHAIN] [smallint] NOT NULL ,
    [BINCODE] [varchar] (25) COLLATE Greek_CI_AI NULL ,
    [FLDSTRING6] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [FLDDATE3] [datetime] NULL ,
    [FLDFLOAT6] [float] NULL ,
    [FLTID3] [int] NULL ,
    [LASTCHANGEPRICEUPDATE] [datetime] NULL ,
    [TAXFREEITEID] [int] NULL ,
    [MRPGROUPID] [int] NULL ,
    [ZPIECES] [int] NULL ,
    [ENTRYDATE] [datetime] NULL ,
    [MRPPARTITIONTYPE] [smallint] NULL ,
    [MRPPLANCODE] [int] NULL ,
    [COSTINGMODE] [smallint] NULL ,
    [LASTUPDDATE] [datetime] NULL ,
    [AUTOSYN] [smallint] NOT NULL ,
    [MU5] [int] NULL ,
    [MU5_1] [float] NULL ,
    [MU1_5MODE] [smallint] NULL ,
    [ZMDA] [int] NULL ,
    [ZCUSTOMERMATCODE] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [ZWEIGHT] [float] NULL ,
    [ZORDER] [int] NULL ,
    [ZSTYLE] [int] NULL ,
    [ZSYNTHESH] [varchar] (50) COLLATE Greek_CI_AI NULL ,
    [WARNING] [varchar] (255) COLLATE Greek_CI_AI NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  9. #9
    Join Date
    Dec 2003
    Posts
    39
    hhmmmm..... i use these query and nothing's wrong

    select *
    from tempdb.dbo.Fin_Codes a join tempdb.dbo.Material b
    on a.Fin_Code COLLATE Greek_CI_AI=b.Code

    select *
    from tempdb.dbo.Fin_Codes a join tempdb.dbo.Material b
    on a.Fin_Code =b.Code COLLATE SQL_Latin1_General_CP1253_CI_AS

    btw u use sql server 7 or 2000? i'm using sql server 2000. In the books online i find that u cant use COLLATE keyword on sql server 7. If the database used to be on sql server 7, but has been restored/migrated to sql server 2000, try
    sp_dbcmptlevel <dbname>, 80

    If its still on sql server 7, sorry cant help, i'v never used version 7
    Link Link

  10. #10
    Join Date
    May 2004
    Posts
    34

    Thumbs up Thanks Jelly Link

    Problems Solved.
    I'm using MSSQL 2000 but the database was restored from MSSQL 7.
    After running the sp_dbcmptlevel <dbname>, 80 everything worked fine.

    Best Regards,
    Manolis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •