Method invocation failed because [System.Data.DataRow] does not contain a method named 'Split'.

bibi92
bibi92 used Ask the Experts™
on
Hello

I try to display starttime and endtime but error occurs Method invocation failed because [System.Data.DataRow] does not contain a method named 'Split'.

$server = "test\srv"
$sqlcmd = (
"DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(1) 
               convert(varchar, DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()))  +'|'+
			   convert(varchar, GETDATE()) +'|'+
               convert(varchar, SQLProcessUtilization)
FROM ( 
	  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], 
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization],
			[timestamp] 
	  FROM ( 
			SELECT [timestamp], CONVERT(xml, record) AS [record] 
			FROM sys.dm_os_ring_buffers 
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
			AND record LIKE N'%<SystemHealth>%') AS x 
	  ) AS y 
ORDER BY record_id DESC;"
)

$datasource = Invoke-SqlCmd -Query "$sqlcmd" -ServerInstance $server -QueryTimeout 200000
if ($datasource) {
foreach ($column in $datasource) {
$array_str = $column.Split("|")
$starttime = $array_str[0].Trim()
$endtime = $array_str[1].Trim()
$Title=$starttime + " to " + $endtime
$Title 
}
}		

Open in new window


Why ?

Thanks

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Awarded 2012
Commented:
Hi,
the response is a data table which contains first the rows, then the items/columns therefore you get the error as you try to split on a datarow.

You might try the following:
$server = "test\srv"
$sqlcmd = (
"DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(1) 
               convert(varchar, DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()))  +'|'+
			   convert(varchar, GETDATE()) +'|'+
               convert(varchar, SQLProcessUtilization)
FROM ( 
	  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], 
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization],
			[timestamp] 
	  FROM ( 
			SELECT [timestamp], CONVERT(xml, record) AS [record] 
			FROM sys.dm_os_ring_buffers 
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
			AND record LIKE N'%<SystemHealth>%') AS x 
	  ) AS y 
ORDER BY record_id DESC;"
)

$datasource = Invoke-SqlCmd -Query "$sqlcmd" -ServerInstance $server -QueryTimeout 200000
if ($datasource) {
	foreach ($row in $datasource) {
		$array_str = $row.Item[0].ToString().Split("|")
		$starttime = $array_str[0].Trim()
		$endtime = $array_str[1].Trim()
		$Title=$starttime + " to " + $endtime
		$Title 
	}
}

Open in new window


Columns are objects, therefore the "ToString".

HTH
Rainer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial