468,242 Members | 1,710 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Problem with export of a table to a text file

Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel

Aug 24 '06 #1
3 3654
Posting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Da************@sig.bizwrote:
>Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel
Aug 24 '06 #2
Hi Roy,

thanks for your answer.

Here's the table definition :
CREATE TABLE [dbo].[Variables] (
[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Var_ref] [int] NOT NULL ,
[Group_Ref] [int] NULL ,
[Machines_Ref] [int] NULL ,
[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Compatibility] [int] NULL ,
[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Category] [int] NULL ,
[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AnalysisResponsible] [tinyint] NULL ,
[AnalysisPriority] [int] NULL ,
[AnalysisDelta] [int] NULL ,
[AnalysisDeltaValue] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Length] [int] NULL ,
[Address] [int] NULL ,
[Offset] [int] NULL ,
[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle1] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle2] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle3] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle4] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Best regards,

Daniel

Roy Harvey schrieb:
Posting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Da************@sig.bizwrote:
Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel
Aug 24 '06 #3
There were no surprises in the table definition. Storage of a row in
SQL Server, with all the varying length columns at their upper limit,
is around 2000 bytes, for whatever that is worth. Output of a maxed
out row to a unicode text file should be no more than a few hundred
bytes longer.

Did you save the DTS package that the export wizard created? Have you
tried running the wizard again? Did you specify fixed-width or
delimited? ASCII or UNICODE? (I expect UNICODE to handle the
NVARCHAR columns.)

I would run the wizard again, being sure to save the package. That at
least will give something to inspect on the code side if the output
file is still screwy.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 06:11:12 -0700, "Daniel Wetzler"
<Da************@sig.bizwrote:
>Hi Roy,

thanks for your answer.

Here's the table definition :
CREATE TABLE [dbo].[Variables] (
[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Var_ref] [int] NOT NULL ,
[Group_Ref] [int] NULL ,
[Machines_Ref] [int] NULL ,
[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Compatibility] [int] NULL ,
[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Category] [int] NULL ,
[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AnalysisResponsible] [tinyint] NULL ,
[AnalysisPriority] [int] NULL ,
[AnalysisDelta] [int] NULL ,
[AnalysisDeltaValue] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Length] [int] NULL ,
[Address] [int] NULL ,
[Offset] [int] NULL ,
[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle1] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle2] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle3] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle4] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Best regards,

Daniel

Roy Harvey schrieb:
>Posting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Da************@sig.bizwrote:
>Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel
Aug 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Pecanfan | last post: by
3 posts views Thread by nologin | last post: by
13 posts views Thread by Hemant Sipahimalani | last post: by
reply views Thread by stuart | last post: by
5 posts views Thread by situ | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.