Shared Dataset with UniqueIdentifier parameter cannot convert

Shared Dataset with UniqueIdentifier parameter cannot convert

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

• 

  traceym

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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.

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 10 September 2018, 11:45 pm EST - Updated 30 September 2022, 12:51 am 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

  

  1275×790 47.3 KB

• 

  traceym

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 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,

• 

  traceym

  • Post Options:
  • Link
    Copy

  Posted 13 September 2018, 12:58 pm EST

  Tried but unsuccessful, I still get the same error.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 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,

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 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,

• 

  michelle

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 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