[dba-SQLServer] Accessing Tables in another server

JWColby jwcolby at colbyconsulting.com
Wed Jan 17 07:47:49 CST 2007


I have the same question basically.  On one of my machines, I have a
database in SQL Server 2K and another database in Sql Server 2005.  I need
to create new tables in SQL Server 2005 and copy the data from SQL Server 2K
into the new tables.  

I tried using a select fld1,fld2,etc, from
[servername].[databasename].[dbo].[tablename] but that failed.  Is there a
syntax like this that can be used?  How do you select data into a dataset
which physically resides on another server?  It seems like a normal thing to
do.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Tuesday, January 16, 2007 10:32 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer] Accessing Tables in another server

SQL2000.

I have two servers registered in enterprise manager.  I would like to run a
stored procedure from a database on one server which gets data from a table
on the second server.  For example, assuming we have:

Stored Procedure1 in database1 on server 1
Table2 in Database2 on Server 2

I would like to do something like this in Database1

Select *
 From Server2.Database2.dbo.Table2

The error message I get when trying to save the procedure is "The number
name contains more than the maximum number of prefixes.  The maximum is
three."

How can I reference the data on the other server?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list