Advertisements

Archive

Archive for the ‘SQL’ Category

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

🙂

 

Advertisements

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

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();
}

 

 

 

SSIS – Read C# ‘List’ collection in ‘Execute SQL Task’

In one of our integration requirements, we had to read the ‘List<>’ collection values, which was set in ‘Script Task’ control and pass to ‘Execute SQL Task’ control.

In this article I am going to list out the step by step process to set and get the ‘List<>’.

Assume that you have a collection List<string> which holds ‘Product Names’

var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};

Lets see how to loop through and read the ‘Product names’ and pass to ‘Execute SQL Task’.

Required SSIS components:

  • A SSIS ‘Variable’ (i.e., varProducts) of type ‘Object’ to set the ‘List<>’ object in ‘Script Task’ control

SSIS_1

  • A SSIS ‘Variable’ (i.e., varProductName) of type ‘String’ to set the ‘Product Name’ for each List<> value.

SSIS_2

  • ‘Foreach Loop container’ to parse and read the ‘List<>’

Steps:

  • Choose ‘varProducts’ as ‘ReadWriteVariables’

SSIS_3

  • In ‘ScriptTask’ set ‘listProduct’ object to ‘varProducts’ variable.
    • var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};
    • Dts.Variables[“User:: varProducts”].Value = listProducts ;
  • In ‘Foreach Loop container’,
    • Choose ‘Enumerator’ as ‘Foreach From Variable Enumerator’
    • Enumerator Variable as ‘User:varProducts’

SSIS_4

  • As the ‘List’ contain ‘Product Name’ collection, read the ‘Product Name’ to ‘varProductName’ variable. ‘Index’ 0 denotes, first value in collection (i.e., Product Name’.

SSIS_5

  • As the final step, In ‘Execute SQL Task’, map the ‘varProductName’ variable to a ‘Parameter’.

SSIS_6

  • In above screen, we are setting ‘@prodName’ parameter with ‘Product Name’ and ‘@prodName’ further used in ‘SQL Statement’ of ‘Execute SQL Task.

🙂

Categories: SQL Tags: ,

Transaction count after EXECUTE indicates a mismatching number – SQL Error

December 8, 2014 Leave a comment

I was getting the below exception when I execute a stored procedure from my C# console application.

SQL Transaction Error

SQL Transaction Error

My Stored procedure executes in Transaction mode and wrapped in Try/Catch block (Refer below)

CREATE PROCEDURE [Name]

BEGIN TRY

BEGIN TRANSACTION;

— My SQL Script

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

END CATCH

Reason

  • We get this error if the transaction started and terminated suddenly without committed or rollback.
  • In my Stored Procedure I had a ‘return’ statement after ‘BEGIN TRANSACTION’ , so the stored procedure terminated neither calling COMMIT nor ROLLBACK.

Fix

  • Put “SET XACT_ABORT, NOCOUNT ON” statement which suppresses the error.
  • So the modified Stored Procedure template is as below

CREATE PROCEDURE [Name]

AS

SET XACT_ABORT, NOCOUNT ON 

DECLARE @starttrancount int

BEGIN TRY

SELECT @starttrancount = @@TRANCOUNT

IF @starttrancount = 0

BEGIN TRANSACTION

      — Your SQL Script

IF @starttrancount = 0

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 AND @starttrancount = 0

ROLLBACK TRANSACTION

END CATCH

🙂