Received an invalid column length from the bcp client for colid Error while Bulk Upload in aspx c#

 I have created sample code to know the route cause of this issue.

So we can write code like this.

Sample code:-

#region bulkCopy

if (MyConnection.State == ConnectionState.Closed) MyConnection.Open();

DataTable dataTableName = GetDataTabletFromCSVFile(filenamestr1);

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyConnection))

{

bulkCopy.DestinationTableName = "Destination_TBL_My_Name_Temp";

try

{

bulkCopy.WriteToServer(dataTableName);

}

catch (Exception ex)

{

WriteErrorLog_Debug("My_FileExtractService " + ex.Message.ToString() + " " + ex.StackTrace.ToString() + " / " + RefNo, "testTimer_Elapsed1");

string message = "";

if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))

{

string pattern = @"\d+";

Match match = Regex.Match(ex.Message.ToString(), pattern);

var index = Convert.ToInt32(match.Value) - 1;

FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);

var sortedColumns = fi.GetValue(bulkCopy);

var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);

var metadata = itemdata.GetValue(items[index]);

var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);

var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);

message = (String.Format("Column: {0} contains data with a length greater than: {1}", column, length));

}

string aa =  message;

WriteErrorLog_Debug("My_FileExtractService Detailed Exception " + message.ToString() + " " + ex.StackTrace.ToString() + " / " + RefNo, "testTimer_Elapsed1");

}

}

#endregion

Get Web Request Error 500,400 Exception details in c#

WebException is a good approch to get the Real cause of 500 error,  

catch (WebException ex)

{
    using (var stream = ex.Response.GetResponseStream())
    using (var reader = new StreamReader(stream))
    {
        Console.WriteLine(reader.ReadToEnd());
    }
}
catch (Exception ex)
{
    // Something more serious happened
    // like for example you don't have network access
    // we cannot talk about a server exception here as
    // the server probably was never reached
}

Some Key to Help for Sql Optimization

 Below having some command used to get/analysis the Sql Query for optimization.

Add Below list of command at top pf the Query with ON and at the end again reset with OFF

Ex: Set NoCount ON

--Query

Set NoCount OFF

----Set Of Commands--------

Set NoExec OFF

Set ParseOnly OFF

Set Concat_Null_Yields_NULL OFF

Set Arithabort OFF

--Set Showplan_Text OFF

Set Statistics Time OFF

Set Statistics IO OFF

Set NOCount OFF

-----Mainly Used below three command only-----

SET NOCOUNT ON

SET STATISTICS TIME ON

set statistics io on

Configure React/NodeJS/ExpressJS App in IIS, Configure React in Windows,

 Step 1: Create your Node (Express) backend

Install node

Download exe from Node site and installed.

Restart the Visual Code application

Drag the Folder in application(Target Folder)

open Terminal

Type npm init -y       (This commond initilise the project and add 1 packge.json file into application)

Type npm i express      (This command to install express js)

create a folder name with server in same directory

create a file name with /server/index.js

copy the given file on /server/index.js file

After Installation of express js to run the application add the script start with associated file

Type npm start (This command will run the application)

Step 2: Create an API Endpoint

Write you code in /server/index.js

Type npm start (This command will run the application)

Step 3: Create your React frontend

Create Client Project

Type npx create-react-app client (Client means name of the project)

for testing purpose add proxy setting on client /client/package.json

go to inside client folder to run the application

Type npm start (This command will run the application)

Step 4: Make HTTP Requests from React to Node

Write Client code request in /client/src/App.js

Type npm start (This command will run the application)

Type npm run build (This command build the application this folder can be confiugure in IIS)

Step 5: Deploy your app on local inside

1. Here for deployement Client and Server i create two application on IIS

2. Client Application (React) create an App Pool with V4.0..

3. No Need to Type write npm start in terminal window, this will work after iis configuration

4. Type npm run build command to buuild the application

5. After build the application Point this location on IIS for Client Application For me i used port (localhost:8080)

6. Client Application ready and you can access without any issue

-----------------------------------------------------------------

7. Server Application (Express Js) create an App pool with Not Managed Code

8. Configure Express js is very TDS task, before configure the application need to check.

9. Weather URL Rewrite(URL Rewrite extension) is installed on not on your server, because Express Js required Reverse Proxy.

10. Reverse Proxy will available on Url Rewrite after Installing ARR (Application Request Routing extension)

11. Before Configure NodeJS/ExpressJs on IIS, create a web.config file on Root directory and copied below text init.

    <configuration>

        <system.webServer>

            <rewrite>

                <rules>

                    <rule name="ReverseProxyInboundRule1" stopProcessing="true">

                        <match url="(.*)" />

                        <action type="Rewrite" url="http://localhost:5057/{R:1}" />

                    </rule>

                </rules>

            </rewrite>

        </system.webServer>

    </configuration>

12. After copied config changes add Reverse proxy rule and set the IP with port number for Forwarding request as per my request i.e. localhost:5057

13. Now confiure the Server on IIS and map to Root directory. In my case i used localhost:8081

14. Now i can access both application like

    Client : http://localhost:8080

    Server : htto://localhost:8081  (Due to Reverse Proxy every Request forward to localhost:5057)

Above Sample Project create with help of below site Hosting did by my own

https://freecodecamp.org/news/how-to-create-a-react-app-with-a-node-backend-the-complete-guide/

Logical SQL query processing order of query clauses

1 FROM (Joins if available)
2 WHERE 
3 GROUP BY 
4 HAVING 
5 SELECT
    5.1 SELECT list
    5.2 DISTINCT
6 ORDER BY  

7 TOP / OFFSET-FETCH