none
Exception calling stored procedure RRS feed

  • Question

  • I am getting an exception, see below.  I am sending a pretty large xml string to a procedure.


    namespace WBpi.DSData
    {
        public static class DatabaseConnector
        {
            
            public static XmlNode DoAction(string p_action, string p_connectionString, XmlNode p_XmlParams)
            {
                string stringResult = null;
                string v_AppPath = HttpContext.Current.Request.ApplicationPath;

                using (SqlConnection connection = new SqlConnection(p_connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(Settings.Default.InteropProc, connection))
                    {
                        command.CommandTimeout = Settings.Default.SqlCommandTimeout;
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add(new SqlParameter("@Action", p_action));
                        command.Parameters.Add(new SqlParameter("@XmlParams", p_XmlParams.OuterXml));

                        SqlParameter XmlResult = new SqlParameter("@XmlResult", stringResult);
                        XmlResult.DbType = DbType.AnsiString;
                        XmlResult.Size = -1;
                        XmlResult.Direction = ParameterDirection.Output;
                        command.Parameters.Add(XmlResult);

                        command.ExecuteNonQuery();
                        stringResult = command.Parameters["@XmlResult"].Value.ToString();
                    }
                    connection.Close();
                }

                //cast stringResult to  XmlNode
                XmlDocument xmlDBResult = new XmlDocument();
                xmlDBResult.LoadXml(stringResult);
                XmlNode xmlObject = xmlDBResult.DocumentElement;

                return xmlObject;
            }
        }
    }

      System.Exception: Creating MTS failed ---> System.Exception: Creating MTS failed ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.     
      at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding encoding)     at System.Text.EncodingNLS.GetString(Byte[] bytes, Int32 index, Int32 count)     
      at System.Data.SqlClient.TdsParserStateObject.TryReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp, String& value)     
      at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)     
      at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName)     
      at System.Data.SqlClient.TdsParser.TryProcessReturnValue(Int32 length, TdsParserStateObject stateObj, SqlReturnValue& returnValue, SqlCommandColumnEncryptionSetting columnEncryptionSetting)     
      at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)     
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)     
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int3

    Wednesday, May 15, 2019 3:17 AM

Answers

  • In the sproc you're using NVARCHAR which is Unicode. In your code you're saying it is an ANSI string. You need to fix your parameters. Because the string is not the correct charset and you're returning it as an output you're getting bad results.

    var XmlResult = new SqlParameter("@XmlResult", stringResult) {
       Direction = ParameterDirection.Output
    };
    command.Parameters.Add(XmlResult);
    See if that solves your problem.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by rgelfand Wednesday, May 22, 2019 2:35 PM
    Friday, May 17, 2019 5:06 AM
    Moderator

All replies

  • Hi rgelfand,

    Thank you for posting here.

    For your question, could you provide the code about the following keywords?

     Settings.Default.InteropProc ,  Settings.Default.SqlCommandTimeout

    We will solve your problem in time if you provide the above information.

    We are waiting for your update.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 15, 2019 5:36 AM
    Moderator
  • I don't have default set, but this

      <applicationSettings>
        <WBApi.Properties.Settings>
          <setting name="InteropProc" serializeAs="String">
            <value>sp_interop_EA</value>
          </setting>
          <setting name="SqlCommandTimeout" serializeAs="String">
            <value>300</value>
          </setting>
          <setting name="EmailSenderAddress" serializeAs="String">
            <value>wbalerts@mydomain.com</value>
          </setting>
          <setting name="EmailSenderPassword" serializeAs="String">
            <value>wb123!</value>
          </setting>
          <setting name="EmailRecipientAddress" serializeAs="String">
            <value>WBAlerts@mycomp.com</value>
          </setting>
          <setting name="GeneralDataApiUrl" serializeAs="String">
            <value>https://wb.....com/WBGeneralDataApi/</value>
          </setting>
          <setting name="ConnectionStringTemplate" serializeAs="String">
            <value>Server={0};database={1};Integrated Security=SSPI;Application Name=DBLServerApp;Max Pool Size=1000</value>
          </setting>
        </WebPOSApi.Properties.Settings>
      </applicationSettings>

    Wednesday, May 15, 2019 12:31 PM
  • If you look at the callstack the issue seems to be getting the response back, not sending it. Notice that the call is to TryReadSqlString and that is failing as it is trying to allocate enough space to store the response string. Is your sproc sending back a string response in the parameter?

    If the XML is really big then sending back as a parameter probably isn't the best option. Unfortunately ADO.NET's XML reader support is buggy if the data is too large such that it has to send it back in blocks. This is documented in MSDN. The preferred approach is to read the XML string back directly as a string using a reader (or perhaps ExecuteScalar) and then convert to XML. But that requires that you change from a parameter to returning the XML directly.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 15, 2019 2:36 PM
    Moderator
  • Hi rgelfand,

    Thanks for the feedback.

    I think that you still don't give the correct procedure. It will be best if you provide the procedure information like in the following link.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017​​​​​​​

    We need to check if your procedure has some errors.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 16, 2019 1:23 AM
    Moderator
  • Below, is the sproc.  Pleae note, the incoming xml contains transaction data.    When fewer transactions are sent, everything work fine.   It is when you send many transaction that this happens.

    CREATE  proc [dbo].[sp_ExecuteAction]
    (
    @Action nvarchar(250),
    @XmlParams nvarchar(max),
    @XmlResult nvarchar(max) output,
    @DebugOutput int=0
    )
    as

    set nocount on
    BEGIN

    declare @incomingXml xml
    set @XmlResult = '<Result/>'

    INSERT INTO ActionCalls

    Action,
    created_dt,
    XmlParams
    )
    SELECT
    @Action,
    GETDATE(),
    @XmlParams

    BEGIN TRY

    set @incomingXml = cast(@XmlParams as xml)


    if (@Action = 'PMS') 
    BEGIN
    exec sp_wb_PMS @XmlParams=@XmlParams, @XmlResult=@XmlResult output,@DebugOutput=@DebugOutput
    --TransactionResponseModel[]
    END

    END TRY
    BEGIN CATCH
    set @XmlResult = '<Result>Execution of '+isnull(@Action,'')+' failed.Error='+isnull(ERROR_MESSAGE(),'')+',line='+isnull(cast(ERROR_LINE() as nvarchar(max)),'')  +'</Result>'
    EXEC SP_AUX_LogSQL @AppName='wb',@Owner= 'sp_ExecuteAction', @Text=@XmlResult,@SqlCode=@XmlParams
    END CATCH

    END


    • Edited by rgelfand Friday, May 17, 2019 4:23 AM
    Friday, May 17, 2019 4:18 AM
  • In the sproc you're using NVARCHAR which is Unicode. In your code you're saying it is an ANSI string. You need to fix your parameters. Because the string is not the correct charset and you're returning it as an output you're getting bad results.

    var XmlResult = new SqlParameter("@XmlResult", stringResult) {
       Direction = ParameterDirection.Output
    };
    command.Parameters.Add(XmlResult);
    See if that solves your problem.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by rgelfand Wednesday, May 22, 2019 2:35 PM
    Friday, May 17, 2019 5:06 AM
    Moderator