HELP: Problem with naming convention.

I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
JohnHi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:

> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'