Solved

Trouble Pulling from a FoxPro ODBC Connection to SQL

Posted on 2014-01-16
6
505 Views
Last Modified: 2014-03-29
I'm trying to pull data from a Foxpro database into SQL using a ODBC connection with the foxpro driver.  It is getting the error:

'ERROR CONVERTING DATA TYPE DBTYPE_DBTIMESTAMP to datetime'

My query is:

SELECT 
*
FROM         OPENQUERY([master_builder], 'select duedte from acpinv') AS ACP_INV

Open in new window


How can I in my select query either cast the data so it comes through or not pull bad dates?

Here's a screenshot if it helps:  http://i.imgur.com/V3wKEK2.png
0
Comment
Question by:lm1189
6 Comments
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39786171
try
SELECT TO_CHAR(datafield)
0
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39786177
or
CAST([SomeDateField] AS varchar(26))
0
 

Author Comment

by:lm1189
ID: 39786296
I tried cast, cast didn't work.  Do I put this in the OPENQUERY SELECT or the normal select?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 41

Expert Comment

by:pcelba
ID: 39786470
It is always better to push (upload) data from FoxPro to SQL Server but that's not this case...

And yes, DateTime is causing problems sometimes...

Try to convert the DateTime to string at FoxPro side as the first attempt:

SELECT * FROM OPENQUERY([master_builder], 'select TTOC(duedte) from acpinv') AS ACP_INV

Then you may investigate what causes problems. It could be empty or null datetime field in FoxPro table.

Could you test just a small FoxPro table containing one DateTime field which is not empty?

And one question: You are using VFP OLE DB provider to pull data from FoxPro probably. ODBC driver is not so easy to use. Am I correct?
0
 
LVL 12

Accepted Solution

by:
jrbbldr earned 500 total points
ID: 39788553
The over-riding question is - Is this a one-time operation or something that needs to be done on an on-going basis?

If a one-time operation - There are a number of ways to Export Foxpro data tables into a different format such as CSV, etc. which SQL Server can more readily Import.

If this is needed to be done repeatedly - Then there are certainly field value issues to have to deal with - most noteably FP Date fields --> SQL Server DateTime fields.

With a better understanding of your needs, we can better assist you.

Good Luck
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39792112
Look out for empty dates in the DBF,there is no value for that in SQL Server. Most probably that's leading to the conversion error.

Bye, Olaf.

EDIT:

If you're not knowing Foxpro: empty differs from NULL in vfp. There is an empty date, with the characteristics to be displayed as "  /  /    " or "  .  .    " depending on your locale and being before any other date in comparisons like somedate<another date, which makes it a good value as ultimo date. You can't compare NULL with some other date. So in some cases the empty date is best replaced with the earliest date SQL Server can handle, in other cases with NULL.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now