[dba-SQLServer] Accessing Tables in another server

If this is a continuous process then you want to create the linked
server as stated earlier in the thread, but if this is a one time job,
you can get away w/ ETL'ing (used to be DTS).

--
Francisco

On 1/17/07, JWColby <jwcolby at colbyconsulting.com> wrote:
> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...