Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Accessing tables across databases? Expand / Collapse
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: Friday, February 13, 2015 4:58 AM
Points: 9, Visits: 12

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?


Post #1594530
Posted Monday, July 21, 2014 5:55 AM


Group: General Forum Members
Last Login: Monday, June 27, 2016 4:28 AM
Points: 174, Visits: 527
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



Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 17,173, Visits: 32,140
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
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse