How to create excel from stored procedure

Question

Post reply

How to create excel from stored procedure

madhuri-425013

Say Hey Kid

Points: 679
More actions
April 1, 2007 at 11:59 pm

#177547

Hi,
I want to create an excel file from stored procedure through sql server.
Any suggestions please?
Thanks,
Madhuri

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You can export SQL Server data into excel file without using stored procedures.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

First, you might want to tell how you'd do that.

Second, the request was specifically for how to do it from a stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Madhuri,

There are several ways to do this... most of them are covered in the following URL...

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 4

Hi Jeff,

Thanks for reply. I tried the options you suggested in URL.

When i tried Creating Excel spreadsheets via ADODB option

"Execute permission denied for SP_OACreate & SP_OADestroy objects under database master owner dbo.

When i tried Manipulating Excel data via a linked server i am getting error as

"User does not have permission to perform this action."

I am getting error when i tried to use OpenDataSource & OpenRowSet as

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."

I have a query and i need to create the result of query in excel file through stored procedure.

Please give some suggestions.

Thanks,

Madhuri

Brandt Smith SSC Veteran Points: 280 More actions · Answer 5

might be easier to create the spreadsheet and populate via a sp

zubamark Right there with Babe Points: 735 More actions · Answer 6

It can be done from the view or table.

Open excel>data>import external data>import data>new sourse>login to the server and choose from views or tables.

prashant-423665 SSC Journeyman Points: 86 More actions · Answer 7

Probably you can try this http://www.mssqltips.com/tip.asp?tip=1202 solution

To avoid error "Ad hoc updates....." db user must have db_securityadmin rights.

Best Regards,

Prashant

HoneyShyam SSC Veteran Points: 269 More actions · Answer 8

This is very nice.

Can we do this using any programming language to get the data from database using store procedure.

thanks for supports

Shyam

DominicHandel Valued Member Points: 61 More actions · Answer 9

Yes you can by using some VBA in the spreadsheet...specifically you would want to set up an ADO connection & recordset and then your SQL statement should be the name of the stored procedure (as string) ...and make sure your adcommandtype (or something similair - forget which) is enumerated as a command rather than straight SQL string.

Suggest you search VBA / ADO topics on google

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

Your application (whatever the language written in) can call up the stored proc.

In general, I found that downloading all relevant records to the application to let the application crunch them is considerably slower than using a stored proc. For small datasets it probably does not make much difference, but this does not scale very well.

The stored proc handles sets whereas I have seen applications looping trough the dataset one record at a time. Also, lots more traffic on the network.

The most extreme case I encountered was using a SELECT statement in Crystal Report v. 8 and 8.5 because it was not possible to preselect relevant records only in a WHERE clause, these earlier versions of CR did not allow passing a parameter usable in the WHERE clause. The record selection option simply meant that ALL records were downloaded and CR itself would discard the non relevant records. On small tables, the performance was acceptable. But when run against large tables, the same report tied up / froze the user's machine for MINUTES.

Removing the SELECT statement in the CR's SQLQuery and replacing it with an invocation of a stored procedure - which did allow using a proper where clause - made the same report considerably faster. SECONDS instead of MINUTES.

I do not know why you would prefer to avoid using a stored procedure, but be sure you really have a point to avoid it. Especially it the record crunching part is done in multiple steps and is within a transaction (if creating new records or updating others), which locks the tables involved.