SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 problem import dbf into sql server using linkserve
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

turkish2
Starting Member

Israel
2 Posts

Posted - 12/23/2013 :  06:44:35  Show Profile  Reply with Quote
Msg 241, Level 16, State 1, Procedure copyDataToRipCategory, Line 4
Conversion failed when converting datetime from character string.
the type of dbf is: database timestamp [DT_DBTIMESTAMP]
the sql server destination is : datetime
this is my sql :

INSERT INTO [serverName].[database].[dbo].[L_category] select * from openquery(linkservername,'SELECT [cat],[catid],[date_time],[insertdate],[lname] from category')


the problematic field :::
**[date_time]

**[insertdate]

sqlserver
destination field should be(datetime) : 2011-05-17 11:38:16.000
foxpro example field :17/05/2011 11:38:16

oren t

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/23/2013 :  09:46:53  Show Profile  Reply with Quote
May be issue with server settings
see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

try converting it to iso formal first and then inserting
ie like

INSERT INTO [serverName].[database].[dbo].[L_category] 
select [cat],[catid],
CONVERT(varchar(30),[date_time],121),
CONVERT(varchar(30),[insertdate],121),
[lname]
from openquery(linkservername,'SELECT [cat],[catid],[date_time],[insertdate],[lname] from category')



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000