CSV Import via SQL Server 2000 DTS - data being truncated 2

[Dabase]

Hi,

I have set up a DTS package that is scheduled to import a CSV file into SQL Server 2000 database on a daily basis. Unfortunately, fields that contain data that are over 255 characters appear to get truncated. Any ideas onwhat could be causing this?

Any help on this will be greatly appreciated.

Thanks
Dabase

 

[jgoodman00]

I cannot replicate this using SQL 2000 SP3.

What is the data-type of the field you are inserting into?

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[Dabase]

James, thanks for your response.

I am also using SQL Server 2000 SP3.

The data-type of the field being inserted into: varchar (8000) NULL.

Thanks
Dabase

 

[jgoodman00]

So if you run the following:

SELECT MAX(LEN(Column)) FROM SomeTable

what value do you get. I tried it on some test data & saw 300+ with a standard package. Does the package create the target table, or is it inserting into an existing table? Can you try inserting the data into a new table? Same result?

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[Dabase]

James,

select max(len(memberof)) from adexport
Result: 748

However:
select * from adexport
where (len(memberof)) > 700

Result: 2 records

1st record: the length of memberof is 241
2nd record: the length of memberof is 246

The package creates the target table.

I appreciate your assistance on this.



Thanks
Dabase

 

[jgoodman00]

Now that doesnt add up!

How are you ascertaining the length of the column in the returned results?

What application are you getting these results from?
EM & QA truncate long strings in the results they return even if the column contains more data...

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[gradley]

jgoodman00 brings up a very good point. If you are viewing this in Query Analyzer, the default max column width is 255. You can change this by performing the following:

1. Select Options from the tools menu in Query Analyzer
2. Select the Results tab.
3. Change the maximum characters per column to a large value like 5000.

Try it again and see if get the entire value

 

[Dabase]

James & Gradley,

Thank you for your help on this one.

Gradley, I followed your advice and changed the maximum characters per column in Query Analyzer to a larger value, and I can now see the entire value.







Thanks
Dabase