October 17, 2004 at 9:24 am
Hey Everyone
i would like to know how to create extended proc and what are the benefits of creating an extended procedure.
anyone could please help me out in this regard.
thanx
Rajiv.
October 17, 2004 at 11:43 pm
Extended stored procedures are a great way to get over the limitations of T-SQL.
If you installed the Development Samples with SQL Server you will have a Samples/ODS directory (under your SQL Server install directory) which has some sample source code for creating your own custom Extended Stored Procedures. Read through the examples, and try compling, registering and running them. They'll provide a great foundation to creating your own. You can find the samples in BOL under "samples, extended stored procedures".
Shipped with SQL Server are a number of extended stored procedures for things like : sending and reading email, accessing the registry, and the accessing the OS file system. Again, these extended stored procedures do things that the T-SQL language doesn't. Anything that you can program can be turned into an extended stored procedure.
There are some performance considerations, and definitely some security ones. Also, make sure that your code is clean, and efficient, and prevents buffer overruns everywhere. The last thing you need is a custom extended stored procedure that crashes SQL server and causes data loss. As far as security is concerned, permissions to extended stored procedures are the same as any other object. You can grant/deny access to whoever you want.You will want to restrict access to only the people who really need access. Do some research with google on extended stored procedure performance and security.
Of course, remember to step back from what you are doing and really consider, is this a task you need to do from within SQL Server, or are you better off creating a program that lives outside SQL Server? Is this a task that you can perform purely in T-SQL or does it require procedures that are beyond T-SQL? Like triggers, extended stored procedures are largely unneeded, and the task is better off executed in a different fashion.
Julian Kuiters
juliankuiters.id.au
October 18, 2004 at 1:35 am
You will need either C++ or Delphi experience to create the extended stored procedures.
The libraries for SQL Server 7 and SQL Server 2000 are different.
I would go with the suggestion that your first point of call is to see if there is an alternative way of doing things.
Given that the SQL Server has the sp_OA stored procedures and these can call ActiveX components this may be a better route.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply