Exporting data with SMO?

I have searched low and high for some information on how to export data with SMO. My old build scripts were using SQLDMO.BulkCopy which was using BCP and it worked fast on entire database.
I can't find a way to do the same with SMO. As alternative, I tried using bcp.exe on each table but it's awefully slow because initial process startup overhead.
Please don't recommend Transfer class as I'm not looking to transfer server-to-server but server-to-hdd. Backup isn't right as well.
Any suggestions?
Thank you.
Alex

I'm not certain but I think the functionality you're looking for is now in the SSIS (SQL Server Integration Services) toolkit. It's the next evolution of DTS (which the BCP util uses by generating an adhoc DTS package). Or you can use the DMO with 2005 (you have to install the backwards compatibility components when installing SQL 2005 or you can add it on later if you want).

Note that SSIS is not available for SQL Server 2005 Express so you have to use DMO to do what you want (or BCP) if you don't have Standard or above. Also, you will find that the tools for designing SSIS packages are much more friendly than hand coding DMO.

|||

With regard to copying data: there is a Transfer object that perform this in an 'online' type of fashion. It creates the target database, creates the schema, and copies the data. All steps are optional.

See this sample for a starting point: http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx

BTW, we decided not to implement BulkCopy in SMO as we lacked the proper library support for it. It ended up being a shim around SQL-DMO if we would have gone along with it. That would have not been much of an improvement.

SSIS is indeed something to look at.

Another option is to use the old DMO BulkCopy ad interim, until System.Data has full BulkCopy support (see the current bulkcopy object; which does not perform all of the functionality that the old BulkCopy object did).

Export to Excel

I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
MadhivananIf you actually want an Excel workbook as output, use a DTS package instead.
bcp is used only for text (or native) output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982106.363043.37730@.o13g2000cwo.googlegroups.com...
I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
Madhivanan|||Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan|||The books online (BOL) discuss DTS. You can also post the question in the
.dts newsgroup. Also, have you tried just using the wizard? This sounds
like a straightforward export.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115983608.621366.69350@.g14g2000cwa.googlegroups.com...
Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan

Export to CSV with field headers

I need to export multiple tables to a csv file with field headers. BCP will not bring across the field headers and I need to do this on a daily basis.
Any suggestions?
John
You could do it using DTS.
"John" <anonymous@.discussions.microsoft.com> schreef in bericht
news:6EF27F05-C2CB-4339-ADF6-804924A47466@.microsoft.com...
> I need to export multiple tables to a csv file with field headers. BCP
will not bring across the field headers and I need to do this on a daily
basis.
> Any suggestions?
> John
|||Hi,
You can create a DTS package with SQL server as source and Excel file as
target. After saving the package you can schedule it to run daily based on
your requirement.
How to create the package:
1. In Enterprise manager
2. Data Transformation option
3. Local packages
4. Right click and create new package
5. Create one connection for sql server and dest connection for excel and
create a transformation task
6. Save this as a package and schedule it using SQL Agent.
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:6EF27F05-C2CB-4339-ADF6-804924A47466@.microsoft.com...
> I need to export multiple tables to a csv file with field headers. BCP
will not bring across the field headers and I need to do this on a daily
basis.
> Any suggestions?
> John
|||Thanks for the answer - I wanted to go to a csv test file to avoid some of the limitations of Excel - 65K records and stick to a neutral format like csv.
|||Hi John
In that case instead of Excel as destination select the destination as Text
file and in the file name give file_name.CSV. By default the file will be
comma seperated.
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2ABACAEE-F714-40B7-A8EA-D462856A21E0@.microsoft.com...
> Thanks for the answer - I wanted to go to a csv test file to avoid some of
the limitations of Excel - 65K records and stick to a neutral format like
csv.

Export Table to Text File Using Query Analyzer

I can use DTS and BCP, but I am trying to export a table into a text
file through Query Analyzer (I need to show documentation in QA). DTS
uses EM and does not leave a "paper trail"
I am looking for the reverse of BULK INSERTHi
I am not sure what you are wanting to audit regarding your paper trail.
There is no BULK EXPORT command. If your DTS package is executed through a
job then you will have the job history. To use a stored procedure to start
the job/package, then you can start a job using sp_start_job or execute a DT
S
package through xp_cmdshell or the sp_OA* procedures see
http://www.databasejournal.com/feat...cle.php/1459181
John
"Justin Chandler" wrote:

> I can use DTS and BCP, but I am trying to export a table into a text
> file through Query Analyzer (I need to show documentation in QA). DTS
> uses EM and does not leave a "paper trail"
> I am looking for the reverse of BULK INSERT
>