Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 9th, 2005, 01:04 PM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Datatype mismatch on datetime column???

Hi,
I'm new on this forum, thanks in advance for looking...

I have an Access database table with rows I'm trying to
transform and add into an existing SQLServer table. The
tables both have a 'datetime' column and that is my problem.

In the DTS Import Wizard, when I choose "all rows" in the
query builder phase, I get a good transfer of rows. But when
I choose criteria to get a "WHERE..." statement, using the
Access table's 'CurrentEndDate' column to choose criteria value,
it looks like this:

WHERE [accesstable].'CurrentEndDate'>='05/06/2005'

...and I get the error message stating that I have a datatype
mismatch in criteria expression. If I use other operands such
as '>', '<', etc. I still get the same error.

I have two questions about this:
1. My premise on this is that since the rows transfer when I don't
put in the "WHERE..." statement, the problem is not with the data
but in the syntax that Import Wizard uses to build the "WHERE..."
clause. Is this correct, and if so, what can I do to repair the statement?
2. What I'd REALLY like to do is have a WHERE statement that
pulls the rows added since the last time the package was run,
if anyone has a suggestion on how to do that?

Thx much,

Reid C.

Reid C.
__________________
Reid C.
 
Old May 9th, 2005, 01:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In the WHERE CLAUSE do not put single quotes around the field name 'CurrentEndDate'. Use CurrentEndDate or [CurrentEndDate].

Access may also require # instead of ' as a field delimiter for datetime data.

Code:
WHERE [accesstable].CurrentEndDate >= '05/06/2005'

Code:
WHERE [accesstable].[CurrentEndDate] >= '05/06/2005'

Code:
WHERE [accesstable].[CurrentEndDate] >= #05/06/2005#

Rand
 
Old May 9th, 2005, 01:44 PM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks much for your help...choice #3 worked fine...

WHERE [accesstable].[CurrentEndDate] >= #05/06/2005#

this format was what was needed.

Can you help me with my second issue which is to, instead of having to put in a date, write a WHERE clause that pulls rows created since the last run of the DTS package?

Thx again!

Reid C.
 
Old May 9th, 2005, 02:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That is an idiosyncrasy of Access data. Access uses the # as a datetime field delimiter. I recalled that from my Access database work, before I started using SQL Server.

As for the other issue. I don't have a quick answer since I don't use DTS very often.

Rand





Similar Threads
Thread Thread Starter Forum Replies Last Post
Default Value for DATETIME column happygv MySQL 5 August 7th, 2009 04:14 PM
(0x800A000D) datatype mismatch peonysmiles ASP.NET 1.0 and 1.1 Basics 1 August 26th, 2008 08:13 AM
DataType Mismatch Errorror sirmilt BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 4 August 4th, 2006 09:05 AM
Changing column datatype abelmirma Access VBA 1 January 16th, 2006 07:25 PM
DataType Mismatch webgen ASP.NET 1.0 and 1.1 Professional 3 November 17th, 2005 12:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.