DTS SQL 2000 Import issue

Static M.e.

(Admin: I hope this is in the right area, if not feel free to move please)

Hi all,

Very new to SQL so this could be a very basic error. I am trying to create a DTS package importing information from a text file, the dates in the text file are stored in the Month day year format (01/30/1998). This is the SQL statement im trying to build
(Minus more entries)

CREATE TABLE C_MASTER
(C_NO nvarchar(20),
C2 float(15),
Reason nvarchar(20),
C_Year smallint(4),
C_Value int(10),
Date_Created smalldatetime(4),
Time_Created nvarchar(10),)

However when I parse query it, I get the following error

Package Error
Description: Deferred package could not be completed
Statements could not be preferred
Column or parameter #4 Cannot specify a column width on date type smalldatetime

Does anything stand out straight away I should change ?

Could this relate to setting defaults in relation to date/ time as in dd/mm/yy or mm/dd/yy

Appreciate any suggestions

kenaman

Nothing jumps out from your post.

Does the data Preview properly in the DTS wizard?

Take a look here its a step by step guide to Importing A Text File Using The DTS Wizard, its a bit long but might contain the detail you need.

madramor

Static M.e. wrote:

CREATE TABLE C_MASTER
(C_NO nvarchar(20),
C2 float(15),
Reason nvarchar(20),
C_Year smallint(4),
C_Value int(10),
Date_Created smalldatetime(4),
Time_Created nvarchar(10),)

the last comma jumps out from your post as a possible error
Time_Created nvarchar(10),)
chnage to
Time_Created nvarchar(10))

edit:
where I read the error message
"Column or parameter #4 Cannot specify a column width on date type smalldatetime"
suggests that
Date_Created smalldatetime(4),
change to
Date_Created smalldatetime(),

Static M.e.

Thanks for the suggestions.

kenaman. Thanks I looked across that Site but again all seems in order. The data previews in the DTS designer wizard.

madramor. I took out the 4 and tried 8, 10 and 0 but the error is always the exact same, when I put in nothing I get an error on the next line.
Also for the last comma I have removed it but it also doesnt seem to fix the problem

Any more suggestions would be more than welcome.

madramor

Static M.e. wrote:

when I put in nothing I get an error on the next line.

basic common sense to post the error you get

try
C_Year smallint,

also read the error message they are usually very direct is causing the problem

Static M.e.

Sorry the message was

Error Description. Deferred prepare could not be completed.
Statements could not be prepared.
Line 5: Incorrect syntax near ')'.

I also tried the putting in "smallint ( )," and also "smallint (4)," but the errors remain the same as per smalldatetime.

Could the error be due to one of the steps after the Execute SQL step ?

Thanks again

Static M.e.

Could the error be in the way I imported the text file ?
I imported the text file with "Fixed Width" Setting and then used Column breaks.

If I keep all datatypes as "char" it all works perfectly. Its just when I try to change them to the relevant data type's (Datetime, Money, Int etc) that I run into all the errors.

Static M.e.

Thanks all for the help.

I got the answer after some more searching / trial + error

CREATE TABLE C_MASTER
(C_NO nvarchar(20),
C2 int,
Reason nvarchar(20),
C_Year int,
C_Value int,
Date_Created datetime,
Time_Created nvarchar(10))

not sure why it works but it does

bonkey

Static M.e. wrote:

not sure why it works but it does

It works because you never should be supplying field-length information for fixed-length datatypes, such as int, smallint, datetime, smalldatetime etc.

More accurately, the original statement was invalid because it supplied field-length information for non-variable-length types...which is invalid in MSSQL.

The only datatypes you can validly supply length information for are variable-length types, such as char, varchar, binary, varbinary and so forth.

jc

Static M.e.

Thanks Bonkey for explaining it, I think I understand now.
(SQL is one big learning curve for me at the moment)

Appreciate it