cross database query

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

Answer 1

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:

• Create Linked Servers (SQL Server Database Engine)
• Linked Servers to SQL Azure

hope it helps./.

Answer 2

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.

Answer 3

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!!!

Answer 4

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.