0

I have an IDbConnection to a SQL server and two stored procedures I would like to start from a console app using the Execute method on the open connection. When I run the first one, there are no problems, but the second one takes more than 30 seconds to execute, so a Timeout is raised.

According to the Internet, waiting for a command to execute is changed with the commandTimeout parameter, but after adding the commandTimeout: 0, commandTimeout: 99999, or commandTimeout: null, both Executes raise a SQL error: String or binary data would be truncated.

These errors don't happen if I run the procedure in MS SQL Server Management Studio.

What should I do to wait for a longer time for the procedure to execute and why does adding a commandTimeout give and error about truncating? I tried searching for a solution, but I'm not well versed in C# yet and didn't find anything useful.

Edit:

Some of you asked for tables or procedures, which I cannot give due to privacy reasons. However they are working correctly when executed in a query in the MS SQL Server Management Studio.

Here's the code in question:

public class MlCePredictedSqlSrvRepository : IMlCePredictedSqlSrvRepository
    {
        private IDbConnection _dbConnection;

        public MlCePredictedSqlSrvRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public bool ExecuteStoredProcedure()
        {
            try
            {
                string sql = @"[dbo].[StoredProcedure]";

                _dbConnection.Open();
                var procedure = _dbConnection.Execute(sql, commandType: CommandType.StoredProcedure, commandTimeout: 0);
                _dbConnection.Close();

                Console.WriteLine("Stored Procedure successfully executed.");

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("\tException in the Stored Procedure: " + ex.GetType().Name + "; " + ex.GetBaseException().Message);
                _dbConnection.Close();
                return false;
            }
        }
    }

It's called directly in the Program. The only change from working (albeit with a Timeout) to not working is the last parameter in Execute

Črt Grahonja
  • 21
  • 2
  • 2
    Without your code, there is no way to tell where your mistake is. – nvoigt May 06 '22 at 07:58
  • _According to the Internet_ ah, well... – Steve May 06 '22 at 08:10
  • Firstly the solution to the timeout is probably to speed up the query by rewriting it and/or better indexing, rather than changing the timeout. The error `String or binary data would be truncated` can sometimes be dependent on exactly where something is calculated. So to help you, we need the queries, the tables and indexes, and please share the query plan via https://brentozar.com/pastetheplan. Either way, neither of these problems is related to `IDbConnection` – Charlieface May 06 '22 at 08:29
  • I have edited the question with the code. The data includes more then 1MIO rows of text data and the procedure is a rule-based text-mining algorithm, I very much doubt that better indices will help here. Rewriting is also out of the question as the rules are mandatory. However the procedure finishes in a few minutes, and I would just like for the Execute to wait for this time. I cannot share my queries, tables and indices due to privacy reasons. – Črt Grahonja May 06 '22 at 09:33
  • 1
    From the second error you are describing i believe the timeout query is working as expected adn you are getting a result back but there is a problem with the result you are getting which is that some part of the data is getting truncated, are you able to get view any results when you get that error? you can read more about possible issues with data truncation here https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated – Armando Bracho May 06 '22 at 09:54
  • @ArmandoBracho Hello, thank you for your input. I cannot find any mistakes in the data itself (as I mentioned, both procedures execute without problems when run manually in MS SQL Server), however I tried printing out a bit more information about the Exception using the line `Console.WriteLine("\tException in StoredProcedure: "+ex.Message+" in "+ex.Procedure+" at "+ex.LineNumber.ToString()+": "+ex.Source);` and got `Exception in StoredProcedure: String or binary data would be truncated. The statement has been terminated. in StoredProcedure at 18: Core .Net SqlClient Data Provider` – Črt Grahonja May 06 '22 at 10:22
  • 1
    It's likely that running it in SSMS as opposed to .NET is yielding a slightly different execution plan, which means that some text got implicitly truncated at a different point, causing the error. We can't see without the execution plan. If you're worried you can anonymize it using SentryOne Plan Explorer, then share it via https://brentozar.com/pastetheplan – Charlieface May 06 '22 at 16:57

0 Answers0