Running dbms_jobs within a timing interval

If you are using the dbms_job and you want to scheduled the jobs to run at a certain time example from 6 am until 11 pm you can use the following example,

This will run the statspack between 6 am until 11 pm.

SQL>
begin

dbms_job.submit(:jobid,'if to_char(sysdate,''HH24'') > 5 and to_char(sysdate,''HH24'') <>
end;

/


To purge the statspack, you can use the following example, statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60);

In prior releases, Statspack identifier tables which contained SQL Text, SQL Execution plans, and Segment identifiers were not purged. It is now possible to purge the unreferenced data in these tables. This is done by requesting the 'extended purge' simply by setting the input parameter i_extended_purge to TRUE when calling the regular purge.

statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60, i_extended_purge=>TRUE);

Oracle - Setting up ODBC / Heterogeneous Database connection to SQL server/Other DB

Basically to setup the connection to the ODBC / Heterogenenous connection, you need to
1) Setup the ODBC system DSN data sources. Remember to put the default database name in the setup. Test the connections after the setup.

2) Goto the $ORACLE_HOME/hs/admin/ folder. Copy the file initodbc.ora and rename it to other name e.g. initHS1.ora. Changed the trace level to OFF and the connect info to your ODBC system DSN name.

e.g.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = HS1
HS_FDS_TRACE_LEVEL = off


3) Then we configure the listener.ora. Create a new SID entry and put the SID_NAME into the ODBC system DSN name.
e.g.


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=HS1) -- Enter the DSN on this line
(ORACLE_HOME = c:\ora10g) -- Enter your Oracle home on this line
(PROGRAM = hsodbc)
)
)

4) Add the hs entry into the tnsnames.ora. e.g.

HS1.world = -- This name can be customized
(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=HS1)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
)

5) Stop and then start the Listener. Check to make sure the services is inside the listener.

6) create a database link, to link to the HS services. Remember in SQL server, the " is needed for the username , password and also to specify column name.

e.g.
create [public] database link link_name connect to "user_name" identified by "password" using 'HS1';

7) If you need to setup additional ODBC / HS setup, just follow the same steps with different DSN name.

For detail setup, follow the guide here.
http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

Oracle Forms - REP-56048: ENGINE RWENG-0 CRASHED

If your Oracle forms crash due to error REP-56048: ENGINE RWENG-0 CRASHED. The problem is due to a large records retrieved. Then there are several solution:-

1) Restrict the number of records return by the reports. To less than 20000 for 4M java memory options.(solution 2)

2)Changed your report.conf at 10.1.2.0/reports/conf by adding the java memory as below. Example , i increase the jvmoptions to 4096k

class="oracle.reports.engine.EngineImpl" id="rwEng" maxengine="1" initengine="1" jvmoptions="-Xoss4096k" callbacktimeout="90000" maxidle="30" englife="1" nengine="0"

3) Remove the "Remove & from Report" in the reports at the report builder. The causes is due to the the report need to process the number of page and this requires a lot of resources.

Working with Oracle Forms and NLS Language/Globalization issues

Recently, we have problem in displaying thai language fonts in the Report builder (windows) and the Oracle Forms servers (unix).

To solved the problem at the Report Builder, first we need to set the language support at the registry .

In the HKLM/Software/OracleDeveloper then at NLS_LANG, we set from the western code "AMERICAN_AMERICA.WE8MSWIN1252" to the unicode ENGLISH_UNITED_KINGDOM.UTF8.

Then we need to change the NLS_LANG at the file default.env , C:\oracle\product\developer\forms\server. Ame thing add the NLS_LANG at this file.


In the Oracle Forms server,
1) First , you need to determine which PPD file you are using. by using the following to test it,
http://:/reports/rwservlet?report=test.rdf&destype=file&desname=/tmp
/test_rep.ps

The content of this file should contain the output:
% NOTE: This file was generated with the PPD file .ppd

2) Then you need to add your fonts into the PPD file, in my case is the screenprinter.ppd at the guicommon/tk/admin/PPD
at the "*DefaultFont: Courier" add the font name.
*Font ArialUnicodeMS: Standard "(001.004)" Standard ROM

add the below, to use the Arial style (bold, italic) fonts
*Font ArialMT: Standard "(001.004)" Standard ROM
*Font Arial-ItalicMT: Standard "(001.004)" Standard ROM
*Font Arial-BoldMT: Standard "(001.004)" Standard ROM
*Font Arial-BoldItalicMT: Standard "(001.004)" Standard ROM


3) Convert the fonts from windows truetypefont, ttf to the AFM type by using ttf2pt1 gnuwin32 tool. Do this for Arial if you want to use this font. Rename the AFM, to just the font name. e.g. the file will be ArialUnicodeMS and ArialUnicodeMS.pfa

Open the file and changed the FontName to the correct short font name. In my case is from Arial Unicode MS to ArialUnicodeMS.

Remember to convert the file from windows to unix format. using dos2unix. To find out if the font still is in msdos format try this. od -a arial.ttf grep cr . If there is output then it is still in msdos format.

4) Copy the AFM and the PFA file to the guicommon/tk/admin/AFM folder.


5) We then need to change the uifont.ali, in the guicommon/tk/admin folder. You can set it in the common uifont.ali or make a copy of the file then set it into a new folder e.g. font_test and then set the environment at /reports/conf/opmn.conf under your Ias-Componentss , and the PATH e.g.


variable id="TK_FONTALIAS" value="/ApplTop/product/10.1.2.0/guicommon/tk/admin/Arial"/

In the uifont.ali
Comment out the the item in the #Mapping from MS Windows and # Mapping from Macintosh

Then goto the end of the file, in the [ PDF:Subset ] section add the following. This will add the Arialunicode ms as pdf font subset and also used the Arial as bold and italic format when the report needed.

"Arial Unicode MS"..... = "ARIALUNI.ttf"
"ArialUnicodeMS"..... = "ARIALUNI.ttf"

# Replace normal Arial fonts with the Arial Unicode MS fonts
"arial"...Bold.. ="arialbd.ttf"
"arial"..Italic.Bold.. ="arialbi.ttf"
"arial"..Italic... ="ariali.ttf"
"Arial"..... ="ARIALUNI.ttf"

6) Copy the .ttf fonts to the path that is inside the opmn.conf. variable id="PATH" value=""

7) Changed the guicommon/tk/admin/US/Tk2Motif.rgb. Add this entry after the
!Tk2Motif*fontMapCs: iso8859-2=EE8ISO8859P2
add this k2Motif*fontMapCs: iso8859-1=UTF8

8) Changed the reports.sh in the $ORACLE_HOME/bin folder. changed this
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG
into
NLS_LANG=AMERICAN_AMERICA.UTF8; export NLS_LANG

9) To test the report, after you restart the opmn services
rwrun.sh report= destype=file desname=/tmp/test.pdf desformat=pdf batch=yes userid=test/test@test


Follow the metalink guide, if you need to configure it properly

Note 356221.1: A Practical Methodology on Porting Reports from Windows to Unix with Different Fonts
Note 726205.1: Step By Step To Generate Arabic PDF Reports on Unix
Note 350971.1: Troubleshooting Guide for Font Aliasing / Font Subsetting / Font Embedding Issues
Note 414803.1: How to Display 'Multiple Designed Fonts' in PDF Outputs of Reports Designed Using Multiple Fonts?

Transfer login to different server

IN Sql server 2000, To transfer the login with password to another sql server , you need to run the below sql

print ''
print '-- Script for restoring standard sql logins.'
print '-- As found on ' + @@servername + ' per ' + cast(getdate() as char)
select cast('exec sp_addlogin ''' + upper(a.loginname) + ''', ' as varchar(40)),
convert(varbinary(64),b.password),
cast(',''' + 'YOURDBSNAME' + ''', ' +
'''' + 'us_english' + ''', ' as varchar(35)),
convert(varbinary(64),b.sid),
',''skip_encryption'' '
from master.dbo.syslogins a,
master.dbo.sysxlogins b
where a.isntname = 0
and a.sid = b.sid
and b.name not in ('sa','repl_publisher','repl_subscriber')
order by b.name
Print ''

IN SQL server 2005, you use this SP_HELP_REVLOGIN

to create this sp, follow this microsoft link http://support.microsoft.com/kb/918992/

SQL Server - changing password of replication

To change password for the subscriber , use the below sql 2000 command,

exec sp_helpsubscriberinfo
exec sp_changesubscriber @subscriber ='abcd', @password 'abcd'

Changing SQL Server Network Connetivity Protocol priority using registry

Without using the SQL network client tools or SQL server client tool, to changed the client pc sql network protocol you can use the below to register. This registry will add a Protocol order and set the "tcp np" as the values. That's means the tcp will be priority over the name pipes.


Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib]
"ProtocolOrder"=hex(7):74,00,63,00,70,00,00,00,6e,00,70,00,00,00,00,00
"Encrypt"=dword:00000000

Favicon's in a SharePoint 2007

There are many ways to add Favicons in the sharepoint , one of the way you can do that is by changing the Default Master Page. Follow this link to the guide on doing this.
http://www.sharepointblogs.com/mossman/archive/2008/01/18/favicon-s-in-a-sharepoint-master-page.aspx

Another way, is to add the Favicons, to the root directory of your website. e.g. in the C:\Inetpub\wwwroot\wss\VirtualDirectories\80 , where this is the virtual directories of the 80.

For some sharepoint website that you have difficulty in doing the above, another way is to changed the default.asp of your sharepoint which is not recommended.
To changed the page , goto C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates\PMD . where the PMD is my directory website that i want to add the favicons. e.g. www.test.com/PMD

Backup the default.aspx and add the following in the asp:content contentplaceholderid="PlaceHolderAdditionalPageHead" after the script tag and put the favicon image into your C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\PMD\IMAGES


link rel="icon" href="/_Layouts/PMD/Images/favicon.ico" type="image/png">
link rel="shortcut icon" href="/_Layouts/PMD/Images/favicon.ico" type="image/png">

Problem :Communication from the Oracle Management Service host to the Agent host failed.

When the oracle enterprise manager able to see the agent is up and you can see that the performance metric but with some error "Communication from the Oracle Management Service host to the Agent host failed."

You need to reconfigure the agent to rediscover the OEM.

In windows , agentctl -d (rediscover)

Amended :-
it should be agentca -d

Discovering IAS failed if the agent is install under another OS user than ias

Recently i have encounter this problem, where if the oracle agent is install as a different user other than IAS user then it is unable to discover the application server.

To resolve it :-

iAS Discovery/Monitoring Fails If Agent Is Installed Under Another OS User Than iAS,
metalink note 437078.1.

Problem: Agent Does Not Discover Other ORACLE_HOME´s, metalink note :371539.1

1) As Ias user:-
cd $IAS_HOME
chmod g+rx $IAS_HOME
chmod g+rx $IAS_HOME/bin
chmod g+rx $IAS_HOME/bin/emtgtctl
chmod ug+s $IAS_HOME/bin/emtgtctl2
chmod g+rx $IAS_HOME/perl
chmod g+rx $IAS_HOME/perl/bin
chmod g+rx $IAS_HOME/perl/bin/perl
chmod g+rx $IAS_HOME/sysman
chmod g+rx $IAS_HOME/sysman/emd
cmod g+rw $IAS_HOME/sysman/emd/targets.xml
touch $IAS_HOME/sysman/emd/centralagents.lst
chmod g+rw $IAS_HOME/sysman/emd/centralagents.lst
chmod g+rx $IAS_HOME/sysman/config
chmod g+rw $IAS_HOME/sysman/config/*.properties
chmod g+rx $IAS_HOME/opmn
chmod g+rx $IAS_HOME/opmn/conf
chmod g+rw $IAS_HOME/opmn/conf/opmn.xml
chmod g+rx $IAS_HOME/config
chmod g+rw $IAS_HOME/config/*.xml
chmod g+rw $IAS_HOME/config/*.properties
chmod g+rx $IAS_HOME/Apache
chmod g+rx $IAS_HOME/Apache/Apache
chmod g+rx $IAS_HOME/Apache/Apache/conf
chmod g+rw $IAS_HOME/Apache/Apache/conf/*conf
chmod g+rw $IAS_HOME/Apache/Apache/conf/*.xml
chmod g+rw $IAS_HOME/Apache/Apache/conf/mime.types
For each OC4J (in this example OC4J_SECURITY):
chmod g+rx $IAS_HOME/j2ee
chmod g+rx $IAS_HOME/j2ee/OC4J_SECURITY
chmod g+rx $IAS_HOME/j2ee/OC4J_SECURITY/config
chmod g+rw $IAS_HOME/j2ee/OC4J_SECURITY/config/*
chmod g+rx $IAS_HOME/dcm
chmod g+rx $IAS_HOME/dcm/config
chmod g+rw $IAS_HOME/dcm/config/*.conf
chmod g+rw $IAS_HOME/dcm/config/*.xml

2) as emagent os user
cd $AGENT_HOME
chmod g+rx $AGENT_HOME
chmod g+rx $AGENT_HOME/bin
chmod g+rx $AGENT_HOME/bin/emtgtctl
chmod ug+s $AGENT_HOME/bin/emtgtctl2
chmod g+rx $AGENT_HOME/sysman
chmod g+rx $AGENT_HOME/sysman/emd
chmod g+rw $AGENT_HOME/sysman/emd/targets.xml
chmod g+rx $AGENT_HOME/sysman/opmn
chmod g+rx $AGENT_HOME/sysman/opmn/conf

3) stop and start the agent
emctl stop agent
emctl start agent

4) initialize new discovery through the enterprise manager.

To check the log files , check in the /sysman/log/ emagent.trc & emagent_perl.trc.

Other than that, ensure that the /etc/oratab contains the path to both of the oracle home.

Caught by SQL injection

At work i have encounter a sql injection that inject a javascript into the database that will cause website to download the js javascript.

For more information, can take a look at this SQL Injection: More of the same (http://isc.sans.org/diary.html?storyid=4565)

I have tighten the security by disabling the selecting of the sysobjects from public, which will mitigate the risk.

To reverse the changes i use this sql to reversed out the changes:-



DECLARE @T varchar(255)
declare @C1 varCHAR(255)

DECLARE Table_Cursor
CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND
(b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @T,@C1

WHILE(@@FETCH_STATUS=0)

BEGIN

EXEC('update ' + @T + ' set ' + @C1 + '= substring(' + @c1+ ',1, CHARINDEX (''< script'',' +@C1 +')-1) where charindex(''< script'',' + @C1 + ') >0')
print 'update ' + @T + ' set ' + @C1 + '= substring(' + @c1+ ',1, CHARINDEX (''< < script'',' +@C1 +')-1) where charindex(''< script'',' + @C1 + ') >0'

FETCH NEXT FROM Table_Cursor INTO @T,@C1
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

Auto Download of Health check report CSV mail and process by Batch

To automatically download the mail, use the macro as in my case i use imacro for Firefox, you can use the imacro for internet explorer but this version has some problem with my mail server.

http://www.iopus.com/imacros/firefox/?ref=fxhome

Here's my macro, with amendments:-

VERSION BUILD=6050612 RECORDER=FX
TAB T=1
URL GOTO=http://mailbox.id
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ACTION:/?service=login&pragma=nocache ATTR=VALUE:Login
TAG POS=1 TYPE=FONT ATTR=TXT:*Report*
TAB T=2
FRAME F=2
TAG POS=1 TYPE=FONT ATTR=TXT:DailyHealthcheck.cab
'SAVEAS TYPE=CPL FOLDER=* FILE=+_{{!NOW:yyyymmdd_hhnnss}}
ONDOWNLOAD FOLDER=* FILE=+_{{!NOW:yyyymmdd_hhnnss}}
TAB T=1
FRAME F=0
BACK
WAIT SECONDS=2
TAB CLOSEALLOTHERS
TAG POS=1 TYPE=INPUT:CHECKBOX FORM=NAME:select ATTR=NAME:tid CONTENT=YES
WAIT SECONDS=2
ONDIALOG POS=1 BUTTON=CANCEL CONTENT=
TAG POS=1 TYPE=FONT ATTR=TXT:DELETEMESSAGE
TAG POS=1 TYPE=INPUT:BUTTON FORM=NAME:NoFormName ATTR=NAME:button1&&VALUE:Return
TAG POS=1 TYPE=IMG ATTR=ALT:Refresh

My batch file looks like this, it will process each file with the cab extension , archive it and expand it to report.csv, then will bcp into database and the next command will processed the data.

for %%f in (*.cab) do copy %%f archive\%%f && expand %%f Report.csv && bcp dbaRepository.dbo.tCSV_Data in Report.csv -t, -S servername-U userid -P pwd -w -C 850 && Del %%f

rem Remember to grant execute permission to the user to execute the stored procedure

osql -S servername -U userid -P password -Q "exec dbaRepository..sp_updateCSVtables"

SQL Server 2000 health check script in CVS(with compress)

create procedure sp_emailDailyHealthCheckCSV
@charfrom varchar(50),
@charsubject varchar(100),
@charemailserver varchar(15),
@intresults int OUTPUT
AS

/******************************************************************
*
* DAILY HEALTH CHECK SCRIPT - SQL Server
*
*
******************************************************************/
/*
declare @rc int
set @rc=0
exec sp_emailDailyHealthCheck
@charfrom = Ntest@test.com',
@charemailserver = N'188.188.1.13',
@charsubject = N'SQL Health Check Report: (Server: "DELL2500")',
@intresults = @rc

CVS format
COLLECTIONTYPE as varchar , e.g. SQLSVR
COLLECTIONSOURCE as varchar, e.g. DELL2500
COLLECTIONDATETIME as varchar, e.g. Mar 6 2008 8:00AM
COLLECTIONNAME as varchar e.g. DISKSPACE
COLLECTIONHEADER as varchar e.g. USEDSPACE
COLLECTIONVALUE1 as varchar e.g. C
COLLECTIONVALUE2 as varchar e.g. 100 or 200

*/
/*****************************************
* Create temp tables for storing the health check output
*****************************************/



declare @COLLECTIONTYPE varchar(20)
declare @COLLECTIONSOURCE varchar(50)
declare @COLLECTIONDATETIME varchar(20)

set @COLLECTIONTYPE = 'SQLSERVR'
set @COLLECTIONSOURCE = @@servername
set @COLLECTIONDATETIME = cast(getdate() as varchar)

CREATE TABLE #csv_output (COLLECTIONTYPE varchar(20), COLLECTIONSOURCE varchar(50),
COLLECTIONDATETIME varchar(20), COLLECTIONNAME varchar(50),
COLLECTIONHEADER varchar(50), COLLECTIONVALUE1 varchar(500),
COLLECTIONVALUE2 varchar(500) )

insert into #csv_output values('COLLECTIONTYPE', 'COLLECTIONSOURCE', 'COLLECTIONDATETIME',
'COLLECTIONNAME','COLLECTIONHEADER','COLLECTIONVALUE1','COLLECTIONVALUE2')

insert into #csv_output values(@COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SQLVERSION','SQLVERSION',replace(replace(@@version,char(9),''),char(10),''),'')



Declare @output varchar(1000)


--- Get the ip address of the server
CREATE TABLE #temp1(t varchar(3000) null)
insert into #temp1 exec xp_cmdshell 'ipconfig'
DECLARE @t1 varchar(300)-- subject to localisation

SET @t1 = (
SELECT top 1 #temp1.t from #temp1
where t like '%IP Address%'
order by t DESC
)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'IPADRESS','IPADRESS',LTRIM(RTRIM(SUBSTRING(t,CHARINDEX(':',t)+1,len(@t1)))),''
from #temp1
where t like '%IP Address%'


drop table #temp1
-- end of getting ip address


/******************************************************************
*
* Get Total Disk Space Availaible
*
* This script displays Drive and total sql size used and free size
*
*
*****************************************************************/
Begin
Declare @a Varchar(50)
,@b Varchar(50)
,@c Varchar(50)
,@TSize1 VARCHAR(255)

Create Table #Ops_Drives
(
Drive Varchar(1),
Size int
)
Declare @a1 int
,@b1 int
,@c1 int
,@d1 int

Create Table #Ops_DriveTmp
(
DBName Varchar(25)
,Location Varchar(255)
,Size Varchar(8)
,MaxSize int
,Growth Int
,Device Varchar(30)
)

Exec SP_MSForEachDB
'Use [?] Insert into #Ops_DriveTmp
Select Upper(Convert(Varchar(25),DB_Name())) as ''Database'',
Convert(Varchar(255),FileName),Convert(Varchar(8),Size/128)''Size in MB'',
MaxSize,Growth,
Convert(Varchar(30),Name)
from SysFiles'


Select @a1=Sum(Convert(Int,Size)) from #Ops_DriveTmp

Insert Into #Ops_Drives
Exec Master..XP_Fixeddrives
Create Table #Ops_TmpDriveInfo
(
Drive Varchar(1),
SQL Varchar(8),
FreeSpace Varchar(255),
TotalSpace varchar(255),
Util varchar(4)
)

DECLARE @oFSO1 INT,@oDrive1 INT, @ret1 INT
EXEC @ret1 = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO1 OUT

Declare CheckDrives Cursor For Select Drive,size from #Ops_Drives Order by Drive
Open CheckDrives
Fetch Next from CheckDrives into @c,@c1
While (@@Fetch_Status=0)
Begin
Select @b1=sum(convert(Int,Size))
from #Ops_DriveTmp
where substring(Location,1,1)=@c

Set @a =@c + ':\'

EXEC @ret1 = master.dbo.sp_OAMethod @oFSO1, 'GetDrive', @oDrive1 OUT, @a
EXEC @ret1 = master.dbo.sp_OAMethod @oDrive1, 'TotalSize', @TSize1 OUT

set @d1 = CAST(ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024,0) AS INT)

Insert into #Ops_TmpDriveInfo values(@c,isnull(@b1,0),@c1,@d1 ,
(100 -(round(( @c1)/ ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024 ,0),3) * 100)) )

Fetch Next from CheckDrives into @c,@c1
End



insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','SQLSERVERDATA',Drive,[SQL]
from #Ops_TmpDriveINFO

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','FREESPACE',Drive,FreeSpace
from #Ops_TmpDriveINFO


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','TOTALSPACE',Drive,TotalSpace
from #Ops_TmpDriveINFO

drop table #Ops_TmpDriveINFO
drop table #Ops_DriveTmp
drop table #Ops_Drives

exec @ret1 = sp_OADestroy @oFSO1
exec @ret1 = sp_OADestroy @oDrive1

Close CheckDrives

Deallocate CheckDrives

End
/******************************************************************
*
* End Of Get Total Disk Space Availaible
******************************************************************/

/******************************************************************
*
* Get Summary Database Details
*
* This script displays Summary Database Details, % used
*
*
*****************************************************************/
SET nocount ON

DECLARE @dbname SYSNAME

CREATE TABLE #datafilestats (
dbname VARCHAR(200) null,
flag BIT DEFAULT 0 ,
fileid TINYINT,
[filegroup] TINYINT,
totalextents DEC(20,1),
usedextents DEC(20,1),
[name] VARCHAR(200),
[filename] VARCHAR(200))


DECLARE @string SYSNAME

SET @string = ''

SET @dbname = ''

WHILE 1 = 1
BEGIN
SELECT top 1 @dbname = name
FROM master..sysdatabases
WHERE name > @dbname
IF @@ROWCOUNT = 0
BREAK
IF NOT (SELECT Databasepropertyex(@dbname,'Status')) = 'ONLINE'
BREAK
SET @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
INSERT INTO #datafilestats
(fileid,
[filegroup],
totalextents,
usedextents,
[name],
[filename])
EXEC( @string)
UPDATE #datafilestats
SET dbname = @dbname,
flag = 1
WHERE flag = 0
UPDATE #datafilestats
SET totalextents = (SELECT SUM(totalextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
UPDATE #datafilestats
SET usedextents = (SELECT SUM(usedextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
END

CREATE TABLE #sizeinfo (
db_name VARCHAR(200) NOT NULL PRIMARY KEY CLUSTERED,
total DEC(20,1) null,
data DEC(20,1) null,
data_used DEC(20,1) null,
[data (%)] DEC(20,1) null ,
data_free DEC(20,1) null,
[data_free (%)] DEC(20,1) null,
LOG DEC(20,1) null,
log_used DEC(20,1) null,
[log (%)] DEC(20,1) null,
log_free DEC(20,1) null,
[log_free (%)] DEC(20,1) null,
status DEC(20,1))

INSERT #sizeinfo
(db_name,
[log],
[log (%)],
status)
EXEC( 'dbcc sqlperf(logspace) with no_infomsgs')

UPDATE #sizeinfo
SET data = d.totalextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET data_used = d.usedextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET total = (data + LOG)

UPDATE #sizeinfo
SET [data (%)] = (data_used * 100.0 / data)

UPDATE #sizeinfo
SET data_free = (data - data_used)

UPDATE #sizeinfo
SET [data_free (%)] = (100 - [data (%)])

UPDATE #sizeinfo
SET log_used = (LOG * [log (%)] / 100.0)

UPDATE #sizeinfo
SET log_free = (LOG - log_used)

UPDATE #sizeinfo
SET [log_free (%)] = (log_free * 100.0 / LOG)

BEGIN

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALDATAUSED',db_name, cast(data_used as varchar)
FROM #sizeinfo WITH (nolock)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALDATAFREE',db_name, cast(data_free as varchar)
FROM #sizeinfo WITH (nolock)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALLOGUSED',db_name, cast(log_used as varchar)
FROM #sizeinfo WITH (nolock)


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALLOGFREE',db_name,cast(log_free as varchar)
FROM #sizeinfo WITH (nolock)

END

DROP TABLE #datafilestats

DROP TABLE #sizeinfo

/******************************************************************
*
* End Of Summary Database Details
*
*****************************************************************/





/******************************************************************
*
* SQL Server Disk Space Check
*
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
******************************************************************/

BEGIN
/*****************************************
* Create temp tables for disk space info
*****************************************/
CREATE TABLE #space (dletter varchar(2) null, fspace int, tspace BIGINT null)
CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255) null)
CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255),
[filegroup] VARCHAR(10) null, [size] VARCHAR(50),
[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

/*****************************************
* populate temp tables
*****************************************/
INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
INSERT INTO #dbsize EXEC master.dbo.sp_databases

-- Create cursor for files
DECLARE c_files CURSOR FOR
SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]),
RTRIM(af.[size]), RTRIM(db.[name])
FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
WHERE af.dbid = db.dbid AND db.version <> 0
DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
DECLARE @tsize INT, @tdbname VARCHAR(50)
DECLARE @SQL NVARCHAR(255)
DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

-- Open cursor
OPEN c_files
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

-- Populate #growth table with file growth details
WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE #fdata
-- Get file data
SET @SQL = 'INSERT INTO #fdata EXEC '
SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
EXEC sp_executesql @SQL
SELECT @growth = growth FROM #fdata
-- Determine if growth is % or Mbytes
IF RIGHT(@growth,1) = '%'
BEGIN
SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
SET @gtype = '%'
END
ELSE
BEGIN
SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
SET @gtype = 'MB'
END
-- Create record for file in #growth table
INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
END

-- Close cursor
CLOSE c_files
DEALLOCATE c_files

/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM #space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT

-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT



-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE #space
SET tspace = CAST(@drsize AS BIGINT)
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace


END

-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks

-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO


/*****************************************
* Return disk space info
*****************************************/

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENAME',db.[name],af.[name]
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILEPATH',af.[name], af.[filename]
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILESIZE',af.[name], CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS varchar)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENEXTEXTENSIONSIZE',af.[name], STR(g.next_exp)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENEXTEXPANSIONSIZE',af.[name], CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS varchar)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]



/*****************************************
* Drop temporary tables
*****************************************/
DROP TABLE #space
DROP TABLE #dbsize
DROP TABLE #fdata
DROP TABLE #growth

END

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
*Check for Last Backup Done.
******************************************************************/


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'BACKUPDETAIL','DATABASELASTBACKUP', B.name ,ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER')
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'BACKUPDETAIL','DAYSSINCELASTBACKUP', B.name ,ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER')
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/


/******************************************************************
*
* Lists user and permission
******************************************************************/


-- ***************************************************************************
-- Declare local variables
DECLARE @DBName1 VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE #Users1 (
[sid] varbinary(85) NULL,
[Login Name] varchar(24) NULL,
[Default Database] varchar(18) NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO #Users1 SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
FROM master.dbo.syslogins
Where sysadmin =1 or securityadmin =1 or serveradmin =1 or setupadmin=1 or processadmin = 1
or diskadmin=1 or dbcreator =1 or bulkadmin=1;

---------------------------------------------------------

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SYSADMIN', [Login Name],[sysadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SECURITYADMIN', [Login Name], [securityadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SERVERADMIN', [Login Name], [serveradmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SETUPADMIN', [Login Name], [setupadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','PROCESSADMIN', [Login Name], [processadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','DISKADMIN', [Login Name], [diskadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','DBCREATOR', [Login Name], [dbcreator]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','BULKADMIN', [Login Name], [bulkadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]


-- ***************************************************************************
-- ***************************************************************************

-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers1 (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
-- ***************************************************************************


-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
-- ***************************************************************************
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- ***************************************************************************
-- ***************************************************************************
--
SELECT @SQLCmd = 'INSERT ##DBUsers1 ' +
' SELECT ''' + @DBName1 + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.LoginName, ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName1 + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN master.dbo.syslogins u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName1 + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName1 + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' AND u.LoginName is null '
EXEC (@SQLCmd)
-- ***************************************************************************
-- ***************************************************************************
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- End of the cursor loop
END
-- ***************************************************************************
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- ***************************************************************************

insert into #csv_output
select distinct @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'ORPHANUSER','ORPHANUSER', [Database User ID], [Database]
FROM ##DBUsers1
ORDER BY [Database User ID],[Database];

-- ***************************************************************************
-- ***************************************************************************
DROP TABLE #Users1;

DROP TABLE ##DBUsers1;
-- ***************************************************************************



/******************************************************************
*
* End of Lists user and permission
*
******************************************************************/

/******************************************************************
*
* Failed Scheduled alert jobs logs
*
******************************************************************/




insert into #csv_output
select distinct @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'FAILEDSCHEDULEDJOBS', 'FAILEDSCHEDULEDJOBS',j.name ,cast(DATEADD
( SECOND, jh.run_duration, CAST
(CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
) ) as varchar)
FROM
msdb..sysjobhistory jh
INNER JOIN
msdb..sysjobs j
ON
j.job_id = jh.job_id
WHERE
jh.step_name = '(Job outcome)'
and jh.run_date >= cast(year(getdate() - 1)as varchar) + right('0'+ cast(month(getdate() -1 )as varchar),2) + right('0'+ cast(day(getdate() -1) as varchar),2)
and jh.run_status =0 -- failure

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
* Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/


CREATE TABLE #Errors (vchMessage varchar(500), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC sp_readerrorlog

--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #Errors
SET ROWCOUNT 20

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERLOGS','LAST20SERVERLOGS', vchMessage, ''
FROM #Errors
WHERE vchMessage NOT LIKE '%Copyright (c)%'
AND (isdate(substring(vchMessage,1,10)) = 1)
AND substring(vchMessage,1,10) > convert(varchar(10),(dateadd(dd,-1,getdate()-1)),120)
ORDER BY ID DESC


DROP TABLE #Errors
SET ROWCOUNT 0

/******************************************************************
*
*End of Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/



/******************************************************************
*
* Writing the script output to csv file
*
******************************************************************/


DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80),
@objFile int


set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT



Declare @Filename varchar(60)
--set @Filename = 'DailyHealthcheck_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') + '.html'
set @Filename = 'c:\temp\DailyHealthcheck'

Select @FileAndPath=@filename + '.csv'


if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,1,True


if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'

Declare msgcursor CURSOR FOR
select Replace(COLLECTIONTYPE + ',' + COLLECTIONSOURCE + ',' +
COLLECTIONDATETIME + ',' + COLLECTIONNAME + ',' +
COLLECTIONHEADER + ',' + COLLECTIONVALUE1 + ',' +
COLLECTIONVALUE2,char(13),'') as msg from #csv_output

Declare @String1 varchar(4000)

Open msgcursor
fetch next from msgcursor
into @String1

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String1

fetch next from msgcursor
into @String1

END

Close msgcursor
Deallocate msgcursor

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


drop table #csv_output



/******************************************************************
*
* End Of Writing the script output to csv file
******************************************************************/


/******************************************************************
*
* Compressing the report
******************************************************************/

declare @SQLCommand varchar(200)

SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'makecab ' + @Filename + '.csv ' + @Filename + '.cab'''

EXEC (@SQLCommand)

/******************************************************************
*
* Compressing the report
******************************************************************/


/******************************************************************
*
* Sending the health check report to email
******************************************************************/
set @Filename = @Filename + '.cab'

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@from = @charfrom,
@to = N'xxx@mail.com',
@server = @charemailserver,
@subject = @charsubject ,
@attachment = @Filename ,
@message = N'See Attached report '


/******************************************************************
*
* End Of Sending the health check report to email
******************************************************************/

GO

Using Sql profiler to check for performance issue .. continue

To use the server side sql tracing, we can use the following

DECLARE @TraceID int

Declare @tracefilename varchar(50)

set @tracefilename = 'C:\Temp\ServerSide_Trace' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')

EXEC CreateTrace

@tracefilename,

@OutputTraceID = @TraceID OUT

-- stored procedures

EXEC AddEvent

@TraceID,

'SP:Completed',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

--- sqls

EXEC AddEvent

@TraceID,

'SQL:StmtCompleted',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

-- only include procedures that has run for more than 10 seconds.

EXEC AddFilter

@TraceID,

'Duration',

10000,

'>='

--- exclude system run ned stored procedures and sqls

EXEC AddFilter

@TraceID,

'ObjectID',

100,

'>='

EXEC StartTrace @TraceID

GO


To clear the server side tracing,

EXEC StopTrace 1

EXEC ClearTrace 1

Using Sql profiler to check for performance issue

If you are intending to check for sql server 2000 performance issue, check out this How to identify SQL Server performance issues, by analyzing Profiler output http://vyaskn.tripod.com/analyzing_profiler_output.htm .

It gives you a good template to trace the performance and find the top running stored procedures , high cpu usage , procedure cache usage information.

mysql common command

mysql> show databases;
+-------------+
| Database |
+-------------+
| mysql |
| servicedesk |
| test |
+-------------+

mysql> use servicedesk;

mysql> show tables;


mysql>
mysql> show privileges;
+-------------------------+--------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+--------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases and tables |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Shutdown | Server Admin | To shutdown the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+--------------------------+-------------------------------------------------------+
21 rows in set (0.00 sec)



mysql> show status;
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 4 |
| Aborted_connects | 6 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 170957189 |
| Bytes_sent | 672760406 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 256 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 175896 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 80 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 463 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 246 |
| Com_savepoint | 0 |
| Com_select | 489861 |
| Com_set_option | 1120 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 10 |
| Com_show_databases | 2 |
| Com_show_errors | 0 |
| Com_show_fields | 11 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 20 |
| Com_show_logs | 2 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 1 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 1 |
| Com_show_tables | 21 |
| Com_show_variables | 254 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 73349 |
| Com_update_multi | 0 |
| Connections | 267 |
| Created_tmp_disk_tables | 199 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 282 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 175954 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_read_first | 189002 |
| Handler_read_key | 16998410 |
| Handler_read_next | 23482498 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 31315 |
| Handler_read_rnd_next | 6107226 |
| Handler_rollback | 499 |
| Handler_update | 772 |
| Handler_write | 30380 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 7173 |
| Key_blocks_used | 36 |
| Key_read_requests | 17488 |
| Key_reads | 263 |
| Key_write_requests | 4642 |
| Key_writes | 0 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_tables | 14 |
| Opened_tables | 27168 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 741859 |
| Rpl_status | NULL |
| Select_full_join | 544 |
| Select_full_range_join | 0 |
| Select_range | 373 |
| Select_range_check | 0 |
| Select_scan | 154713 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 202632 |
| Sort_rows | 855337 |
| Sort_scan | 2217 |
| Table_locks_immediate | 664883 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 266 |
| Threads_running | 1 |
| Uptime | 2415211 |
+----------------------------+-----------+
163 rows in set (0.00 sec)

Restore database (or database table) from backup.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql


mysql> show global variables;



mysql> show global variables like '%LOG%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| expire_logs_days | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | OFF |
| log_error | .\SGUATSUPPORT01.err |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| sync_binlog | 0 |
+--------------------------------+----------------------+
25 rows in set (0.00 sec)

Configuring http protocol for Vmware ESX Server 3.5 Plug in for OEM

Both ESX Server 3.5 and VirtualCenter 2.5 handle connections to Web-based services, including the API and the Managed Object Browser (MOB), through an Http Reverse Proxy service. This service has its own
configuration file, proxy.xml, that can be modified to support HTTP connections to the ESX Server. See the ESX Server 3 Configuration Guide for complete details. Figure 2-1 shows a proxy.xml file that has been
modified to support HTTP for both the MOB and the SDK.

To modify the Web proxy service on ESX Server 3.5 to support HTTP
1 Log in to the service console as the root user.
2 Change directories to /etc/vmware/hostd.
3 Use a text editor to open the proxy.xml file.
4 Navigate to the list of endpoints in the file (identified by the tag) that contains settings for
the Web service supporting the SDK. The nested tags may look something like this:
...

<_type>vim.ProxyService.NamedPipeServiceSpec
httpsWithRedirect
/var/run/vmware/proxy-sdk
/sdk

...
5 Change the accessMode to httpAndHttps. Alternatively, to completely disable HTTPS, you can set to httpOnly.
6 Optionally, change the setting for the MOB as well.
7 Save your settings and close the file.
8 Restart the vmware-hostd process by entering the following command:
service mgmt-vmware restart




<_type>vim.ProxyService.NamedPipeServiceSpec
httpAndHttps
/var/run/vmware/proxy-sdk
/sdk

Error encounter while installing 10gr2 RAC on RHEL 5

If you encounter below error while installing 10gr2 RAC on RHEL5, follow the url guide :-

Error:-Error while running the root.sh, error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

Remove the LD_ASSUME_KERNEL=2.4.19

URL :-
http://www.loveunix.cn/html/44/59044-13085.html
http://blog.360.yahoo.com/blog-sWxKwVc0YarVsfeFmtf3gg--?cq=1&p=45



Error:-
The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

URL :-
http://surachart.blogspot.com/2006/03/given-interfaces-eth0-is-not-public.html
and also read this
http://www.oracledba.ca/blogs/index.php?blog=1&p=47&more=1&c=1&tb=1&pb=1


conclusion use the vipca. to configure the rac1 vip.

Sql server not connecting to port 1433 in windows 2003

The combination of Windows 2003 and SQL 2000 no SP will caused that the TCP port 1433 is not listen by the sql server,
To resolved this install the SQL 2000 Sp3. Then reenable the tcp via the SQL server network utility

Cursor Movement Error in SQL Developer

If you encounter cursor movement error while in SQL Developer, try to changed the settings as in this link
http://www.jroller.com/agileanswers/entry/cursor_movement_in_oracle_s

Errror in Upgrading Oracle Enterprise Manager to Release 3

It's a headache in patching the OEM10g to release 3. More than twice failed when it is updating the repository.

Once the SYSMAN.EMD_MAINTENANCE package error and the upgrade repository failed. Even if manually recreate the database encounter problem when dropping the sysman user.
as in metalink note 433419.1

more emrepmgr.log.839830.errors
9287: ORA-04021: timeout occurred while waiting to lock object SYSMAN.EMD_MAINTE
NANCE
90232: Warning: Package Body created with compilation errors.
491120: ORA-04063: package body "SYSMAN.EMD_MAINTENANCE" has errors
491121: ORA-06508: PL/SQL: could not find program unit being called
491122: ORA-06512: at line 3
530335: ORA-04063: package body "SYSMAN.EMD_MAINTENANCE" has errors
530336: ORA-06508: PL/SQL: could not find program unit being called
530337: ORA-06512: at "SYSMAN.JOB_SUMM_INS_TRIGGER2", line 91
530338: ORA-04088: error during execution of trigger 'SYSMAN.JOB_SUMM_INS_TRIGGE
R2'
530339: ORA-06512: at "SYSMAN.MGMT_JOB_ENGINE", line 10924
530340: ORA-06512: at "SYSMAN.MGMT_JOB_ENGINE", line 650
530341: ORA-06512: at "SYSMAN.MGMT_JOB_ENGINE", line 7987
530342: ORA-04063: package body "SYSMAN.EMD_MAINTENANCE" has errors
530343: ORA-06508: PL/SQL: could not find program unit being called
530344: ORA-06512: at "SYSMAN.JOB_SUMM_INS_TRIGGER2", line 91
530345: ORA-04088: error during execution of trigger 'SYSMAN.JOB_SUMM_INS_TRIGGE
R2'
530346: ORA-06512: at line 95
559591: ORA-04063: package body "SYSMAN.EMD_MAINTENANCE" has errors
559592: ORA-06508: PL/SQL: could no

Error in dropping the sysman.
SQL*Plus: Release 10.1.0.4.0 - Production on Thu Mar 20 16:32:17 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> DROP USER sysman CASCADE;
DROP USER sysman CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 5
ORA-04031: unable to allocate 4000 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","seg:kggfaAllocSeg")

Finally after few times, and hint from my senior dba .. we need to do the installation checklist before installing.. It's strange that the release notes didn't highlight this..

The metalink note is 422061.1, Installation Checklist for EM 10g Grid Control 10.2.0.x to 10.2.0.3 OMS and Repository Upgrades. Part of it is shown below:-

To speed up the data migration part, a couple of maintenance tasks should be performed on the repository prior to the upgrade.

NOTE: All the statements below should be run as the 'SYSMAN' user (Repository owner).

1. Run the partition maintenance routines
In order to run the partition maintenance routines, the OMS's will have to be stopped.
After all the OMS's have been shutdown, perform the following steps:

    SQL> EXEC emd_maintenance.remove_em_dbms_jobs;

  SQL> ALTER SYSTEM SET job_queue_processes=0 SID='*';

  SQL> EXEC emd_maintenance.partition_maintenance;

  SQL> ALTER SYSTEM SET job_queue_processes=10 SID='*';

  SQL> EXEC emd_maintenance.submit_em_dbms_jobs;

2. Clean up all pending-delete ECM snapshots
Delete all pending-delete ECM snapshots, by running these two PL/SQL routines:

    DECLARE

    pcb_params MGMT_PURGE_CALLBACK_PARAMS :=

      MGMT_PURGE_CALLBACK_PARAMS(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

  BEGIN

    ecm_ct.DELETE_SNAPSHOTS(pcb_params);

  END;

  /



  DECLARE

    pcb_params MGMT_PURGE_CALLBACK_PARAMS :=

      MGMT_PURGE_CALLBACK_PARAMS(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

  BEGIN

    ecm_ct.DELETE_HOST_CONFIG_SNAPSHOTS(pcb_params);

  END;

  /

3. After running all the cleanup, make sure the statistics on all the repository tables are accurate and up-to-date. Run this procedure to analyze the statistics on the repository tables:

    SQL> EXEC emd_maintenance.analyze_emd_schema('SYSMAN');

Remember to install the patch required before proceeding, mine is
Patch 4329444 SKIP SCAN OF COMPRESSED INDEX ON INDEX-ORGANIZED TABLE (IOT) GIVES WRONG RESULTS

SQL server Maintainance - reindexing the database

To reindex the database, 1 is to use the maintenance plan, 2 manually reindex the database based on the reindex statistics

http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx

Oracle 10g instance client - unable to connect protocol adapter error

After installing the 10g instant client if there is an protocol adapter error appear even when the tnsnames.ora is properly configured it could be that the tns_admin is not properly register.


You can simply add "String Value" in registry and it should solve your problem. Perform this and see if it works.

Start->Run->Regedit
In the registry goto
HKEY_LOCAL_MACHINE->Software->Oracle->RightClick and add String Value and Name it TNS_ADMIN and give it the value of your correct Network Files.
For example if your TNSNAMES.ORA and SQLNET.ORA files are located under C:\oracle\product\10.2.0\client_1\network\admin then you should specify this value for TNS_ADMIN.

Readyboost on WindowsXP - increase your system performance

eBoostr
http://www.eboostr.com/

eBoostr™ allows you to use an additional drive (flash memory or hard disk) as another layer of performance-boosting cache for your Windows XP®. There is no need to purchase a Vista upgrade to get the benefits of the Vista’s ReadyBoost® technology. With the newly developed eBoostr™, the booting of your OS and applications startup get much faster thanks to the smart caching mechanism.

Free for 4 hours

or try another way paging to the thumb drive
http://www.windowsxlive.net/?p=1337

SQL Health Check report for SQL 7

CREATE procedure sp_emailDailyHealthCheck
@charfrom varchar(50),
@charsubject varchar(100),
@charemailserver varchar(15),
@intresults int OUTPUT
AS

/******************************************************************
*
* DAILY HEALTH CHECK SCRIPT - SQL Server
*
*
******************************************************************/
/*
declare @rc int
set @rc=0
exec sp_emailDailyHealthCheck
@charfrom = N'DEL5@tst.com',
@charemailserver = N'1.8.1.1',
@charsubject = N'SQL Health Check Report: (Server: "DELL2500")',
@intresults = @rc
*/
/*****************************************
* Create temp tables for storing the health check output
*****************************************/
--drop table #script_output

CREATE TABLE #script_output (msg varchar(1000),row [int] IDENTITY (1, 1) NOT NULL )
Declare @output varchar(1000)

insert into #script_output values ('')
insert into #script_output values ('

Daily Health Check Report - ' + @@servername + ' - '+ cast(getdate() as varchar) + '

')

set @output = 'SERVER NAME: ' + @@servername + '

'
insert into #script_output values (@output)

set @output = 'VERSION: ' + @@version + '

'
insert into #script_output values (@output )

--- Get the ip address of the server
CREATE TABLE #temp1(t varchar(3000) null)
insert into #temp1 exec xp_cmdshell 'ipconfig'
DECLARE @t1 varchar(300)-- subject to localisation

SET @t1 = (
SELECT top 1 #temp1.t from #temp1
where t like '%IP Address%'
order by t DESC
)


insert into #script_output
SELECT 'IP Address:' +LTRIM(RTRIM(SUBSTRING(t,CHARINDEX(':',t)+1,len(@t1)))) + '
' from #temp1
where t like '%IP Address%'


drop table #temp1
-- end of getting ip address


/******************************************************************
*
* Get Total Disk Space Availaible
*
* This script displays Drive and total sql size used and free size
*
*
*****************************************************************/
Begin
Declare @a Varchar(50)
,@b Varchar(50)
,@c Varchar(50)
,@TSize1 VARCHAR(255)

Create Table #Ops_Drives
(
Drive Varchar(1),
Size int
)
Declare @a1 int
,@b1 int
,@c1 int
,@d1 int

Create Table #Ops_DriveTmp
(
DBName Varchar(25)
,Location Varchar(255)
,Size Varchar(8)
,MaxSize int
,Growth Int
,Device Varchar(30)
)

Exec SP_MSForEachDB
'Use [?] Insert into #Ops_DriveTmp
Select Upper(Convert(Varchar(25),DB_Name())) as ''Database'',
Convert(Varchar(255),FileName),Convert(Varchar(8),Size/128)''Size in MB'',
MaxSize,Growth,
Convert(Varchar(30),Name)
from SysFiles'


Select @a1=Sum(Convert(Int,Size)) from #Ops_DriveTmp

Insert Into #Ops_Drives
Exec Master..XP_Fixeddrives
Create Table #Ops_TmpDriveInfo
(
Drive Varchar(1),
SQL Varchar(8),
FreeSpace Varchar(255),
TotalSpace varchar(255),
Util varchar(4)
)

DECLARE @oFSO1 INT,@oDrive1 INT, @ret1 INT
EXEC @ret1 = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO1 OUT

Declare CheckDrives Cursor For Select Drive,size from #Ops_Drives Order by Drive
Open CheckDrives
Fetch Next from CheckDrives into @c,@c1
While (@@Fetch_Status=0)
Begin
Select @b1=sum(convert(Int,Size))
from #Ops_DriveTmp
where substring(Location,1,1)=@c

Set @a =@c + ':\'

EXEC @ret1 = master.dbo.sp_OAMethod @oFSO1, 'GetDrive', @oDrive1 OUT, @a
EXEC @ret1 = master.dbo.sp_OAMethod @oDrive1, 'TotalSize', @TSize1 OUT

set @d1 = CAST(ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024,0) AS INT)

Insert into #Ops_TmpDriveInfo values(@c,isnull(@b1,0),@c1,@d1 ,
(100 -(round(( @c1)/ ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024 ,0),3) * 100)) )

Fetch Next from CheckDrives into @c,@c1
End



--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

Disk Space

')
insert into #script_output values ('')


insert into #script_output
select ''
from #Ops_TmpDriveINFO
insert into #script_output values ('

Drive	SQL Server Data(MB)	Free Space (MB)	Total Space (MB)	Utilisation(%)
' + Drive + '	' + [SQL]+ '	' + FreeSpace + '	' + TotalSpace + '	' + Util + '

')

drop table #Ops_TmpDriveINFO
drop table #Ops_DriveTmp
drop table #Ops_Drives

exec @ret1 = sp_OADestroy @oFSO1
exec @ret1 = sp_OADestroy @oDrive1

Close CheckDrives

Deallocate CheckDrives

End
/******************************************************************
*
* End Of Get Total Disk Space Availaible
******************************************************************/

/******************************************************************
*
* Get Summary Database Details
*
* This script displays Summary Database Details, % used
*
*
*****************************************************************/
SET nocount ON

DECLARE @dbname SYSNAME

CREATE TABLE #datafilestats (
dbname VARCHAR(200) null,
flag BIT DEFAULT 0 ,
fileid TINYINT,
[filegroup] TINYINT,
totalextents DEC(20,1),
usedextents DEC(20,1),
[name] VARCHAR(200),
[filename] VARCHAR(200))


DECLARE @string SYSNAME

SET @string = ''

SET @dbname = ''

WHILE 1 = 1
BEGIN
SELECT top 1 @dbname = name
FROM master..sysdatabases
WHERE name > @dbname
IF @@ROWCOUNT = 0
BREAK
-- IF NOT (SELECT Databasepropertyex(@dbname,'Status')) = 'ONLINE'
-- BREAK
SET @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
INSERT INTO #datafilestats
(fileid,
[filegroup],
totalextents,
usedextents,
[name],
[filename])
EXEC( @string)
UPDATE #datafilestats
SET dbname = @dbname,
flag = 1
WHERE flag = 0
UPDATE #datafilestats
SET totalextents = (SELECT SUM(totalextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
UPDATE #datafilestats
SET usedextents = (SELECT SUM(usedextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
END

CREATE TABLE #sizeinfo (
db_name VARCHAR(200) NOT NULL PRIMARY KEY CLUSTERED,
total DEC(20,1) null,
data DEC(20,1) null,
data_used DEC(20,1) null,
[data (%)] DEC(20,1) null ,
data_free DEC(20,1) null,
[data_free (%)] DEC(20,1) null,
LOG DEC(20,1) null,
log_used DEC(20,1) null,
[log (%)] DEC(20,1) null,
log_free DEC(20,1) null,
[log_free (%)] DEC(20,1) null,
status DEC(20,1))

INSERT #sizeinfo
(db_name,
[log],
[log (%)],
status)
EXEC( 'dbcc sqlperf(logspace) with no_infomsgs')

UPDATE #sizeinfo
SET data = d.totalextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET data_used = d.usedextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET total = (data + LOG)

UPDATE #sizeinfo
SET [data (%)] = (data_used * 100.0 / data)

UPDATE #sizeinfo
SET data_free = (data - data_used)

UPDATE #sizeinfo
SET [data_free (%)] = (100 - [data (%)])

UPDATE #sizeinfo
SET log_used = (LOG * [log (%)] / 100.0)

UPDATE #sizeinfo
SET log_free = (LOG - log_used)

UPDATE #sizeinfo
SET [log_free (%)] = (log_free * 100.0 / LOG)

BEGIN

--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

Database Summary

')
insert into #script_output values ('' +
'')

Insert into #script_output (msg)
SELECT ''
FROM #sizeinfo WITH (nolock)
ORDER BY total desc

insert into #script_output values ('

Database Name	Total(MB)	Data (MB)	Data Used (MB)	Data Free (MB)	Data Used(%)	Log(MB)	Log Used(MB)	Log Free(MB)	Log Used(%)
' + db_name + '	' + cast(total as varchar) + '	' + cast(data as varchar) + '	' + cast(data_used as varchar) + '	' + cast(data_free as varchar) + '	' + ''+ cast([data (%)]as varchar)+ '	' + cast([log] as varchar) + '	' + cast(log_used as varchar)+ '	' + cast(log_free as varchar)+ '	' + ''+ cast([log (%)] as varchar)+ '

')

END

DROP TABLE #datafilestats

DROP TABLE #sizeinfo

/******************************************************************
*
* End Of Summary Database Details
*
*****************************************************************/





/******************************************************************
*
* SQL Server Disk Space Check
*
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
******************************************************************/

BEGIN
/*****************************************
* Create temp tables for disk space info
*****************************************/
CREATE TABLE #space (dletter varchar(2) null, fspace int, tspace float null)
CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255) null)
CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255),
[filegroup] VARCHAR(10) null, [size] VARCHAR(50),
[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

/*****************************************
* populate temp tables
*****************************************/
INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
INSERT INTO #dbsize EXEC master.dbo.sp_databases

-- Create cursor for files
DECLARE c_files CURSOR FOR
SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]),
RTRIM(af.[size]), RTRIM(db.[name])
FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
WHERE af.dbid = db.dbid AND db.version <> 0
DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
DECLARE @tsize INT, @tdbname VARCHAR(50)
DECLARE @SQL NVARCHAR(255)
DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

-- Open cursor
OPEN c_files
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

-- Populate #growth table with file growth details
WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE #fdata
-- Get file data
SET @SQL = 'INSERT INTO #fdata EXEC '
SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
EXEC sp_executesql @SQL
SELECT @growth = growth FROM #fdata
-- Determine if growth is % or Mbytes
IF RIGHT(@growth,1) = '%'
BEGIN
SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
SET @gtype = '%'
END
ELSE
BEGIN
SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
SET @gtype = 'MB'
END
-- Create record for file in #growth table
INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
END

-- Close cursor
CLOSE c_files
DEALLOCATE c_files

/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM #space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace float

-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT



-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE #space
SET tspace = CAST(@drsize AS float)
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace


END

-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks

-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO


/*****************************************
* Return disk space info
*****************************************/
insert into #script_output values ('
')
insert into #script_output values ('

Database Detail Space Usage Info

')
insert into #script_output values ('')
insert into #script_output values
('')


insert into #script_output (msg)
SELECT ''
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]
ORDER BY ds.dbsize desc ,db.[name], af.[name]

insert into #script_output values ('

Database Name	File Name	Database Size(MB)	File Size(Mb)	Free DiskSpace(Mb)	Total DiskSpace(Mb)	Next Extension	Next Expansion	File Path
' + db.[name] + '	' + af.[name] + '	' + CAST(ROUND(CAST(ds.dbsize AS FLOAT) / 1024,0) AS varchar) + '	' + CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS varchar) + '	'+ CAST(s.fspace AS varchar)+ '	' + CAST(ROUND((CAST(s.tspace AS FLOAT) / 1024) /1024,0) AS varchar) + '	' + STR(g.next_exp) + ' KB	' + CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS varchar) + '	' + af.[filename] + '

')

/*****************************************
* Drop temporary tables
*****************************************/
DROP TABLE #space
DROP TABLE #dbsize
DROP TABLE #fdata
DROP TABLE #growth

END

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
*Check for Last Backup Done.
******************************************************************/
--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

Last Backup

')
insert into #script_output values ('')

insert into #script_output (msg)
SELECT ''
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

insert into #script_output values ('

Database Name	Days Since LastBackup	LastBackup
' + B.name + '	' + ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') + '	' + ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') + '

')

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/


/******************************************************************
*
* Lists user and permission
******************************************************************/


-- ***************************************************************************
-- Declare local variables
DECLARE @DBName1 VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE ##Users (
[sid] varbinary(85) NULL,
[Login Name] varchar(24) NULL,
[Default Database] varchar(18) NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO ##Users SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
'NO'
-- CASE [bulkadmin]
-- WHEN 1 THEN 'Yes'
-- ELSE 'No'
-- END AS [bulkadmin]
FROM master.dbo.syslogins
Where sysadmin =1 or securityadmin =1 or serveradmin =1 or setupadmin=1 or processadmin = 1
or diskadmin=1 or dbcreator =1 -- or bulkadmin=1;

---------------------------------------------------------


--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

List Of Users That Has Server Roles

')
insert into #script_output values ('' +
'' +
'')

insert into #script_output (msg)
SELECT ''
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]


insert into #script_output values ('

Login Name	Default Database	Login Type	sysadmin	securityadmin	serveradmin	setupadmin	processadmin	diskadmin	dbcreator	bulkadmin
' + [Login Name] + '	' + [Default Database]+ '	' + [Login Type] + [AD Login Type] + '	' + [sysadmin] + '	' + [securityadmin] + '	' + [serveradmin] + '	' + [setupadmin] + '	' + [processadmin] + '	' + [diskadmin] + '	' + [dbcreator] + '	' + [bulkadmin] + '

')

-- ***************************************************************************
-- ***************************************************************************

-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
-- ***************************************************************************


-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
-- ***************************************************************************
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- ***************************************************************************
-- ***************************************************************************
--
SELECT @SQLCmd = 'INSERT ##DBUsers ' +
' SELECT ''' + @DBName1 + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.LoginName, ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName1 + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN master.dbo.syslogins u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName1 + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName1 + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' AND u.LoginName is null '
EXEC (@SQLCmd)
-- ***************************************************************************
-- ***************************************************************************
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- End of the cursor loop
END
-- ***************************************************************************
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- ***************************************************************************

--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

List Of Orphan Users

')
insert into #script_output values ('' +
'')


-- Return the Database User data
insert into #script_output (msg)
SELECT ''
FROM ##DBUsers
ORDER BY [Database User ID],[Database];


insert into #script_output values ('

Database User ID	Server Login	Database Role	Database
' + [Database User ID] + '	' + [Server Login] + '	' + [Database Role] + '	' + [Database] + '

')

-- ***************************************************************************
-- ***************************************************************************
DROP TABLE ##Users;

DROP TABLE ##DBUsers;
-- ***************************************************************************



/******************************************************************
*
* End of Lists user and permission
*
******************************************************************/

/******************************************************************
*
* Failed Scheduled alert jobs logs
*
******************************************************************/

--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

Failed Scheduled SQL Job

')
insert into #script_output values ('')

insert into #script_output
SELECT ''
FROM
msdb..sysjobhistory jh
INNER JOIN
msdb..sysjobs j
ON
j.job_id = jh.job_id
WHERE
jh.step_name = '(Job outcome)'
and jh.run_date >= cast(year(getdate() - 1)as varchar) + right('0'+ cast(month(getdate() -1 )as varchar),2) + right('0'+ cast(day(getdate() -1) as varchar),2)
and jh.run_status =0 -- failure

insert into #script_output values ('

Job Name	Run Date & Time
' + j.name + '	' + cast(DATEADD ( SECOND, jh.run_duration, CAST (CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') AS DATETIME ) ) as varchar) + '

')

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
* Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/


CREATE TABLE #Errors (vchMessage varchar(500), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC sp_readerrorlog

--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #Errors
SET ROWCOUNT 0

--- set the output
insert into #script_output values ('
')
insert into #script_output values ('

Last 20 SQL Servers log

')

insert into #script_output (msg)
SELECT top 20 ''
FROM #Errors
WHERE vchMessage NOT LIKE '%Copyright (c)%'
AND (isdate(substring(vchMessage,1,10)) = 1)
AND substring(vchMessage,1,10) > convert(varchar(10),(dateadd(dd,-1,getdate()-1)),120)
ORDER BY ID DESC


insert into #script_output values ('

' + vchMessage + '

')

DROP TABLE #Errors


/******************************************************************
*
*End of Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/



/******************************************************************
*
* Writing the script output to html file
*
******************************************************************/

insert into #script_output values ('')


DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80),
@objFile int


set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT



Declare @Filename varchar(30)
--set @Filename = 'DailyHealthcheck_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') + '.html'
set @Filename = 'c:\DailyHealthcheck.html'

Select @FileAndPath=@filename


if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,1,True


if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'

Declare msgcursor CURSOR FOR
select msg from #script_output order by row

Declare @String1 varchar(2000)

Open msgcursor
fetch next from msgcursor
into @String1

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String1

fetch next from msgcursor
into @String1

END

Close msgcursor
Deallocate msgcursor

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

drop table #script_output

/******************************************************************
*
* End Of Writing the script output to html file
******************************************************************/


/******************************************************************
*
* Sending the health check report to email
******************************************************************/
---- get all the operator email
Declare c2 scroll cursor
for
SELECT email_address FROM msdb.dbo.sysoperators
where enabled=1

Declare @email varchar(3000)
Declare @tmpemail varchar(500)
set @email =''

open c2
fetch next from c2 into @tmpemail
While (@@Fetch_Status <> - 1)
begin

set @email= @email + @tmpemail + ','
fetch next from c2 into @tmpemail
end
close c2
deallocate c2

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@from = @charfrom,
@to = @email,
@server = @charemailserver,
@subject = @charsubject ,
@attachment = N'c:\DailyHealthcheck.html',
@message = N'See Attached report '


/******************************************************************
*
* End Of Sending the health check report to email
******************************************************************/






GO