Hello Look at the following CLR Stored procedure, it just sets the value for the output parameter @Op, and this value should be returned to the client application: ' Server Side: CLR Stored Procedure <Microsoft.SqlServer.Server.SqlProcedure()> _Public Shared Sub SCN_TestXML(<Out()> ByRef Op As SqlTypes.SqlInt32, ByVal CodUser As SqlTypes.SqlInt16, ByVal Record As SqlTypes.SqlXml) Op = 1000 ' Value to be returned to the client application End Sub At the client side, the message box shows "3333", whereas it should show "1000":objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = 3333 ' Value that should be overriden objComando.Parameters.Add(New SqlClient.SqlParameter("@CodUser", SqlDbType.SmallInt, 0, ParameterDirection.Input)).Value = 1 objComando.Parameters.Add(New SqlClient.SqlParameter("@Record", SqlDbType.Xml, 0, ParameterDirection.Input)).Value = "<xml/>"objComando.ExecuteNonQuery() MsgBox(objComando.Parameters("@Op").Value) ' this should show "1000" I don't understand. What could be wrong? Thanks a lot.
[quote user="EMoscosoCam"]objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = 3333 ' Value that should be overriden[/quote] the pararamete @op has fixed value 3333 , so it return 3333 modified as: op = 1000 ' or any value that may be overwritten somwhere in the code ' and the next statement must be objComando.Parameters.Add(New SqlClient.SqlParameter("@Op", SqlDbType.Int, 0, ParameterDirection.InputOutput)).Value = op' not 1000 ..... ...... objComando.ExecuteNonQuery() MsgBox(objComando.Parameters("@Op").Value) ' it must write "1000"
Thanks, but I think you mistook the code I put in my post: The first part is the CLR Stored Procedure (therefore server-side), and the second -where I am adding parameters to a SQLCommand object) is from the client application calling that stored procedure. So, the sequence is as follows: 1) In the client app I add parameters to the SQLCommand object and execute it, sending a value of 3333 to the ouput parameter. 2) The CLR stored procedure takes that call and overrides the previous value with 1000, supposedly sending it back. 3) After the SQL Command execution, a Message Box is displaying showing the returned value of the parameter. So, somehow the overriden value is not being returned to the client application. Any ideas?
did you test your CLR stored procedure from query analyzer.? If ok , review client part if not ok , review your CLR procedure.
Thanks. Using the SSMS I can see that the output parameter returns succesfully. So, it turns out that somehow the SQLParameter object does not accept the value...