locked
SQL Azure select from database RRS feed

  • Question

  • Hi,

    I know the question has been asked before, but I don't seem to be able to find a recent reply.  Please forgive me as I am asking the question on behalf of some legacy SQL developers (as in older SQL not that the developers are legacy!).

    We are in the process of attempting to migrate our current SQL 2000/2005 estate into Azure, which is a task in itself; however in a number of scenarios it is necessary for one database to reference perhaps just a table in another database.  Esentially something like client postcodes may be held in a table on a second database as they are only needed for certain results in another database.

    Is there an easy way to do this? I have seen references saying that it's not possible to do a select command to get the second database and table, but then I spotted other references regarding creating a temporary table and doing a select into query to get the data.

    Any help would be much appreciated, and I'm sorry if I'm repeating another post (feel free to point me there), but this is stopping us migrating the 6 major customer databases we have an we could do with some help.

    Many thanks,

    Paul

    Wednesday, May 30, 2012 1:28 PM

Answers

  • Paul,

    Since cross database references are not supported by SQL Azure at this time. There are two workarounds choose the one which is best for your requirements

    • Combine your database in SQL Azure
    • Combine your data in your on premise database & then to data sync to SQL Azure.

    You can refer Cross database joins in SQL Azure 

    Let me know if you have any queries.


    Thanks,
    |Sandip Shinde|Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip|

    Wednesday, May 30, 2012 2:16 PM

All replies

  • it is necessary for one database to reference perhaps just a table in another database.

    Hello Paul,

    In SQL Azure this is not possible, you can query "cross databases", even three-parts qualifierer (database.schema.objectname) are not supported.

    The only option is to query separatley both table and join them in memory on client side; Ado.Net supports this.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, May 30, 2012 2:12 PM
  • Paul,

    Since cross database references are not supported by SQL Azure at this time. There are two workarounds choose the one which is best for your requirements

    • Combine your database in SQL Azure
    • Combine your data in your on premise database & then to data sync to SQL Azure.

    You can refer Cross database joins in SQL Azure 

    Let me know if you have any queries.


    Thanks,
    |Sandip Shinde|Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip|

    Wednesday, May 30, 2012 2:16 PM
  • Hi,

    Many thanks for the reply's I passed these on to our developers who say that is going to cause a little problem as there is a vast amount of data that cannot be combined in a single database; trying to stay away from an on premise system is proving to be difficult.

    Do we know if there is any scope for this position of connections to other Azure databases within the same zones being changed at all in the near future?

    I assume from your replies (again playing the 'not very knowledgeable' card here) that what is being said is that if we had a database in Azure we would be able to reference that easily from an on premise SQL database.  Just not between databases in Azure.

    Regards,

    Paul

    Wednesday, May 30, 2012 3:55 PM
  • This is certainly on our roadmap, but not on any public timeline. 

    Evan

    • Proposed as answer by amber zhang Thursday, May 31, 2012 1:41 AM
    Wednesday, May 30, 2012 8:21 PM