May 26, 2016 at 7:02 pm
This one has my head spinning. I would like to run a query on all of the servers registered in the central management server and then write the output to a table on our management server. I can get the output and also load it into the database but what is loaded is not right. Server Name and Node Name don't go into a table correctly. I've been reading a bunch of examples from Google but really don't understand them. Somehow I need to split the output into two parts so one column can go into ServerName and one into NodeName.
Any help would be greatly appreciated.
#Get list of servers in CMS
$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSRSI.server_group_id = '11'
" -serverinstance "TESTSRV2"
$results = @()
#Collect server name and node node from each into a variable
foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "
SELECT @@SERVERNAME as ServerName, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as NodeName
" -ServerInstance $instanceName.Name}
#Insert the results into the STG_Results table in the DBA database
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='TESTSRV2';database='DBA';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
foreach($node in $results)
{
$sql = "insert into STG_Results (ServerName,NodeName)
select '$node.ServerName','$node.NodeName'"
$Command.CommandText = $sql
$Command.ExecuteNonQuery()
}
$Connection.Close()
August 2, 2016 at 4:27 pm
Did you get this working?
Are you still trying to resolve this?
If you still need help the in what way does it fail?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 2, 2016 at 5:33 pm
Hi Gary. I never got it working.
Instead of inserting the ServerName and NodeName in the database it just inserts this for every line:
System.Data.DataRow.ServerNameSystem.Data.DataRow.NodeName
Howard
August 2, 2016 at 6:09 pm
I am in a caravan on the Dorset coast during a windy week on my mobile so cannot check but I suggest outputting the types of things like $node e.g. $node.GetType() and output them.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 3, 2016 at 6:59 am
Gary Varga (8/2/2016)
I am in a caravan on the Dorset coast during a windy week on my mobile so cannot check but I suggest outputting the types of things like $node e.g. $node.GetType() and output them.
Gary is wise.
use this instead:
$node.ServerName.ToString()
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 3, 2016 at 9:44 am
$results.GetType() returns this:
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Object[] System.Array
If I do the SQL insert like this:
foreach($node in $results)
{
$sql = "insert into STG_Results (ServerName,NodeName)
select '$node.ServerName.ToString()','$node.NodeName.ToString()'"
...
}
It comes out like this:
ServerNameNodeName
System.Data.DataRow.ServerName.ToString()System.Data.DataRow.NodeName.ToString()
There are multiple columns and rows in $results because it is loaded by looping through multiple servers. I'd like to pluck the values of each row out of $results and insert them in the database table. Certainly I'm missing some fundamental concept of how it should be written.
August 3, 2016 at 10:03 am
Hmm. There's something fundamentally wrong with my PoSh setup on this computer - it doesn't like that "+=" constructor you have when building up $results...
Will have to think again about this. Sorry.
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 3, 2016 at 10:06 am
I'm sorry Thomas, I see now that I missed a line in the original script.
I had to define the variable as an array first like this
$results = @()
August 3, 2016 at 11:14 am
Hi everyone. jrv found the solution to this one.
Changing the query to insert the values solved the problem.
$sql = "insert into STG_Results(ServerName,NodeName) VALUES('{0}','{1}')" -f $node.ServerName, $node.NodeName
Thanks again for your input.
Howard
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy