returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”. (SQL Server Import and Export Wizard)

You are here because you probably get strange errors while trying to upload a text or comma delimited file to SQL Server using their import and export utility.

Among the many errors  you are receiving, one of the most discernible ones is:

returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”. (SQL Server Import and Export Wizard)

After going back and editing your mappings by increasing the nvarchar size or even changing the field type to text, you are still receiving this error.

Well below is a depiction of the steps that you should take either during your initial upload of the text file or after the error is received.  Please note that to get to these different screens, use the navigation on the left hand side column.  The options are General, Columns, Advanced and Preview.

First, you’ll arrive at the place were you choose the flat file that you will be uploading to SQL server.  If the column names are in the first row, be sure to check the check box for that.

Choosing Flat File Source in SQL SERVER import wizard

Next we can preview the data and set the delimiter if it is wrong.

Set your column and row delimiters in SQL Server Import Utility

This next one is the important one, the one that will keep you from getting that error.  Click on “Advanced” and you will be taken to a page where you will be setting the column properties.  Here, you will go to the column that is causing problems based on your error result, or you can proactively change the column type before you finish out the import wizard.  In this example, I know that I my column Provider_Name needs to be greater than the default nvarchar(50) value set by SQL server.   So I changed mine to 255.

Advanced Section - Flat File import - SQL Server

Lastly, you want to preview your data.  Although this step is optional, it is still good to view it in case any characters within your data acted as a delimiter wrongfully, thus throwing off your rows and columns.

Preview your Flat File data as it will go into SQL Server

Once you have completed that, finish out the wizard like you normally would.   Be sure that you drop the table that was created in anticipation of the previous export attempt.  Otherwise you will be facing another error.

Good luck.

Where is DTS in SQL Server Management Studio

If you are not familiar with the DTS Wizard utility in SQL Server Management Studio, it is a utility that is used to import and export data to and from SQL Server databases.  You can also import and export data from and to other sources such as flat files, Excel workbooks and Access databases with the DTS import and export wizard found in SQL Server Management Studio.

Note – In later software versions, you might find that DTS may have changed its name to SSIS.

To take advantage of the DTS wizard, open up your SQL Server Management Studio application and follow the steps below.

1.)    Go to Tools
2.)    Click External Tools
3.)    Click Add
4.)    Under title, give it a name like DTS Import and Export
5.)    Click the browse button right below the ‘Title’ text box (the square button with the ellipse).
6.)    Navigate to:  C:\Program Files\Microsoft SQL Server\90\DTS\Binn
7.)    Select DTSWizard.exe and click open
8.)    Then click Apply followed by Okay

Now if you go to your tools menu, you will find have the option to open the DTS Import Export Wizard on its own.

Multiple line SQL string in VB.NET

It has been a while since I’ve had to write a rather long line in VB.NET and I forgot how to continue it on the following line so that it would not continue of the screen. My search on the web was fruitless (surprisingly), maybe I am a bad searcher, so I am writing this post.

Below is an example that will work on any long line of code that you wish to carry over to the next line.

I open Visual Studio 2005. This is an asp.net page with code included in the file.