I have a dts package that copys information into staging tables. When the table are empty the package runs correctly.
When i try to run it a second time with a select * from table where field+field+field not in (select field+field+field from table)

I get a syntax error converting chartacter to string to smalldatetime data type.

If tables are truncated it runs fine i only want to update information that is not in the last table

If the tables are truncated then you have no data and therefore do not need
to convert anything
You are trying to join attributes together with incompatible datatypes

What is the structure of the table, sample data and your expected result?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

when the package is first run, it inserts the data into the tables correctly. But when i run the package for the second to update any new records with the select statement that is when i get the error message. If i remove the field that hs the datestamp the package excutes fine. But i need to test the timestamp because there could be to values the same. And the time stamp will seperate them

----- Allan Mitchell wrote: -----

If the tables are truncated then you have no data and therefore do not need
to convert anything
You are trying to join attributes together with incompatible datatypes

What is the structure of the table, sample data and your expected result?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

First tim e you run it there is no data so no problems.
Second time you run it the tables have data.

Forget DTS right now and issue the same query in Query Analyser. Does that
allow the statement ?

select * from table where field+field+field not in (select field+field+field
from table)

If you remove the timestamp field and it works that suggests the casting of
the field is not implicit and you may need to

CAST(field as varchar(20)) before concatenting the fields.



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

records with the select statement that is when i get the error message. If i
remove the field that hs the datestamp the package excutes fine. But i need
to test the timestamp because there could be to values the same. And the
time stamp will seperate them