I've got an ASP.NET MVC web app, running in Azure, against an Azure SQL database, using EF 6.

Yesterday I discovered an error when opening a page that uses a stored procedure to get some data:

System.Data.SqlClient.SqlException (0x80131904):
Conversion failed when converting the ****** value '******' to data type ******

The asterisks are in the error message, not put there by me.

The error occurs in a controller action with a single line:

return JsonConvert.SerializeObject(Uow.StoredProcedures.GetClientLiasonDetails(highlightOnIfi, hasMedicalIssues, medicalSummaryStatusIds, duplicate, azContactUserId).ToList());

I would have expected that to call GetClientLiasonDetails, but for some reason the stack trace in Elmah (see below) doesn't show that. Assuming it made it into GetClientLiasonDetails, the stored procedure would be fired like this:

return DbContext.Database.SqlQuery<ClientLiasonDetailsResult>("dbo.GetClientLiasonDetails @HighlightOnIfi, @HasMedicalIssues, @MedicalSummaryStatusId, @Duplicate, @AzContactUserID", highlightOnIfiParam, hasMedicalIssuesParam, medicalSummaryStatusIdsParam, duplicateParam, azContactUserIdParam);

The error happens every time I open the page, but so far I haven't been able to replicate it in a dev database, or by calling the stored procedure in the production database, even when logged in as the website user.

The database has transparent data encryption enabled, but no column encryption or data-masking.

I've tried looking in the DMVs, and although I can see the SQL that inserts the ELMAH error, there is no sign of the SQL that causes the error.

There don't seem to be any other problems with the system - other calls to stored procedures are working fine.

---

Update 1

The stored procedure causing the problem (GetClientLiasonDetails) uses a SQL Server function (GetClientLiasonFullData) to get its data. When I wrote the function, a couple of years ago, it used string_split, which worked in my local SQL Server, and worked in Azure SQL, but caused an error when the Azure SQL database was exported.

To get round the export issue, I wrote my own version of string_split (a function called AzStringSplit).

It seems that the cause of today's issue was somewhere in AzStringSplit, because I've just switched GetClientLiasonFullData to use the built-in string_split instead of AzStringSplit, and the issue has gone away.

---

Update 2

I was about to post the code for AzStringSplit so someone could explain how it had caused the problem, but then I idly wondered whether it was the recompilation of GetClientLiasonFullData that had fixed the issue, rather than the change to string_split... I switched the function back to using AzStringSplit, and it still works.

So the issue was fixed by recompiling the function, GetClientLiasonFullData. The code for that is below, in case anyone can explain how it worked before, then caused an error, and now works again.

CREATE FUNCTION [dbo].[GetClientLiasonFullData](
    @HighlightOnIfi VARCHAR(MAX),
    @HasMedicalIssues VARCHAR(MAX),
    @MedicalSummaryStatusId VARCHAR(MAX),
    @Duplicate VARCHAR(MAX),
    @AzContactUserID NVARCHAR(128)
)
RETURNS TABLE
AS
    RETURN

    SELECT      gcld.ClientId,
                gcld.Client,
                anu.FirstName AS AzContactUserFirstName,
                anu.LastName AS AzContactUserLastName,
                gcld.ProgrammeId,
                gcld.Programme,
                gcld.ProgrammeOrder,
                gcld.ParticipantId,
                gcld.ParticipantFirstName,
                gcld.ParticipantLastName,
                gcld.HighlightOnIfi,
                g.Description AS Gender,
                gcld.HasMedicalIssues,
                gcld.MedicalSummaryStatusId,
                mss.Description AS MedicalSummaryStatus,
                CASE WHEN mss.DisplaySortOrder = 1 THEN 1 WHEN Duplicate = 1 THEN 2 ELSE mss.DisplaySortOrder + 1 END AS StatusOrder,
                gcld.HasNotes,
                Duplicate,
                Withdrawn

    FROM        dbo.GetClientLiasonData() gcld

    INNER JOIN  dbo.Gender g
    ON          g.GenderId = gcld.GenderId

    INNER JOIN  dbo.MedicalSummaryStatus mss
    ON          mss.MedicalSummaryStatusId = gcld.MedicalSummaryStatusId

    INNER JOIN  dbo.AspNetUsers anu
    ON          anu.Id = gcld.AzContactUserId

    INNER JOIN  AzStringSplit(@HighlightOnIfi, ',') hoi
    ON          gcld.HighlightOnIfi = hoi.value

    INNER JOIN  AzStringSplit(@HasMedicalIssues, ',') hmi
    ON          gcld.HasMedicalIssues = hmi.value

    INNER JOIN  AzStringSplit(@MedicalSummaryStatusId, ',') mssi
    ON          gcld.MedicalSummaryStatusId = mssi.value

    INNER JOIN  AzStringSplit(@Duplicate, ',') d
    ON          gcld.Duplicate = d.value

    WHERE       (@AzContactUserID IS NULL
    OR          gcld.AzContactUserId = @AzContactUserID);

---

Stack Trace from Elmah

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the ****** value '******' to data type ******.
   at System.Data.SqlClient.SqlConnection.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.Read()
   at System.Data.Entity.Core.Objects.Internal.ShapelessBufferedDataRecord.Initialize(String providerManifestToken, DbProviderServices providerSerivces, DbDataReader reader)
   at System.Data.Entity.Core.Objects.Internal.BufferedDataReader.Initialize(String providerManifestToken, DbProviderServices providerServices, Type[] columnTypes, Boolean[] nullableColumns)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass69`1.<ExecuteStoreQueryReliably>b__68()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass69`1.<ExecuteStoreQueryReliably>b__67()
   at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Az.Ems.Web.Controllers.ParticipantsController.OverviewResults(Boolean[] highlightOnIfi, Boolean[] hasMedicalIssues, Int32[] medicalSummaryStatusIds, Boolean[] duplicate, String azContactUserId) in C:\Users\Jon\Source\Repos\EMS\Az.Ems.Web\Controllers\ParticipantsController.cs:line 510
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)