April 1, 2007 at 11:59 pm
Hi,
I want to create an excel file from stored procedure through sql server.
Any suggestions please?
Thanks,
Madhuri
April 2, 2007 at 9:26 am
You can export SQL Server data into excel file without using stored procedures.
April 2, 2007 at 9:48 pm
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
Change is inevitable... Change for the better is not.
April 2, 2007 at 9:50 pm
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
Change is inevitable... Change for the better is not.
April 2, 2007 at 11:48 pm
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
April 3, 2007 at 6:57 am
might be easier to create the spreadsheet and populate via a sp
April 3, 2007 at 7:29 am
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.
April 3, 2007 at 8:27 am
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.
August 18, 2008 at 10:14 pm
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
August 20, 2008 at 5:02 am
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
August 20, 2008 at 9:48 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply