Advertisements

Archive

Posts Tagged ‘SQL Server’

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.

Reason:

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

Fix:

  • 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 @SQLStatement VARCHAR(MAX);
DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;

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

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

  • 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

🙂

 

Advertisements

[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.

Prerequisites:

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

ABlob_1

  • 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…’

ABlob_3

  • 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’

ABlob_4

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

ABlob_5

  • 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

ABlob_2

🙂

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

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: , , ,