SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Accessing tables across databases?


Accessing tables across databases?

Author
Message
paul.wallis
paul.wallis
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 12
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.
rahulgsingh
rahulgsingh
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40515 Visits: 32665
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search