How to export sql server data into multiple excel sheets ?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Data Warehousing » Data Transformation Services (DTS) » How to export sql server data into multiple...

Add to briefcase

34 posts, Page 2 of 4

««1 2 3 4 »»»

How to export sql server data into multiple excel sheets ?

Expand / Collapse

Author

Message

a1_technology_geb

Posted Thursday, May 01, 2008 9:13 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, July 29, 2011 5:53 PM
Points: 11, Visits: 85

This is great. I am having trouble actually getting the dynamic execution of the openrowset call. That way I can feed the path variable and sheet name from the loop I created to call a stored procedure with the excel file insert query inside.

Thanks for the reply, I really appreciate it.

Post #494024

Posted Monday, May 05, 2008 12:39 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:34 AM
Points: 449, Visits: 1,772

Hi there A1_Technology_geb,

show me what you have so far and I will try to give you some pointers.

regards

GermanDBA

Regards,

WilliamD

Post #494794

Posted Wednesday, May 07, 2008 1:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 07, 2008 1:35 AM
Points: 6, Visits: 18

I have create a SSIS package .Now i m trying to schedule this SSIS in sql agent jobs.

but i m geeting error. unable to run it.

pls. help me how can i run this?

steps of creating jobs in sql server 2005 ??????????????????

is it any credential required for this?

Post #496119

Posted Thursday, May 08, 2008 6:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 195, Visits: 1,252

You say that you don't want to use DTS or SSIS.
Have you consider to use Excel and VBA instead?
If you have Excel installed at the "production machine" then think about it.
The nice thing is that you have much more control of Excel formulas and formats
and can combain the figures with filtering and charts. Your result is more "reportlike"
including analysing/filtering/pivoting options if you like.
I do a lot of jobs creating and distributing Excel files and started with DTS and
stored procedures but now I do it all in Excel and VBA.
If you already are familiar with Excel and VBA the step to add ADO with Database functions is not scaring.

Post #497063

peter.roethlisberger

Posted Thursday, May 29, 2008 3:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 5:54 AM
Points: 1, Visits: 42

Fantastic article ! Helped me a lot !

Post #508250

Jim Russell-390299

Posted Friday, May 30, 2008 5:22 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403

I much prefer treating this requirement as a pull rather than a push. Make each query into a view, and give your user(s) select access to the view.
Then create a workbook, and for each tab, install a "Data/Import External Data/New Database Query" to present the results of each view. Set the query data range properties to "refresh data on file open" (and other settings as you like.)

The advantages:
1. It is easier to implement
2. The user always sees the most current query results
3. To the user, there is no visible difference between a pre-populated workbook and one that gets populated when it is opened.
(Just warn them that changes they try to make to the data by hand will be lost.)

Post #508971

Posted Thursday, June 12, 2008 8:50 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

I am Balaji new to this discussion. My Code is::

exec xp_cmdshell 'copy C:\templates\BALAJI1.xls C:\export\BALAJI1.xls'

insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\export\BALAJI1.xls;;HDR=YES','SELECT * FROM [Sheet1$]')
select * from balaji1

ERROR:: Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'BALAJI1'.

Post #515993

Jim Russell-390299

Posted Thursday, June 12, 2008 10:19 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403

(Guesswork follows...)
Does "select * from balaji1" work without the insert statement? (Is balaji1 a table/view in the SQL Server database?) Might you need to further qualify the select statement source with db name, and schema?

Post #516082

Posted Thursday, June 12, 2008 11:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 5:09 PM
Points: 42, Visits: 194

I attach the word document that contains the definition of the stored procedures USP_DMOExportToExcel that allows you to export the result set of a query on Excel.

Call for example:

With SQL Server authentication:

USP_DMOExportToExcel
@SourceServer='ServerName',
@SourceUID= 'UserName',
@SourcePWD = 'Password',
@QueryText = 'use DBTEST Select Field1, Field2 From Table1',
@filename = 'C:\Table1.xls',
@WorksheetName='MyTable1',
@WorksheetIndex=1,
@RangeName ='MyRangeTable1'
With integrated security:

USP_DMOExportToExcel
@SourceServer='ServerName',
@QueryText = 'use DBTEST Select Field1, Field2 From Table1',
@filename = 'C:\Table1.xls',
@WorksheetName='MyTable1',
@WorksheetIndex=1,
@RangeName ='MyRangeTable1'

I hope it is useful, I usually use it for export data.

Sergio

Post Attachments

USP_DMOExportToExcel.doc (216 views, 95.00 KB)

Post #516108

Posted Friday, June 13, 2008 5:13 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

BALAJI1 IS A table which would contain the records present in the excel sheet.

Post #516560

« Prev Topic | Next Topic »

Add to briefcase

34 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions

Expand / Collapse