How to create linked server between On-premise and Azure SQL data warehouse

In my previous post, l wrote about how to create linked server between On-premise and Azure SQL database.

In this post, let us see how to create linked server between On-premise and Azure SQL data warehouse and after establishing linked server, how we can query Azure SQL data warehouse tables from local On-premise SQL Server.

I have created Azure SQL data warehouse in my previous post and for this example I have created table "dwtable1" within azure sql dw.

Below are the steps to publish linked server connection between On-premise and azure sql dw.


How to reset azure sql admin password

In this post, I have shared the resource which explains different ways to reset azure sql admin password

1) From Azure portal - >SQL Server Overview page -> Reset Password
2) From Powershell -> using Azure CLI command - az sql server update 
3) From Powershell -> using power shell command - Set-AzureRmSqlServer
4) From SSMS -> using t-sql command - ALTER LOGIN

Reset lost admin account password

See Also:

How to delete files in Azure data lake store

In this post, let us see how to delete files in Azure data lake store using powershell and Azure CLI commands.


I have created Azure data lake store with account name trndls and uploaded some JSON files.

How to copy multiple tables to Azure blob using Azure data factory

In my previous article, I wrote about introduction on ADF v2. In this post, let us see how to copy multiple tables to Azure blob using ADF v2 UI.

For this example, I have created tables named Test, Test1 within Azure SQL database - Source for the copy operation.

I have created Azure blob with Container called myfolder - Sink for the copy operation.

To copy multiple tables to Azure blob in JSON format, created
Pipeline1 - For Each activity with Copy activity
Pipeline2 - Lookup activity and Execute pipeline activity

Lookup activity provides the list of tables, output of Lookup are mapped to Object type parameter. For Each activity on each iteration (tables list in object parameter) provides the table name to source & sink within copy activity.

Below are the steps:

SQL Server Row level security

In SQL Server, implementing Row-Level Security enables to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context). 

In this post, let us see an example for RLS with Session_Context, advantage of this technique is we don't need to create separate users to enforce row level security.

We can implement RLS by creating SECURITY POLICY and predicates created as inline table valued functions. 

Let us consider this use case, an Insurance provider company database has all the agency company, its adviser, policy and commission details. Insurance provider has the application dashboard where any agency / individual adviser from an agency company can login (using single sign on) and view their details.

Requirement for Insurance provider is one agency company data should not be exposed to other company and only admins can modify the commission data within their company.

Temp tables in Azure sql database and Azure sql data warehouse

In this post, let us see about temporary tables in Azure SQL database and Azure SQL data warehouse.

Temp tables in Azure SQL database:


Global (DB scoped) & Local temporary tables are supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses.

Local temporary table (#table_name), 
Database scoped global temporary table (##table_name)

Azure data factory v2

Azure Data Factory visual tools enabled in public preview few days back. In this post, let us see how the look and feel is and whats new in ADF v2.

From Azure portal, while creating Azure data factory we need to select the version as v2, once created click on Author & Monitor.




In my previous post, I had shared an example to copy data from Azure blob to Azure cosmos DB using Copy data wizard.


How to concatenate values with specified delimiter

In SQL Server 2017, new function is introduced to concatenate values with specified delimiter

In SQL Server 2012, Concat() was introduced. Only difference between Concat() and Concat_WS(), Concat_WS() allows delimiter to be specified to concatenate values.

Both CONCAT & CONCAT_WS ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting as shown in below image.

How to combine separate row values into delimited string

In one of my old post, I wrote about how to combine separate row values into delimited string. Now starting from SQL Server 2017, new string function was introduced to perform this string aggregation easily. Let us see few examples in this post.

DECLARE @Tmp TABLE (ID INT,
                    Name VARCHAR(30))
INSERT INTO @Tmp SELECT 1,'SQL Server'
INSERT INTO @Tmp SELECT 2,'Oracle'
INSERT INTO @Tmp SELECT 3,'DB2'
SELECT * FROM @Tmp

SELECT STRING_AGG (Name, ',') FROM @Tmp

How to split delimited string into separate row

In this post, let us see how to split delimited string into separate row. 

In previous versions of SQL Server, this can be achieved using User defined function

When support for querying JSON data was introduced in SQL Server, using OPENJSON seems to be simpler technique to split delimited string into separate row.

Finally starting from SQL Server 2016, new built string function was introduced to do this. Let us see few examples with various delimiters:

How to load data into Azure SQL Data Warehouse using SSIS

In this post, let us see how to load files from local folder into Azure SQL Data Warehouse using Azure SQL DW Upload Task in SSIS.

We need to download and install Azure Feature pack to get the Azure components in SSIS.

Azure SQL DW Upload Task is designed to work in such a way that it consumes the files in local folder and places in Azure Blob storage as staging (there is an option to retain files within Blob storage)  and then it is loaded into Azure SQL data warehouse.


How to upload files into Azure blob storage using SSIS

In this post, let us see how to upload files from local folder into Azure blob storage using SSIS.


In the SSIS Toolbox, we can see Azure component grayed out.



How to create linked server between On-premise and Azure SQL database

In this post, let us see how to create linked server between On-premise and Azure SQL database and after establishing linked server, how we can query Azure SQL database & create objects in Azure SQL database from local On-premise SQL Server.

In previous posts, I wrote about 

How to play video or music from SSRS

In this post, I will show how to play video or music from SSRS using two options.

Option 1: Using SSIS package that invokes the video or audio file that is stored in SQL Server filetable (or normal windows directory folder)

To see an example on SQL server filetable, refer my previous post.