Home Forums Programming Powershell Write PowerShell Output to SQL Server Table RE: Write PowerShell Output to SQL Server Table

  • 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!!!