Relation across multiple databases with parameters
Posted: Wed Aug 08, 2012 1:43 pm
Hello,
I have the following situation with version 2012.1 (release version):
Database A (SQL Server)
- Table A
- Id (uniqueidentifier)
- B_Id (uniqueidentifier)
Database B (SQL Server)
- Table B
- Id (uniqueidentifier)
There's a one to one relationship between the tables in the different databases. Table A.A (B_Id) references Table B.B (Id).
I've created data sources for both tables (two different connections). I've added a relation from table A (child) to table B (parent).
I just put the datarow on the report (no master detail, no sub report, just one row with both parent and child data as it is 1-1). When I preview, it show all data, it works perfectly. But, to match the relationships, all data from B is queried (select * from B), which can become a huge table, so I don't want that.
I've searched these forums to make it so that it only collects the records from B which are actually referenced. This seems to be possible, but somehow I can't get it to work!
I've tried adding parameter with expression to Table B datasource, resulting in query: SELECT * FROM B WHERE Id = @Id. With parameter expression: A.B_Id. I've tried to change the reconnect on each row checkbox, but it all does not seem to help, it either seems to keep querying all records or I get errors like the following (I think because of the parameter with expression?):
"Specified cast is not valid."
It seems to have issues casting the values to guid or so? What I DO see is that there's a difference in type (columns seem to be guid, parameter I have set to uniqueidentifier manually).
Either way: questions:
1. Why the error and how to solve?
2a. Should it be possible to get this 1-1 relationship (no master detail) working the way I want (no select*, using a select per row?)
2b. If so, which are the exact steps to get this done? Or could it be working already if the error is solved?
3. About that 'reconnect on each row': if this setting is necessary, do I need to set it on the child datasource or the parent datasource or both?
4. Could this situation also work on two different database types? Like Oracle and SqlServer for the databases A and B?
Thanks in advance.
I have the following situation with version 2012.1 (release version):
Database A (SQL Server)
- Table A
- Id (uniqueidentifier)
- B_Id (uniqueidentifier)
Database B (SQL Server)
- Table B
- Id (uniqueidentifier)
There's a one to one relationship between the tables in the different databases. Table A.A (B_Id) references Table B.B (Id).
I've created data sources for both tables (two different connections). I've added a relation from table A (child) to table B (parent).
I just put the datarow on the report (no master detail, no sub report, just one row with both parent and child data as it is 1-1). When I preview, it show all data, it works perfectly. But, to match the relationships, all data from B is queried (select * from B), which can become a huge table, so I don't want that.
I've searched these forums to make it so that it only collects the records from B which are actually referenced. This seems to be possible, but somehow I can't get it to work!
I've tried adding parameter with expression to Table B datasource, resulting in query: SELECT * FROM B WHERE Id = @Id. With parameter expression: A.B_Id. I've tried to change the reconnect on each row checkbox, but it all does not seem to help, it either seems to keep querying all records or I get errors like the following (I think because of the parameter with expression?):
"Specified cast is not valid."
It seems to have issues casting the values to guid or so? What I DO see is that there's a difference in type (columns seem to be guid, parameter I have set to uniqueidentifier manually).
Either way: questions:
1. Why the error and how to solve?
2a. Should it be possible to get this 1-1 relationship (no master detail) working the way I want (no select*, using a select per row?)
2b. If so, which are the exact steps to get this done? Or could it be working already if the error is solved?
3. About that 'reconnect on each row': if this setting is necessary, do I need to set it on the child datasource or the parent datasource or both?
4. Could this situation also work on two different database types? Like Oracle and SqlServer for the databases A and B?
Thanks in advance.