Express Yourself with Managed Database Types

I resisted the urge to post something about the latest range of Express versions of VS 2005 until I had something to say (maybe I should have waited longer..). I have been dying to check out “Yukon” for ages, especially since I think the ability to write user-defined types in managed code presents so many interesting opportunities for application design, and probably an equal number of chances to shoot yourself in the foot. Also the whole idea of it has always raised a lot of questions in my mind - How do you apply referrential integrity to a property? Can you create an index on a property? Can you create an index on a method? With all this in mind I installed SQL Server 2005 Express with a great deal of anticipation. I had heard in a few places that some products in the “Express” line downloaded a fair amount of stuff from the network. I was pleasantly surprised to see that the SQL Server 2005 Express install did not need any additional downloads. It installed without a hitch, requiring a single re-boot. Unsurprisingly it works fine with my current SQL Server 2000 install. It does install a new MMC snap-in type manager, but not much else in the way of graphical tools (command line query tools are included). I expect the full beta (probably available now to MSDN subscribers) will have “SQL Workbench“ or whatever it is called now included also.

I was not able to connect via query analyzer (from my SQL 2K install) but my Tech-Ed US CTP install of VS 2005 seemed quite happy to play with it though.

VB.NET MVP, data access afficianado and all-round nice guy Greg Low gave an interesting presentation at my local SQL Server Users Group last week on the CLR features in Yukon. It had lots of code demos (for yukon beta 1 - which might require some converting). The content for the presentation is here.

Update: I tried connecting via SQL Server 2000 enterprise manager (David Hayden commented that he was having some problems giving access to the ASPNET account on his machine, and I was trying to see if you could administer SQL 2005 Express from SQL 2K enterprise manager) but it failed with the message “You must use SQL Server Workbench or SQL Server Management Objects (SMO) to connect to this server.” Also SQL 2005 Express has a weblog http://weblogs.asp.net/sqlexpress/ and a newsgroup http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us

Tim Sneath has a great serise of articles on CLR integration in SQL Server 2005 (A.K.A Yukon) (1, 2, 3, 4)

Comments

David Hayden
Hi Joseph. I had no problem installing SQL Server Express as well and I have the same set-up as you.

Maybe you know the answer to this question. How do you give the ASPNET User account admin privileges in SQL Server Express without putting it in the Administrators group of the PC?

I am playing with Visual Web Developer Express and unless I put the ASPNET User in the Administrators Group, which I don’t like, I always get a failed login for MACHINENAME/ASPNET on my SQL Server Express databases that are hosted in web apps on my local machine. Am I overlooking some basic understanding? In SQL Server you just add ASPNET as a user with admin privileges and everything works fine. I don’t see a place to do that with SQL Server Express.
30/06/2004 4:11:00 AM
JosephCooney
Hi David - I’m as new to this as anybody else, but I imagine that the SQL adminstration stored procs like sp_grantdbaccess would still work for adding users to databases and roles. Also I have read in some places things that suggest you can connect to Yukon via SQL Server 2000 Enterprise Manager if you specify ‘MachineName\InstanceName’. If you can do that then you should be able to add users to databases etc. from within Enterprise Manager. I will try this out tonight and post an update.
30/06/2004 11:43:00 AM
Greg Low
Hi Guys,

Yep, you just create it (aspnet) as a login and put it in the fixed server admin role (if that’s what you want to achieve).

HTH,

Greg
1/07/2004 5:32:00 PM
yaip
I have both SSE 2005 Beta and VS.NET 2005 Beta. I have a mdf file in c:\intetpub\wwwroot.…So I do the following:

try
sp_grantlogin [newdell\apsnt]
go
use databasename
go
sp_grantdbacces [newdell\aspnet]

it doesn’t like the second command (use databasename). It tells me entry not found. What am I doing wrong?
22/08/2004 5:00:00 AM