Reply to Re: Stored procedures, linked servers, and cursors

Your name:

Reply:


Posted by Erland Sommarskog on 02/19/06 01:11

Daniel Manes (danthman@cox.net) writes:
> I've been trying to copy tables from a linked server to a SQL Server
> Express database. Express seems to have no direct/automatic way to do
> it, so I've been looking into doing this by hand (i.e., with a T-SQL
> procedure).
>
> I've discovered some system stored procedures that seem relevant (like
> sp_tables_ex and sp_columns_ex). But they're procedures, not
> table-valued functions, so I'm not sure if I can actually do anything
> with the data they return.
>
> If I could get to the data, I thought maybe I could use a cusor and a
> while loop to recreate the tables in Express.

I assume that the linked server is also SQL Server?

The simplest method is surely to script the tables, and then use
BCP to copy the data. You run BCP from a command-line window. In the
most simple form, you would do:

bcp src_db.dbo.tbl out tbl.bcp -S src_server -T -n
bcp target_db.tbo.tbl in tbl.bcp -S .\SQLEXPRESS -T -n

for each table.

-n specifies native format, which is the best to use when copying from
SQL Server to SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация