Reporting Services offers two "out of the box" methods to manage subscriptions (subscriptions are scheduled report executions, controlled by Reporting Services): Each report has a "Subscriptions" tab (depending on permissions) which shows your/all subscriptions (again, depending on permissions) for that report only All your subscriptions can be managed using the "My Subscriptions" link in the top right-hand corner of Report Manager There's one key thing missing from the two methods: how does an administrator or power user manage all subscriptions in Reporting Services? The Query I'm going to provide a starting point for a better way...

The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format. I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job: SELECT [Name], CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML FROM ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK) WHERE --get only reports, not folders or other resources [Type] =...

Recently, my boss paid for me to attend Microsoft Course 2780 Maintaining a Microsoft SQL Server 2005 Database at Solid Quality Learning (thanks, boss!) Around 18 months ago, our department was put in charge of a SQL Server 2005 box. I've been using SQL 2005 since then which of course gave me a huge headstart in the course. Way back when, I attended the 2779 training (focused more on developers), and thought that the 2780 training would complement it and make me into a DBA :-) Well, the training is over, and I'm not a DBA, but I got a lot out of the...

I'm very fortunate to be at SQL Server 2005 training in Melbourne all week this week. Our instructor is Aussie SQL Server MVP Rob Farley who blogs at msmvps.com/blogs/robfarley/ and is unbelievably knowledgeable about the product (of course).So far I've collected 2 useful code snippets from Rob - more on useful stuff from the course later:--show all plans in the cache, courtesy Rob Farley--adding "OPTION (RECOMPILE)" means that subsequent executions --of this query aren't added to the cacheSELECT  * FROM    sys.dm_exec_cached_plans p CROSS APPLY         sys.dm_exec_sql_text(p.plan_handle) t CROSS APPLY         sys.dm_exec_query_plan(p.plan_handle) q OPTION (RECOMPILE) --comma-separated from resultset in 1 call,...

I haven't posted anything on Reporting Services recently. So, I have two links for the infamous matrix control: Advanced Matrix Reporting Techniques by David Leibowitz on Red Gate's Simple Talk site How to format the subtotals of a Reporting Services matrix differently, using InScope by fellow Aussie and SQL Server MVP Rob Farley Because I feel I need to compensate :-) Tags: sql server, database, reporting services, matrix

We have an upcoming requirement to kick all users out of a database before running a backup. So I'm saving this script for future reference (thanks to Chris for posting it to the SQL Down Under List):CREATE PROC [dbo].[sp_SpidKill] @db VarChar(200) AS DECLARE @Tmp VarChar(10) DECLARE @spid VarChar(10) DECLARE @Kill VarChar(200) SELECT @spid = Min(spid) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@db) WHILE @spid IS NOT NULL BEGIN SET @Kill = 'KILL ' + @spid EXEC(@Kill) SET @Tmp = @spid SET @spid = NULL SELECT @spid = Min(spid) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@db) AND spid > @Tmp END Tags: sql server, database,...

...I'd be using Red Gate's SQL Multi Script. The initial "alpha" version is available now for free. I've given it a whirl and it works exactly as advertised, with further improvements in the pipeline too. At first glance I thought a utility like this would be better off being built into SQL Server Management Studio. After using it (and reading on the forum what other users want it for), I reckon a small, stand-alone product is probably better. I've been advised that this alpha release will stop working at the end of 2007. Tags: sql server, database, script, server

In the continuing battle over database documentation, Jon Galloway has released a new .NET 2.0 weapon (with source!): Data Dictionary Creator Data Dictionary Creator collects extended properties/descriptions for SQL Server tables and columns and outputs the resulting documentation to a variety of formats. It's also easy to use, and did I mention the source is downloadable too? A tool I've used in the past for retroactively adding extended properties to views, stored procedures, and stored procedure parameters is the no-longer available XpressProps. Nowadays, I add extended properties to both the procedure and all parameters while scripting the object creation - here's some script...

I notice that the latest Australian MSDN Flash from Frank Arrigo mentions IdeaBlade DevForce, a (quite mature) object-relational mapping tool: Complimentary DevForce Express Cut down the time you spend developing data-intensive client/server applications. Get IdeaBlade's DevForce Express, a developer productivity solution for .NET that is fully integrated with Microsoft Visual Studio. DevForce: helps professional developers build, maintain, and operate smart client Internet applications by bridging the infrastructure gap between the Application layer and .NET. The solution is a "must have" for .NET development that will improve your productivity and increase application quality, reduce overall costs, and accelerate time-to-market. I have been using the...