Call Excel functions from SQL stored procedure

Question

Call Excel functions from SQL stored procedure

BBGBP

SSC-Addicted

Points: 417
More actions
November 17, 2004 at 8:57 am

#60441

Does anyone know of a way or a resource to call an Excel function from a SQL stored procedure?
I've created a stored procedure to mimic an excel financial function, but it takes several minutes rather than seconds. If I can call the Excel function or pass a temporary table to Excel to perform the function and return a single value, that would help tremendously.
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Best way is to create an instance of excel within an activex script in a dts package, open the excel document, do the logic and then write back the results into a temp table or a global variable within the dts package.

Go to http://www.sqldts.com if you want to get a good starting point on dts packages if you know little about them.

------------------------------
The Users are always right - when I'm not wrong!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

What "financial function" are you talking about?

There are quite a few that can easily be transferred to T-SQL. And then you can use the sp_OA* procedures to instanciiate them as COM objects. You might want to search the Google archives for this, since SQL Server MVP Steve Kass posted quite a few examples on how to do this.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

BBGBP SSC-Addicted Points: 417 More actions · Answer 3

Thanks to you both for your quick replies.

I'm trying to use the XIRR function from Excel. I created a stored procedure which produces the same result as the function, given the same input, but it takes minutes to itterate enough times to get an accurate result.

Do you know of existing XIRR SQL code?

I'll check the website for the DTS info. Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I for one wouldn't do any Internal Rate of Return calculations with SQL. Why?

Read this and decide for yourself

http://www.intelligententerprise.com/online_only/celko/030303_1.jhtml

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

BBGBP SSC-Addicted Points: 417 More actions · Answer 5

I saw something similar. That's why I want to do it with Excel. The IRR calc has to be done off data in a SQL database and referenced in a Crystal Report.

What fun...

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 6

Here's some code I use to run an excel macro called update once my datawarehouse has finished populating. Use the macro to call the function you want.

Function Main()

Dim objExcel , i

Set objExcel = CreateObject("Excel.Application")

With objExcel.FileSearch

.LookIn = "\\<<Folder Path>>\"

.SearchSubFolders = True

.FileName = "*.xls"

If .Execute > 0 Then

For i = 1 to .FoundFiles.Count

On Error Resume Next

objExcel.Workbooks.Open .FoundFiles(i)

objExcel.Run "Update"

objExcel.ActiveWorkbook.Close(FALSE)

End With

Main = DTSTaskExecResult_Success

End Function

SQL Draggon Right there with Babe Points: 766 More actions · Answer 7

you have the option of creating a query in Excel or use DTS transform that brings the data into Excel. Then you can use any Excel function you want.

NOTE: You can't import more then 65K rows into Excel.

Good Luck.

Don't count what you do, do what counts.

SQL Draggon

dtamati SSC Enthusiast Points: 153 More actions · Answer 8

Have you tried using Report Servises. Thats if you can afford .net 2003. I have read it is possible to interact with excel.