locked
cross database query RRS feed

  • Question

  • User241115960 posted

    Is it possible to implement cross database query in azure database. RIght now I am doing that through c# datatable as cross database tables join not working in sql server.

    Thanks

    Friday, January 10, 2014 12:50 AM

All replies

  • User-1509636757 posted

    You may require to check for Linked Servers (Database Engine) Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

    You may find this article useful on same:

    hope it helps./.

    Friday, January 10, 2014 12:58 AM
  • User241115960 posted


    SELECT top 10 * FROM [lpwekwwwaaaaa.database.windows.net].[trade_ess_staging].[dbo].[tblRights]

    Error is:

    Reference to database and/or server name in 'lpwekwwwaaaaa.database.windows.net.trade_ess_staging.dbo.tblRights' is not supported in this version of SQL Server.

     my version is 

    Microsoft SQL Azure (RTM) - 11.0.9206.34
    Dec 10 2013 02:54:32 Copyright (c) Microsoft Corporation

    Thanks.

    Friday, January 10, 2014 3:52 AM
  • User1470285101 posted

    SELECT top 10 * FROM [lpwekwwwaaaaa.database.windows.net].[trade_ess_staging].[dbo].[tblRights]

    Error is:

    Reference to database and/or server name in 'lpwekwwwaaaaa.database.windows.net.trade_ess_staging.dbo.tblRights' is not supported in this version of SQL Server.

     my version is 

    Microsoft SQL Azure (RTM) - 11.0.9206.34
    Dec 10 2013 02:54:32 Copyright (c) Microsoft Corporation

    AFAIK,There is no cross join support between databases in SQL Azure,you can see it in the feedback section of Azure here. May be you can Linq in C# to achieve it.

    Refer these links,may helps you

    http://www.brentozar.com/archive/2010/01/sql-azure-frequently-asked-questions/

    http://geekswithblogs.net/SeanBarlow/archive/2011/11/18/you-cant-do-cross-joins-in-sql-azure-but-there.aspx

    http://stackoverflow.com/questions/11284998/cant-query-between-databases-in-sql-azure

    Hope this helps!!!

    Friday, January 17, 2014 12:30 AM
  • User-729837442 posted

    Old thread but this might help people on the future!

    It is possible in the following way:

    create master key encryption by password = '<password>'
    
    create database scoped crendetial yourcredentials with identity = '<username>', secret = '<password>'
    
    -- CREATE DATA SOURCE --
    create external data source RemoteDataSource with
    (
        type = RDBMS,
        location = '<servername>.database.windows.net',
        database_name = '<databasename>',
        credential = yourcredentials
    )
    
    -- CREATE TABLES --
    create external table [RemoteDepartment]
    (
        [ID] [int],
        [Name] [nvarchar](50)
    )
    with
    (
        data_source = RemoteDataSource,
        schema_name = 'dbo',
        object_name = 'Department'
    );

    You can then do a simple select:

    select e.Name, e.Role, d.Name from Employee as e inner join
        RemoteDepartment as d on d.ID = e.DepartmentId

    Notice the Employee table. This exists in a secondary DB (the one currently being queried from) and the RemoteDepartment comes from another DB, both in Azure. The arhitecture can be discussed but this works fine as an example.

    Wednesday, June 1, 2016 9:02 AM