Page 1 of 2

pipeline stopped error when closing out-gridview

Posted: Thu Sep 29, 2016 11:34 am
by LtMandella
pipeline stopped error when closing out-gridview:


Here's the code I am using to show data using an out-gridview (it's in a cell content click event of datagridview)

The exec-proc function returns the first resultset returned from call to stored proc, and I make sure it always returns data that will not cause an issue.

My problem is that if the user clicks the [X] icon in the out-gridview window to close the gridview window before all rows are loaded, we get a "pipeline stopped" error that I haven't been able to catch in a try/catch or clear or silently continue.

The only way I have been able to limit the hassle is to add the "start-sleep" command to help limit the users opportunity to close while still loading.

Any suggestions?

thanks,ken

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = $script:connStr;
$rpt = Exec-Sproc -Conn $SqlConnection -Sproc "$ImportDatabase.adpIntegrations.[GetRaw_Source_Import]"
Start-Sleep -s 5
$rpt | Out-GridView -ErrorAction SilentlyContinue;
Start-Sleep -s 2

Re: pipeline stopped error when closing out-gridview

Posted: Thu Sep 29, 2016 12:09 pm
by jvierra
Start-Sleep is pointless.

The stored proc apparently is not a single table and not compatible with what you are trying.

Where did you get the stored procedure?

Re: pipeline stopped error when closing out-gridview

Posted: Mon Oct 03, 2016 10:23 am
by LtMandella
agreed, start sleep does seem pointless. however much to my surprise, the first start sleep does at runtime definitely shorten the time it takes for the out-gridview to fully load from the time it first displays, which reduces the opportunity for user to close it before it is fully loaded.

the stored proc returns a single result set:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create proc [mydb].[GetRaw_Source_Import]
as
begin
set nocount on
select
[EmployeeID]
,[Fullname]
,[Gender]
,[HireDate]
,[RehireDate]
,[TermDate]
,[Status]
,[EffectiveDate]
,[Class]
,[PayFreq]
,[RegTemp]
,[FullPartTime]
,[ReportingLocCode]
,[ReportingLocBuilding]
,[WorkLocCode]
,[WorkLocBuilding]
,[DeptCode]
,[DeptDescription]
,[JobCode]
,[JobDescription]
,[ManagerLevel]
,[ReportingClassCode]
,[EmployeeFTE]
,[TimeKeeperFTE]
,[OffSite]
,[Reason]

from [my].[myimportable]

end

Re: pipeline stopped error when closing out-gridview

Posted: Mon Oct 03, 2016 11:42 am
by jvierra
I cannot see how this would be true. The grid will always load synchronously.

Re: pipeline stopped error when closing out-gridview

Posted: Mon Oct 03, 2016 2:43 pm
by LtMandella
jvierra wrote:I cannot see how this would be true. The grid will always load synchronously.
You are probably right, I have not stopwatched it. Maybe it is just my imagination that the start-sleep actually reduces the time for the outgridview to load...

In any event do you know if is it expected behavior that closing an outgridview while it is still loading causes "pipeline stopped" error?

I can repro it reliably.

Re: pipeline stopped error when closing out-gridview

Posted: Mon Oct 03, 2016 3:10 pm
by jvierra
What is an outgridview?

Re: pipeline stopped error when closing out-gridview

Posted: Mon Oct 03, 2016 6:02 pm
by jvierra
I will try to clarify. If you are not using a form and you are using the CmdLet Out-Gridview (not outgridview?) then the grid will load from the pipeline if you are feeding the result of the SP to the grid. Since we have no idea what you function does there is really no way to determine how your issue is being caused.

Re: pipeline stopped error when closing out-gridview

Posted: Tue Oct 04, 2016 10:57 am
by LtMandella
jvierra wrote:I will try to clarify. If you are not using a form and you are using the CmdLet Out-Gridview (not outgridview?) then the grid will load from the pipeline if you are feeding the result of the SP to the grid. Since we have no idea what you function does there is really no way to determine how your issue is being caused.

appreciate your help!


yes CmdLet Out-Gridview. per the code I pasted in initial posting:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = $script:connStr;
$rpt = Exec-Sproc -Conn $SqlConnection -Sproc "$ImportDatabase.adpIntegrations.[GetRaw_Source_Import]"
Start-Sleep -s 5
$rpt | Out-GridView -ErrorAction SilentlyContinue;

but this has to be synchronous loading as you previously explained? $rpt variable must be fully populated prior to pipelining to out-Gridview?

###################################custom functions start ############################################
# Executes a Stored Procedure from Powershell and returns the first output DataTable
function Exec-Sproc
{

param ([System.Data.SqlClient.SqlConnection]$Conn,
$Sproc,
$Parameters = @{ })

## INTENTIONALLY NO ERROR HANDLING HERE, PUT IT IN THE CALLER BECAUSE YOU KNOW THE CONTEXT FOR MESSAGE BACK TO USER IF FAILURE

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandTimeout = $script:SqlCommandTimeOut;
$SqlCmd.Connection = $Conn
if ($Conn.State -ne 'Open') { $Conn.open(); }
$SqlCmd.CommandText = $Sproc
foreach ($p in $Parameters.Keys)
{ [Void]$SqlCmd.Parameters.AddWithValue("@$p", $Parameters[$p]) }
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$x = $SqlAdapter.Fill($DataSet)
return $DataSet.Tables[0]

} ##Exec-Sproc

Re: pipeline stopped error when closing out-gridview

Posted: Tue Oct 04, 2016 12:03 pm
by jvierra
There is no way tat $rpt is not "loaded".

A function must be declared before it is called. You cannot place functions at the end of a file and call them in the start of the file

Re: pipeline stopped error when closing out-gridview

Posted: Tue Oct 04, 2016 12:39 pm
by LtMandella
jvierra wrote:There is no way tat $rpt is not "loaded".

A function must be declared before it is called. You cannot place functions at the end of a file and call them in the start of the file
Yes, I didn't past the complete script in the posting. The function is declared prior to calling.

Anyway no worries, it is not a major issue for us.