SQL CLR Stored procedure deployment
Have you got this error while deploying an SQL CLR stored procedure ?
If yes, then look no further and check out the steps below to deploy a sample SQL CLR Stored procedure.
1/ Create a New SQL Server 2005 Database and name it "CLRTest"
2/ In order to set the correct permissions for the database to execute the SQL CLR stored procedure we need to set the Trustworthy property of the database to "true". By default the Trustworthy property is set to "false".
Execute the following statement to set the Trustworty property to "true"
ALTER DATABASE CLRTest SET TRUSTWORTHY ON
3/ Create a new user in SQL Server named "clrtestuser" and use the authentication type as "SQL Server Authentication".
Assign the db_owner permission to the "clrtestuser" on the "CLRTest" database.
4/ The next step is to grant the "external access assembly" permission to the "clrtestuser" user.
This can be done by running the following statement.
GRANT EXTERNAL ACCESS ASSEMBLY TO clrtestuser
5/ Create a new "SQL Server project" in Visual Studio 2005 named "CLRAssembly"
Add a new user-defined function to the project which will have the following code.
Partial Public Class UserDefinedFunctions
Public Shared Function MyFirstCLRFunction() As SqlString
' Add your code here
Return New SqlString("Hello")
6/ Change the database settings for the project "CLRAssembly" to point to the "CLRTest" database.
To make this change in Visual Studio 2005 go to Project --> CLRAssembly Properties
Go to the "Database" tab and set the Connection String to point to the "CLRTest" database. The connection string should use SQL Authentication and the user name "clrtestuser"
Go to the "Database" tab and set the Permission Level to "External"
7/ If you want to debug the SQL CLR stored procedure from Visual Studio 2005, then assign the "sysadmin" role to the "clrtestuser".
8/ The SQL CLR stored procedure can now be deployed using the "Build-->Deploy CLRAssembly" menu.
9/ If you want to deploy the SQL CLR stored procedure manually then build the "CLRAssembly" project.
Log on to SQL Server using windows authentication and run the following statement.
DROP FUNCTION fnMyFirstCLRFunction
DROP ASSEMBLY CLRAssembly
CREATE ASSEMBLY CLRAssembly
WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE FUNCTION fnMyFirstCLRFunction() returns nvarchar(200)
AS EXTERNAL NAME [CLRAssembly].[CLRAssembly.UserDefinedFunctions].[MyFirstCLRFunction]
Note: If a database has been backed up and restored and the above steps for creating an assembly are run on that database, then the assembly will not be installed and it will throw an error.