Shared Dataset with UniqueIdentifier parameter cannot convert

Posted by: traceym on 10 September 2018, 3:09 pm EST

  • Posted 10 September 2018, 3:09 pm EST

    I am evaluating ActiveReports Server to identify if it will suit our company's needs. Documentation would indicate this is so, however I have hit an early road-block.

    I am trying to create a Shared Dataset on the server which accepts a parameter of type UniqueIdentifier, however I am continually getting the following error on verify:

    "Conversion failed when converting from a character string to uniqueidentifier."

    The query for the DS is:

    SELECT * FROM Employees
    WHERE CompanyId = @CompanyId

    I add a parameter to the DS (data type of string), setting the default value to:
    AC4CAF47-CCD5-438E-9FBB-4CC9EE9FEECF

    However I receive the error when validating. I have tried with/without quotes, with/without braces, nothing allows me to save this DS with a string parameter for this UniqueIdentifier Field.

    I have tried using an integer parameter on an integer field and this works fine. It seems to just be the UniqueIdentifier field causing the issue.




  • Replied 10 September 2018, 11:45 pm EST

    Hi,

    I tried replicating the issue at my end but was unable to. When adding a Parameter to a Shared DataSet and setting the value you provided as the DefaultValue of the parameter, the Validation completed successfully. I have also attached a screen shot of the screen after validation.

    I'm not really sure of the cause and hence would request you to share detailed steps that I can follow to reproduce the behavior.

    Thanks
  • Replied 11 September 2018, 1:16 pm EST

    Steps are as follows:

    1. In the Data Sets Admin screen, Click “Create Data Set”
    2. Setup properties and click “Test Connection” – Success notified
    3. Enter Data Source General Properties , click next
    4. Add new parameter, click edit icon and update properties
    5. Click Validate - “Conversion failed when converting from a character string to uniqueidentifier."
    6. Click Finish
    7. Edit Dataset
    8. Click Validate
    9. “Conversion failed when converting from a character string to uniqueidentifier."

    Although I receive this error, I am able to use the Data Source to create a new report.
  • Replied 12 September 2018, 8:09 pm EST

    Hello Tracey,

    Could you please try after replacing the @CompanyId in the query with CAST(@CompanyId AS UNIQUEIDENTIFIER) as follow:


    SELECT * FROM Employees
    WHERE CompanyId = CAST(@CompanyId AS UNIQUEIDENTIFIER)


    Hope it helps.

    Thanks,
  • Replied 13 September 2018, 12:58 pm EST

    Tried but unsuccessful, I still get the same error.
  • Replied 13 September 2018, 3:51 pm EST

    Hello,

    I have escalated the issue to our development team(264007) and will inform once I get any information from them.

    Thanks,
  • Marked as Answer

    Replied 13 September 2018, 7:07 pm EST

    Hello,

    Please try the following syntax


    SELECT * FROM Employees
    WHERE CompanyId = CAST(CAST(@CompanyId as varbinary(36)) AS UNIQUEIDENTIFIER)

    Or

    SELECT * FROM Employees
    WHERE CompanyId = CAST(CAST(@CompanyId as varbinary(16)) AS UNIQUEIDENTIFIER)


    Hope it resolves your issue.

    Thanks,
  • Replied 14 May 2020, 2:06 pm EST

    Hello, I am experiencing the exact same error/issue as described above. I have created a stored procedure which takes in a varchar parameter. In the stored procedure I've cast the varchar per the accepted answer above using both varbinary(16) and varbinary(32). While I am now no longer receiving the "Conversion failed when converting from a character string to uniqueidentifier.", the stored procedure returns nothing. If I remove the cast and just pass the value as a varchar, a record is returned in SSMS. Any help would be much appreciated.
  • Replied 19 May 2020, 10:31 pm EST

    Hello,

    It is very difficult to provide the solution without looking at the Stored Procedure. Could you please try directly cast the Varchar without using the varbinary(32).

    Thanks,
    Mohit
  • Replied 19 May 2020, 10:31 pm EST

    Hello,

    It is very difficult to provide the solution without looking at the Stored Procedure. Could you please try directly cast the Varchar without using the varbinary(32).

    Thanks,
    Mohit
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels