SQL Server, T-SQL create Linked Server with Azure SQL Database

Today, we got a requirement to connect our Azure VM SQL (Report server) instance to access the Azure Database. Let me show you how create linked server for SQL Database on Azure from Azure VM SQL instance.

Please follow the mentioned steps to create a Linked Server to Azure SQL Databse:

Step 1: Open SQL Server Management Studio

Step 2: Execute script: "SP_addlinkedserver".

EXEC sp_addlinkedserver
@server='AzureDB', -–Provide Linked Server Name
@srvproduct=''    
@provider='sqlncli',
@datasrc='xxxxxx.database.windows.net', –provide Azure SQl Server name
@location='',
@provstr='',
@catalog='AzureDatabaseName' –Provide azure database name

Step 3: Execute the script to provide the SQL Server login for the above created linked server.

Exec sp_addlinkedsrvlogin ‘Azure’, ‘FALSE’, NULL, ‘’, ‘’;

Step 4: Verify the created linked server, under the Databases > Server Objects > Linked Servers > Your Linked Server Name

Step 5: Access the azure SQL database table. You need to use FOUR part query. [linked server name].[database name].[schema name].[table name]"

SELECT top 10 * 
FROM [linked server name].[database name].[schema name].[table name]

Reference : Tejas Shah http://www.SQLYoga.com

SQL Server Reporting Services, List out all the data sources with Report Name and Path

In Enterprise application, we can have many reports hosted. Sometimes there are few customized reports too. Over period of time, there may be many Data Sources used by the different reports. To find out Report and its data source, we can use the following query: (Execute on ReportServer Database)

SELECT C2.NAME AS Data_Source_Name
 , C.NAME AS ReportName
 , C.Path AS ReportPath
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.CATALOG AS C ON DS.ItemID = C.ItemID
 AND DS.Link IN (
  SELECT ItemID
  FROM ReportServer.dbo.CATALOG
  WHERE Type = 5
  ) --Type 5 identifies data sources
FULL JOIN ReportServer.dbo.CATALOG C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
ORDER BY C2.NAME ASC
 , C.NAME ASC;

This will gives following result:

Reference : Tejas Shah http://www.SQLYoga.com