Stored Procedure

Question

Hi What is an extended stored procedure. Can you instantiate a COM object by using T-SQL ?

Jignesh Trivedi · Answer

hi In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures Functions Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation thread management or iteration methods which can be complicated for T-SQL. please refer http://msdn.microsoft.com/en-us/library/ms164653.aspx http://msdn.microsoft.com/en-us/library/ms190790%28v=sql.105%29.aspx hope this help.

Vulpes · Answer

They're stored procedures contained in a dll which (in effect) extends the capabilities of SQL Server. Before it can be used a system administrator needs to regiser each extended stored procedure with SQL Server by using the sp_addextendedproc system stored procedure. It's possible to instantiate COM objects in SQL Server by using an OLE Automation Stored Procedure which is an extended stored procedure with an sp_OA prefix. Although still supported extended stored procedures have been deprecated in SQL Server 2012 for security reasons and will be removed in a future version. CLR integration should be used instead.