Unable to pass paramter to web service

[Rob Miller]

Hi Folks,

I have a CLR Trigger that calls a web service [see code below]. When I
execute using the uncommented line, that passes a string literal, the web
service executes without error. When I run the code as is, I get an exception
in my web service about a SQL timeout, specifically:
"System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding." Considering I only change this one line, I'm not believing a
real timeout is happening.

My guess was that my third parameter was being passed by reference and the
web service couldn't retrieve the value. I changed my web service to VB.net
and specified ByVal but that didn't work. I tried unboxing the value before
passing to the web service but that didn't work either.

Any ideas? I'll I'm doing is flipping between these two lines of code to
generate that exception.

while (sqlRdr.Read())
{
ws.ExecSQL(WebServiceConnection, WebServiceProcedure, (string)sqlRdr[0]);
//ws.ExecSQL(WebServiceConnection, WebServiceProcedure, "x");
}

 

[John Saunders]

Hi Folks,

I have a CLR Trigger that calls a web service [see code below]. When I
execute using the uncommented line, that passes a string literal, the web
service executes without error. When I run the code as is, I get an
exception
in my web service about a SQL timeout, specifically:
"System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding." Considering I only change this one line, I'm not believing a
real timeout is happening.

My guess was that my third parameter was being passed by reference and the
web service couldn't retrieve the value. I changed my web service to
VB.net
and specified ByVal but that didn't work. I tried unboxing the value
before
passing to the web service but that didn't work either.

Remember that your web service doesn't receive the parameter either by value
or by reference. It receives some XML which was serialized from the
parameter on the client side. It then deserialzes it into a value on the
server side.

You could be getting a timeout if the server doesn't respond. Does it
respond? Does it respond with an error?

John

 

[Rob Miller]

Hi John,

Yeah, little oversight there on the byval/byref to a web service
method...duh.

When I use:

ws.ExecSQL(WebServiceConnection, WebServiceProcedure, "x");

the web service method does it's thing and returns. No error.

When I use:

ws.ExecSQL(WebServiceConnection, WebServiceProcedure, (string)sqlRdr[0]);

I get the following, after a few seconds. No other code has been changed:

Msg 6522, Level 16, State 1, Procedure tiu_MigrateToUniData_Test, Line 1
A .NET Framework error occurred during execution of user defined routine or
aggregate 'tiu_MigrateToUniData_Test':
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: Server was unable to process
request. ---> System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLExec.ExecSQL(String Connection, String Proc, String Param)
--- End of inner exception stack trace ---
System.Web.Services.Protocols.SoapException:
at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters)
at UniDataMigrationTriggers.LocalHost.SQLExec.ExecSQL(String Connection,
String Proc, Stri...

 

[John Saunders]

Hi John,

Yeah, little oversight there on the byval/byref to a web service
method...duh.

When I use:

ws.ExecSQL(WebServiceConnection, WebServiceProcedure, "x");

the web service method does it's thing and returns. No error.

When I use:

ws.ExecSQL(WebServiceConnection, WebServiceProcedure, (string)sqlRdr[0]);

I get the following, after a few seconds.

So, what's the value of (string) sqlRdr[0]? Was it "x"? If not, try sending
that value as a string literal, the way you did for "x".

What does your event log say? I presume that your web service logs
exceptions to the event log or something like it? If so, what did it write?

Find out what the web service is sending back to the client. Use one of the
trace tools from http://www.pocketsoap.com , or an equivalent (I like
SOAPScope from MindReef).

My general feeling on this is that the computer is telling you the truth
here - there was indeed a timeout. The trick now is to determine what the
truth means.

John