Niels SQL Server Blog

Debugging in SQL Server 2008

nielsb — Wed, 16 Jul 2008 16:44:00 GMT

As good as SQL 2005 was (well, still are), one disappointment was that you needed Visual Studio if you wanted to debug your stored procedures. Seriously, what was MS thinking when they did that, especially as in SQL 2000, Query Analyzer had debug capabilities?!!

Anyway, today I am playing around, errm - doing serious stuff in the RC0 release of SQL Server 2008, and just by coincidence notice that there is a debug menu entry in the toolbar(how blind can one be - I must have been looking at that toolbar quite a few times). So I wrote some T-SQL code, put in a couple of breakpoint and hit Alt + F5, and lo and behold - my bp’s were hit and I could step through the code. I then wrote a very basic stored proc, wrote some code that called the proc, put a bp at the call into the proc and executed. When the execution stopped at the bp I hit F11 and I stepped into the proc - WoHoo!!! Call me sad, but stuff like this make me happy!!

Now, let’s hope that MS will keep this feature in and not pull it at the last minute - anyone remember the XQuery designer in one of the very early SQL 2005 beta’s??

SqlClrProject version 2.6 Released

nielsb — Wed, 27 Feb 2008 09:59:00 GMT

I have just released a new version - 2.6 - of the deployment tool for SQLCLR assemblies. It is a minor release, but it implements some changes that are fundamental for coming versions and also fixes a couple of minor bugs.

The download page for it is here, and if you want general information about what the SqlClr project is, you should go here.

SQL Server 2008 February CTP Available

nielsb — Wed, 20 Feb 2008 09:51:00 GMT

Euan posted late yesterday evening that SQL Server 2008 February CTP (CTP 6) has been released. Grab it from here (one of the pages are dated November 2007, but the link will take you to the correct download).

Update of SQLCLRProject

nielsb — Wed, 30 Jan 2008 08:11:00 GMT

I’ve had some feed-back about SQLCLRProject (thanks Doug et al!), and based on that fixed some issues.

Read all about it, and get the updates on the download page.

As always, comments and suggestions for improvements are very welcome!!

Niels

Like Phoenix Rising ... or New Version of SQLCLRProjct

nielsb — Mon, 14 Jan 2008 15:37:00 GMT

Hi everyone, it’s been a while (shame one me!)!!

As some of you may know, back in the days I developed a tool for deploying .NET assemblies to SQL Server 2005 (or, as it was called then, Yukon). Initially it was just a tool used from the command line. As time went by, it evolved into a project named SQLCLRProject, consisting of the command line tool (YukonDeploy), a stand-alone front-end GUI, DeployProperties, and an add-in (with project and item-templates) for Visual Studio, DeployAddIn.

The latest release of SQLCLRProject was back in February 2006 (wow, that was a long time ago ) , and up until a month or so, nothing much was done to it. I used it whenever I did any SQLCLR work and I know other developers were using it as well. Anyway, a while back I started receiving emails from people wondering if I could fix some “undocumented features” and/or implement some new features.

So, I decided to resurrect the project and the last weeks I have been working on fixing the issues and adding some more features. I’m fairly happy with it as it is right now, and today I release version 2.5. The download page for it is here.

So what has been done:

The project has now it’s own web-pages, so I have a place to point people to when explaining what SQLCLRProject is.
- main page
- download page.
The Visual Studio add-in (and templates) supports both VS 2005 as well as VS 2008
The VS add-in (as well as the other tools) supports both SQL Server 2005 as well as SQL Server 2008 (Katmai).
The previous version of the tool allowed you to re-deploy a UDT without manually dropping tables with columns based on the UDT. The tool either dropped the whole table or just the column (based on a configurable setting), before re-deploying. In this version the choice is as before to either to drop the whole table or the column. However if choosing to drop the column:
- first the table is altered and a new column is added (varchar(max) or varbinary(max) - also based on a configurable setting),
- then the data from the original column is copied over to the new column
- finally the original column is dropped.
In T-SQL we have the notion of procedure parameters with default values. In .NET we don’t have anything similar (well, VB.NET has optional parameters, but that is a compiler hack). The tool now allows you to, by using an attribute, defining parameters in your .NET code that will be created as T-SQL object with default values.
Fixed a bug where the add-in for VS could not handle project with white spaces in the path.

So, if you are interested, go to here to read more about SQLCLRProject and if you want to download; the download page is here.

Comments etc are always welcome, post a comment here (or on the main page) or drop me an email.

Niels

Rebooting the Blog

nielsb — Mon, 21 Aug 2006 07:42:00 GMT

I’ve been blogging on and off (mosly off) for a couple of years now, and blogging has been trying to quit smoking - but the opposite: you keep it up (blogging) for a while but then you fall back into the old habits (not blogging).

I have decided to give it a final go; I’ll try to blog with some frequency, if I can not do that for a sustained period I have told myself to give it up totally. In conjunction with trying to get back to blogging I have also decided to move to a wordpress.com hosted blog. For a little while I may do some cross-posting to here, but I suggest that you go over to my wordpress blog.

In this blog I’ll cover (as before) topics regarding databases (SQL Server in general), data access technologies (ADO.NET, LINQ etc). As I have recently switched to Mac and OSX I may also write about the experiences switching to Mac after having been a Windows user for a looooooong time.

I’ll try and do a re-direction of the feed from here to the new feed at wordpress, but I can not guarantee that I will succeed in doing that, so if you are interested in my rants please re-subscribe at: http://nielsb.wordpress.com/feed.

The Devil Wears Skates ...

nielsb — Sat, 13 May 2006 13:22:00 GMT

..... the hell has frozen over. What am I on about?

I wrote in my earlier post today that I was checking out a new blog client, and that the reason for it would become clear in a future post - well this is it. I have trough out my whole computing life been a Microsoft/Windows guy through and through. I started way back in the Windows 1.0 days and I have been faithful ever since.

Actually that is a lie, I had a fling with Red hat Linux for two days - but that is all. It has been Microsoft and Windows for me. I have been beta testing all the various Windows releases, and I must say I have been fairly happy with them all (well, I don't know about Windows Bob), and have used them in production way before they've RTM:ed.

Until now that is, and Vista. When I first heard about Longhorn, I was really, really excited and thought this was going to be great - that was PDC 2003 BTW. Well, we know what happened, but I was still excited and looked forward to the beta cycle for the "new" Longhorn - Vista.

For each beta release I have installed it, tried to work with it and as quickly un-installed it. For me Vista is almost the new Bob! Its is fancy window dressing, but not much more. And most of the added features of Vista can be had with other OS:es. One huge difference is that, especially when it comes to security features, they are usable in other OS:es compared to Vista.

Now, here's the part why hell has frozen over; I have switched to OSX. Ever since Apple decided to go Intel, I have glanced towards the Apple space. I guess what pushed me over was Parallels. As I still make my living teaching in the Windows space, I can now have the best of both worlds, running OSX as my host and Windows virtual machines. For the last couple of weeks. I have used OSX for a while now, and I must say it is a really nice OS (heck, any OS that includes Emacs must be OK). I have had surprisingly few issues, it just works.

So, there we go. I will undoubtedly install Vista, at least on a VM, cause Vista will have some really nice features that I am extremely interested in; the transactional filesystem to mention one - but for now my main OS is OSX.

Updated Version of SqlClrProject

nielsb — Tue, 10 Jan 2006 02:15:00 GMT

Back in December (26:th to be exact) I released a new version of the SqlClrProject and wrote about it here [0].

One drawback of the deployment functionality was that you could only deploy locally as the deployment process used the CREATE ASSEMBLY based on the location of the assembly; i.e. CREATE ASSEMBLY myasm FROM 'path_to_dll'.

Well, this is now fixed, as I have chenged the code so it now uses the binary representation of the assembly. You who know your CREATE ASSEMBLY stuff may think "what about dependent assemblies?" as dependent assemblies are not automatically deployed when using the binary representation. Well, the code handles that as well, it uses reflection to see what the dependent assemblies are and retrieves the binary representation of those as well during deployment.

So if you have installed the original SqlClrProject which was available from here [1], there is now an update here [2]. Download the update, un-zip and follow the readme.txt. The original download has now been updated as well, so if you haven't retrived it yet, go and get it [3].

[0]: http://staff.develop.com/nielsb/PermaLink,guid,200487e5-f2e2-449c-8a3f-ee28e6aa8735.aspx

[1]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

[2]: http://staff.develop.com/nielsb/code/yukondeployupdate.zip

[3]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

SQL Server Service Broker Admin Tool for SQL Server 2005 RTM

nielsb — Tue, 27 Dec 2005 13:53:00 GMT

Back in July I released the (then) latest version of SsbAdmin. For you who don'r know what it is; it's a tool which allows you to graphically administer SQL Server Service Broker.

I have now uploaded a new version which is compiled against the released versions of SQL Server 2005 and .NET 2.0.

You can download it from here: [0].

After download, un-zip to some directory and read the README.doc file.

[0]: http://staff.develop.com/nielsb/code/ssbadmin.zip

New release of SqlClrProject

nielsb — Mon, 26 Dec 2005 08:04:00 GMT

I thought I better post before this year ends. :-)

Anyway, you may know that I have developed a MSBUILD task dll for deployment of SQLCLR assemblies. I also created some templates for use from with Visual Studio 2005, the SQLCLRProject.

I have re-created this as an Add-In for VS, so you now have the ability to automatically deploy from inside of VS as well as debug! I have also created a GUI front-end for those who don't use VS which allows deploying of assemblies using this front-end (setting properties etc). So what exactly does all this do:

The SqlClrProject is a project type for Visual Studio 2005 for creation and deployment of assemblies to SQL Server 2005. It consists of templates for both Visual C# (C#) and Visual Basic (VB) with skeleton code for creation of CLR methods to be used as stored procedures, User Defined Functions (UDF's), triggers, User Defined Types (UDT's) and User Defined Aggregates (UDA's). Some of the features are:

Ability to deploy the assembly to SQL Server and create the SQL Server methods, all from inside the Visual Studio IDE.
Automatic creation of T-SQL deployment scripts.
Automatic creation of T-SQL DML scripts for testing of the created objects.
Ability to alter an assembly and only deploy newly added methods from the assembly.
Ability to create objects in a non default schema "schemaname.objectname".
Debug facilities from inside VS.

For you who have used the SQL Server Project project type from VS to do this, you may wonder what the differences are. Things that the SqlClrProject allows you to do, which the VS SQL Server project can't do:

Automatic creation of T-SQL deployment scripts.
Automatic creation of T-SQL DML scripts for testing of the created objects.
Ability to alter an assembly and only deploy newly added methods from the assembly.
Ability to create objects in a non default schema "schemaname.objectname".

Anyway, if you are interested you can download all needed from here: [0].

Un-zip the file and read the README.txt file in the root directory.

Comments are as usual very welcome!!!

Finally I wish you all a very Happy New Year!!!

[0]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

Test from Blogjet

nielsb — Thu, 04 Aug 2005 10:58:00 GMT

This is just a test

SQLCLR and CAS in SQL Server 2005

nielsb — Sat, 30 Jul 2005 00:32:00 GMT

By now, you who have created .NET assemblies for execution in SQL Server 2005, know about the three permission sets used when cataloguing an assembly:

SAFE
EXTERNAL_ACCESS
UNSAFE

So, for example, if you need to access things outside of the database you assign the permission set EXTERNAL_ACCESS to the assembly. But what about when you do not want to allow an assembly to everything that is possible for that particular permission set? You do not want the assembly for example to be able to do File IO, but it should be allowed to do external data access.

For those scenarios you can use CAS policies, exactly as you’d do with a “normal” .NET assembly. What, – I can hear you ask - can you actually edit permissions in the SQLCLR permission sets? No you can not (AFAIK), but you can create your own CAS permission set and apply that to the assembly.

In the .NET 2.0 Configuration Wizard, create a new permission set under the, for example, Machine level, and give that permission set the permissions you want to allow. One necessary permission is the Security permission; without it your assembly will not load. Having created the permission set, you create a new code group, choose the condition type (a Strong Name condition is good). Finally you assign the permission set to the code group. Re-start SQL Server, execute your code and see how your permission set works inside SQL Server. Oh, one last thing you need to do is at the properties dialog for the code group under the General tab, check the first check-box in the “If the membership condition is met:“ group.

If you change your permission set you need to re-start SQL Server for them to take effect as the permissions are parsed during start-up.

I wish I could say it was due to my brilliance (hah) that I found this out, but this was a thing that came up in class last week (guys – you were brilliant, I had so much fun). We spoke about the three levels of permissions in SQLCLR, and Bob – who taught the module in question – said something about being able to assign CLR permissions to SQLCLR assemblies. Neither me nor the students, really believed him (and he wasn’t sure himself) so we decided to try it out. Most of the hard work was done by one of the students, unfortunately I’ve forgotten his name but if you read this, please leave a comment – praise to who praise is due.

SQL Server Service Broker Examples

nielsb — Sun, 17 Jul 2005 07:25:00 GMT

Recently there has been questions on some of the newsgroups about examples for SQL Server Service Broker. So, yesterday on a flight back to England I crofted up three different SQL Server projects as examples:

LocalSample
RemoteSampleServer1
RemoteSampleServer2

All three examples are jus your very basic “Hello World” example, but they show:

LocalSample – communication between two databases on the same SQL server instance. MasterKeys in a database and the database being TrustWorthy
RemoteSampleServerX – These two samples are meant to run on two different machines/instances and in the samples you set up both transport security and dialog security by using Certificates.

Download the zip file from here [0], unzip and read the README.txt file, and – Have Fun!!

[0] : http://staff.develop.com/nielsb/code/servicebrokerexamples.zip

New Release of SQL Server Service Broker Admin tool

nielsb — Wed, 13 Jul 2005 09:29:00 GMT

Back in May I released the, then, latest version of SSB Admin; an administration tool for SQL Server Service Broker. Since then I have been travelling a lot (Portugal, Spain, India – to mention a few places) and not have had time to do much work with it. However, there has been some development and today I have uploaded a new version of SSB Admin. Some new stuff:

Added functionality to create users and logins. This was needed in order to create Certificates and Endpoints (see below).
Added functionality to create Service Broker endpoints.
Added functionality to create certificates.
Changed the behavior when looking at a service. Previously you’d only see conversations for that service which were in a “conversing” state. It is now changed so you’ll see all conversations (including ended, errors etc.).
Added the functionality to be able to filter what conversations to see when looking at a service. You can filter on Source (Target or Initiator) and/or State.

Download it from here and unzip it. Then read the README.doc document.

As always, comments, flames etc. are very welcome.

SQL Server 2005 Service Broker Challenge is Over

nielsb — Fri, 24 Jun 2005 03:41:00 GMT

Solution to BrokerChallenge 0:

It has been a week since I posted BrokerChallenge 0 and I am quite happy to see several people successfully cracked it. Still others gave it a shot but were not completely successful. So I decided to post the solution to the challenge. You can get the sample T-SQL script from here and modify it to suit your hostname, port and database name…

[Via Write Ahead Blog]

The challenge is over, and I never got into the Broker Hall of Fame. :-( I don’t know why customers don’t want to open up ports in their firewalls: “Hey Mr Customer, can you please open port 4022 in your firewall. I need to have that port open to be able to get into the Broker hall of Fame”. They just look at me strangely and walks away, hmpf!!

Congrats to all of you that managed to solve the challenge. Maybe in the future we’ll have some tools that helps us with setting this up.

Stay tuned for new challenges from Rushi!!