InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:28:26 GMT20http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxOkay. Best suggestion then is to try some PowerShell user groups / sites. If you find the answer feel free to share it. :)Fri, 12 Apr 2013 17:58:16 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxNo, I have not asked. I am new to powershell as well. I am in no rush.Fri, 12 Apr 2013 17:11:24 GMTjuan Munozhttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxHi there.I am glad that the script has helped you out (I actually have plans for expanding it soon-ish).Unfortunately, I know nothing about PowerShell, so I won't be able to help you out. :( Have you tried posting this question on other forums to see if an expert can assist you?Fri, 12 Apr 2013 16:44:49 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxTo Sean Smith,Reg: usp_SQL_Server_System_ReportFirst off, Thank you for this script. It has made my life a lot easier. I was woundering if you would be able to modify the script to use mounting points for the disk drive info? I am not the greatest tSql developer and I have had the hardest time trying to modify your script. I have tried to modify it using the following code as a starting point.declare @svrName varchar(255)declare @sql varchar(400)--by default it will take the current server name, we can the set the server name as wellset @svrName = @@SERVERNAMEset @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'--creating a temporary tableCREATE TABLE #output(line varchar(255))--inserting disk name, total space and free space value in to temporary tableinsert #outputEXEC xp_cmdshell @sql--script to retrieve the values in MB from PS Script outputselect rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'from #outputwhere line like '[A-Z][:]%'order by drivename--script to retrieve the values in GB from PS Script outputselect rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'from #outputwhere line like '[A-Z][:]%'order by drivename--script to drop the temporary tabledrop table #outputPlease let me if this is something you can work on.JMFri, 05 Apr 2013 16:18:32 GMTjuan Munozhttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxActually, that is a decent workaround for now. :)Wed, 12 Sep 2012 10:56:23 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxNot sure if this meets your requirement but if you choose the option to send the report by email the recipient can then save it manually.Wed, 12 Sep 2012 10:11:11 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxThere are various methods of saving output to a file. Unfortunately it is too long to go into detail here, but if you Google "SQL Server saving results to a file" you should be able to find some good articles to help you out. I've noted the idea as it is a good one and I will likely add it as an option when I do my next update of the code to allow for saving of the data to HTML, text, CSV, etc.Wed, 12 Sep 2012 05:29:44 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxHi Sean, U have done a fantastic job designing a script which will give entire server report. But , is there any possibility, to save the report to local disk in the form of some html report?Wed, 12 Sep 2012 01:24:02 GMTmissionrestarthttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxFantastic. Hope you enjoy some of the new features of this version. :)Wed, 05 Sep 2012 10:20:46 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxDoh! Hadn't set the output mode to E. It all works fine now. Thanks againWed, 05 Sep 2012 10:03:37 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxThanks that works however, I'm still not getting the report via email when I run my SQL job. Database mail is working. Is the only line I need to change SET @vRecipients? Thanks for your patience.Wed, 05 Sep 2012 09:53:15 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxOkay, copy the script in full from this site and run it in the appropriate database (don't do anything past that point). Let me know if it at least creates the proc properly.Wed, 05 Sep 2012 09:33:25 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxI've made a bit of a hash of this I'm afraid. Firstly I ran the latest script ok but had problems getting my scheduled job to work .......... so I deleted the old sp and the new one and tried running the new script again but when I do I get the error shown below. Please help.Msg 102, Level 15, State 1, Line 49Incorrect syntax near 'Calculation'.Msg 111, Level 15, State 1, Line 67'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.Wed, 05 Sep 2012 09:29:34 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxNo I don't. Does this mean it's not the latest version?Wed, 05 Sep 2012 03:12:04 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxDo you have the "@v_Output_Mode" parameter in the script?Tue, 04 Sep 2012 09:19:57 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxOn closer inspection I think I probably do have the latest - I created the report on 6/7/2010. How could I tell for sure?Tue, 04 Sep 2012 08:48:26 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxIt might, or at the least be easier to trouble shoot. The latest version is the one currently posted on SSC.com:http://www.sqlservercentral.com/scripts/Administration/70243/Tue, 04 Sep 2012 08:20:51 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxNo I don't. Do you think it will solve the problem?Tue, 04 Sep 2012 08:18:50 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxand where is the best place to get the most current script?Tue, 04 Sep 2012 08:18:39 GMTSQL_Padrehttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxThere were some changes to the script recently. Do you have the latest one?Tue, 04 Sep 2012 08:16:08 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxI've been using this report quite happily for well over a year now on 3 of my servers. However, on one of them it has started failng with the error below. Any ideas? I should add that I recently upgraded from 2005 to 2008R2 but it was working fine for a couple of weeks after that.Executed as user: Domain\username. There is insufficient result space to convert a money value to varchar. [SQLSTATE 22003] (Error 234). The step failed.Tue, 04 Sep 2012 08:02:29 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxSome new changes coming very soon to this script! :)"Last Backup Set Details" Section Will Only Show Information For Databases Currently On The InstanceAdded "@v_Output_Mode" Input Parameter (you can select if you want the results to be displayed on-screen or via e-mail now!)Added "backup_finish_date", "database_version", "machine_name", And "server_name" To "Last Backup Set Details" SectionAdded "connections", "first_day_of_week", "is_clustered", And "windows_version" To "Server Instance Property Information" SectionAdded "create_date" And "file_name" To "Database Recovery Model / Compatibility / Size (Detailed) / Growth Stats" SectionAdded "schema_name" To "Unused Indexes" SectionExtended "CONVERT" Character Length To Deal With "Msg 234" ErrorRewrote Time Calculation Logic, Code Restructuring For Better Query Performance & Minor Changes To Code StyleSeanFri, 27 Jan 2012 07:36:11 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxHmm... interesting issue. I haven't tried, but I may have taken the approach of writing the results out to a file (HTML tags included) into a text file, but name it something like "my_results.html" and then send a text email with the file as an attachment. Let me know if that makes sense.Fri, 19 Nov 2010 14:34:39 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxHey Sean, This is an excellent script and I am happy to use it. Our environment does not allow any email format but plain text. I been messing with the script, trying to create an attached (html) file but have little luck (except to rewrite the code, drop each result in a table and then bcp it as a file to be attached) which results in html formatting code being placed where values need to be. Have you though of doing this yourself?Fri, 19 Nov 2010 11:00:17 GMTgbradenhttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxI was thinking of doing that, but I noticed an "issue" with a one-to-many relationship for tables that were partitioned to I opted to leave it out for the first version of the report. I think I will revisit it though. Thanks for the comment. :)Thu, 04 Nov 2010 15:21:56 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxJust read my last post. Not sure what happened to the text but what I wrote got garbled in the translation. What I meant was F:\data\dbname.mdfThu, 04 Nov 2010 04:24:06 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxSeanIt would be really useful if you could add the path and fiilename of the database and log files to the Database Size (Summary) / Distribution Stats table, e.g. F:\dataorthwind.mdf.George25Thu, 04 Nov 2010 04:15:59 GMTgeorge25http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxFor sure. You would need to have all of your linked servers set up correctly, and then from there you could add an input parameter to the proc which would pass the linked server name and append it to the SQL code (some adjustments would need to be made to the code). Or, you could query all of the linked server names from the system tables and loop through, producing a report for each (again, modifications to the code would be needed). Let me know if this all makes sense. :)Sat, 16 Oct 2010 10:16:49 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxUnfortunately, it will likely be a while from now. I've recently changed jobs and on top of that I am expecting my first child in < 1 month. So, things are busy! But it's still on my radar, just no ETA. For the recent jobs run, you *may* have a process which clears the jobs out of the history tables fairly frequently (I had the same problem on one server I was working with... someone had setup a cleanup process to run every 5 minutes!). You may want to query these tables separately to see what they contain. Let me know if you need help, and thanks for the feedback!Sat, 16 Oct 2010 10:09:30 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxWould it be possible to run this script from a "central server" and using Linked Servers collect data from remote servers?Fri, 15 Oct 2010 09:34:14 GMTSQL_Padrehttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxSean, love the report (but for me it doesn't produce anything for the scheduled SQL jobs recently run?).You seem to have a list of improvements/additions from the various respondees, so any idea when the revamped version is going to arrive?More power to the efforts put in by the likes of yourself, as sharing the 'tricks' of the trade all helps make our lives a lot easier.Thanks.Mon, 11 Oct 2010 08:05:30 GMTUDBNThttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxGood news. Thanks for sharing the solution. :)Tue, 05 Oct 2010 18:10:30 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxYes. I believe I figured out what the problem was. It just so happens that the 2008 servers are larger servers and therefore have more databases, indexes, etc. After confirming in the system views that the mail indeed showed "sent" I suspected it had something to do with the data itself so I changed the "body" to print "test" and it went through fine. I suspected that it may have been the size of the data in the @vBody variable so I commented out the "unused indexes" section and ran it again and it went through fine. I then tried changing it to send the output in an attachment with the parameter to allow for the longer length fields and it indeed erred out with a size limit. So, I believe the mystery is solved.Tue, 05 Oct 2010 16:22:11 GMTanapattersonhttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxIt's pointing to the correct db mail profile and you changed the email address in the proc, correct?Tue, 05 Oct 2010 16:12:20 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxI've tried to run this on my 2008 machines that have Database Mail set up and running successfully with job notifications, but I don't receive this sproc's email.Tue, 05 Oct 2010 06:10:56 GMTanapattersonhttp://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxThe report is intended to compile SQL Server information and then send the results to a recipient via email. You will need to have mail functionality turned on and configured. There's a lot of great resources online to help you do this:http://www.google.ca/search?q=how+to+set+up+sql+server+2005+mailLet me know if you have question or feel I can help more.Wed, 30 Jun 2010 18:15:34 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxMy SQL server database mail is not set upWed, 30 Jun 2010 17:53:40 GMTBarcelona10http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxIt sounds like you are set up to send mail from SQL Server, but can you confirm this? And if yes, do you have any other processes that currently send mail from SQL Server?Wed, 30 Jun 2010 17:22:34 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxhmmm. Let me think. I wish Sean (the author) was onlilne right now. But he is good about getting back to you within a day. I'm going to look at the previous replies to this...You too. Let's see what we come up with.Wed, 30 Jun 2010 17:17:39 GMTamc-885860http://www.sqlservercentral.com/Forums/Topic922456-1371-1.aspxI was running on 2008.Would that mather?Wed, 30 Jun 2010 16:58:03 GMTBarcelona10