Creating Stored Procedures

Article
04/01/2012

All stored procedures must be associated with a common language runtime (CLR) or Component Object Model (COM) class in order to be used. The class must be installed on the server — usually in the form of a Microsoft ActiveX® dynamic link library (DLL) — and registered as an assembly on the server or in an Analysis Services database.

Stored procedures are registered on a server or on a database. Server stored procedures can be called from any query context. Database stored procedures can only be accessed if the database context is the database under which the stored procedure is defined. If functions in one assembly call functions in a different assembly, you must register both assemblies in the same context (server or database). For a server or a deployed Microsoft SQL Server Analysis Services database on a server, you can use SQL Server Management Studio to register an assembly. For an Analysis Services project, you can use Analysis Services Designer to register an assembly in the project.

Security Note
COM assemblies might pose a security risk. Due to this risk and other considerations, COM assemblies were deprecated in SQL Server 2008 Analysis Services (SSAS). COM assemblies might not be supported in future releases.

Registering a Server Assembly

In Object Explorer in SQL Server Management Studio, server assemblies are listed in the Assemblies folder under an instance of Analysis Services. Server assemblies can contain both .NET (CLR) assemblies and COM libraries.

To create a server assembly

Expand the instance of Analysis Services in Object Explorer, right-click the Assemblies folder, and then click New Assembly. This displays the Register Server Assembly dialog box.
For Type specify the type of assembly:
- For a managed code (CLR) DLL, specify .NET Assembly.
- For a native code (COM) DLL, specify COM DLL.
For File name, specify the DLL containing the stored procedures.
For Assembly name, specify a name for the assembly.
If this is a debug build of the library that you are going to use to debug stored procedures, select the Include debug information check box. For more information about debugging stored procedures, see Debugging Stored Procedures.
You can click OK to register the assembly immediately, or on the dialog box toolbar, you can click a command on the Script menu to script the registration action to a query window, a file, or the Clipboard.

After you register a server assembly, you can configure it by right-clicking the assembly in Object Explorer and then clicking Properties.

Registering a Database Assembly on the Server

In Object Explorer in SQL Server Management Studio, database assemblies are listed in the Assemblies folder under an Analysis Services database. Database assemblies can contain both .NET (CLR) assemblies and COM libraries.

To create a database assembly on a server

Expand the instance the Analysis Services database in Object Explorer, right-click the Assemblies folder, and then click New Assembly. This displays the Register Database Assembly dialog box.
For Type specify the type of assembly:
- For a managed code (CLR) DLL, specify .NET Assembly.
- For a native code (COM) DLL), specify COM DLL.
For File name, specify the DLL containing the stored procedures.
For Assembly name, specify a name for the assembly.
If this is a debug build of the library that you are going to use to debug stored procedures, select the Include debug information check box. For more information about debugging stored procedures, see Debugging Stored Procedures.
You can click OK to register the assembly immediately, or on the dialog box toolbar, you can click a command on the Script menu to script the registration action to a query window, a file, or the Clipboard.

After you register a database assembly, you can configure it by right-clicking the assembly in Object Explorer and then clicking Properties.

Registering a Database Assembly in a Project

In Solution Explorer in SQL Server Data Tools (SSDT), database assemblies are listed in the Assemblies folder under an Analysis Services project. Database assemblies can contain both .NET (CLR) assemblies and COM libraries.

To create a database assembly in an Analysis Service project

Expand the instance the Analysis Services database in Object Explorer, right-click the Assemblies folder, and then click New Assembly Reference. This displays the Add Reference dialog box. The .NET tab of the Add Reference dialog box lists existing .NET (CLR) assemblies, while the Projects tab lists projects.
You can click an existing component or project and then click Add to add it to the Analysis Services project. To add a reference to a COM DLL, click the Browse tab to find the file. The Selected projects and components list shows the name, type, version, and location for each component that you are adding to the project.
When you are finished selecting components to add, click OK to add them to the Analysis Services project.

Script Format For an Assembly

Registering a .NET assembly is fairly simple. A .NET assembly is added to a database in binary format using the following format:

<Create>
   <ObjectDefinition>
      <Assembly>
         <Files>
            <File>
               <Name>filename</Name>
               <Type>filetype</Type>
               <Data>
                  <Block>binarydatablock</Block>
                  <Block>binarydatablock</Block>
                  ...
               </Data>
            </File>
         </Files>
         <PermissionSet>PermissionSet</PermissionSet>
      </Assembly>
   <ObjectDefinition>
</Create>

Share via