Advertisements

Archive

Archive for the ‘SQL’ Category

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

SQL Server- Drop If Exists (DIE) Objects

September 28, 2018 Leave a comment

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Categories: SQL Tags: ,

SqlError: The backup set holds a backup of a database other than the existing database

I got below error, when I was trying restore a database from Azure Blob Storage to my local SQL server using ‘SQL Server Management Studio (SSMS)’.

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)

Reason:

  • I created a new Database (DB) first and tried to restore the DB from Azure blob to this new DB.

Fix:

Below approach solved the issue

  • Don’t create a new Database before hand.
  • Right click ‘Databases’ and select ‘Restore Database’
  • Pick your ‘Source’ Database
  • In the ‘Destination’ section, provide a new Database name as highlighted below

DB_Restore1

  • Click ‘OK’ to complete the Restore process.

If you want to create a Blank Database first and restore.

  • Set below settings in ‘Options’ tab of ‘Restore Database’ window

DB_Restore2

  • Pick existing Database
  • Click ‘OK’ to complete the Restore process.

🙂

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

SQL Server Installation – VS Shell installation has failed with exit code 1638

I was getting below error during the SQL server 2017 installation.

VS Shell installation has failed with exit code 1638

Reason:

  • Exit code 1638 denotes “Another version of this program is already installed”.
  • In my case, I already had Visual Studio 2017 installed on my machine and there was conflict due to “Microsoft Visual C++ 2017 Redistributable” components which got installed with Visual Studio 2017.

Fix:

This issue was annoying and had to spend couple hours to find the actual reason and fix. Below fix worked in my case.

  • Uninstall Visual Studio 2017
  • Go to Control Panel and uninstall below ‘Microsoft Visual C++ 2017 Redistributable‘ components

VC Redist

  • Restart the machine
  • Try installing SQL Server
  • Proceed with Visual Studio 2017 installation

🙂

 

C# – SQL – Bulk Insert records from Data Table and CSV

March 31, 2018 1 comment

In my previous article I provided the steps to update records in single transaction using SQL “Table-Valued Parameter Types”.

In this article, lets see an easy way to bulk insert the records using SQLBulkCopy

What is SQLBulkCopy:

  • SQLBulkCopy class comes with ‘System.Data’ namespace and can be used to write/Insert data to SQL Server tables.
  • Though there are other ways to load data into a SQL Server table (i.e., INSERT statements, for example) but ‘SQLBulkCopy’ offers a significant performance advantage.

Below are the steps to use SQLBulkCopy with the combination of ‘Data Table’

Steps:

To simplify the explanation, I am taking a ‘Student’ table with 3 columns.

  • Create a ‘Student’ table.

Data Table 1

  • In C# console application, prepare a ‘Data Table’ with the structure similar to ‘Student’ SQL table.

tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));

// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;

//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);

  • Instantiate and execute the ‘SQLBulkCopy’ by passing the Data Table

using (SqlBulkCopy bulkCopy = new SqlBulkCopy({SQLConnection}, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkCopy.DestinationTableName = “Student“; // Your SQL Table name

//Optional mappings. Not required, if your ‘Data Table’ column names match with ‘SQL Table’ column names
bulkCopy.ColumnMappings.Add(“StudentID”, “StudentID”);
bulkCopy.ColumnMappings.Add(“StudentName”, “StudentName”);
bulkCopy.ColumnMappings.Add(“City”, “City”);
bulkCopy.WriteToServer(tableStudents);
isSuccuss = true;
}

  • Execute the code which inserts rows from ‘Data Table’ to ‘Student’ SQL table.

Now lets see how to insert the CSV data to SQL table with query.

Note: You can also do the same with SQL Import Wizard

Bulk insert records from CSV to SQL Table:

  • Assume that you have data available in a CSV file and want to insert them as records in SQL Table.

Insert 1

  • Below is the query

Bulk Insert {SQL Table Name}

from ‘CSV File Path’

with
(rowterminator=’\n’,fieldterminator=’,’)

Insert 2

Categories: SQL Tags: , ,

C# – Bulk Update Records – Pass Data Table to SQL Stored Procedure

March 30, 2018 3 comments

I was building a console application to transact with large set of data in a SQL table and got a requirement to bulk update records.

Below are the options I got.

Option 1:

  • Loop through the records in Console and make SQL update call for each record.
  • This is a tedious and non-scalable for huge volumes data, as we need to make one SQL server transaction for each record from client (i.e, Console).

Option 2:

  • Populate the records to update as rows in a ‘Data Table’ and pass it to a Stored Procedure, and have the Update statement in Stored Procedure.
  • This is optimal as the heavy  SQL updates are off loaded from Console to SQL server.

Lets see how to implement options 2.

To make the Stored Procedure accept Data Table, we need to define “Table-Valued Parameter Types” in SQL first.

What is a ‘Table-Valued Parameter Type”:

  • Table-valued parameters are based on strongly-typed table structures that are defined by using SQL CREATE TYPE statements.
  • You need create a “Table-Valued Type” (i.e.,StudentTableType from below scenario) with the structure similar to your Table (i.e., Student table from below scenario).

Steps to implement Option 2:

To simplify the explanation, I am taking a ‘Student’ table with 3 columns.

  • Create ‘Student’ Table

Data Table 1

  • Create a ‘User-Defined Table Type’ (i.e., ‘StudentTableType‘) with ‘Student’ columns.
    • Note: You don’t need to include all columns. Add columns only you need to update.

Data Table 2

  • Create a Stored Procedure with StudentTableType as Parameter

Data Table 3

  • In the Console, populate a ‘Data Table’ with ‘Student’ records as ‘Data Row’

tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));

// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;

//Add the Data Row to Table

tableStudents.Rows.Add(rowStudent);

  • Execute the Stored Procedure from Console by Passing ‘DataTable’ (i.e.,tableStudents) as Parameter

using (SqlConnection connection = ConnectionManager.SQLConnection)
{
SqlCommand cmdUpdateStudents = new SqlCommand(“BulkUpdateStudents”, connection)
{
CommandType = CommandType.StoredProcedure
};

SqlParameter tvpParam = cmdUpdateStudents.Parameters.AddWithValue(“@tvpStudents”, tableStudents);
tvpParam.SqlDbType = SqlDbType.Structured;

cmdUpdateStudents.ExecuteNonQuery();
}