Bharat T. Motwani

Wednesday, July 22, 2009

SQL CLR Stored procedure deployment

"CREATE ASSEMBLY for assembly 'CLRAssembly' failed because assembly 'CLRAssembly' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission."

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.

USE master
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO clrtestuser
GO

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.
"UserDefinedFunctions.vb"

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
<microsoft.sqlserver.server.sqlfunction()>_
Public Shared Function MyFirstCLRFunction() As SqlString
' Add your code here
Return New SqlString("Hello")
End Function
End Class


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
GO
DROP ASSEMBLY CLRAssembly
go
CREATE ASSEMBLY CLRAssembly
FROM 'C:\Projects\CLRAssembly\bin\CLRAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION fnMyFirstCLRFunction() returns nvarchar(200)
AS EXTERNAL NAME [CLRAssembly].[CLRAssembly.UserDefinedFunctions].[MyFirstCLRFunction]
GO


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.

Sunday, March 30, 2008

New Tech site

Checkout this amazing site. www.techiecrossing.com

Tuesday, November 06, 2007

Homestead is cool

Homestead is a cool site which has the following offerings.
True to its name, Homestead QuickSites whips up Web sites in a hurry. As with Bubbler, you can establish an attractive, functional Web presence without knowing the first thing about Web hosting or HTML. But whereas Bubbler uses a blog-tool-on-steroids approach (operating entirely from a local client), QuickSites is better thought of as a full-blown—though approachable—Web site creation tool and hosting service
small business website design
real estate website design
custom website design
ecommerce website design
Check out the links below.
http://www.homestead.com
http://www.homestead.com/~site/small-business-website-design.ffhtml

Friday, May 04, 2007

Gadget on Live.com

I got the Webrockstar Gadget posted on the Live.com site.
The gadget can be accessed by clicking on the following link

http://gallery.live.com/LiveItemDetail.aspx?li=24aec5c7-b4f8-4271-899a-3776a9ebddff

This is the Mumbai Live Videos gadget and can be added to the users Live Page..

Sunday, February 04, 2007

I am a Web Rockstar

Microsoft had a contest for the Web Rockstars in India.
I am one of the winners and get an XBox 360.
Check out the winners list on the WebRockStars site..

My Gadget for the contest was the "Mumbai Live Traffic Videos".
This gadget can be added by going to the http://live.com site
and adding the following URL

http://bharatmotwani.spymac.com/MumbaiVideo/MumbaiVideoGadget.xml

This gadget displays Live Videos from Mumbai//

Thanx "Microsoft" for the XBox (Which i am yet to get).

Monday, January 22, 2007

One more post on /.

Today one of my Posts was listed on the /. site. I tried to add the same post to indianpad.com but it did not turn up on the home page.

Its good to see one of my post back again on slashdot.org
The post was about "India Brings back Orbiting Satellite to Earth."
Check it out at http://science.slashdot.org/article.pl?sid=07/01/22/1344254

Monday, August 28, 2006

Ganpati

Yes.. its back again this year. and i have quite some snaps
Check the link below to view the snaps

At Flickr