March 3, 2011 at 6:24 am
The scripts have several write-verbose statements which you can thing of as PRINT statements only that you need to "turn them on" in PowerShell. Here's how you would do that:
Open a PowerShell console and run
$VerbosePreference
Notice the default setting is
SilentlyContinue
Change $VerbosePreference:
$VerbosePreference = 'Continue'
Next manually run the scripts instead of using the SQL Agent jobs (BTW you do need to modify the spaceanalysis.job.sql script and change Z102063V\SQL2K8 to your server instance where the spacedm database is located)
Note: Your path to the scripts may differ from C:\bin if so you'll need to change the SQL job.
C:\bin\Write-VolToDb.ps1 'Z102063V\SQL2K8 'spacedm'
C:\bin\Write-DbSpaceToDb.ps1 'Z102063V\SQL2K8' 'spacedm'
The list of target SQL Servers are pulled from the table server_space_lku by the respective scripts write-voltodb.ps1 and write-dbspacetodb.ps1. In my example the table is located on the Z102063V\SQL2K8 server instance in the spacedm database.
You should see the verbose statements "print" in a yellow font by default. This should give you an indication of what parameters are being passed to the script. More than likely one of the parameters isn't correct.
March 3, 2011 at 2:22 pm
Thanks cmille19 for the quick response.
As suspected it was my fat fingering in the server_space_lku table, even though I checked it 5 times.
I also added a timeout to the connection string as we are having network issues at the moment.
I have changed the SQL query to be a LEFT outer join between sysfiles and sysfilegroups so I can get log information as well. This required me to change the Group name column to allow nulls.
Thanks Chad for a fantastic solution.
March 3, 2011 at 2:27 pm
No problem, glad its working for you.
March 7, 2011 at 3:04 pm
Does Powershell have a Try\Catch concept or some way to resume after certain types of errors?
My situation is the loop through the table of servers fails completely if there is a SQL timeout or connection failure during a connection.
I would like the process to continue even if 1 server is unavailable.
March 7, 2011 at 4:28 pm
PowerShell V2 (released in October 2009 after this article was written), does have try/catch. In addition you may want to look at your $erroractionpreference setting. The default setting continue, should be enough to not stop on one server.
May 23, 2011 at 10:16 pm
I had a question on automating the report generated by reporting services
At the moment the report can be generated using the reporting services with the start and the end date
When I enter a subscription to run the reort and email then the start and end date needs to be specified
Is there a way that I can specify the start date but get the end date as the date the report is being run?
May 24, 2011 at 6:04 am
Its possible, but you would need to modify the report and have the dates default to calculated dates i.e. getdate() minus 30.
May 25, 2011 at 9:24 pm
thanks for the suggestion
getdate() did not work for me but using DateAdd("d",0,Today()) for today's date and DateAdd("d",-1,Today()) for yesterday's date worked
May 22, 2012 at 2:54 pm
I just found this post and I am trying to set everything up using the lowest privileges. But I keep getting an access denied error. Does the user account have to be an administrator on the servers being monitored?
Thanks
July 31, 2012 at 9:32 am
Hello, great scripts but when i've run the JOB, the following error appears in POWERSHELL:
The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 38 in a PowerShell script. The corresponding line is ' Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" | '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invalid class '. Process Exit Code 0. The step succeeded.
Thanks a lot
August 17, 2012 at 9:42 am
I changed the script to pull the server name from __Server instead of SystemName because a few of my servers had no information in that field. After doing that the column mapping gets skewed and columns from the data table are not matched up with the columns from the sql table durring the writetoserver call. Any idea's on how this column mapping can be set?
August 17, 2012 at 11:04 am
I answered my own questions. You need to use:
$bulkCopy.ColumnMappings.Add("SourceCol", "DestCol")
to define the mapping between columns.
August 17, 2012 at 12:59 pm
You got it. Sometimes I'll just recreate the table definition to match the column order in order to avoid mappings.
If you go that route you'll need to look at the column order after its been converted to a datatable using get-member.
October 3, 2012 at 6:16 am
Hi, getting an error in the write-VolToDb
Exception calling "WriteToServer" with "1" argument(s): "Column 'server_name' does not allo
w DBNull.Value."
At C:\Bin\Write-VolToDb.ps1:89 char:28
+ $bulkCopy.WriteToServer <<<< ($dt)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Table is populated with one server, stepping through in debug mode (PowerGUI) is showing all parameters and connection strings are correct. Running a C:\Bin\test.ps1 connects and returns the correct server held in the server_space_iku.
Any ideas?
October 3, 2012 at 5:21 pm
Take a look at the $dt variable and make sure server_name property has a value.
Viewing 15 posts - 61 through 75 (of 94 total)
You must be logged in to reply to this topic. Login to reply