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
NimishaTry 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|||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|||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|||Thanx guys for all the input apprecaite it.
Nimisha