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
pipeline stopped error when closing out-gridview
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
- LtMandella
- Posts: 61
- Last visit: Mon May 07, 2018 4:03 pm
Re: pipeline stopped error when closing out-gridview
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?
The stored proc apparently is not a single table and not compatible with what you are trying.
Where did you get the stored procedure?
- LtMandella
- Posts: 61
- Last visit: Mon May 07, 2018 4:03 pm
Re: pipeline stopped error when closing out-gridview
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
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
- LtMandella
- Posts: 61
- Last visit: Mon May 07, 2018 4:03 pm
Re: pipeline stopped error when closing out-gridview
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...jvierra wrote:I cannot see how this would be true. The grid will always load synchronously.
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
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.
- LtMandella
- Posts: 61
- Last visit: Mon May 07, 2018 4:03 pm
Re: pipeline stopped error when closing out-gridview
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
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
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
- LtMandella
- Posts: 61
- Last visit: Mon May 07, 2018 4:03 pm
Re: pipeline stopped error when closing out-gridview
Yes, I didn't past the complete script in the posting. The function is declared prior to calling.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
Anyway no worries, it is not a major issue for us.