Export SQL Database Tables to Excel (User Destination)

[Igawa29]

Good Morning!

I haven't dealt much in exporting to Excel from SQL so I was hoping I could be pointed in the right direction. I am trying to export all my tables (22) from SQL Server into Excel, then zip the entire set. I also want to allow the user to decide where the zip file will be saved at through a window's popup.

If exporting to Excel would be too resource heavy, I was debating if exporting to Access would work instead.

Basically I am trying to figure out how to backup the database. Normal methods are failing since the SQL Server is housed on a virtual machine that will not allow me to use normal backup methods to save outside that virtual machine.

Thanks!

 

[SkipVought]

Hi,

Max row count for any table exceed 1,048,576, assuming Excel 2007 or greater?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jebenson]

the SQL Server is housed on a virtual machine that will not allow me to use normal backup methods to save outside that virtual machine."

This sounds a bit off. Can you run a Maintenance Plan in SQL Server? Can you copy the resulting backups to another computer? I ask this because I have SQL Server running on a few virtual machines, and I have no trouble at all backing them up. I can use the Maintenance Plan method, as well as the SQL Server Agent in ArcServe Backup.

Anyway, if you really can't run a backup, take a look at SQL Server Integration Services (SSIS):

https://msdn.microsoft.com/en-us/library/ms141026(v=sql.110).aspx

How to run an SSIS package from a .NET application:

http://microsoft-ssis.blogspot.com/2012/09/call-ssis-2012-package-within-net.html

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Igawa29]

Max row count for any table exceed 1,048,576, assuming Excel 2007 or greater?

Yes it will be using Excel 2010

This sounds a bit off. Can you run a Maintenance Plan in SQL Server? Can you copy the resulting backups to another computer? I ask this because I have SQL Server running on a few virtual machines, and I have no trouble at all backing them up. I can use the Maintenance Plan method, as well as the SQL Server Agent in ArcServe Backup.

I can try this route, I was using SQL Management Studio and the virtual machine that my database sits on is being regulated by another area at work than mine. So my guess is they are locking some features down. I am also having to create triggers for audit reporting rather than using some built in functionality as well.

Anyway, if you really can't run a backup, take a look at SQL Server Integration Services (SSIS):

https://msdn.microsoft.com/en-us/library/ms141026%...

How to run an SSIS package from a .NET application:

http://microsoft-ssis.blogspot.com/2012/09/call-ss...

Thanks for the suggestion, I have a co-worker who is familiar with SSIS.