By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

Stored procedures, linked servers, and cursors

P: n/a
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

Feb 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Daniel Manes (da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 18 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.