I'm executing a query against an ORACLE DB. If I execute this query using
TOAD it works fine...a little slow but I have the result rows.
If I execute this query inside the Report Designer I get the next message:
" An error occurred while reading data from the query result set.
OCI-22053: overflow error "
Any idea'
ThanxI created a new project and the problem solved.
"Pato Connon" wrote:
> I'm executing a query against an ORACLE DB. If I execute this query using
> TOAD it works fine...a little slow but I have the result rows.
> If I execute this query inside the Report Designer I get the next message:
> " An error occurred while reading data from the query result set.
> OCI-22053: overflow error "
> Any idea'
> Thanx
>
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Friday, March 9, 2012
Occasional Error When Executing CLR Stored Procedure
This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.
Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader
Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()
_SQLCommandRequest.CommandText = _spReportParms
_SQLCommandRequest.CommandType = CommandType.StoredProcedure
' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader
' make sure that we have something first.
If Not drReportParameters Is Nothing Then
' find out if we have any rows returned.
If drReportParameters.HasRows Then
' read each of the rows looking for the respective value.
While drReportParameters.Read
' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then
' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select
End If
End While
End If
End If
Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)
Finally
' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If
End Try
End Sub
</code>
Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?
_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.
Steven
|||
Understood. Thanks for the explanation.
So all I have to do is make everything local, non-shared then right?
Except for the main sub procedure declaration below which has to remain shared right?:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _
Am I on the right track?
|||Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.
|||
Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.
Occasional Error When Executing CLR Stored Procedure
This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.
Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader
Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()
_SQLCommandRequest.CommandText = _spReportParms
_SQLCommandRequest.CommandType = CommandType.StoredProcedure
' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader
' make sure that we have something first.
If Not drReportParameters Is Nothing Then
' find out if we have any rows returned.
If drReportParameters.HasRows Then
' read each of the rows looking for the respective value.
While drReportParameters.Read
' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then
' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select
End If
End While
End If
End If
Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)
Finally
' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If
End Try
End Sub
</code>
Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?
_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.
Steven
|||
Understood. Thanks for the explanation.
So all I have to do is make everything local, non-shared then right?
Except for the main sub procedure declaration below which has to remain shared right?:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _
Am I on the right track?
|||Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.
|||
Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.
Friday, February 24, 2012
Obtained an error when performing a dbcc on an sms database
An error occurred while executing the following
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment on what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the full output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment on what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the full output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
Obtained an error when performing a dbcc on an sms database
An error occurred while executing the following
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return
error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment o
n what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the ful
l output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return
error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment o
n what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the ful
l output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
quote:
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
Obtain the query plan of a running process
Hi,
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
Thanks in advance,
Thyagu.DOne way to do this task is: Set up a Profile Trace and add Show Plan
event , save the info , and filter with the SPID
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"Thyagu" <tdelli@.gmail.comwrote in message
news:1158834419.604983.220140@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
Hi,
>
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
>
Thanks in advance,
Thyagu.D
>
Subscribe to:
Posts (Atom)