user defined extended stored procedures
-
Does anyone have a good source with examples on how to write user defined extended stored procedures ?
TIA
Mathew J Kulangara
sqladventures.blogspot.com -
Ken Henderson describes it (in short terms) in The Guru's Guide to SQL Server Stored Procedures, XML, and HTML. You can probably find some resources online with Google.
Out of curiosity, what do you want to use xprocs for? Sometimes there are other solutions that are better, and/or in SQL Server 2005 we will be able to use CLR Integration for solving the same problems in a much easier way.
-
What I'd like to do is as follows..
I want to be able to generate excel reports where I would get information about formating etc from some metadata tables I have set up..as well as data. I know the easy way to create excel reports of this nature is use DTS packages, and vba automation on the excel side, or reporting services perhaps...but I want the functionality of being able create these reports on the fly just by updating the metadata tables..
So my thought would be to have something like this ..
xp_create_my_excel @report_id @save_location @file_name
-- in the dll ..instantiate an excel object..return the record set to the excel file..make the necessary formatting requirements..and save the file to a drive somewhere
The xp returns 1 for success or 0 for failure.
where the @report_id would get you everything you need to create the excel report (from metadata tables on the back end, and @save_location would give where the file would be located..and @file_name would be the name of the file.
Any thoughts ?
-
My general recommendation for scenarios such as this is that the DBMS should not be doing those kind of things. For this specific case I think you would even need to have Excel installed on the server (not sure of that though). It is really not part of it's job description.
I usually recommend someone to have some kind of polling client application that in this case would create the Excel files. You have much wider possibilities using for instance a .Net application for performing these tasks. Either have the application poll the metadata tables to look for changes, or add a trigger that writes 'workitems' for the application in a queue when the metadata tables are updated. The app polls the queue and performs the workitems as they come in.
This is good for two reasons: 1) It is much easier to implement this way with a better environment, and 2) you do not need to do a lot of work in the trigger that would make the transaction longer than necessary.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply