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,

DanielPosting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>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

|||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:

Quote:

Originally Posted by

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"
<Daniel.Wetzler@.sig.bizwrote:
>

Quote:

Originally Posted by

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

|||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"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>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:
>

Quote:

Originally Posted by

>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"
><Daniel.Wetzler@.sig.bizwrote:
>>

Quote:

Originally Posted by

>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

Problem With DTS Package - To Delay Process

Hello All SQL Experts.

Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

Please advise. Thank you.

Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

declare @.MainUpdate datetime
declare @.TempUpdate datetime

select @.MainUpdate=Main_Update_CET from APMEAPV_Compare
select @.TempUpdate=Temp_Update_CET from APMEAPV_Compare
--select @.MainUpdate, @.TempUpdate

if @.MainUpdate<>@.TempUpdate
begin
DECLARE @.commandK varchar(1000)
SET @.commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls"'
exec master..xp_cmdshell @.commandK, No_Output

ENDIf you don't have the ability to crate a JOB on the server that will run steps
and create each step as a different DTS or executable command.

You can do a couple of things technically.

Put a step in the DTS to loop a number 36000 = 1 second I think.

Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

opackage.Execute

opackage.UnInitialize

Set opackage = Nothing

I would ask what are you trying to accomplish and is this the best tool to do this.

Hope this helps.|||Hello rbackmann.

Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

Thank you.