SQLBulkCopy : Received an invalid column length from the bcp client for colid 10

SQLBulkCopy : Received an invalid column length from the bcp client for colid 10.

I recently encountered this exception while migrating data from old system to new system. I am using SQLBulkCopy ddl to bulk copy of data and here is C# Code

C#:

void BulkInsert<TEntity> (IEnumerable items, string schemaName, bool keepIdentity = true) where TEntity : class

        {

            var context = new DBEntities() { AuditChanges = false };

            context.Database.Connection.Open();

            var conn = context.Database.Connection;

            var options = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints;

            if (!keepIdentity)

            {

                options = SqlBulkCopyOptions.CheckConstraints;

            }

            using (var scope = conn.BeginTransaction(IsolationLevel.ReadCommitted))

            {

                var copy = new SqlBulkCopy(conn as SqlConnection, options, scope as SqlTransaction)

                {

                 

                    BatchSize = 20000,               

                    DestinationTableName = schemaName + $".{typeof(TEntity).Name}",

                };

                var dbReader = items.AsDataReader();              

                copy.WriteToServer(dbReader);

                scope.Commit();

            }

         }

I looked into data and destination table schema and found that there are few record in source table, which length is exceeded to destination table's column.

So to resolve this issue, I simple increased the length of destination table column from varchar (10) to varchar (20).

SQL Script –  Increase size of varchar column 10 to 20

         

 ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] Varchar(20)

Thanks for visiting!!

SQL database Project : A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016

“A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016.”

I recently encountered the above error message while publishing or generating SQL script from database project.

After getting this error, I investigated and found that database project’s Target Platform was SQL Server 2017 and Target SQL Database is SQL Server 2016 version and followed to below steps to resolve this incompatibility issue.

Step 1: Click on Advanced Button

Step 2: Advance Publish Settings à General Tab à Check “Allow incompatible platform” option under Advanced Deployment Options

Step 3: Click on Generate Script Button and your SQL script is successfully generated

Thanks for visiting !!

System.Data.SqlClient.SqlException : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

In this blog, we will discuss how to resolve the SQL Exception - Execution Timeout Expired

Recently I was dealing with huge data approximate 160M records and performing data analyzing task and on some point, I encountered SQL Exception.

System.Data.SqlClient.SqlException

  HResult=0x80131904

  Message=Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

  Source=.Net SqlClient Data Provider

  StackTrace:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Usually default command timeout is 30 seconds and if you don’t receive anything from database within 30 seconds, it will throw System.Data.SqlClient.SqlException : Execution Timeout Expired.

To resolve this problem, we need to increase the connection timeout for SQL command, if you feels SQL script /proc is already well tuned and tables are proper indexed.

Eg. :

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 60000;

You can simply set waiting time for SQLCommand

There are two timeout options available for application to access SQL Server Database :

• Connection Timeout
• Command Timeout

Connection Timeout: it is waiting time for application to establish connection with Database and if application is not able to establish connection with database, it throws SQL Exception

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

You can increase the waiting time for SQL connection timeout and you have to add connection timeout attribute in SQL connection string

string connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";

Command Timeout – it is waiting time for command to execute script, if within command timeout it does not complete execution, it throws SQL Exception

System.Data.SqlClient.SqlException : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

And by default, command timeout value is 30 seconds and you can increase the waiting time for command timeout.

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 6000; (values in millisecond)

Thanks for visiting!!