October 20, 2009 at 4:56 am
graeme.black (10/20/2009)
cmille19 (10/20/2009)
graeme.black (10/20/2009)
The error message I get isstill get the following error
Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At C:\BIN\Write-DbSpaceToDb.ps1:27 char:19
+ [void]$da.fill( <<<< $dt)
I am running
C:\BIN>Write-DbSpaceToDb.ps1 "IEOAK-SQL2005-B" "spacedb"
If you log into IEOAK-SQL2005-B with SQL Server Management Studio, does this T-SQL statement complete successfully?
USE spacedb;
SELECT * FROM server_space_lku
yes i get IEOAK-SQL2005-B
October 20, 2009 at 6:19 am
Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters
./test.ps1 IEOAK-SQL2005-B spacedb
If successful you should see the list of servers from the server_space_lku table.
param($destServer, $destdb)
#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt
} #Get-SqlData
Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"
October 20, 2009 at 7:10 am
cmille19 (10/20/2009)
Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters./test.ps1 IEOAK-SQL2005-B spacedb
If successful you should see the list of servers from the server_space_lku table.
param($destServer, $destdb)
#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt
} #Get-SqlData
Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"
I am getting the same error
G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm
Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful,
this script can potentially harm your computer. Do you want to run
G:\powershell\test.ps1?
[D] Do not run [R] Run once Suspend [?] Help (default is "D"): R
Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At G:\powershell\test.ps1:14 char:15
+ [void]$da.fill( <<<< $dt)
could i have the wrong version of powershell or .net?
October 20, 2009 at 4:05 pm
graeme.black (10/20/2009)
cmille19 (10/20/2009)
Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters./test.ps1 IEOAK-SQL2005-B spacedb
If successful you should see the list of servers from the server_space_lku table.
param($destServer, $destdb)
#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt
} #Get-SqlData
Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"
I am getting the same error
G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm
Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful,
this script can potentially harm your computer. Do you want to run
G:\powershell\test.ps1?
[D] Do not run [R] Run once
Suspend [?] Help (default is "D"): RException calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At G:\powershell\test.ps1:14 char:15
+ [void]$da.fill( <<<< $dt)
could i have the wrong version of powershell or .net?
It looks like your execution policy is set to allsigned or restricted. Run this command to set the execution policy to remote signed and try executing the test.ps1 script again:
set-executionpolicy RemoteSigned
October 21, 2009 at 2:29 am
cmille19 (10/20/2009)
graeme.black (10/20/2009)
cmille19 (10/20/2009)
Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters./test.ps1 IEOAK-SQL2005-B spacedb
If successful you should see the list of servers from the server_space_lku table.
param($destServer, $destdb)
#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt
} #Get-SqlData
Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"
I am getting the same error
G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm
Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful,
this script can potentially harm your computer. Do you want to run
G:\powershell\test.ps1?
[D] Do not run [R] Run once
Suspend [?] Help (default is "D"): RException calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At G:\powershell\test.ps1:14 char:15
+ [void]$da.fill( <<<< $dt)
could i have the wrong version of powershell or .net?
It looks like your execution policy is set to allsigned or restricted. Run this command to set the execution policy to remote signed and try executing the test.ps1 script again:
set-executionpolicy RemoteSigned
PS C:\WINDOWS\system32\windowspowershell\v1.0> set-executionpolicy RemoteSigned
PS C:\WINDOWS\system32\windowspowershell\v1.0> G:\powershell\test.ps1
File G:\powershell\test.ps1 cannot be loaded. The file G:\powershell\test.ps1 i
s not digitally signed. The script will not execute on the system. Please see "
get-help about_signing" for more details..
At line:1 char:22
+ G:\powershell\test.ps1 <<<<
October 21, 2009 at 3:09 pm
What's the output of this command?
get-executionpolicy
October 25, 2009 at 9:50 pm
Hi,
I get the following error when the job step Write-VolToDb runs:
"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."
Therefore I see that the db_space table is populated but vol_space is not.
Looks like there is a class name missing after Get-WmiObject. I am running this on XP SP3. Any suggestions?
October 26, 2009 at 2:57 pm
Win32_Volume class isn't available on XP. It is on Server 2003 and Vista and above. Change the vol script o use Win32_LogicalDisk instead.
November 20, 2009 at 5:46 am
Hi,
When executing the powershell script for Write-DbSpaceToDb.ps1 i get the following error :
PS SQLSERVER:\SQL\JHB-BARTUSD\DEFAULT> param($destServer, $destdb)
The term 'param' is not recognized as a cmdlet, function, operable program, or
script file. Verify the term and try again.
At line:1 char:6
+ param( <<<< $destServer, $destdb)
Please help.
November 20, 2009 at 8:27 pm
bartusp (11/20/2009)
Hi,When executing the powershell script for Write-DbSpaceToDb.ps1 i get the following error :
PS SQLSERVER:\SQL\JHB-BARTUSD\DEFAULT> param($destServer, $destdb)
The term 'param' is not recognized as a cmdlet, function, operable program, or
script file. Verify the term and try again.
At line:1 char:6
+ param( <<<< $destServer, $destdb)
Please help.
You need to execute the script as follows:
./Write-DbSpaceToDb.ps1 ' Z002\SQL2K8' spacedm
Replacing Z002\SQL2K8 with your server name. Also you need to navigate to the filesystem directory or fully qualify the script name:
C:\bin\Write-DbSpaceToDb.ps1 ' Z002\SQL2K8' spacedm
December 24, 2009 at 3:35 am
Thanks a lot for this post, great stuff.
Not sure if graeme.black managed to resolve the error about "invalid object server_space_lku", but I found the cause when I had the same issue. I had to prefix the table with the schema name in the 2 powershell scripts to get it to work, e.g. capplan.server_space_lku. Most installatoions would default to dbo.server_space_lku, might be worth a try for anyone having this same issue.
Hope this helps
September 2, 2010 at 11:50 am
I have made changes to both the scripts and reflected correct servername\instancename (' VMSTDVRD001\SQL01'). The "Space Collector" job completes successfully but I do now see any rows when I issue queries. Any help will be highly appreciated.
Job completes successfully with the folowing:
Date9/2/2010 10:34:30 AM
LogJob History (Space Collector)
Step ID2
ServerVMSTDVRD001\SQL01
Job NameSpace Collector
Step NameWrite-DbSpaceToDb
Duration00:00:16
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: VMSTDVRD001\SYSTEM. The step did not generate any output. Process Exit Code 0. The step succeeded.
The following queries do not gvie me any rows:
SELECT d1.server_name, d1.dbname, d1.physical_name, d1.dt, d1.size_mb, d1.free_mb,
ROUND(CAST(d1.free_mb AS float)/d1.size_mb * 100,2) AS percent_free,
(d1.size_mb - d1.free_mb) AS allocate_mb,
(d1.size_mb - d1.free_mb) - (d2.size_mb -d2.free_mb) AS daily_growth_mb
FROM dbo.db_space d1
JOIN dbo.db_space d2
ON d1.server_name = d2.server_name
AND d1.dbname = d2.dbname
AND d1.physical_name = d2.physical_name
AND d2.dt = DATEADD(dd,-1,d1.dt)
WHERE d1.size_mb > 0
SELECT v1.server_name, v1.vol_name, v1.dt, v1.vol_lbl, v1.size_gb, v1.free_gb, v1.percent_free,
(v1.size_gb * .8) AS usable_size_gb, (v1.size_gb - v1.free_gb) AS allocated_gb,
(v1.size_gb - v1.free_gb) - (v2.size_gb -v2.free_gb) AS daily_growth_gb
FROM dbo.vol_space v1
JOIN dbo.vol_space v2
ON v1.server_name = v2.server_name
AND v1.vol_name = v2.vol_name
AND v2.dt = DATEADD(dd,-1,v1.dt)
execute dbo.db_space_capacity_sp '1/1/2010', '12/31/2010'
September 4, 2010 at 7:50 am
The capacity report/query calculates an average growth rate over a given period, so in order for the query to return results you must have at least two days of collected data. The more data collected, the more likely the average will be accurate.
Do you have rows in dbo.server_space_lku and dbo.vol_space?
February 10, 2011 at 4:00 am
I am getting this error:
A job step received an error at line 83 in a PowerShell script. The corresponding line is 'foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY KEY constraint 'PK_db_space'. Cannot insert duplicate key in object 'dbo.db_space'. The duplicate key value is (****\***, _DB, R:\DB.mdf, Feb 10 2011 12:00AM). The statement has been terminated." Violation of PRIMARY KEY constraint 'PK_db_space'. Cannot insert duplicate key in object 'dbo.db_space'. The duplicate key value is (****\***, DB, R:\DB.mdf, Feb 10 2011 12:00AM). The statement has been terminated. '. Process Exit Code -1. The step failed.
I assume this is because we cannot run this more than once on a day. But what if we added a new instance to monitor. Is there a way to rather replace any existing data so that the job continues.
March 2, 2011 at 10:41 pm
Hi, fantastic article, but like some of the others I to am getting the following error:
Exception calling "Fill" with "1" argument(s): "A network-related or instance-s
pecific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: Name
d Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
At E:\dbspace\test.ps1:14 char:15
+ [void]$da.fill( <<<< $dt)
I have tracked this down to the line :
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
in the Get-SqlData function.
If I replace @servername with a hardcoded server everything works. The hardcoded value is the same as the value in the lookup table. My powershell isnt good enough to know what to try next.
For example, I see there is a verbose logging there, but what is the flag to show verbose tracing?
The odd thing is the function Get-SqlData works fine in the first place to get the list of servers. The only server in the lookup table is the server the script is running on.
Viewing 15 posts - 46 through 60 (of 94 total)
You must be logged in to reply to this topic. Login to reply