Service Broker GUID does not match the one in sys.databases

Sometimes you can get a problem when trying to configure Service Broker on a database that has been restored / copied from another source (e.g. to a DR server), or even when trying to enable Database Mail on an msdb database that has been copied from elsewhere.

The error is something along the lines of:

Alter failed for database 'xxx'

Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number)
ALTER DATABASE statement failed.

This is because the master database also keeps track of service broker for each database, so this is just saying the master database thinks service broker has last processed a different record to the the one the database believes.

In the situation above, where we know this is the case because the database has come from another source, the quickest fix is just to reset the service broker as follow:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

This will drop and recreate all service broker settings and queues - so please only use this if you really do want to reset everything, don't do it if you have queues with statements in that you want processing - but for a quick fix on a DR database it can save a lot of headscratching.

Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.

Net Send becomes MSG

OK, so it's not news, but NET SEND no longer is the favoured way to send messages to machines, and MSG is the new method - unfortunately it's not as easy to use, and by default doesn't even work!

NET SEND is built into SQL which is a problem when that's used as an alternative to email for getting alerts.  Getting it to use MSG instead can raise other problems too.

Basic syntax to use MSG to send a message to a user / workstation :- the main issue here is you need to know what machine the user is on, but unlike netsend, you can at least configure it to auto close, so you don't come in at 8am and find 5000 popups to click close on.

MSG * /SERVER:workstation001 /TIME:60 Hello I'm testing

Next issue is you are likely to get this error:

Error opening Terminal server Workstation001
Error [1722]:The RPC server is unavailable.

It seems that the latest versions of Windows, by default, do not allow the relevant RPC call

To fix, you need to open regedit, navigate to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TerminalServer

Open the AllowRemoteRPC registry entry, and change the value to 1

You should now be able to use the MSG command.

You can use this to get SQL alerts as follows:
Create a file (e.g. msg.cmd file) with entries for each persons machine you want notified, such as:
MSG * /SERVER:workstation001 /TIME:60 %1
MSG * /SERVER:workstation002 /TIME:60 %1

Create a SQL agent job that calls that file via a CmdExec type step, along with [A-MSG], such as
\\network\DBA\msg.cmd "$(ESCAPE_NONE(A-MSG)) FROM $(ESCAPE_NONE(SRVR)) "

I've then configured alerts (e.g. backupfailed, replication issues etc.) on failure response to execute this job.