Error converting data type varchar to numeric

I have faced this error "error while converting data type varchar to numeric" several times when converting string data to numeric. Every time I found a unique solution .

This usually happens in a scenario when we do not have much control over the data which is being pulled from different source, which again could be a private internal data or data from external public sites.

Recently in one of my projects while pulling data from external url I faced this issue to convert string data to numeric.

Following are few steps I follow to troubleshoot this error :-)

• Make sure the column used for conversion doesn't have any visible special characters. If any then try to replace them. 
• Make sure to use the replace for non visible characters like TAB, Line Feed, Carriage Return. In T-SQL these are can be identified using CHAR function
• TAB => CHAR(9)
• Line Feed => CHAR(10)
• Carriage Return => CHAR(13) (Resource: MSDN )
• So use the replace function to replace the above non visible characters with '' (space).
• There are few scenario's where after doing all these I still get the error. To solve this I recently used the ASCII() function. Use this function to know if there are any unwanted non visible data in the column. Probably update the column to '0.0' where ASCII(ColumnName) IS NULL.

Let me know if you are aware of any other methods to handle this error.

SSIS error: [Flat File Destination [187]] Error: Data conversion failed. The data conversion for column "col1" returned status value 4 and status text.

If you have worked on SSIS tool, at some point while working in Dataflow task we will face this issue.
Error statement : "[Flat File Destination [187]] Error: Data conversion failed. The data conversion for column "col2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

This usually happens when we are loading data from database source to flat file destination or if loading from flat file source to database destination.
I assume it occurs more often when it is database source to flat file destination.

Here are the few steps to troubleshoot this issue. I usually follow the same order.

• Run the query in SSMS and see if you are seeing some dirty data. Usually this happens when we pull data from different platform like Mac or Linux. Example the file path.
• Right click on flat file destination, click on Show advance editor and go to Input and output column tab. On external columns see if the data type and length of the col2 (here in example) is as per request. Should be wide enough . Match the same with input columns.

Once you are sure the data types are matching, go to next step.

• Double click on the destination file connection in connection manager and make sure if you have checked unicode option or not. If you are using nvarchar or DT_WSTR as datatpe then I would suggest to check this option.

I have used above steps most of the times and it has worked for me.

Let me know if you are aware of any other trouble shooting methods in SSIS for the above mentioned error.

Generate insert scripts with data

There have been instances where we need to create insert statements for a table and share it across different dev /test environments.
We can do this pretty easily using SSMS. (Below screen shots are from SQL 2012)

First, right click on database from where you want to generate insert scripts with data -> Tasks -> Generate Scripts..

Next, Generate and Publish scripts wizard opens,

Click next on introduction page,

On Choose object page, select the required database object you want to script and then click Next.

On Set scripting options page, click on Advanced button as show below

On General listings, select drop down for "Types of data to script" and select "Schema and data" and then click OK.

then select the option whether to save it as file, or open in clipboard or to save it in new query window.
This way you can share insert scripts with data to your dev/test team.

Hope this helps !

Restoring DB from network path /remote server using SQL Server

If you are reading this, then you might have already restored database in local server instance. There are scenario's where we need to restore database from a network path. So the question is how to do this? let's find it out .

Scenario:
Consider we have 2 servers "ServerA" and "ServerB". We have to restore database from ServerA to ServerB without copying the .bak file over the network (probably copying is waste of time).

Solution:
Follow the below steps,

1. Create a folder in ServerA (let's name it as Backups). Share the folder and grant "Everyone" full control permission on the folder. So now the network path of this folder will be "\\ServerA\Backups".
2. Take the backup of database you want to restore, into the folder created above (i.e. Backups).
3. Log into ServerB -> Open MyComputer -> click on Map network drive as show below
4. Once clicked you will find the below screen
5. Select drive name of your choice and mention the network path. In our case Drive = S: and network path ="\\ServerA\Backups", click finish, Once this is done you should be able to see this drive in My Computer explorer.
6. Now that we have mapped the network drive in explorer, we also need to register the same in SSMS.
7. Open SSMS in ServerB(preferably as administrator), connect to db local instance and run the below scripts. (Make changes based on your scenario for drive and network path).
8. Once this is done, right click on databases-> restore database->Source as Device->  Browse -> Backup media type as File -> Add-> Now you should be able to see the network drive. Select the database you want to restore. 

Hope this helps :-)

Start

Finally!!
I have decided to write down all the things I learn. I am a night person, are you??
Happy learning :-)