Board index MS SQL Server

DTS, creating incorrect table

DTS, creating incorrect table

Post by Ala » Tue, 16 Apr 2002 19:34:41



Hi,

I have a DTS package that first, drops a table, then creates one and
populates it with a text file(csv) from a unix server. This works
perfectly and was scheduled to run every five minutes.

Now.... if I manually execute the script using enterprise manager, it
still works. The problem is, the scheduled run job, is creating a
table with incorrect data in it. It runs every 5 minutes but populates
the table with incorrect values from another text file.

I cannot work out why, when maually run, it works. Scheduled it
doesn't.

I am completly lost in what to do (being relativly inexperianced using
SQL 2000), so I would much appreciate if anyone could help me.

Thanks,

Alan

 
 
 
Top

DTS, creating incorrect table

Post by Sue Hoegemeie » Tue, 16 Apr 2002 23:20:31


For the text file source, are you using a UNC path to refer
to this file? The location reference for this file when you
run the package may not be the same referenced location when
the package is run as a job. DTS packages run under the user
context of whoever executes the package. If you run it
manually, it runs under your user. If you schedule it, it
runs under the user context of the SQL Server Agent service
account. The same applies to location - it executes from the
where it is executed. If you run it from your PC, it runs
from your PC. When schedule as a job, it runs from the
server. For more information, refer to the following:
Q269074 - INF How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/support/kb/articles/Q269/0/74.asp

-Sue



>Hi,

>I have a DTS package that first, drops a table, then creates one and
>populates it with a text file(csv) from a unix server. This works
>perfectly and was scheduled to run every five minutes.

>Now.... if I manually execute the script using enterprise manager, it
>still works. The problem is, the scheduled run job, is creating a
>table with incorrect data in it. It runs every 5 minutes but populates
>the table with incorrect values from another text file.

>I cannot work out why, when maually run, it works. Scheduled it
>doesn't.

>I am completly lost in what to do (being relativly inexperianced using
>SQL 2000), so I would much appreciate if anyone could help me.

>Thanks,

>Alan


 
 
 
Top

DTS, creating incorrect table

Post by Ala » Wed, 17 Apr 2002 17:17:40


Thanks for the reply Sue,
I checked all the file location (you were correct with the UNC
assumption) but all is fine. I finally rebooted the PC last night and
this cured the problem!!! It seems that it was cacheing the file
somehow.

The problem unfortuanatly has arose again this morning. I am now
looking at last nights data again. I've rebooted the server, and it
all works again but I suspect, at somepoint, the data that is
populating the table will become corrupted again.

I'm completly at a loss as what to do, apart from manually run the DTS
every 5 minutes :-).

Any ideas?

Alan


> For the text file source, are you using a UNC path to refer
> to this file? The location reference for this file when you
> run the package may not be the same referenced location when
> the package is run as a job. DTS packages run under the user
> context of whoever executes the package. If you run it
> manually, it runs under your user. If you schedule it, it
> runs under the user context of the SQL Server Agent service
> account. The same applies to location - it executes from the
> where it is executed. If you run it from your PC, it runs
> from your PC. When schedule as a job, it runs from the
> server. For more information, refer to the following:
> Q269074 - INF How to Run a DTS Package as a Scheduled Job
> http://support.microsoft.com/support/kb/articles/Q269/0/74.asp

> -Sue



> >Hi,

> >I have a DTS package that first, drops a table, then creates one and
> >populates it with a text file(csv) from a unix server. This works
> >perfectly and was scheduled to run every five minutes.

> >Now.... if I manually execute the script using enterprise manager, it
> >still works. The problem is, the scheduled run job, is creating a
> >table with incorrect data in it. It runs every 5 minutes but populates
> >the table with incorrect values from another text file.

> >I cannot work out why, when maually run, it works. Scheduled it
> >doesn't.

> >I am completly lost in what to do (being relativly inexperianced using
> >SQL 2000), so I would much appreciate if anyone could help me.

> >Thanks,

> >Alan

 
 
 
Top

DTS, creating incorrect table

Post by Allan Mitchel » Thu, 18 Apr 2002 03:12:50


Make sure you set the workflow to drop the connection when finished

Are the files the same name ?

Get DTS to write out the date on the file everytime it executes it to see if
it is picking up an old file.

--

Allan Mitchell
www.allisonmitchell.com


> Thanks for the reply Sue,
> I checked all the file location (you were correct with the UNC
> assumption) but all is fine. I finally rebooted the PC last night and
> this cured the problem!!! It seems that it was cacheing the file
> somehow.

> The problem unfortuanatly has arose again this morning. I am now
> looking at last nights data again. I've rebooted the server, and it
> all works again but I suspect, at somepoint, the data that is
> populating the table will become corrupted again.

> I'm completly at a loss as what to do, apart from manually run the DTS
> every 5 minutes :-).

> Any ideas?

> Alan




- Show quoted text -

> > For the text file source, are you using a UNC path to refer
> > to this file? The location reference for this file when you
> > run the package may not be the same referenced location when
> > the package is run as a job. DTS packages run under the user
> > context of whoever executes the package. If you run it
> > manually, it runs under your user. If you schedule it, it
> > runs under the user context of the SQL Server Agent service
> > account. The same applies to location - it executes from the
> > where it is executed. If you run it from your PC, it runs
> > from your PC. When schedule as a job, it runs from the
> > server. For more information, refer to the following:
> > Q269074 - INF How to Run a DTS Package as a Scheduled Job
> > http://support.microsoft.com/support/kb/articles/Q269/0/74.asp

> > -Sue



> > >Hi,

> > >I have a DTS package that first, drops a table, then creates one and
> > >populates it with a text file(csv) from a unix server. This works
> > >perfectly and was scheduled to run every five minutes.

> > >Now.... if I manually execute the script using enterprise manager, it
> > >still works. The problem is, the scheduled run job, is creating a
> > >table with incorrect data in it. It runs every 5 minutes but populates
> > >the table with incorrect values from another text file.

> > >I cannot work out why, when maually run, it works. Scheduled it
> > >doesn't.

> > >I am completly lost in what to do (being relativly inexperianced using
> > >SQL 2000), so I would much appreciate if anyone could help me.

> > >Thanks,

> > >Alan

 
 
 
Top

1. Table Properties show incorrect Row number after DTS

Hi.

I have .dbf format database that I want to import to the SQL Server.
There are about 30 files and some of them show incorrect row number in
the Table Properties. For example,

Actual Row number    Table Properties
2440                            34825
490                               59905
488                               59393
140                               35840
646                               34306

It seems that, somewhere the two bytes were swapped.

This problem happens to Japanese NT4.0(SP4)  with English SQL Server
7.0.
Also it happens to JapaneseNT4.0(SP4) with Japanese SQL Server 7.0.
.dbf file is English, I mean, created in the English environment and
uses only alphabets.

Because of this, when I try to open the table in the Enterprise Manager,
the following
error appears;
    [Microsoft][ODBC Driver Manager]  General error: invalid window
handle

I can open the table once and only if I keep that table open, I can open
other
tables. But once you close all tables, and try to open any table again,
it won't allow you to open any table.  This error from ODBC Driver
Manager,
however, only happens to  the English SQL Server7.0 on the
JapaneseNT4.0.
Even though the Japanese SQL Server7.0 still shows the incorrect Table
Properties,
it can open any table without the error above.

The version of ODBC32.dll on the English and Japanese SQL Servers are
same.
I added MDAC2.1(Japanese) to the OS, but didn't  fix the problem.
This problem never happened to the English SQL Server on the English
NT4.0.

I tried to do the same thing through VB code. I made two ADO recordsets.
One stores .dbf information
and copies to the other empty ADO recrodset. After the the second ADO
recordset does .UpdateBatch,
the same Row number problem occurrs to  the Table Properties.

At least, the row number can be obtained by makeing a query in the Query
Analyzer. But, none of the tables
cannot be opened.

I've been trying to fix this problem for more than two weeks. I don't
know what to do at this moment.
Does anyone have any idea how to fix this problem?
I'm open to any suggestion. I REALLY need help.

Thank you,

Shizuko I. Cox

2. Creating frames at Run time?

3. DTS: Using table from Source and table from Destination to create new table in Destination

4. ?UNICODE/ANSI problems with DAO database

5. incorrect View result and incorrect table properties

6. Dynamic SQL

7. Create Table with Date as Name in Access using DTS

8. Java access to C-ISAM (or ODBC)?

9. DTS challenge: SQLTask (create table) in Access

10. Create a dbase III table with DTS

11. DTS Error (The parameter is incorrect)

12. Unable to create DTS package that executes a stored proc that uses temp tables

13. DTS Creates Tables of its Own?


All times are UTC
Board index