Basics of Foxpro, Conversion failure

Recently I had a situation where my SSIS package was not able to read the data from Foxpro, error message is [Conversion failed because the data value overflowed the specified type.];
I spent long hours investigating finding actual record impacted, following is the take away for me!

• How to read data from Foxpro Database:
1. Add Foxpro Database as Linked Server to SQL Server: May be Linked Server is already added to the Server, please check!

EXEC master.dbo.sp_addlinkedserver @server = N'VFP', @srvproduct=N'VFP', @provider=N'VFPOLEDB.1', @datasrc=N'\\FOXPRODBLOCATION\FOLDERS\Data\Main'

2. Next option is to install Microsoft Visual Foxpro and read the data.
a. Could not find any simple way to install but I was lucky, found it was installed on one of the old Developer Server

Basic commands to get the data from Foxpro Database: (Map the location of Foxpro data files as a Drive on local Server, Z drive in below example)
USE z:\data\main\tablename.dbf shared
select * from tablename where colname = “ABCDABCDXYZ”
select * from tablename where col1 subeventid = “D201409301” and col2 = “ABC-DF2”

• Actual problem causing the error:
One of the analyst entered date as ‘0214-10-29’ instead of ‘2014-10-29’. This was a typo but it created all the problem!
Foxpro accepts it and considers as 29th Oct 14 but when the data is transferred to SQL Server it throws an error as SQL Server accepts minimum date ‘1753-01-01’

• Identifying the impacted record is the next most difficult part! It is all your analytical skills, I am not talking about it now! ALL THE BEST…

– Vijred

This entry was posted in SQL and tagged , , . Bookmark the permalink.

One Response to Basics of Foxpro, Conversion failure

  1. Pingback: DATE format in Foxpro – Microsoft Visual Foxpro | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s