Bharat T. Motwani

SQL CLR Stored procedure deployment

2009-07-22T06:02:00.000-07:00

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

New Tech site

2008-03-30T23:46:00.000-07:00

Checkout this amazing site. www.techiecrossing.com

Homestead is cool

2007-11-06T00:34:00.000-08:00

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

http://www.homestead.com/~site/real-estate-website-design.ffhtml

http://www.homestead.com/~site/custom-website-design.ffhtml

http://www.homestead.com/~site/ecommerce-website-design.ffhtml

Gadget on Live.com

2007-05-04T00:23:00.000-07:00

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..

I am a Web Rockstar

2007-02-04T23:56:00.000-08:00

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).

One more post on /.

2007-01-22T22:01:00.000-08:00

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

Ganpati

2006-08-28T23:27:00.000-07:00

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

At Flickr

My First Submission on /.

2005-11-08T11:13:00.000-08:00

Wow,, finally the guys at /. (www.Slashdot.org) accepted one of my
submissions and its good to see my name on the /. front page..
Check out the /. page for
the article on History's Worst Software Bugs..

The submission is for an article on wired.com and its generated some
hits on my blog toooooo..
The wired article is at the foll. link

nnnnnnnnjoy..

Online Ver. of Windows.

2005-11-02T21:47:00.000-08:00

I just checked out this new online ver. of Windows which MS has just launched
and its cooooll..
Check it out at www.live.com
Windows Live is a set of Internet-based personal services, such as e-mail, blogging and instant messaging.

Permission error while debugging ASP app.

2005-10-03T23:34:00.000-07:00

Today i made a second copy of my ASP appln and tried debugging it.
My ASP app was accessing a COM+ component and the component was not in a compiled state.
I was running the component directly from the VB IDE so that i could debug the component also.

All this stuff is currently working in my current ver. of the ASP app but just as i made another copy of the ASP appln and tried to run it i got an error which goes like this..

Server object, ASP 0178 (0x800A0005) The call to Server.CreateObject failed while checking permissions. Access is denied to this object.

I tried to recollect the solun. to this cos i have faced this a lot of times and i finally got it at the following MS link..

http://support.microsoft.com/kb/259725/EN-US/

google helped me again in this and with just a single search..

Debugging ASP apps in Interdev.

2005-06-30T07:51:00.000-07:00

" What ? a technical post ",
yeahh i decided to start with posting some technical solutions to problems which we developers face in our day 2 day life..

Most of us work on VS 6.0 & Visual Interdev and are not aware that we can debug the Visual Interdev solutions / ASP apps just as we debug the normal VB apps. To enable Visual Interdev to debug below go thro the steps given below.

1. First Install a Working copy of Visual Interdev 6.0.

2. Next we need to install the Back Office components. These are typically present in the 5th CD of the Visual Studio 6.0 set.Insert this Cd and then select Launch Backoffice Installation Wizard after that select the following components to install.a. Remote Machine Debuggingb. Frontpage Server Extensions.(imp. these are the 2 apps which are needed for debugging ASP apps.)Complete the Backoffice Installation.

3. Next give a breakpoint in one of your ASP page and run your Visual Interdev solution.

The first time you run it there would be a message box which says. "This Project is not set up to enable ASP Debugging. Would you like to enable ASP Debugging on this Project ?" Select Yes on this message box (cos we need ASP pages to be debugged).
Now if you get another message box which says. "Unable to set server into Correct Debugging state automatically..." Select Yes over here also.
Now browse to the page where you have set up the breakpoint and the Visual Interdev app will go in the Debugging mode and you will be able to look into variable values, Immediate window etc.
If after the c. step you are still unable to debug the ASP application then stop the Interdev app perform the following steps.

a. Go To Control Panel --> Administration --> Internet Services Manager / Internet Information Service / IIS.
b. Here go to your Virutal Directory and right-click --> Properties.
c. Click on the Configuration button which is in the Directory tab. Select the Debuggin Tab in the Application Configuration window.
d. Check the boxes 'Enable ASP server-side script debugging' and 'Enable ASP client-side script debugging'
Run the Interdev application and you will be able to debug it now.

(imp. You will also need to enter the Administrators / Logged In Users password while running the Interdev app. So enter the correct password.)

This was it about Debugging the ASP scripts in Visual Interdev.

We can also similarly debug the Client-Side vb/javascript code in the ASP/HTML pages.

While running the ASP page in IE Webbrowser click on the menu View --> Script Debugger --> Open.
This will open the Script Debugger in Interdev after which you can debug your client code.
If you want to debug after say clicking on the Submit button then select View --> Script Debugger --> Break at Next Statement.
This will open the code at the onclick event of the Submit button.

This was a very brief overview about debugging the Interdev apps.

If you are still unable to make Debugging work youcan find the MSDN article at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvid/html/msdn_videbugging.asp

My 2nd Blog...

2005-06-27T06:06:00.000-07:00

Hi Evry1..

This is my 2nd blog after my prev. 1 on MSN spaces didnt work quite well.. Bloggers interface
is quite good and way ahead then the one on MSN Spaces... Lets see how this turns out..

Keep coming back in a while to check out my Posts...

Cya...