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

Accessing tables across databases? Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:56 AM
Points: 9, Visits: 10
Hi,

As far as I am aware T-SQL stored procedures can only access tables in the database where the stored procedure is running, and it is not possible to create views that span databases. The requirement I have is to combine data from a table in two different databases into another table in another database.

Do I need to create a worker role with a queuing mechanism then instruct that role to open a connection to each of the three databases, so read the 1st table data via 1st connection, read 2nd table via 2nd connection and write to the 3rd table via the 3rd connection?

What other techniques are there for accessing tables across databases in Azure?

Thanks,

Paul.
Post #1594530
Posted Monday, July 21, 2014 5:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 173, Visits: 449
Cross db queries are not supported in azure , searches on google gave a solution that query this tables individually and join it in the application itself.
Post #1594538
Posted Tuesday, July 22, 2014 5:40 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
If you need to access data on more than one database within Microsoft Azure SQL Database, you must do that at that application level. The databases are absolutely isolated from each other. The reason for this is, while you define a server for your databases, it's only a virtual construct. The actual location of the databases maybe be on any number of different physical boxes within your data center. There's no way for them to define linked servers for these, especially because they'll be constantly moving anyway as failovers occur. That's not even to mention the nature of cross-server queries and their tendency to default to scans of all data. There's just no way they can build a shared infrastructure and support such a mechanism.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1595030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse