Posts Tagged ‘SQL Server’

SQL SERVER | Merge Statement | The insert column cannot contain multi-part identifiers

Other day while executing SQL ‘MERGE’ statement on 2 tables, I ran in to following error.

The insert column list used in the MERGE statement cannot contain multi-part identifiers

Before we understand the reason and fix for this error, lets get the basics of ‘MERGE’ statement.

‘MERGE’ statement:

  • ‘MERGE’ statement can be used, if you have 2 SQL tables and to synchronize both two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
  • ‘MERGE’ runs insert, update, or delete operations on a target table from the results of a join with a source table.

Step by Step using ‘MERGE’:

Lets understand how MERGE works with an example.

  • Create 2 tables, ‘Cust_Source’ and ‘Cust_Trg’. Column ‘CID’ acts as joining column between 2 tables.
‘Source’ and ‘Target’ tables
  • Lets use MERGE and sync ‘Cust_Trg’ using ‘Cust_Source’ table by covering following points:
    • Update the matching records in ‘Cust_Trg’ using the ‘Cust_Source’ rows.
    • Create (i.e., INSERT) the unavailable records in ‘Cust_Trg’ from ‘Cust_Source’.
    • Delete the excessive records from ‘Cust_Trg’ by comparing with ‘Cust_Source’ rows.
‘MERGE’ query

MERGE [dbo].[Cust_Trg] AS Trg
USING [dbo].[Cust_Source] AS Src
WHEN MATCHED — Update the Target record fields from Source values.
THEN UPDATE SET Trg.Salary=Src.Salary,Trg.WorkExp=Src.WorkExp
WHEN NOT MATCHED BY TARGET — INSERT the record in Target as its not part of Target yet.
THEN INSERT (CID, [Name], Salary, WorkExp)
VALUES (CID, [Name], Salary, WorkExp)
WHEN NOT MATCHED BY SOURCE — Delete the record from Target as its not part of Source.

DELETED.Name AS TargetName,
DELETED.Salary AS TargetSalary,
DELETED.WorkExp AS TargetWorkExp,
INSERTED.Name AS SourceName,
INSERTED.Salary AS SourceSalary,
INSERTED.WorkExp AS SourceWorkExp;

SELECT @@ROWCOUNT ‘No ofAffected rows’

  • Execute the query and you would get results as below.

Issue encountered with MERGE:

  • When I ran my initial MERGE query, ran in to following exception.
Don’t use Alias in ‘INSERT’
  • Issue was due to the usage of ‘Alias’ in INSERT statement (i.e., Trg.Salary, etc.)
  • Remove ‘Alias’ and rerun the query should solve the issue.
  • MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn’t exist, or updating a row if it matches.
  • When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements.


Categories: SQL Tags: , ,

[Step by Step] Canvas App using on-premise SQL server as Data Source

June 15, 2019 5 comments

In this post, lets see how to build a Canvas App with data from on-premise SQL Server.

Using Gateways ,data can be transferred quickly and securely between a canvas app and an on-premises data source, such as an on-premises SQL Server database or an on-premises SharePoint site.

Lets get started to configure a Gateway and read the on-premises SQL Data which acts as a Canvas App’s ‘Data Source’.


  • SQL Server with a Database and a simple table.


  • Dynamics 365 subscription. Subscribe to 30 days trail here.

Configure a ‘Gateway’ to on-premise SQL Server:

We need to install ‘On-Premises Data Gateway’ service on the machine with ‘SQL Server’.

  • Connect to PowerApps maker portal using your Dynamics 365 credentials.
  • Go to Data -> Gateways and click on ‘+New gateway’ button.


  • You will be redirected to downloads page and click on ‘Download’ under ‘On-Premises Data Gateway’ section.


  • Download and click on the ‘GatewayInstall’ application.


  • Provide your Dynamics 365 email (i.e., User ID) and click ‘Sign in’ to connect to Power platform.


  • Provide your desired gateway name,recovery key and click ‘Configure’


  • You will end up with below success screen.


  • We are done with Gateway configuration.
  • If you go to the ‘Services’ on your machine, you should see ‘On-premises data gateway service’ up and running.


  • Go back to PowerApps portal and refresh the ‘Gateways’ and you should see the configure gateway.


Create a Canvas App:

As we completed ‘Gateway’ configuration, the next step to create a Canvas app and connect to on-premises SQL server using the ‘Gateway’.

  • Click on ‘+Create’ and choose ‘Canvas app from blank’


  • Provide the ‘App name’ and click on ‘Create’


  • Now we need to establish the connectivity between Canvas App and SQL Server.
  • Click on ‘Connect to data’ and select ‘SQL Server’ from the list.


  • Choose ‘Connect using on-premises data gateway’ option and provide the SQL server, Gateway details and click ‘Create’.


  • Select the table (i.e.,Contact) and click ‘Connect’ which creates a new ‘Data source’.


  • Now add a Gallery component and map the ‘Data source’.


Additional Details:

  • To install the ‘Gateway’, machine should have minimum configuration mentioned here.
  • Gateway installs as ‘On-premises data gateway service’ on the server. Make sure this service is running all the time.
  • Along with service, a desktop application by name ‘On-premises data gateway’ gets installed. You can enable logging and manage settings using this application.



SQL Server- Troubleshoot ‘String or binary data would be truncated’ error

October 20, 2018 Leave a comment

In one of my Data Migration requirement, I had to join data from multiple tables and  move to a single table. Requirement is complex as data is around 20 GB and table has around 300 columns.

So, I built a Stored Procedure to join tables and move data to single table, the query ran for an hour and finally failed with below error:

String or binary data would be truncated.


  • Length of the data I was trying to insert to is more than the defined column size in target table.


  • Identifying the problematic column was difficult as my source tables had 300 columns.
  • To trouble the issue, I ran a query on my source tables to get max data length of all columns, which helped me to identify the mismatched column.
  • Below query helped me to identify the maximum data length of source table columns

DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;

SELECT @SQLStatement = @SQLStatement + ‘SELECT ‘ + QUOTENAME(, ””) + ‘ AS ”Column Name”, ‘ + QUOTENAME(, ””) + ‘ AS ”Data Type”, ‘ +
QUOTENAME(cols.max_length, ””) + ‘ AS ”Defined Length”, MAX(DATALENGTH(‘ + QUOTENAME( + ‘)) AS ”Max Data Length” FROM ‘+@TableName+ char(10) +’ UNION ‘
types.system_type_id = cols.system_type_id and != ‘sysname’
cols.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement)-6)
— Execute SQL statement

  • To simplify the understanding I created a ‘Product’ table with 4 columns and added few rows

Product Table_1

  • Execute the query and it lists out ‘Column Name’,’Data Type’, ‘Defined Column Length’ and ‘Max Data Length’.
    • In the below result, my ‘Description’ column’s max data length is 142

Product Table_2



[Step by Step] Restore a Database from Azure Blob to Azure SQL Server

In one of the requirements, we had to move a Database uploaded to ‘Azure Blob Storage’ to Azure SQL Server.

If you got a question, why we need to move SQL Database file from Azure Blob to Azure SQL Server, like me, below is a sample scenario

  • Customer IT team, uploads their Database file to Azure Blob storage every week using AZcopy
  • To consume the Data, we either have to restore the Database file to Azure SQL Server or to your local SQL server.

Below are the steps to restore Database file from Azure Blob to Azure SQL Server.


  • Azure Subscription
  • Create a ‘Storage Account’ with ‘Blob’. Refer my previous article for steps to create
  • Database file uploaded in Azure Blob Storage


  • SQL Server Management Studio (SSMS), as I am going to use this tool in next steps.

Steps to restore Database from Blob to Azure SQL Server:

  • Connect to ‘Azure SQL Server’ using SQL Server Management Studio (SSMS)
  • Right click on ‘Databases’ and choose ‘Import Data-tier Application…’


  • In ‘Import Settings’ tab of the ‘Import Data-tier Application’ window
    • Select ‘Import from Windows Azure’
    • Click ‘Connect…’
    • Provide ‘Azure Storage account’ name
    • Account Key
    • Click ‘Connect’
    • In the next window, pick the Azure Database back up file upload in the Blob and click ‘OK’


  • In ‘Database Settings’ tab, provide the ‘New database name’ and click ‘Next’


  • That’s it, now the Restore process should start with ‘Progress’ window.
  • Give it some time and once the process completed, you will see ‘Success’ Status



[Step by Step] Connecting to Azure SQL Server using OLEDB Connection from SSIS

August 23, 2018 3 comments

Connecting to Azure SQL Server from SSIS using ‘OLEDB Connection manager’  is not a straight forward way, if you are to connect using your ‘Azure Account’ credentials.

The only way to connect using ‘Azure Account’ is by using ‘ADO.NET connection’ manager.

Azure SQL_6

But then, how to connect to Azure SQL using OLEDB? Answer is by using ‘SQL User’. And yes, we need to create ‘SQL User’ on the Azure DB which you are connecting to and use the same in SSIS OLEDB.

Below are the steps to create ‘SQL User’ and use that to connect to ‘Azure SQL Server’ from SSIS OLEDB.

Create a ‘SQL Login’ on Azure SQL Server:

  • Connect to Azure SQL Server using SQL Server Management Studio (SSMS) using the Azure Account. ‘Azure Account’ must be Administrator to ‘Azure SQL Server’

Azure SQL_1

  • Select ‘Master’ Database and open a ‘New Query’ window.
    • Note that, ‘SQL Login’ creation query must only run on ‘Master’ database
  • Create a ‘Login Account’ using below query. In my query I am creating a Login Account by name ‘MyLogin

Azure SQL_2

Create a ‘SQL User’ on required Databases:

  • Post creation of ‘SQL Login’, now we have to create ‘SQL User’ account against all the Databases, which you want access from SSIS OLEDB.
  • From the SSMS -> Object Explorer, select the Database and open a ‘New Query’ window.
  • Create a ‘SQL User’ using below query. In my query I am creating a SQL User by name ‘MyUser’

Azure SQL_3

  • Add ‘db_datareader’ and ‘db_datawriter’ roles to the ‘MyLogin’ Login Account using below queries.

Azure SQL_4

  • Note: You can combine and run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries together.
  • Also, run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries against ‘Master’ Database as well.
  • Refer this article for list of ‘Roles’

Connect to Azure SQL from SSIS OLEDB:

As we have ‘SQL User’ created and granted access against the required databases, to connect to Azure SQL from OLEDB.

  • Open the ‘OLEDB Connection Manager’
  • Set ‘Server name’ to ‘Azure SQL Server’ name
  • Set ‘Authentication’ to ‘SQL Server Authentication’
  • Set ‘User name’ and ‘Password’ to ‘SQL User’ credentials created in above section

Azure SQL_5


Categories: CRM, SQL Tags: , , ,