locked
Sync Service Not Returning Error Message When Attempting to Write Null Value to Non-Null Field RRS feed

  • Question

  • I am currently writing an iPad app which interacts with the Sync Framework via JSON, as per the Microsoft supplied sample app.  It works ok for the most part, but under certain circumstances an error message is not returned when it ought to be.  This is important for testing and data integrity.  To demonstrate this, please see the example logs below.  TaskDescription is non-null, and if I attempt to upload a null value to it the service should return an error.  The correct behaviour is shown in the log below:

    POST https://195.188.14.159:5012/PharmaCoach2Sync/DefaultScopeSyncService.svc/DefaultScope/UploadChanges?employeeid=2ee9f49f-4004-cbfa-36d6-5dd4073695d3

    content-type: Application/json

    Accept: Application/json

    Accept-encoding: gzip

    Content-Type: application/json

    {

      "d" : {

        "__sync" : {

          "moreChangesAvailable" : false,

          "serverBlob" : "AAEAAAD/////AQAAAAAAAAAMAgAAAFlNaWNyb3NvZnQuU3luY2hyb25pemF0aW9uLlNlcnZpY2VzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAK01pY3Jvc29mdC5TeW5jaHJvbml6YXRpb24uU2VydmljZXMuU3luY0Jsb2IFAAAAIDxDbGllbnRLbm93bGVkZ2U+a19fQmFja2luZ0ZpZWxkIDxDbGllbnRTY29wZU5hbWU+a19fQmFja2luZ0ZpZWxkHDxJc0xhc3RCYXRjaD5rX19CYWNraW5nRmllbGQaPEJhdGNoQ29kZT5rX19CYWNraW5nRmllbGQaPE5leHRCYXRjaD5rX19CYWNraW5nRmllbGQHAQADAwIBbVN5c3RlbS5OdWxsYWJsZWAxW1tTeXN0ZW0uR3VpZCwgbXNjb3JsaWIsIFZlcnNpb249NC4wLjAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iNzdhNWM1NjE5MzRlMDg5XV1tU3lzdGVtLk51bGxhYmxlYDFbW1N5c3RlbS5HdWlkLCBtc2NvcmxpYiwgVmVyc2lvbj00LjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPWI3N2E1YzU2MTkzNGUwODldXQIAAAAJAwAAAAYEAAAAJDg2MWVkZWMwLTIwNTYtNDQ1Ni1hMWNiLWQ5MjY4MDM3ZjIzZAAKCg8DAAAAkAAAAAIAAAAFAAAAAAAAAAEAAAAAAAAABQAAEAAAAAKGHt7AIFZEVqHL2SaAN/I90m9oeItZSvKIIqdGdVKnDwAAABgAABABKAIAAAEAAAAVAAAAAgAAAAEAAAAAAAAAAQAAAAEAAAABAAAAAAAAct4AAAAXAAAAAQAAABYAAAABAAMAAAAAAQAAAAAAAAAZAQAAAAAL"

        },

        "results" : [

          {

            "AssigneeID" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "DevelopmentIndicatorID" : null,

            "EndDate" : "/Date(1339412839000)/",

            "LastModifiedBy" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "LastModifiedTimeStamp" : "/Date(1339412839000)/",

            "OwnerID" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "StartDate" : "/Date(1339412839000)/",

            "TaskDescription" : null,

            "TaskID" : "92F9EA9C-68FD-45DF-9EF3-9156D8962DD7",

            "TaskStatusID" : "51e05dd4-4ecd-4405-b838-2582e9006ac8",

            "__metadata" : {

              "tempId" : "E689DB79-58F0-4CA4-BE57-8F11A30AED21",

              "type" : "DefaultScope.Task",

              "uri" : ""

            }

          },

          {

            "EmployeeID" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "LastModifiedTimeStamp" : "/Date(1339412839000)/",

            "TaskID" : "92F9EA9C-68FD-45DF-9EF3-9156D8962DD7",

            "TaskStatusHistoryID" : "30A1D2DB-C31F-4755-92F5-2EAB2D42A10A",

            "TaskStatusID" : "51e05dd4-4ecd-4405-b838-2582e9006ac8",

            "__metadata" : {

              "tempId" : "341ED5C1-011B-4B4E-A848-B00001A875C2",

              "type" : "DefaultScope.TaskStatusHistory",

              "uri" : ""

            }

          }

        ]

      }

    }

     -- response --

    200 OK

    Content-Length:  2791

    Content-Type:  application/json

    Server:  Microsoft-IIS/7.5

    SyncServiceVersion:  1.0

    X-Powered-By:  ASP.NET

    Date:  Mon, 11 Jun 2012 11:19:51 GMT

    {"d":{"__sync":{"moreChangesAvailable":true,"serverBlob":"AAEAAAD\/\/\/\/\/AQAAAAAAAAAMAgAAAFlNaWNyb3NvZnQuU3luY2hyb25pemF0aW9uLlNlcnZpY2VzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAK01pY3Jvc29mdC5TeW5jaHJvbml6YXRpb24uU2VydmljZXMuU3luY0Jsb2IFAAAAIDxDbGllbnRLbm93bGVkZ2U+a19fQmFja2luZ0ZpZWxkIDxDbGllbnRTY29wZU5hbWU+a19fQmFja2luZ0ZpZWxkHDxJc0xhc3RCYXRjaD5rX19CYWNraW5nRmllbGQaPEJhdGNoQ29kZT5rX19CYWNraW5nRmllbGQaPE5leHRCYXRjaD5rX19CYWNraW5nRmllbGQHAQADAwIBbVN5c3RlbS5OdWxsYWJsZWAxW1tTeXN0ZW0uR3VpZCwgbXNjb3JsaWIsIFZlcnNpb249NC4wLjAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iNzdhNWM1NjE5MzRlMDg5XV1tU3lzdGVtLk51bGxhYmxlYDFbW1N5c3RlbS5HdWlkLCBtc2NvcmxpYiwgVmVyc2lvbj00LjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPWI3N2E1YzU2MTkzNGUwODldXQIAAAAJAwAAAAYEAAAAJDg2MWVkZWMwLTIwNTYtNDQ1Ni1hMWNiLWQ5MjY4MDM3ZjIzZAAKCg8DAAAAnAAAAAIAAAAFAAAAAAAAAAEAAAAAAAAABQAAEAAAAAKGHt7AIFZEVqHL2SaAN\/I90m9oeItZSvKIIqdGdVKnDwAAABgAABABKAIAAAEAAAAVAAAAAgAAAAEAAAAAAAAAAQAAAAIAAAAAAAAAAAAAAAQAAAABAAAAAAAAct4AAAAXAAAAAQAAABYAAAABAAMAAAAAAQAAAAAAAAAZAQAAAAAL"},"results":[{"__metadata":{"uri":"https:\/\/195.188.14.159:5012\/PharmaCoach2Sync\/DefaultScopeSyncService.svc\/Task(TaskID=guid'92f9ea9c-68fd-45df-9ef3-9156d8962dd7')","type":"DefaultScope.Task","isDeleted":true},"__syncError":{"errorDescription":"Cannot insert the value NULL into column 'TaskDescription', table 'PharmaCoach2Sync.dbo.Task'; column does not allow nulls. INSERT fails.\u000d\u000aThe statement has been terminated.","changeInError":{"TaskID":"92f9ea9c-68fd-45df-9ef3-9156d8962dd7","StartDate":"\/Date(1339412839000)\/","EndDate":"\/Date(1339412839000)\/","AssigneeID":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","DevelopmentIndicatorID":"00000000-0000-0000-0000-000000000000","OwnerID":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","TaskStatusID":"51e05dd4-4ecd-4405-b838-2582e9006ac8","TaskDescription":null,"LastModifiedBy":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","LastModifiedTimeStamp":"\/Date(1339412839000)\/","__metadata":{"tempId":"E689DB79-58F0-4CA4-BE57-8F11A30AED21","uri":"https:\/\/195.188.14.159:5012\/PharmaCoach2Sync\/DefaultScopeSyncService.svc\/Task(TaskID=guid'92f9ea9c-68fd-45df-9ef3-9156d8962dd7')","type":"DefaultScope.Task"}}}},{"TaskStatusHistoryID":"30a1d2db-c31f-4755-92f5-2eab2d42a10a","TaskID":"92f9ea9c-68fd-45df-9ef3-9156d8962dd7","EmployeeID":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","TaskStatusID":"51e05dd4-4ecd-4405-b838-2582e9006ac8","LastModifiedTimeStamp":"\/Date(1339412839000)\/","__metadata":{"tempId":"341ED5C1-011B-4B4E-A848-B00001A875C2","uri":"https:\/\/195.188.14.159:5012\/PharmaCoach2Sync\/DefaultScopeSyncService.svc\/TaskStatusHistory(TaskStatusHistoryID=guid'30a1d2db-c31f-4755-92f5-2eab2d42a10a')","edituri":"","type":"DefaultScope.TaskStatusHistory"}}]}}

    The message "Cannot insert the value NULL into column 'TaskDescription', table 'PharmaCoach2Sync.dbo.Task'; column does not allow nulls. INSERT fails." is present and correct in the response.  However if I also set AssigneeID to null (which is itself not-null) and try the same request, no error message comes back:

    POST https://195.188.14.159:5012/PharmaCoach2Sync/DefaultScopeSyncService.svc/DefaultScope/UploadChanges?employeeid=2ee9f49f-4004-cbfa-36d6-5dd4073695d3

    content-type: Application/json

    Accept: Application/json

    Accept-encoding: gzip

    Content-Type: application/json

    {

      "d" : {

        "__sync" : {

          "moreChangesAvailable" : false,

          "serverBlob" : "AAEAAAD/////AQAAAAAAAAAMAgAAAFlNaWNyb3NvZnQuU3luY2hyb25pemF0aW9uLlNlcnZpY2VzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAK01pY3Jvc29mdC5TeW5jaHJvbml6YXRpb24uU2VydmljZXMuU3luY0Jsb2IFAAAAIDxDbGllbnRLbm93bGVkZ2U+a19fQmFja2luZ0ZpZWxkIDxDbGllbnRTY29wZU5hbWU+a19fQmFja2luZ0ZpZWxkHDxJc0xhc3RCYXRjaD5rX19CYWNraW5nRmllbGQaPEJhdGNoQ29kZT5rX19CYWNraW5nRmllbGQaPE5leHRCYXRjaD5rX19CYWNraW5nRmllbGQHAQADAwIBbVN5c3RlbS5OdWxsYWJsZWAxW1tTeXN0ZW0uR3VpZCwgbXNjb3JsaWIsIFZlcnNpb249NC4wLjAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iNzdhNWM1NjE5MzRlMDg5XV1tU3lzdGVtLk51bGxhYmxlYDFbW1N5c3RlbS5HdWlkLCBtc2NvcmxpYiwgVmVyc2lvbj00LjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPWI3N2E1YzU2MTkzNGUwODldXQIAAAAJAwAAAAYEAAAAJDg2MWVkZWMwLTIwNTYtNDQ1Ni1hMWNiLWQ5MjY4MDM3ZjIzZAAKCg8DAAAAkAAAAAIAAAAFAAAAAAAAAAEAAAAAAAAABQAAEAAAAAKGHt7AIFZEVqHL2SaAN/I90m9oeItZSvKIIqdGdVKnDwAAABgAABABKAIAAAEAAAAVAAAAAgAAAAEAAAAAAAAAAQAAAAEAAAABAAAAAAAAct4AAAAXAAAAAQAAABYAAAABAAMAAAAAAQAAAAAAAAAZAQAAAAAL"

        },

        "results" : [

          {

            "AssigneeID" : null,

            "DevelopmentIndicatorID" : null,

            "EndDate" : "/Date(1339412839000)/",

            "LastModifiedBy" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "LastModifiedTimeStamp" : "/Date(1339412839000)/",

            "OwnerID" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "StartDate" : "/Date(1339412839000)/",

            "TaskDescription" : null,

            "TaskID" : "92F9EA9C-68FD-45DF-9EF3-9156D8962DD7",

            "TaskStatusID" : "51e05dd4-4ecd-4405-b838-2582e9006ac8",

            "__metadata" : {

              "tempId" : "E689DB79-58F0-4CA4-BE57-8F11A30AED21",

              "type" : "DefaultScope.Task",

              "uri" : ""

            }

          },

          {

            "EmployeeID" : "2ee9f49f-4004-cbfa-36d6-5dd4073695d3",

            "LastModifiedTimeStamp" : "/Date(1339412839000)/",

            "TaskID" : "92F9EA9C-68FD-45DF-9EF3-9156D8962DD7",

            "TaskStatusHistoryID" : "30A1D2DB-C31F-4755-92F5-2EAB2D42A10A",

            "TaskStatusID" : "51e05dd4-4ecd-4405-b838-2582e9006ac8",

            "__metadata" : {

              "tempId" : "341ED5C1-011B-4B4E-A848-B00001A875C2",

              "type" : "DefaultScope.TaskStatusHistory",

              "uri" : ""

            }

          }

        ]

      }

    }

     -- response --

    200 OK

    Content-Length:  2364

    Content-Type:  application/json

    Server:  Microsoft-IIS/7.5

    SyncServiceVersion:  1.0

    X-Powered-By:  ASP.NET

    Date:  Mon, 11 Jun 2012 11:27:24 GMT

    {"d":{"__sync":{"moreChangesAvailable":true,"serverBlob":"AAEAAAD\/\/\/\/\/AQAAAAAAAAAMAgAAAFlNaWNyb3NvZnQuU3luY2hyb25pemF0aW9uLlNlcnZpY2VzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAK01pY3Jvc29mdC5TeW5jaHJvbml6YXRpb24uU2VydmljZXMuU3luY0Jsb2IFAAAAIDxDbGllbnRLbm93bGVkZ2U+a19fQmFja2luZ0ZpZWxkIDxDbGllbnRTY29wZU5hbWU+a19fQmFja2luZ0ZpZWxkHDxJc0xhc3RCYXRjaD5rX19CYWNraW5nRmllbGQaPEJhdGNoQ29kZT5rX19CYWNraW5nRmllbGQaPE5leHRCYXRjaD5rX19CYWNraW5nRmllbGQHAQADAwIBbVN5c3RlbS5OdWxsYWJsZWAxW1tTeXN0ZW0uR3VpZCwgbXNjb3JsaWIsIFZlcnNpb249NC4wLjAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iNzdhNWM1NjE5MzRlMDg5XV1tU3lzdGVtLk51bGxhYmxlYDFbW1N5c3RlbS5HdWlkLCBtc2NvcmxpYiwgVmVyc2lvbj00LjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPWI3N2E1YzU2MTkzNGUwODldXQIAAAAJAwAAAAYEAAAAJDg2MWVkZWMwLTIwNTYtNDQ1Ni1hMWNiLWQ5MjY4MDM3ZjIzZAAKCg8DAAAAnAAAAAIAAAAFAAAAAAAAAAEAAAAAAAAABQAAEAAAAAKGHt7AIFZEVqHL2SaAN\/I90m9oeItZSvKIIqdGdVKnDwAAABgAABABKAIAAAEAAAAVAAAAAgAAAAEAAAAAAAAAAQAAAAIAAAAAAAAAAAAAAAUAAAABAAAAAAAAct4AAAAXAAAAAQAAABYAAAABAAMAAAAAAQAAAAAAAAAZAQAAAAAL"},"results":[{"TaskID":"92f9ea9c-68fd-45df-9ef3-9156d8962dd7","StartDate":"\/Date(1339412839000)\/","EndDate":"\/Date(1339412839000)\/","AssigneeID":"00000000-0000-0000-0000-000000000000","DevelopmentIndicatorID":"00000000-0000-0000-0000-000000000000","OwnerID":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","TaskStatusID":"51e05dd4-4ecd-4405-b838-2582e9006ac8","TaskDescription":null,"LastModifiedBy":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","LastModifiedTimeStamp":"\/Date(1339412839000)\/","__metadata":{"tempId":"E689DB79-58F0-4CA4-BE57-8F11A30AED21","uri":"https:\/\/195.188.14.159:5012\/PharmaCoach2Sync\/DefaultScopeSyncService.svc\/Task(TaskID=guid'92f9ea9c-68fd-45df-9ef3-9156d8962dd7')","edituri":"","type":"DefaultScope.Task"}},{"TaskStatusHistoryID":"30a1d2db-c31f-4755-92f5-2eab2d42a10a","TaskID":"92f9ea9c-68fd-45df-9ef3-9156d8962dd7","EmployeeID":"2ee9f49f-4004-cbfa-36d6-5dd4073695d3","TaskStatusID":"51e05dd4-4ecd-4405-b838-2582e9006ac8","LastModifiedTimeStamp":"\/Date(1339412839000)\/","__metadata":{"tempId":"341ED5C1-011B-4B4E-A848-B00001A875C2","uri":"https:\/\/195.188.14.159:5012\/PharmaCoach2Sync\/DefaultScopeSyncService.svc\/TaskStatusHistory(TaskStatusHistoryID=guid'30a1d2db-c31f-4755-92f5-2eab2d42a10a')","edituri":"","type":"DefaultScope.TaskStatusHistory"}}]}}

    There is no error message in the response when there ought to be.  As anybody able to shed any light into why this is happening?

    Wednesday, June 13, 2012 1:40 PM

Answers

  • Actually in relation to the above we have confirmed that using the RAISEERROR in a stored proc, does indeed propogate to the IPad device, by attaching to the error tag in the JSON response.
    • Marked as answer by xcfrt Friday, June 15, 2012 9:35 AM
    Friday, June 15, 2012 9:35 AM
  • good to hear you have it all figured out.  yes, raiseerror in the bulk SPs will result to an error that will get reported in the ApplyChangeFailed event as well which will eventually find its way to the json response.

    • Marked as answer by xcfrt Monday, June 18, 2012 8:58 AM
    Sunday, June 17, 2012 12:39 PM

All replies

  • looks  like the null values you passed has been replaced with an empty guid

    "AssigneeID":"00000000-0000-0000-0000-000000000000","DevelopmentIndicatorID":"00000000-0000-0000-0000-000000000000"

    Thursday, June 14, 2012 1:54 AM
  • Shouldn't the service return an error if we're trying to set a foreign key to null rather than just putting an empty guid in there?  And the fact remains that even the empty guids TaskDescription is set to null, but no error is returned when it should be.

    So even though the guids are empty, and thus not mapping (i think this is what you mean), should we still not get an error message back for the null value.

    or is it the case that it not map on the first foreign key, i.e. AssigneeID and so therefore doesnt get to the null task id and thus does not throw an error message?

    Thursday, June 14, 2012 9:37 AM
  • can you confirm if rows were actually inserted for the rows? just to confirm if its even trying to apply the change. better, set verbose logging on the service and see if there's any other error.

    looking at the code, I can see some codes that tries to do some conversion on guids and some of them return an empty guid. am not sure if they're getting hit though. if you can run the service side on debug, you might be able to pinpoint if there's some conversion error.

    Thursday, June 14, 2012 10:22 AM
  • Hi, June,

    yes indeed, I re-checked and we have customised the bulkinsert stored procedures, and in jected our own business logic. Thus we were in a sense by-passing the  validation if you like, by simply not inserting the record, and thus no excpetion getting raised.

    I have had to have this business logic in place as part of the client's requirment, and thus would need in such cases to return a custom error message to the ipad.

    My question is how can this be done. Can we simply do a RASISEERROR in the stored procedure and this will migrate through to the ipad response?

    Or do we need to change the c# code for the service and do in there. I am hoping we can do as TSQL rather than c# as this is always easier to redploy?

    Thursday, June 14, 2012 2:20 PM
  • Actually in relation to the above we have confirmed that using the RAISEERROR in a stored proc, does indeed propogate to the IPad device, by attaching to the error tag in the JSON response.
    • Marked as answer by xcfrt Friday, June 15, 2012 9:35 AM
    Friday, June 15, 2012 9:35 AM
  • good to hear you have it all figured out.  yes, raiseerror in the bulk SPs will result to an error that will get reported in the ApplyChangeFailed event as well which will eventually find its way to the json response.

    • Marked as answer by xcfrt Monday, June 18, 2012 8:58 AM
    Sunday, June 17, 2012 12:39 PM
  • Thanks June, yeh I think this is neat that u can throw errors in sql and manifest down to ipad, as this means I can write custome error messages around business logic (as all my business logic is in the stored procs).

    Thanks again

    Monday, June 18, 2012 8:58 AM