Tuesday, 29 May 2007

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

Friday, 25 May 2007

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.

Thursday, 24 May 2007

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 

Wednesday, 23 May 2007