ajiteshmalhotra (8/11/2014)
Hi,I have created the power shell script for database information.Please find the below query:-
ForEach ($instance in Get-Content "C:\temp\test\sqlserverlist.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs=$s.Databases
$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
ForEach ($dbs in $dbss)
{
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=AITSSQL22; Initial Catalog=Workdb; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$name=$dbs.name
$Collation=$dbs.Collation
$CompatibilityLevel=$dbs.CompatibilityLevel
$AutoShrink=$dbs.AutoShrink
$RecoveryModel=$dbs.RecoveryModel
$Size=$dbs.Size
$SpaceAvailable=$dbs.SpaceAvailable
$insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)
VALUES('$Name', '$Collation', '$CompatibilityLevel', '$AutoShrink', '$RecoveryModel', '$Size', '$SpaceAvailable')"
$cmd.ExecuteNonQuery()
$conn.Close()
}
}
But data is not going on database table. Could you please help me out for this.
I am new in powershell.
Thanks
Ajitesh Malhotra
$insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)
needs to be before
$cmd.CommandText = $insert_stmt
Otherwise you have no statement to run. This is because strings are immutable objects that are treated as value types i.e. you assigned the value of $insert_stmt at that time i.e. null.
Hope that helps.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!