take a report, and apply it to all server?
-
i have a cool report made here, but i have it going against
only one server. is there a way to apply it to ALL servers,
then e-mail the results to me?
so basically one report per server or list of specific servers any way.
i am new to reporting services so forgive me if this is a simple
question.
thanks in advance.
_________________________
-
Assuming your are running a SQL Query against the server to return the result set you will need to run the same query against the other servers as well. The only way I found to do this was to execute the query against all applicable servers at a set time say in a job, and then populate a table with the results and using reporting services to query the table. This works fine for point in time reporting of things like backup history or other types of static information. If you need to report on real time data then probably the best bet would be to set up a data source for each of the applicable servers and individually invoke them through something like linked reports. Hope this helps.
-
well then so the next question is 'how' does one get the 'report' they created
to execute from within a job AND to output the results in the same format
the report had created?
thoughts?
_________________________
-
The report I have checks to see the status of the most recent backup job on all of my servers. I have a base query that selects this information and what I do is customize it within a cursor to use linked servers that I have setup from my central admin server. I then put this code into a stored procedure and execute it every night at 10:00pm. I then have a Reporting Services Subscription that emails me the report. Here is an example of how I set it up:
TRUNCATE TABLE BAK_REPORT
DECLARE report CURSOR FOR
SELECT SVR_NAME
FROM DBA_SERVER -- this is a table where I store all of my server names
WHERE SVR_ENVIRONMENT LIKE 'D' AND SVR_ACTIVE = 1
DECLARE @svr_name VARCHAR(200)
OPEN report
FETCH NEXT FROM report INTO @svr_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(5000)
SET @sql = 'INSERT INTO BAK_REPORT'+'
SELECT TOP 1 '+''''+@svr_name+''''+' AS Server_Name,
a.name,
CASE WHEN b.run_status = 0 THEN '+''''+'Failed'+''''+'
WHEN b.run_status = 1 THEN '+''''+'Succeeded'+''''+'
WHEN b.run_status = 2 THEN '+''''+'Retry'+''''+'
WHEN b.run_status = 3 THEN '+''''+'Canceled'+''''+'
WHEN b.run_status = 4 THEN '+''''+'In Progress'+''''+'
END AS Status,
SUBSTRING(CONVERT(VARCHAR(25),b.run_date),1,4) '+'+'+''''+'/'+''''+'+'+'SUBSTRING(CONVERT(VARCHAR(25),b.run_date),5,2) '+'+'+''''+'/'+''''+'+'+'SUBSTRING(CONVERT(VARCHAR(25),b.run_date),7,2) AS run_date,
CASE WHEN SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),1,2) IN ('+''''+'10'+''''+','+''''+'11'+''''+')THEN SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),1,2)'+'+'+''''+':'+''''+'+'+' SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),3,2)'+'+'+''''+' AM'+''''+'
WHEN LEN(RUN_TIME)<6 THEN SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),1,1)'+'+'+''''+':'+''''+'+'+' SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),2,2)'+'+'+''''+' AM'+''''+'
ELSE SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),1,2)'+'+'+''''+':'+''''+'+'+' SUBSTRING(CONVERT(VARCHAR(255),RUN_TIME),3,2)'+'+'+''''+' PM'+''''+' END AS run_time,
NULL,
CASE WHEN LEN(b.run_duration) = 1 THEN '+'+'+''''+'0:0'+''''+'+'+' CONVERT(VARCHAR(25),b.run_duration)
WHEN LEN(b.run_duration) = 2 THEN '+'+'+''''+'0:'+''''+'+'+' CONVERT(VARCHAR(25),b.run_duration)
WHEN LEN(b.run_duration) = 3 THEN '+'+'+''''+'0'+''''+'+'+' SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),1,1)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),run_duration),2,2)
WHEN LEN(b.run_duration) = 4 THEN SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),1,2)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),3,2)
WHEN LEN(b.run_duration) = 5 THEN SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),1,1)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),2,2)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),4,2)
WHEN LEN(b.run_duration) = 6 THEN SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),1,2)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),3,2)'+'+'+''''+':'+''''+'+'+'
SUBSTRING(CONVERT(VARCHAR(25),b.run_duration),5,2)
END AS run_duration
FROM '+@svr_name+'.msdb.dbo.sysjobs a LEFT OUTER JOIN '+@svr_name+'.msdb.dbo.sysjobhistory b
ON a.job_id = b.job_id
WHERE a.name LIKE '+''''+'%Backup%'+''''+' AND b.run_date IN
(SELECT MAX(run_date) FROM '+@svr_name+'.msdb.dbo.sysjobhistory WHERE job_id IN (SELECT job_id FROM '+@svr_name+'.msdb.dbo.sysjobs WHERE name LIKE '+''''+'%Backup%'+''''+'))
AND b.step_id = 0
ORDER BY b.instance_id DESC'
EXEC(@sql)
FETCH NEXT FROM report INTO @svr_name
END
CLOSE report
DEALLOCATE report
-
great script!
but it's looking for a table called: BAK_REPORT
don't see it created in here though.
_________________________
-
Yeah, thats just a report table I created for my specific report.. It's defined as:
CREATE TABLE [dbo].[BAK_REPORT] (
[SVR_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOB_NAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOB_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RUN_DATE] [datetime] NULL ,
[RUN_TIME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BAK_TODAY] [int] NULL ,
[RUN_DURATION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
excellent! thanks ramses
_________________________
-
oops... what about the definition
for Table:
DBA_SERVER
_________________________
-
Here you go:
CREATE TABLE [dbo].[DBA_SERVER] (
[SVR_NAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SVR_ENVIRONMENT] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVR_ACTIVE] [bit] NULL
) ON [PRIMARY]
GO
-
Again... many thanks my friend
_________________________
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply