Load data using powershell

  • Hi Poweshell Gurus

    I am a power shell newbie , looking to import SQL server version information of multiple instances and load the data to a different table. Using some help from different blogs post, I am able to come up with the script below but apparently it is not serving the purpose.

    Any feedback would be appreciated. Thanks for reading my post.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    . C:\Test\powershell\Write-DataTable.ps1

    $serverlist = Invoke-sqlcmd -ServerInstance “ABC” -Database "Test" -Query “Select InstaceName FROM InstanceTable”
    foreach ($server in $serverlist)
    {

    $dt=Invoke-SqlCmd -ServerInstance $serverlist -Database master -Query “SELECT @@version”
    Write-DataTable -ServerInstance "XYZ" -Database "Test1" -TableName "SQLVersion" -Data $dt}
    -------------------------------------------------------------------------------------------------------------------------------------------------------------

  • pshrestha4 - Friday, February 17, 2017 1:05 PM

    Hi Poweshell Gurus

    I am a power shell newbie , looking to import SQL server version information of multiple instances and load the data to a different table. Using some help from different blogs post, I am able to come up with the script below but apparently it is not serving the purpose.

    Any feedback would be appreciated. Thanks for reading my post.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    . C:\Test\powershell\Write-DataTable.ps1

    $serverlist = Invoke-sqlcmd -ServerInstance “ABC†-Database "Test" -Query “Select InstaceName FROM InstanceTableâ€
    foreach ($server in $serverlist)
    {

    $dt=Invoke-SqlCmd -ServerInstance $serverlist -Database master -Query “SELECT @@versionâ€
    Write-DataTable -ServerInstance "XYZ" -Database "Test1" -TableName "SQLVersion" -Data $dt}
    -------------------------------------------------------------------------------------------------------------------------------------------------------------

    You've misspelled InstanceName in your SELECT query.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks for the reply, actually the column name is "InstaceName".

  • Shouldn't:
    $dt=Invoke-SqlCmd -ServerInstance $serverlist -Database master -Query “SELECT @@version”

    Be:
    $dt=Invoke-SqlCmd -ServerInstance $server -Database master -Query “SELECT @@version”

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Gary Varga - Sunday, February 19, 2017 1:31 PM

    Shouldn't:
    $dt=Invoke-SqlCmd -ServerInstance $serverlist -Database master -Query “SELECT @@versionâ€

    Be:
    $dt=Invoke-SqlCmd -ServerInstance $server -Database master -Query “SELECT @@versionâ€

    I tried both but no luck..

  • Did you try putting db name master in single quotes?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply