problem import dbf into sql server using linkserve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

problem import dbf into sql server using linkserve

Author

Topic

turkish2
Starting Member

2 Posts

Posted - 2013-12-23 : 06:44:35

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

52326 Posts

Posted - 2013-12-23 : 09:46:53

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources