Stored procedures, linked servers, and cursors

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.

Is this crazy?

Really could use some advice on this.

Thanks,

-Dan

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx