bibi92
asked on
invoke-sqlcmd : Error converting data type varchar to numeric
Hello,
I try to collect dbspace with ps script :
-The table ddl and the ps script are :
invoke-sqlcmd : Error converting data type varchar to numeric.
At line:20 char:9
+ invoke-sqlcmd @params -Query $InsertResults
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionE xception
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServ er.Managem ent.PowerS hell.GetSc riptComman d
How can I resolve this problem ?
Thanks
I try to collect dbspace with ps script :
-The table ddl and the ps script are :
CREATE TABLE [dbo].[DSpace](
[SERVERNAME] [varchar](50) NULL,
[DatabaseName] [varchar](128) NULL,
[Log_filename] [varchar](128) NULL,
[Log_filesize] [decimal](18, 2) NULL,
[Used_space] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
$SQLInstance = "SQLTEST"
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance
$DBStats = $srv.Databases
foreach ($DB in $DBStats) {
IF ( $DB.status -eq "Normal" ) {
$DBName = $DB.Name
$db.get_logfiles() | % { New-Object PsObject -Property @{
'Log File' = $_.FileName
'Size (MB)' = [math]::round($_.Size/1KB,2)
'Used Space (MB)' = [math]::round($_.UsedSpace/1KB,2)
} | tee -Variable vals | Format-Table -auto
$val.{Log File}
$val.{Size (MB)}
$val.{Used Space (MB)}
}
$InsertResults = @"
INSERT INTO DSpace (SERVERNAME ,DatabaseName ,Log_filename ,Log_filesize ,Used_space)
VALUES ('$SQLInstance', '$DBName', '$val.{Log File}', '$val.{Size (MB)}', '$val.{Used Space (MB)}')
"@
invoke-sqlcmd @params -Query $InsertResults
}
}
The error returned is :invoke-sqlcmd : Error converting data type varchar to numeric.
At line:20 char:9
+ invoke-sqlcmd @params -Query $InsertResults
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionE
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServ
How can I resolve this problem ?
Thanks
In your code you wrote the following:
tee -Variable vals
Is that val instead?
tee -Variable vals
Is that val instead?
ASKER
For qlemo, thanks :
invoke-sqlcmd : Incorrect syntax near ','.
At line:1 char:1
+ invoke-sqlcmd @params -Query $InsertResults
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionE xception
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServ er.Managem ent.PowerS hell.GetSc riptComman d
invoke-sqlcmd : Incorrect syntax near ','.
At line:1 char:1
+ invoke-sqlcmd @params -Query $InsertResults
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionE
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServ
Can't find any issue. Please add this before or after line 34 for debugging:
Write-Host $InsertResults
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok i will test tomorrow. Thanks
But there is another issue - if $DB.status is not "Normal", you still try to insert values, which are not recent by then - the INSERT belongs into the IF block.
And there is a typo in the tee-object, so the wrong variable is filled.
Open in new window