SQL Server Dapper error "String or binary data would be truncated."

I am getting the error
String or binary data would be truncated.

when running the following line of code. I can't determine why and everything I search regarding this error is with inserts, such as exceeding the char limit in the database table. The data types of the database match the object string to nvarchar, DateTime to datetime etc. The following line of code follows the query below and is where the error is thrown. I am using Dapper 2.0.123

var result = (await connection.QueryAsync<OutputWithIds>(commandDefinition)).ToList();

Stack Trace

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.<>c__DisplayClass190_0.<ReadAsync>b__1(Task t)
at System.Data.SqlClient.SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDispose)

--- End of stack trace from previous location ---

at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext()
at OQCESIResultsUrl.Repository.OqcRepository.<GetStoredUrlsForManufacturingItemAsync>d__3.MoveNext() in ...
at OQCESIResultsUrl.Repository.OqcRepository.<GetStoredUrlsForManufacturingItemAsync>d__3.MoveNext() in ...
at OQCESIResultsUrl.UrlProviders.OqcUrlProvider.<ProvideUrlsForMeasurementAsync>d__2.MoveNext() in ...
at OQCESIResultsUrl.UrlProviders.MasterUrlProvider.<ProvideUrlsAsync>d__3.MoveNext() in ...
at OQCESIResultsUrl.Services.EsiResultsUrlRetrievalService.<GetEsiResultsUrlByMfgItemIds>d__2.MoveNext() in ...
at Program.<>c.<<<Main>$>b__0_6>d.MoveNext() in ...

The query that is being run is as follows

await using var connection = GetOpenConnection(OQCDomain);
var commandDefinition = new CommandDefinition(@" 
                SELECT CASE WHEN ser.PromassDirectoryPath IS NOT NULL OR ser.PromassDirectoryPath != '' THEN '' 
                    ELSE 'No record found'
                    END [ErrorMessage], 
                    CASE WHEN ser.PromassDirectoryPath IS NOT NULL OR ser.PromassDirectoryPath != '' THEN 1 
                    ELSE 0
                    END [Success], 
                    ser.PromassDirectoryPath [Url], 
                    ser.ProcessingDateTime, 
                    s.MfgItemId
                FROM dbo.Sample AS s WITH (NOLOCK)
                    LEFT JOIN dbo.SampleESIResult AS ser WITH (NOLOCK) ON ser.SampleESIResultId = s.SampleESIResultId
                    LEFT JOIN dbo.QCProcessHistory AS QPH WITH (NOLOCK) ON s.QCContainerId = QPH.QCContainerId
                WHERE s.MfgItemId IN (@manufacturingItemId)
                    AND QPH.QCStatusId = 9 /* ESI Reviewed */
                GROUP BY s.MfgItemId, ser.ProcessingDateTime, ser.PromassDirectoryPath",
    parameters: new
    {
        manufacturingItemId = string.Join(',', measurementDetail)
    },
    cancellationToken: cancellationToken);

The query does work when running it on the database directly, returning the expected results.

Object it's trying to map to is

public class OutputWithIds
{
    public string? ErrorMessage { get; set; }
    public bool Success { get; set; }
    public string? Url { get; set; }
    public DateTime? ProcessingDateTime { get; set; }
    public int? MfgItemId { get; set; }
}

Data types in the database are

MfgItemId = int
ProcessingDateTime  = datetime
Url nvarchar(256)

Any help would be appreciated. Thanks!

I have tried removing all but one of the select columns to try and narrow down which is causing the issue but all are throwing an error.

I am not sure about behavoir of SQL Server with virtual fields, but for my experience in other databases, it can be related with your virtual field [ErrorMessage] .

Just try to think step-by-step:

SQL Server tries to extract very first row from the database, and has some value in the column ser.PromassDirectoryPath . So, in the virtual field [ErrorMessage] you will have empty value, i.e. null, i.e. CHAR(0) or CHAR(1).

On the second row, you don't have value in the ser.PromassDirectoryPath column, and SQL Server tries to put string line "No record found" into virtual field, which, as it already know, has type CHAR(0).

So, maybe it will be anouth to CAST datatype of this virtual column to the CHAR(50)?

Something like this:

SELECT 
    CONVERT(CHAR(50), CASE WHEN ser.PromassDirectoryPath IS NOT NULL OR ser.PromassDirectoryPath != '' THEN '' 
            ELSE 'No record found'
            END) [ErrorMessage], 
    CASE WHEN ser.Promas

I am not sure about right syntax for SQL Server, but I hope I was clear.

SQL Server Dapper error "String or binary data would be truncated."

1条答案

相关问题

热门标签

最新问答