Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    Unanswered: Gettin data fromdifferent database

    Hi

    My query syntax is such

    select 'INSERT INTO UserCourse(Start_Date, User_ID) values
    (Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
    FROM DB1..EMPLOYEE_TRAINING, DB2..User
    WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

    I run this query in the Query Analyser on the database DB1 and get the following error

    The column prefix 'User' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Line 1
    The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

    What I need is to get the ID and Date from differnt tables in different databases
    Any clues or suggestions
    Nimisha

  2. #2
    Join Date
    Jan 2003
    Posts
    126
    Try changing:

    FROM DB1..EMPLOYEE_TRAINING, DB2..User

    WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

    to

    FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User


    WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

    HTH

    Ken

  3. #3
    Join Date
    Apr 2003
    Posts
    18

    Re: Gettin data fromdifferent database

    Hi,
    Try this
    In clause replace the DB2..User
    with servername.databasename.userid.tablename
    and in where clause simply use the column name.

    e.g

    select *
    from a, ndokech1.echi.dbo.tx_echi c
    where a.myid = c.myid

    i think it will work..

    Cheers
    Gola munjal


    Originally posted by Nimisha
    Hi

    My query syntax is such

    select 'INSERT INTO UserCourse(Start_Date, User_ID) values
    (Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
    FROM DB1..EMPLOYEE_TRAINING, DB2..User
    WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

    I run this query in the Query Analyser on the database DB1 and get the following error

    The column prefix 'User' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Line 1
    The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

    What I need is to get the ID and Date from differnt tables in different databases
    Any clues or suggestions
    Nimisha

  4. #4
    Join Date
    Apr 2003
    Posts
    18
    Sorry i thought u asked for getting data from different servers.
    i think the problem is with table name user, bcause user is a keyword in sql.
    give user table name with in [] this.
    select *
    from fscms..[user] ,fscms_backup..[user]
    where fscms..[user].Employee_Number = fscms_backup..[user].Employee_Number

    i tried this ..its working ..

    Cheers
    gola

    Originally posted by GA_KEN
    Try changing:

    FROM DB1..EMPLOYEE_TRAINING, DB2..User

    WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

    to

    FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User


    WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

    HTH

    Ken

  5. #5
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    Getting data from table in another database

    Thanx guys for all the input apprecaite it.
    Nimisha

Posting Permissions

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