Script to generate Nums table

Forever tracking this down so I thought I'd post it here so I can get it easily.


-- create table
CREATE TABLE dbo.nums (n INT NOT NULL PRIMARY KEY)
GO
DECLARE @rows INT

SET @rows = 10000
-- prime the table
INSERT INTO dbo.nums VALUES (1)

-- loop around while rows are being inserted
WHILE @@rowcount > 0
BEGIN
  INSERT dbo.nums
  SELECT t.n + x.MaxRowNum   FROM dbo.nums t
    CROSS JOIN (SELECT MAX(n) MaxRowNum FROM dbo.nums) x
  WHERE t.n <= @rows - x.MaxRowNum
END
GO


Handy Virtual PC trick

While setting up PCs for a training course that uses nine virtual machines, I discovered a real handy trick for quickly adding the Virtual Machines to the Virtual PC Console.

Just create shortcuts to the Virtual Machine files in,
C:\Documents and Settings\< your profile name >\Application Data\Microsoft\Virtual PC\Virtual Machines

You may need to alter the folder options in Windows Explorer to view hidden folders. If you’ve got the console open, you’ll need to close and re-open it. When it opens your Virtual Machines are magically added the list.

Listing Report Server Subscription Details

Ever get annoyed by the list of SQL Agent Reporting Services jobs with their non-sensical names. You don't know which ones are active subscriptions and which ones are only once off? Users ring you to say they're not receiving the report they subscribed to, and there is no quick way to see how the subscription is setup.

In Report Manager you can only view the subscriptions for the selected report. To see the schedule details to need to drill down another couple of screens.

Using Management Studio you are still stuck with looking at each individual report and then each subscription. At least when you bring up the properties for the subscription it's all within one dialog.

The following query will list the basic details about all the subscriptions in the ReportServer database. In the next iteration I hope to include more details such as file format, parameters selected, etc...

SELECT
  cat.path AS [ReportServerPath]
  , own.username AS SubscriptionCreatedBy
  , jb.date_created AS [SubscriptionCreatedOn]
  , mod.username AS [SubscriptionModifiedBy]
  , sub.ModifiedDate AS [SubscriptionModifiedOn]
  , sub.Description AS [SubscriptionDescription]
  , sub.EventType AS [SubscriptionType]
  , CASE
      WHEN PATINDEX('%Recurrence%', sub.MatchData) > 0THEN 'Recurring' 
      ELSE 'Once Off'
    END AS [ScheduleType]
  , sch.LastRunTime AS [SubscriptionLastRun]
FROM msdb.dbo.sysjobs jb
  INNER JOIN [ReportServer].[dbo].[Schedule] sch
  ON jb.name = CAST(sch.scheduleid AS VARCHAR(40))
    INNER JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.eventdata = sub.subscriptionid
      INNER JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.Report_OID = cat.ItemID
      INNER JOIN [ReportServer].[dbo].[Users] own
      ON sub.OwnerID = own.UserID
      INNER JOIN [ReportServer].[dbo].[Users] mod
      ON sub.ModifiedByID = mod.UserID 

Happy snap from my desk

Just wanted to see how blogging from my phone looked.

Just in case you thought I might be moving

I'm not ... I'm staying right here where I don't have to put up with my server at home randomly rebooting every 2-3 days.