SQL Reporting use 2 different database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SQL Reporting use 2 different database

Author

Topic

ampmy123
Starting Member

5 Posts

Posted - 2013-02-15 : 14:41:35

HI,
I am a new for reporting, I have a assignment that on the report i have to compare data from 2 database Prod and Test, i have 2 data sets from both database, and i have 2 parameters here is my query
/******** this from Prod database*************/
SELECT permitTypeId, description, season
FROM Permits
WHERE (season = @season) AND (permitTypeId = @permitTypeId)
/*************from Test database ***************/
SELECT permitTypeId, description, season
FROM Permits
WHERE (season = @season) AND (permitTypeId = @permitTypeId)
/***********************************************/

here is my result
Permit ID | Description | Season | | PermitID | Description | Season|
111 | Dove |2011/2012|| 111 | Dove |2010/2012
/*****************************

purpose of this report to check Requirements for Configuration Compare between Prod environment and Test environment.
So i to get both of database to display data in the same table in the report , Is a way to accomplish this task. ?
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 14:47:13

why not put them onto separate tables side by side?

or bring them as a single resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ampmy123
Starting Member

5 Posts

Posted - 2013-02-15 : 14:51:35

That another option that i'm consider, i have tried to created separate tables side by side. one table have data display but another that use different dataset( different database) was have nothing display.
I don't know how to binding 2 different database in single report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 15:00:15

to bind two different datasets onto same table you've apply a dummy dataset to table and inside expression explicitly call required datasets in scope

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ampmy123
Starting Member

5 Posts

Posted - 2013-02-15 : 15:00:43

I have try to separate tables again , I have figure out that i set something wrong in the dataset properties that why the another table was not display anything.
I put them into the same table because i have to check between 2 database if something column is different, so i will highlight the row that is different.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-16 : 02:46:59

ok...if you've to relate between rows in dataset its always best to combine and bring them in same dataset itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ampmy123
Starting Member

5 Posts

Posted - 2013-02-19 : 10:32:40

HI ViaKH16, Can you explain more how to create dummy data set and apply to the table? Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 12:43:05

create a dataset with query like select 1 as key. Map table to the dataset. the other expressions inside should be specified with scope as dataset name ie suppose if you want field1 of dataset1 use First(Fields!Field1.value,"dataset1") etc

but one thing to note here is that you cant select individual values. you need to apply some kind of aggregation like MIN(),SUM(),First() etc

for getting all detail value best way is to bring them in same dataset if possible or Lookup() function if using 2008 R2 and above

http://msdn.microsoft.com/en-us/library/ee210531.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ampmy123
Starting Member

5 Posts

Posted - 2013-02-19 : 14:43:01

Thanks for the reply visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 01:26:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-26 : 22:03:31

Hi,

Assuming you want to compare the results of a query from your prod database to the same query on your test database the easiest option would be to do all the work in a single dataset, then your report is a simple, single table.

I think this is what visakh16 was trying to get to.

So create a dataset with a query something like this.

Note: This is assuming the code is running from your PROD database and both databases are on the same server. If they are on different servers, make sure there is a linked server setup between the two servers and then prefix the table names in the query with the server too (e.g. TEST.dbo.Permits would become DEVSERVER.TEST.dbo.Permits)


SELECT 
   p.PermitID AS Prod_PermitID, 
   p.Description AS Prod_Description, 
   p.Season AS Prod_Season,
   t.PermitID AS Test_PermitID, 
   t.Description AS Test_Description, 
   t.Season AS Test_Season
FROM
   (SELECT PermitID, Description, Season FROM Permits
    WHERE Season = @Season and PermitTypeID = @PermitTypeID) p
   JOIN 
   (SELECT PermitID, Description, Season FROM TEST.dbo.Permits
    WHERE Season = @Season and PermitTypeID = @PermitTypeID) t
      ON p.PermitID = t.PermitID
-- optionally add a where clause to filter to just the differences
      AND ((p.Description != t.Description) or (p.Season != t.Season))

Well you get the idea, you can change the query to give you your required results such as if you wanted to test if some records don't exist on either side then you could FULL JOIN the tables

Anyway, once you have this dataset it's just a case of dropping the results into a simple table on your report.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources