Niels SQL Server Blog

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

I have the pleasure to introduce the SqlClrProject project type.

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.
• Versioning of assemblies in SQL Server.
• Ability to alter an assembly and only deploy newly added methods from the assembly.
• The ability to use attributes to define in CLR code the size, precision and scale of the parameters and return types that will be created in SQL Server.

It can be used with any SKU of Visual Studio 2005, and you get it from here. What you down-load is a zip file. Un-zip the file to some arbitrary directory and read the README.txt file in the root directory.

Comments are as usual very welcome!!!

As Bob is doing, whenever a new build is released I tend to check out various stuff to see if it works as before. This however jumped up and bit me the other day.

You create two databases, say SSB1 and SSB2, in the same server instance. You have learnt your lesson from the April CTP that you have to create a master key in the database if you want to start a dialog conversation with encryption (which is the default). Now you create your various SSB objects in the two databases (obviously using SSB Admin, which makes life so much easier <g>).

You start the dialog and send a message, go over to the other database, selects from the queue and … no message!! What the ****. So you go back to the initiator db and sure enough, in sys.transmission_queue you find your message with an error saying something like: "This message could not be delivered because the user with ID 1 in database ID 6 does not have permission to send to the service. Service name: 's2'. " 

Hmm strange, the user is dbo in both databases, what goes on??? Well it turns out that MS has tightened up the security a bit, so in this CTP (June) you need to create/alter your database as TRUSTWORTHY: “alter database db_name set trustworthy on”. In coming CTP’s you’ll also need to do some more stuff, like granting the owner of the database certain permissions in the other database. That is however topic for another blog-post.

I got back from a SQL Server 2005 gig in Spain late last night. I won’t be home for long though, later today I’m off again, this time to London for a SQL Server class and then at the end of next week I’m flying off to India.

Anyway, Spain was a real blast, and the students were great!! If anyone of you are reading this – I had a great time. Thanks guys!!! BTW, the code is up at the location I said it would be.

I have updated my yukondeploy task application for the June CTP of SQL Server. A couple of changes:

• ALTER ASSEMBLY is now fully implemented. In other words, you can change your assembly, add methods etc. and instead of re-deploying the assembly you set the Alterassembly property to true and the task will make sure that only new methods etc are created. The SQL Server Project in Visual studio does not do this.
• In the documentation I have added instructions how to set up Visual Studio so you can deploy assemblies etc. from inside of VS without having to shell out to a command window.

For more info about what the task does, read the documentation in the docs folder of the download. You can download it from here.

I’d love feedback – what’s good, bad, ugly and what features you’d like to see in future versions.