Post reply

CMS

  • November 8, 2012 at 12:20 pm

    #266836

    As below I am running this powershell script as a sqlagent job on SQL Server - XXXXXXX which is the Central management (registered) server.The job executes fine but the table dbo.test is not getting populated with the required data.Can someone help me figure this out.

    $CentralManagementServer = "XXXXXXX"

    $HistoryDatabase = "YYYYYYYY"

    function ResultInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedResult)

    {

    $sqlQueryText = "INSERT INTO dbo.test (servername1,servername2) VALUES('$EvaluatedServer', '$EvaluatedResult')"

    write-output $sqlQueryText

    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText

    }

    $sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true");

    $q = "select name from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal];"

    $sconn.Open()

    $cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);

    $cmd.CommandTimeout = 0;

    $dr = $cmd.ExecuteReader();

    while ($dr.Read()) {

    $ServerName = $dr.GetValue(0);

    $sqlQueryText = "select @@version"

    $selectResult=Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlQueryText

    [string]$a=$selectResult.column1

    ResultInsert $CentralManagementServer $HistoryDatabase $ServerName $a;

    }

    $dr.Close()

    $sconn.Close()

    ..

  • November 8, 2012 at 1:33 pm

    #1557343

    You could try this:

    http://nclsqlinv.codeplex.com/releases/view/78228

    I have this deployed on a central server, that gets all the info for all the registered servers I enter in its database. It has a lot more info than you would probably need, but you can always customize it.

  • November 16, 2012 at 12:36 pm

    #1559918

    I got the above script working.But now I only have the servername and the version in the database.I would like to have the following details as per the below t-sql.

    SELECT @@servername as Servername, name as DBName,[crdate] AS [LastStartupDate],

    substring(@@version,1,26) as SQLServerVersion

    FROM [master].[dbo].[sysdatabases]

    --,[dbo].[Table_1]

    WHERE [master].[dbo].[sysdatabases].[name] = 'tempdb'

    --order by LastStartupDate desc

    order by @@ServerName desc.

    Can someone please include the LastStartupDate into the power shellscript above.

  • May 5, 2015 at 6:55 am

    #1795572

    Hi. I am trying to use the same script and facing the issue that the values are not loaded to the table. How you made the script working?

Viewing 4 posts - 1 through 3 (of 3 total)

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