The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.
In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.
Tuesday, 13 July 2010
Monday, 5 July 2010
Find SSRS Subscription Jobs
A helpful query found on Patrick LeBlanc's blog:
The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.
The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.
Subscribe to:
Posts (Atom)