What is the expansion of storage process?
Extended stored procedure that you can use as the C programming language to create your own external routines. On the users, extended stored procedures and general storage process, the implementation of the same methods. Parameters can be passed to expand storage process, extended stored procedure can return results can be returned to the state. Extended stored procedure can be used to extend Microsoft ® SQL Server ™ 2000 function.
Expansion of the SQL Server stored procedure can be loaded dynamically and implementation of the dynamic link library (DLL). Extended stored procedures in SQL Server directly address space operations, the use of SQL Server and open data services (ODS) API programming.
Better prepared to expand storage process, the sysadmin fixed server role can be a member of the SQL Server extended stored in the registration process, and then grant other users the implementation of the process privilege. Extended stored procedure can only be added to the master database.
Prepared to expand the use of C # stored procedure
Below Take a simple example to demonstrate how to use C # prepared by the extended stored procedures.
First, we create a simple C # class library documentation:
/ / C # file: Csserver.cs
Using System;
Using System.Runtime.InteropServices;
Using System.Reflection;
Using System.Runtime.CompilerServices;
[Assembly: AssemblyTitle ( "CSServer")]
[Assembly: AssemblyDescription ( "Test SQL. NET interop")]
[Assembly: AssemblyVersion ( "1.0.0.1")]
[Assembly: AssemblyDelaySign (false)]
[Assembly: AssemblyKeyFile ( "MyKey.snk")]
Namespace SQLInterop (
(Public interface ITest
String SayHello ();
)
[ClassInterface (ClassInterfaceType.AutoDual)]
Public class CsharpHelper: ITest (
Public string SayHello () (
Return "Hello from CSharp";
)
)
)
Then create sn-k with the creation of a strong class library of key documents, and the compiler.
Sn-k MyKey.snk
Csc / t: library Csserver.cs
Registration Class:
Regasm / tlb: Csserver.tlb csserver.dll / codebase
Such an expansion of storage on the preparation of the registration process finished, we have the following sql server in the test results.
T-SQL stored proc.
DECLARE @ object int
DECLARE @ hr int
DECLARE @ property varchar (255)
DECLARE @ return varchar (255)
DECLARE @ src varchar (255), @ desc varchar (255)
-- Create Object examples.
EXEC @ hr = sp_OACreate 'SQLInterop.CsharpHelper', @ object OUT
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
-- Calling object.
EXEC sp_OAMethod @ @ hr = object, 'SayHello', @ return OUT
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
PRINT @ return
-- Destruction object instance.
EXEC sp_OADestroy @ @ hr = object
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END |
