Fixing a Broken Event Broker Queue

When a database is restored from a backup, a lot of times the Service Broker is disable. Therefore any actions scheduled by the Event Broker will not happen.

To check if an Event broker is enabled:

 SELECT is_broker_enabled, [name], database_id FROM sys.databases WHERE database_id = db_id()  

To reenable the broker:

 ALTER DATABASE NameOfTheDatabase SET ENABLE_BROKER 

Retrieving Identities on Bulk Inserts

If you need to insert a bunch of records at one time, and need to get the identity of all the inserted records, you can use a cursor.  But sometimes, using a cursor just feels wrong.  The “Inserted” key word comes to the rescue!  Use it like the following.  In SQL2008 they have further improved this so that you don’t even need the temp table.

     CREATE TABLE #newNotes (ID INT)  
     INSERT INTO Note (NoteText)  
         OUTPUT INSERTED.NoteId INTO #newNotes  
         SELECT Note.NoteText
             FROM Note  
             WHERE initial_report_number = @initialReportNum  
         INSERT INTO ClaimNote (claim_number, NoteId)  
   	     SELECT @claimNum, #newNotes.ID  
                 FROM #newNotes  
                 DROP TABLE #newNotes