Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Merge 2 Databases Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 9:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

Hi geniuses!
I need to merge 2 databases to colect information for my reports.

Have really no clue about how to set things up.
The query must be written into the query 'box' of the Dataset Properties, RIGHT?

Thanks in advance.
Regards!
Post #1365953
Posted Monday, October 1, 2012 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
What it looks like you need is a UNION clause, either in a stored proc on direct in the data set.

SELECT
1,2,3
FROM
DB1.dbo.Table2
UNION / UNION ALL
SELECT
1,2,3
FROM
DB2.dbo.Table1





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366362
Posted Monday, October 1, 2012 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

OK. Thanks!
It is possible to relate 2 tables from different datasources?
For example: I have Dataset1 from Datasource1 and Dataset2 from Datasource2.
I want from Dataset1 : ProjectName;
and from Dataset2 : ProjectStage. Which is in another DB.

How do I do this and add these fields into the same tablix?

Sorry if it sounds stupid, but I'm learning, this is all new for me.

Thanks in advance.
Regards
Post #1366425
Posted Monday, October 1, 2012 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
Not sure on that, you will need to build some sort of linking in 1 of the servers.

Something like using linked servers, or building a merged database of both DB's using SSIS or something along them lines.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366427
Posted Monday, October 1, 2012 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668
OK thanks anyway!
What about linkin 2 tablix? Possible? Crazy stuff right?
Regards.
Post #1366445
Posted Monday, October 1, 2012 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
again unsure, never had to do it, would be worth a google and if it is possible let us know how.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366579
Posted Monday, October 1, 2012 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

OK! In the mean time how do I create a Stored Procedure where I want data from 2 Databases.
I want ProjectName from DB1 & ProjectStage from DB2.

Thanks

Best Regards
Post #1366585
Posted Monday, October 1, 2012 8:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
Something like the below
SELECT
DB1.ProjectName,
DB2.ProjectStage
FROM
Database1.dbo.Table1 DB1
INNER JOIN
Database2.dbo.Table2 DB2
ON
DB1.ProjectStageID = DB2.ProjectStageID

Without knowing your data schemas it is hard to say.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366588
Posted Monday, October 1, 2012 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

This not working for some reason.

Instead, do you know a way to link different datasets from 2 different datasources in a tablix cell expression?
Dataset1 from Datasource1
Dataset2 from Datasource2

Thanks

Regards
Post #1366639
Posted Tuesday, October 2, 2012 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
Google it, I am unsure if it can be done.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse