Prev: why end stored procedures with RAISERROR statement (with error number = 0)?
Next: Assign permissions t user defined functions
From: gerry on 29 Mar 2010 15:16
We have an sqlclr Stored procedure that invokes a web service.
SqlServer sits on on one machine and the web service on another within the
same lan.
Everything works fine until the web service machine is rebooted - at which
point all attempts to use the SP bomb with the exception below until we
restart the Sql Service.
I am probably wrong but it sounds like a connection to the webservice is
being opened once and held open for all subsequent requests. When the
webservice machine is rebooted the connection is broken causing this
exception.

MySP was created by using 'Add Web Reference'

using ( MySP ws = new MySP() )
{
///
/// errors during ws call leave connection open
/// even though object is disposed
/// 2 solutions so far :
///
/// 1:
/// try {
/// rtn = ws.Call();
/// } catch(ThreadAbortException){
/// try{ ws.Abort(); }catch(Exception){}
/// throw;
/// }
///2:
/// change
/// rtn = ws.Call();
/// to
/// rtn = ws.EndCall( ws.BeginCall( null , null ) );
///
/// see
http://dbaspot.com/forums/ms-sqlserver/218732-aborting-web-service-method-invokes-after-threadabortexception.html
///
// string response = ws.Call1( null , null , Method , Arg1 );
string response = ws.EndCall1( ws.BeginCall1( null , null , Method ,
Arg1 , null , null ) );
return response;
}




Msg 6522, Level 16, State 1, Procedure MySP, Line 0
A .NET Framework error occurred during execution of user-defined routine or
aggregate "MySP":
System.TypeInitializationException: The type initializer for
'Microsoft.SqlServer.Server.SmiContextFactory' threw an exception. --->
System.Threading.ThreadAbortException: Exception of type
'System.Threading.ThreadAbortException' was thrown.
System.Threading.ThreadAbortException:

System.TypeInitializationException:
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at GmsSqlClr.StoredProcedures.MySP(String Request)


From: gerry on 29 Mar 2010 15:54
I adjusted the code example for a little more context.
I also added an additional try catch to see if that catches something.

"gerry" <germ2(a)newsgroup.nospam> wrote in message
news:%23OJSjR3zKHA.928(a)TK2MSFTNGP05.phx.gbl...
> We have an sqlclr Stored procedure that invokes a web service.
> SqlServer sits on on one machine and the web service on another within the
> same lan.
> Everything works fine until the web service machine is rebooted - at which
> point all attempts to use the SP bomb with the exception below until we
> restart the Sql Service.
> I am probably wrong but it sounds like a connection to the webservice is
> being opened once and held open for all subsequent requests. When the
> webservice machine is rebooted the connection is broken causing this
> exception.
>
> MySP was created by using 'Add Web Reference'
>



[SqlProcedure]
public static void HPU_MySP( string Request )
{
using ( MyWS ws = new MyWS() )
{
///
/// errors during ws call leave connection open
/// even though object is disposed
/// 2 solutions so far :
///
/// 1:
/// try {
/// rtn = ws.Call();
/// } catch(ThreadAbortException){
/// try{ ws.Abort(); }catch(Exception){}
/// throw;
/// }
///2:
/// change
/// rtn = ws.Call();
/// to
/// rtn = ws.EndCall( ws.BeginCall( null , null ) );
///
/// see
http://dbaspot.com/forums/ms-sqlserver/218732-aborting-web-service-method-invokes-after-threadabortexception.html
///
// string response = ws.Call1( null , null , Method , Arg1 );
try{
string response = ws.EndCall1( ws.BeginCall1( null , null , Method ,
Arg1 , null , null ) );

}catch(Exception){
try( ws.Abort(); } catch(Exception ) { }
throw;
}
...
}
}


>
>
>
>
> Msg 6522, Level 16, State 1, Procedure MySP, Line 0
> A .NET Framework error occurred during execution of user-defined routine
> or aggregate "MySP":
> System.TypeInitializationException: The type initializer for
> 'Microsoft.SqlServer.Server.SmiContextFactory' threw an exception. --->
> System.Threading.ThreadAbortException: Exception of type
> 'System.Threading.ThreadAbortException' was thrown.
> System.Threading.ThreadAbortException:
>
> System.TypeInitializationException:
> at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
> at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
> at GmsSqlClr.StoredProcedures.MySP(String Request)
>
>


 | 
Pages: 1
Prev: why end stored procedures with RAISERROR statement (with error number = 0)?
Next: Assign permissions t user defined functions