InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 18:48:07 GMT20http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxThanks for the great script, but I want to save the output to servername.htmlSun, 10 Mar 2013 01:01:36 GMTcletocshttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi This is krupanandh.N working as a sql dataabase Administrator in spi mysore.i have scheduled sp "dbo.uspEmailSQLServerHealth" daily triggered at @9 am .But The job is failing but iam getting reports.when iam executed this job iam geting the following error.MessageExecuted as user: domain\user An INSERT EXEC statement cannot be nested. [SQLSTATE 42000] (Error 8164). The step failed. any one help me on this.Thankskrupanandh.NMon, 08 Oct 2012 03:25:10 GMTnimmagaddakrupanandhhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxThanks for the script...I did some modifications as per my requirement and it works like a charm.But, when i scheduled this as a job i am getting a blank report. but if i run the same code in the job in query window, i get the html output.I tried running the job myself manually, still same result.Any hints?ThanksMvMTue, 04 Sep 2012 17:48:52 GMTmanus4uhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxThe very first post has a link in it´s footer:http://www.sqlservercentral.com/Forums/Attachment5016.aspxMon, 14 May 2012 05:36:01 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxand this is the code which i use:-- Variable declaration DECLARE @TableHTML VARCHAR(MAX), @StrSubject VARCHAR(100), @Oriserver VARCHAR(100), @Version VARCHAR(250), @Edition VARCHAR(100), @ISClustered VARCHAR(100), @SP VARCHAR(100), @ServerCollation VARCHAR(100), @SingleUser VARCHAR(5), @LicenseType VARCHAR(100), @StartDate DATETIME, @EndDate DATETIME, @Cnt int, @URL varchar(1000), @Str varchar(1000), ---------------------------- Section A ------------------------------ SET @TableHTML = '<font face="Verdana" size="4">Server Info</font> <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50"> <tr> <td width="27%" height="22" bgcolor="#000080"><b> <font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td> <td width="39%" height="22" bgcolor="#000080"><b> <font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td> <td width="90%" height="22" bgcolor="#000080"><b> <font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td> </tr> ---------------------------- Section B ------------------------------ <tr> <td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td> <td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td> <td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td> </tr> </table>Sat, 12 May 2012 23:48:23 GMTalnawrass2002http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote][b]Dirk.Hondong (4/24/2012)[/b][hr]Look at the very 1st post.There is an attachment with the script. It has an .txt ending. Regarding the code:Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.[code="sql"]-- Variable declaration DECLARE @TableHTML VARCHAR(MAX), @StrSubject VARCHAR(100), @Oriserver VARCHAR(100), @Version VARCHAR(250), @Edition VARCHAR(100), @ISClustered VARCHAR(100), @SP VARCHAR(100), @ServerCollation VARCHAR(100), @SingleUser VARCHAR(5), @LicenseType VARCHAR(100), @StartDate DATETIME, @EndDate DATETIME, @Cnt int, @URL varchar(1000), @Str varchar(1000) [/code]RegardsDirk[/quote]Dear DirkI cant find any txt file attached in this post,but i copy and paste your code there then i got the following error msg:Must declare the scalar variable "@ServerIP".Msg 105, Level 15, State 1, Line 16Unclosed quotation mark after the character string '</font></td> </tr> </table&gtSat, 12 May 2012 21:50:30 GMTalnawrass2002http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi there,it´s the same on my side.I look out for useful scripts, tools etc and adapt them.The repository mentioned in the tacklebox is running on one server in the company I work for and gathers information from more than 200 productive instances.So the only thing I did was to enter all servers in on of the tables of the repository. That´s the "source" for the ssis package which then connects to each server indiviudally and gathers the information.Quite easy if you can setup up some kind of service account which has access to all your servers/instances.This account is also the proxy account on the server which is running the ssis package. To set it up properly and let it run in my environment it took me about 3-4 hours and now works like a charm.Regards and have a great weekendDirkSat, 12 May 2012 10:29:22 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxThanks a lot Dirk for your quick response..!!Info provided is really good but am looking for a such a automating scripts that should be running on one server and retrieves the SQL service's status for all servers in domain.Benefit of it would be that one server will act something like "Monitor server". Am a core SQL server DBA and do not have much knowledge on t-sql programming part...Let me know if you get some info on the same.Thanks,Fri, 11 May 2012 05:35:07 GMTglamourthhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxI never tried it in this way...What I did to run the solution:Implemented the proc and a corresponding sql srv job on each server.Now I get from every server a report in the morning. With the help of a filter rule I highlight those emails which contain a failed or false error message If you need to gather the informations first, then I would recommend that you take a look at [url=http://www.sqlservercentral.com/articles/books/67536/]Rodney Landrum's Tacklebox. [/url]There's an example how to build a repository, get information from multiple servers via a SSIS package and afterwards run reports against these information.Fri, 11 May 2012 00:48:46 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi All,Your the scripts is awesome..!!Can you guide how this can be modified to get the same information for multiple servers i.e running this script in one server that will provide information for all server of the same domain...I will be glad to get guidance on this...Thanks.!Thu, 10 May 2012 07:30:50 GMTglamourthhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxLook at the very 1st post.There is an attachment with the script. It has an .txt ending. Regarding the code:Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.[code="sql"]-- Variable declaration DECLARE @TableHTML VARCHAR(MAX), @StrSubject VARCHAR(100), @Oriserver VARCHAR(100), @Version VARCHAR(250), @Edition VARCHAR(100), @ISClustered VARCHAR(100), @SP VARCHAR(100), @ServerCollation VARCHAR(100), @SingleUser VARCHAR(5), @LicenseType VARCHAR(100), @StartDate DATETIME, @EndDate DATETIME, @Cnt int, @URL varchar(1000), @Str varchar(1000) [/code]RegardsDirkTue, 24 Apr 2012 02:15:34 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxWhere is the .sql File. Also, i do not see SP code for TableHTMLMon, 23 Apr 2012 23:31:25 GMTglamourthhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote][b]Dirk.Hondong (4/10/2012)[/b][hr]What exactly do you need or in which part of the sproc do you want to declare an additional variable?[quote][b]alnawrass2002 (4/8/2012)[/b][hr]How can i declared a variable at the beginning of the stored procedure, @TableHTML ?[/quote][/quote]Its mentioned in the article: " I have declared a variable at the beginning of the stored procedure, @TableHTML, which gets built and then executed at the end before it sends an e-mail".What the code the declare the same?RegardsSat, 14 Apr 2012 23:10:14 GMTalnawrass2002http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxWhat exactly do you need or in which part of the sproc do you want to declare an additional variable?[quote][b]alnawrass2002 (4/8/2012)[/b][hr]How can i declared a variable at the beginning of the stored procedure, @TableHTML ?[/quote]Tue, 10 Apr 2012 02:31:45 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxSorry Christopher,I completely forgot to look after the modification to get rid of the overflow error.The only difference I have found is in line 330 (depending on your editor of choice).It's the [code="sql"]select ...from sys.databases MSTinner join (select b.name [LOG_DBNAME], CONVERT(DECIMAL(10,2), sum.... [/code]Here I have a DECIMAL(20,2).Afterwards the script worked fine for me.[quote][b]christopher.jones 39322 (11/18/2011)[/b][hr]Has there been a fix for the error below?Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.[/quote]Tue, 10 Apr 2012 02:27:05 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHow can i declared a variable at the beginning of the stored procedure, @TableHTML ?Sun, 08 Apr 2012 08:46:16 GMTalnawrass2002http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxI have to take a look on monday when I´m back in office.Also had the error in the beginning. Just changed a variable or column of a temp table since it was too small.That fixed it for me.Cannot remember right now where the problem exactly wasFri, 18 Nov 2011 12:48:54 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHas there been a fix for the error below?Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.Fri, 18 Nov 2011 12:08:49 GMTchristopher.jones 39322http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxFind it useful. Many Thanks!RoyTue, 15 Nov 2011 19:24:46 GMTroy_cslhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHello,I'm not a expert but there something tricky because I didn't get the right execution time for jobs.First I replaced "INSERT #jobs_status EXEC msdb.dbo.sp_help_job" by "select sj.job_id, sj.name, sc.name as Category, sj.Enabled, sjs.last_run_outcome,(select max(run_date) from sysjobhistory sjh where sjh.job_id = sj.job_id) as last_run_dateinto #jobs_statusfrom sysjobs sjjoin syscategories scon sj.category_id = sc.category_idjoin msdb.dbo.sysjobservers sjson sjs.job_id = sj.job_id "It works but I thing there are a problem with this part of script :"Datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes"Why did you use "last_executed_step_date" and not "start_execution_date" to calculate the execution time of job.I executed this script to check I get a different result but when I compared with the history of jobs I got the right result :select A.job_id,A.start_execution_date,A.stop_execution_date,datediff(mi, A.start_execution_date, A.stop_execution_date) execution_time_minutes from msdb..sysjobactivity A Could you help me ?Tue, 14 Sep 2010 03:30:45 GMTERIC CRUDELIhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxIs there a SQL2000 version of this SP?...thanksThu, 22 Jul 2010 08:48:49 GMTrew-370421http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi all,just a quick hint concerning the change of the proc using xp_dirtree instead of xp_cmdshell.You have to modify the string [code="sql"]INSERT #dirpaths values('')[/code]to[code="sql"]INSERT #dirpaths values('','','')[/code]Otherwise you get an error when inserting the blank row into #dirpaths (got it by myself on a SQL 2008)RegardsDirk Wed, 16 Jun 2010 04:15:29 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx@Ritesh,Welcome :-)Mon, 14 Jun 2010 02:46:13 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx@Raj thats excellent and thank you so much for posting on this article :)Mon, 14 Jun 2010 02:00:44 GMTRitesh Medhehttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx@Ritesh,[quote]@arr.nagaraj i will work on CPU thing[/quote]CPU used by SQL Server can be found from sys.dm_os_ring_buffers.Refer [url] http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html[/url]Mon, 14 Jun 2010 01:01:03 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote][b]nplace6530 (2/2/2010)[/b][hr]Hi AllI was also a little concerned about turning on cmdshell so I have made the following changes to the original script which shows backup file data but does not use cmdshell.Replace:[code="sql"]CREATE TABLE #dirpaths ( files VARCHAR(2000))[/code]With:[code="sql"]CREATE TABLE #dirpaths ( files nvarchar(2000), Depth int, IsFile bit)[/code]And replace:[code="sql"] SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D''' INSERT #dirpaths SELECT 'PATH: ' + @URL[/code]With:[code="sql"] select @Str = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1' INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1[/code]This replaces the "EXEC master.dbo.xp_cmdshell" command with the undocumented "EXEC Master.dbo.xp_DirTree" command.No need to switch on the potentially dangerous xp_cmdshell option.[/quote]First off, thanks for the code. It is amazing and it will help me greatly if i resolve this issue.The code was working fine but for security reasons I needed to turnoff xp_cmdshell on production environment. Then I tried the work around above and I replaced both segments of the code but i couldnt get it working to return the Physical Backup Files . However when I only commented out SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D''' and it just work fine. Was it supposed to work eventhough i commented out this part of the code? I checked again and xp_cmdshell is still turned off. Can I then assume it is safe and continue using it on production environment safely?Please help.Thanks,Thu, 04 Mar 2010 22:02:12 GMTDaniSQLhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxYou need to use two single quotes eg:@query = 'exec uspEmailSQLServerHealth10 ''192.168.1.120'', ''abc'', ''suyog.pagare@gmail.com;;'', ''test'', ''MYSELF''',If you're editing the SQL statement in Management Studio the entire value for the @query parameter should be read to indicate a string (the default coloring).Tue, 23 Feb 2010 17:33:21 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxhi Ritesh,Thanks for the query... its very useful for DBA..i have one doubt..how to send the output of query as an attachment.i use exec msdb.dbo.sp_send_dbmail @profile_name = 'test', @recipients = 'suyog.pagare@gmail.com', @query = 'exec uspEmailSQLServerHealth10 '192.168.1.120','abc','suyog.pagare@gmail.com;;','test','MYSELF'', ', @subject = 'Work Order Count', @attach_query_result_as_file = 1 ;but i got one error sayingincorrect syntax near '192.168'i have put server name instead of IP but its not working.please help me out with some resolutions...my basic requirement is to send the output to outlook mail.Tue, 23 Feb 2010 05:58:24 GMTsuyog007_extchttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxA selection of examples[url=http://www.sqlservercentral.com/articles/powershell/65324/]http://www.sqlservercentral.com/articles/powershell/65324/[/url][url=http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/]http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/[/url][url=http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/]http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/[/url]Wed, 03 Feb 2010 17:12:09 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi Phil,/* With the push towards Powershell and CLR, expect a lot, read all, of these extended stored procedures to be phased out. */Powershell/CLR can be a good alternative for a undocumented option. Would be gr8 if you couldelaborate how CLR/Powershell would solve the current issue. As already a few folks have started using this solution, they would be benifit by your response :-)/*My preference is to never use undocumented procedures when a documented alternative is available.*/Agreed again. But under the current circumstances, we wanted to compare xp_cmdshell and xp_dirtreeand clearly realise the issues attached with xp_cmdshell.Regards,RajWed, 03 Feb 2010 16:54:29 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote][b]nplace6530 (2/3/2010)[/b][hr]The xp_dirtree function was only added in SQL2005 so it’s not likely to disappear and will probably be around for quite a while.[/quote]Actually xp_dirtree has been around for a while, probably as long as xp_cmdshell.With the push towards Powershell and CLR, expect a lot, read all, of these extended stored procedures to be phased out.My preference is to never use undocumented procedures when a documented alternative is available.Wed, 03 Feb 2010 15:17:23 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx@nplace6530,Yes true. Along with their longevity, the scary part about undocumented stuffis their behavior after a patch installation/hot fix update which alter system tables and hence can change the behavior of undocumented sp. MS needn't announce it as its Undocumented.To me Undocumented is acceptable on a health check script but not on application codeWed, 03 Feb 2010 03:09:18 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxThe xp_dirtree function was only added in SQL2005 so it’s not likely to disappear and will probably be around for quite a while.Wed, 03 Feb 2010 02:59:45 GMTnplace6530http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxphilcart,The idea is if a health check script fails(becos of undocumented function), applications dont crash and users dont complain. But if a server crashes or data loss occurs because of hack attack using xp_cmdshell then people seriously complain and the DBA is in a soup.So I prefer xp_dirtree to xp_cmdshell. If you have any specific reason to differ please explain.Regards,RajWed, 03 Feb 2010 02:16:04 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote]As Health check scripts are used only by DBAs undocumented stuffare not a huge pblm.[/quote]Unfortunately, as evidenced by a number of replies to this article, its not only executed by DBAs. In this day and age, it unfirtunate that more and more non-DBAs are being asked to look after databases with no real training at all.Yes, you can replace OSQL in my example with SQLCMD. It will basically be the same thing. For an even more up-to-date approach, you could use a Powershell script in a SQL Agent job.Wed, 03 Feb 2010 02:05:23 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx@phil,OSQL/SQLCMD is n't a gr8 option either. OSQL marked depreciation and replaced by SQLCMD. I would like to know the reason why u say its dangerous to use xp_dirtree ( other than it being undocumented )IMO, I prefer dirtree to xp_cmdshell as enabling it allows someone to execute any DOS cmd. As Health check scripts are used only by DBAs undocumented stuffare not a huge pblm.Wed, 03 Feb 2010 00:34:00 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspx[quote][b]nplace6530 (2/2/2010)[/b][hr]Hi All... With:[code="sql"] select @Str = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1' INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1[/code]This replaces the "EXEC master.dbo.xp_cmdshell" command with the [b]undocumented[/b] "EXEC Master.dbo.xp_DirTree" command.No need to switch on the potentially dangerous xp_cmdshell option.[/quote]Not really a good solution to replace one potentially dangerous option with another.If the filename from msdb.dbo.backupmediafamily isn't sufficient and you need to retrieve the file names directly from the disk, how about running a short batch file using Windows Task Scheduler.Something like,[code]DIR G:\MyBackups /B/O:D > C:\backup_file_list.txtOSQL -E -S. -Q"delete from MyDB.dbo.backup_file_list"BCP MyDB.dbo.backup_file_list in "C:\backup_file_list" -c -T[/code]Then you have the list of physical files there ready for your "health check" procedure.Tue, 02 Feb 2010 23:03:14 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxnplace, thx this is awesome...Tue, 02 Feb 2010 22:28:08 GMTRitesh Medhehttp://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxHi AllI was also a little concerned about turning on cmdshell so I have made the following changes to the original script which shows backup file data but does not use cmdshell.Replace:[code="sql"]CREATE TABLE #dirpaths ( files VARCHAR(2000))[/code]With:[code="sql"]CREATE TABLE #dirpaths ( files nvarchar(2000), Depth int, IsFile bit)[/code]And replace:[code="sql"] SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D''' INSERT #dirpaths SELECT 'PATH: ' + @URL[/code]With:[code="sql"] select @Str = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1' INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1[/code]This replaces the "EXEC master.dbo.xp_cmdshell" command with the undocumented "EXEC Master.dbo.xp_DirTree" command.No need to switch on the potentially dangerous xp_cmdshell option.Tue, 02 Feb 2010 05:19:41 GMTnplace6530http://www.sqlservercentral.com/Forums/Topic854906-2606-1.aspxVote Of Thanks!!!!!!!Thanks for all the posts. I really appretiate and I hope that this article will be helpful for DBAs and make their life little easy.Special thanks (For making my solution perfect and fixing the bugs :))@Mark-1022992: For pointing out exec EmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile'exec uspEmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile'@SRosewarne-795472: For recommending table vairable.@nplace6530: For fixing An INSERT EXEC statement cannot be nested@david.beechum: reducing the backup stats range from 1 month to one day this has certainly improved the performance.@timothy.shawley: For fixing the HTML tags issue.Thanks a lot guys.......I have also added this post at the begining as you guys deserve the credit.....Mon, 01 Feb 2010 01:34:51 GMTRitesh Medhe