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