Preview datasource results gives invalid unique constraint error

[Van]

I have a datasource that returns data similar to:

MemberId,
MemberName,
PurchaseItem,
PurchaseDate,
PurchaseAmount

Naturally, the MemberId is going to be repeated in the results, once for every purchase.  That's expected because I'm grouping by MemberId in the report.

But when setting up the query using the Configure SQL DataSource wizard, if I execute the query on the "Preview Data Source Results" step of the wizard I get the error "Column MemberId is constrained to be unique. Value '25' is already present'".

Why is this even being checked?  There's nothing wrong with a value appearing on more than one row. I'm not inserting to the actual table where the constraint would certainly apply. I'm just selecting data.

Is there a setting I'm missing?  If I just ignore the error and don't show any preview, the report itself works fine. It seems to be a bug in the preview.

I'm using MySql as the database.

[Neli]

Hi Van,

I checked in our database and it seems that this is the first time a user experienced such an issue. I would recommend testing the execution of the same query in the Management Studio. Please, let me know if the same error will be produced.

I also researched this issue in the Internet and I found the following external resources that you may find helpful:

- Troubleshooting: Column ‘FiscalYearName’ is constrained to be unique. Value ‘Fiscal Year 2019’ is already present

- Column 'StageTableId' is constrained to be unique. Value '1' is already present.

[Van]

I'm not sure what you mean by testing the execution  "in the Management Studio."  Do you mean Sql Server Management Studio?  If so, this is MySql, not Sql Server.  

But anywhere I run the query, it returns data just fine. It's only in the report datasource preview that the error appears.

The error would make perfect sense if I was actually inserting those records into the source table in the database, like the examples you provided.  But I'm not inserting anything to the database. I'm just selecting data for the report.  A constraint on a select query makes no sense.

[Neli]

Hi Van,

I am sorry for the misunderstanding. I completely agree that the error looks logical if you are adding records.

It will help me a lot if you can send me a video that demonstrates all of the steps of SQL Wizard. I also need to know if you are using parameters with the query. 

You can attach a Trace Listener as explained in the Standalone Report Designer Problems article and send me the generated log file.

[Van]

I'm not adding records.

This is a report query. Just a standard select statement.  Any select statement that returns the same primary key column value more than once will cause the error.  I'm not using parameters, but that wouldn't matter either.  A report query should not generate a unique constraint error -- it makes no sense.

just do this:

-- create a "member" table

create table member
( id int not null auto_increment,
name varchar(10) not null,
primary key(id));

-- Add one member

insert into member(name) values ('john');

-- Create a payment table

create table payment
(id int not null auto_increment,
member_id int not null,
amount decimal (5,2),
primary key(id));

-- Create several payments for the same member

insert into payment(member_id, amount) values (1, 5.00);
insert into payment(member_id, amount) values (1, 10.00);
insert into payment(member_id, amount) values (1, 15.00);

-- Now use this as the report sql datasource select statement:

select
m.id,
m.name,
p.id as payment_id,
p.amount
from member m
join payment p on m.id = p.member_id;

Preview the results.  You're going to get the same member.id back for every row, and it doesn't like that.

[Todor]

Hello Van,

Thank you for the detailed steps for reproducing the issue.

I tested first with the MSSQL database and the query was executed as expected, without any errors.

Then I tested with MySQL 5.7 database that we have locally for test purposes. The query was executed successfully - see the linked muted video for reference. In the report from the video, the component sqlDataSource2 is configured to connect to the MySQL database, sqlDataSource1 connects to the MS SQL database.

The issue may be related to the ADO.NET provider version. Which provider do you use?

Can you attach a Trace Listener to the Standalone Designer? Hopefully, it will log some relevant information that would let us troubleshoot further.