|
Home > Archive > Microsoft SQL Server forum > February 2006 > Stored procedures, linked servers, and cursors
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored procedures, linked servers, and cursors
|
|
| Daniel Manes 2006-02-17, 1:23 pm |
| 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
| |
| Erland Sommarskog 2006-02-18, 8:23 pm |
| 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
|
|
|
|
|