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 with Expand/Collapse Feature

Hello,
I've searched the news group postings and have not found an answer. The
question is:
Is it possible to export a report from RS into excel and have the
expand/collapse feature in excel work?
Thanks,
RossIf your report has drill down the resulting Excel spreadsheet will have
expand/collapse
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ross_co" <ross_co@.discussions.microsoft.com> wrote in message
news:060EB67A-BB2E-47C3-8400-D088AEC095F1@.microsoft.com...
> Hello,
> I've searched the news group postings and have not found an answer. The
> question is:
> Is it possible to export a report from RS into excel and have the
> expand/collapse feature in excel work?
> Thanks,
> Ross|||I have 2 levels of drill down. The first level does expand/collapse. The
second level, the details, do not get exported unless that section was
already open in the report. I am using Excel XP.
Any ideas?
Thanks,
Neil
"Bruce L-C [MVP]" wrote:
> If your report has drill down the resulting Excel spreadsheet will have
> expand/collapse
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ross_co" <ross_co@.discussions.microsoft.com> wrote in message
> news:060EB67A-BB2E-47C3-8400-D088AEC095F1@.microsoft.com...
> > Hello,
> >
> > I've searched the news group postings and have not found an answer. The
> > question is:
> >
> > Is it possible to export a report from RS into excel and have the
> > expand/collapse feature in excel work?
> >
> > Thanks,
> > Ross
>
>|||I got the same problem but with Excel 2003.
"Neil Gould" wrote:
> I have 2 levels of drill down. The first level does expand/collapse. The
> second level, the details, do not get exported unless that section was
> already open in the report. I am using Excel XP.
> Any ideas?
> Thanks,
> Neil
> "Bruce L-C [MVP]" wrote:
> > If your report has drill down the resulting Excel spreadsheet will have
> > expand/collapse
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "ross_co" <ross_co@.discussions.microsoft.com> wrote in message
> > news:060EB67A-BB2E-47C3-8400-D088AEC095F1@.microsoft.com...
> > > Hello,
> > >
> > > I've searched the news group postings and have not found an answer. The
> > > question is:
> > >
> > > Is it possible to export a report from RS into excel and have the
> > > expand/collapse feature in excel work?
> > >
> > > Thanks,
> > > Ross
> >
> >
> >|||I have the same problem with RS2000 SP2, the drill down works on web, but
export to excel has no expand and collapse.
"Neil Gould" wrote:
> I have 2 levels of drill down. The first level does expand/collapse. The
> second level, the details, do not get exported unless that section was
> already open in the report. I am using Excel XP.
> Any ideas?
> Thanks,
> Neil
> "Bruce L-C [MVP]" wrote:
> > If your report has drill down the resulting Excel spreadsheet will have
> > expand/collapse
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "ross_co" <ross_co@.discussions.microsoft.com> wrote in message
> > news:060EB67A-BB2E-47C3-8400-D088AEC095F1@.microsoft.com...
> > > Hello,
> > >
> > > I've searched the news group postings and have not found an answer. The
> > > question is:
> > >
> > > Is it possible to export a report from RS into excel and have the
> > > expand/collapse feature in excel work?
> > >
> > > Thanks,
> > > Ross
> >
> >
> >|||I'm having a similiar issue - We have a report where with 2 levels where the
top is collapsable. It's fine when you view the report, but when you export
the report to Excel, it no longer has the collapsable operation - it jus
tlists each sub-item with ########.
Any ideas?
"Bruce L-C [MVP]" wrote:
> If your report has drill down the resulting Excel spreadsheet will have
> expand/collapse
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ross_co" <ross_co@.discussions.microsoft.com> wrote in message
> news:060EB67A-BB2E-47C3-8400-D088AEC095F1@.microsoft.com...
> > Hello,
> >
> > I've searched the news group postings and have not found an answer. The
> > question is:
> >
> > Is it possible to export a report from RS into excel and have the
> > expand/collapse feature in excel work?
> >
> > Thanks,
> > Ross
>
>|||Sorry, forgot to mention, this is for SRS 2005.