DTS re-uses old text file input

Hi All,

A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.

The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.

A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.

I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.

I tried mapping to a UNC to no avail.

Is it buffering the old file somewhere? I need help.

current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.

Thanks Satya,

I'm not sure what you mean by "old settings"? Here's what I do know.

Drive 'G' is mapped to \\sun001\data

The input file name is weekly_updates.txt and does not change.

My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.

It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?

dts question, data pump task hangs when i try to edit it!

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Quote:

Originally Posted by catstevens

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Have you connected to your data source before doing this?|||hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks|||

Quote:

Originally Posted by catstevens

hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks

Hey dude,

What do you mean by editing on the server. I am a newbie to the data world and am trying to do the same ..extracting the data from TeraData to SQL Server. Its hanging and I was never able to get this done.

Can you please help me here...

THanks,
Rahul

DTS Question - copy table from SQL server to non-SQL server

I'm breaking into SQL 2000, and have completed a DTS package that imports and massages a file and creates a table with about 200M records.

Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.

I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.

Any tips to get me started will be appreciated.

Thanks,

RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).

Then define your source query (or table), your destination and any mapping changes you want to make.

Best of luck,

hmscott

...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql

DTS packages and SQL 2005 upgrade/issues

Case one - upgrade from SQL 2000 to SQL 2005 in the same box

(actually it is a copy of the test box being upgraded)

Case two - install fresh SQL 2005 in a different box

Results for running (dts packages migrated to dtsx) in SSIS from a job

Note all other jobs in (Case one) using the old DTSRUN works fine, but new jobs trying to run migrated packages fail. Migrating this new package to (Case two) runs. How do I troubleshoot this? The process exit code was -532459699.

Is this exit code something that can be looked up?

Case one
Date 4/17/2007 2:51:49 PM
Log Job History (TEST SSIS5)

Step ID 1
Server USXXXX4O
Job Name TEST SSIS5
Step Name SSIS5
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:51:49 PM. The return value was unknown. The process exit code was -532459699. The step failed.

Case two
Date 4/17/2007 2:34:07 PM
Log Job History (TEST SSIS5)

Step ID 1
Server USXXXX4
Job Name TEST SSIS5
Step Name SSIS5
Duration 00:00:07
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:34:07 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 2:34:07 PM Finished: 2:34:14 PM Elapsed: 6.328 seconds. The package executed successfully. The step succeeded.

You should set up logging to get more descriptive messages.

Have you run the package manually to see if it gives different results or where it fails?
|||

Manually runs fine for both cases from within SQL Server BI Dev Studio and also the SQL Server Mmgt Studio.

Got a link to the logging setting you mentioned earlier.

Thanks.

DTS Package problem...

Hi all
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
Al
Harag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al
|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.

>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.

>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com.. .
>
|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:

>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
[vbcol=seagreen]
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.

DTS Package problem...

Hi all
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
AlHarag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.
>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.
>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al
>|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>>Harag
>>First, you can SQL Server Profiler to see what is going on during the
>>package's execution.
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
>>Second, would it be much better to backup/restore the database on
>>destination server rather to copy tables/store procedures?
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.
>>
>>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al

DTS Package Copy

I want to copy the contents of one package into another package. I can brin
g
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a wa
y
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:

> I want to copy the contents of one package into another package. I can br
ing
> up the 2 packages side by side and I can copy things like the Connection a
nd
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a
way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...

DTS Package Copy

I want to copy the contents of one package into another package. I can bring
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...
Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:

> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...

DTS Package Copy

I want to copy the contents of one package into another package. I can bring
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...

DTS package

I am tring to back up my remote site's data to my computer here at home.

I use DTS wizard from EM and copy objects and data (is this correct?)

Then I choose to save the package as SQL package, give it a name and times to run etc.

However I can't find where it has saved if indeed it has.
I've looked under SQL server agent jobs and management local packages but it's not there.

I'm sure I'm doing it all wrong!

Thanks for any advice.

JBDid you create the package on Your computers Sql Server or on the Remote Server? I am sure that is the problem. Check both locations.|||Thanks Wes,

But.. how on earth do I create this on the remote Database?

JB

DTS or stored procedure

I need to copy some rows from a table in one server to a table in another
server. The amount of data is not very large and nothing fancy happens to
the data on the way.
Does anyone have any advice on the reletive merits of DTS as opposed to just
doing it in a stored procedure?
regards
SpikeHi
In your situation there is probably very little difference. You are not
really using any of the more specialised features of dts!
If you don't have any other DTS packages then there would be no real reason
to create one just for this.
John
"Spike" wrote:

> I need to copy some rows from a table in one server to a table in another
> server. The amount of data is not very large and nothing fancy happens to
> the data on the way.
> Does anyone have any advice on the reletive merits of DTS as opposed to ju
st
> doing it in a stored procedure?
> regards
> Spike
>|||Spike,
One should use DTS only if it is entirely necessary. For all other situation
one shd code the same thing using a sproc. Specifically in your case where
nothing fancing is happening to your data on the way you are better off usin
g
a sproc.
There can be hundreds of difference between DTS and sproc.
just to start with: sproc can be faster than DTS (when properly coded).
although it can be a complex codingbut then hey thats why we get good money
for doing it isnt it ?
sproc are in your db itself and are backed up by your regular db backup.
nothing special needs to be done.
I believe for DTS if you back up your msDB then its okay. additionally DTS
can also be saved as a file. (but still its an extra effort)
DTS is nice to look at and fancy to work wiht coz you can use your vb script
skills and stuff.
Abhishek Pandey
"Spike" wrote:

> I need to copy some rows from a table in one server to a table in another
> server. The amount of data is not very large and nothing fancy happens to
> the data on the way.
> Does anyone have any advice on the reletive merits of DTS as opposed to ju
st
> doing it in a stored procedure?
> regards
> Spike
>

DTS only copies first row

Hi,

I'm trying to copy results from a view on one table to another table in a different db using DTS in SQL Server Management Studio

Both source and destination db's are version 8.0.194

Everything reports as working, but when I check the results, only one row has copied - DTS reports that 1167 rows were copied.

This happens to different sources and destinations so I doubt it's my query.

When I perfrom the same DTS task using the old enterprise manager DTS in SQL Server 2000, everything copies fine.

Obviously one answer is "Use SQL Server 2000 Enterprise Manager then. " but I'd rather upgrade to 2005 and Management Studio permanently.

Any ideas?

I don't know what the problem may be but you need to identify exactly where the problem is occurring. My first avenue of investigation would be to put a data viewer immediately prior to your destiantion to see how many rows are flowing into it. Check that the number displayed in teh GUI is correct.

-Jamie

DTS launch from SQL Server Agent

Hi,

I want to build a task that does 3 things automatically at a fixed time interval:

1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it

Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).

I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.

Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?

Anyone? How is this done where you've worked?

Thanks,

Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.

You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).

-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.

DTS job to copy

I created a DTS package to copy a database from one server to another. I wan
t to change the collation of the existing database to insenseitive. During t
he set up of the DTS package I didn't check off collation , so I assume my d
estination database will ov
erride the source. I had an error after and and the process finished.
[microsoft][odbc sql serverdriver][sql server]user or role "user
_name"already exists in the current database.
what happened, and how can I correct it.I would guess you either already had a user in the database or you have
two users in the source database which only differ in case and are
considered the same user in the destination.
Either don't copy users or get rid of the duplicates
try in the source
select upper(name)
from sysusers
group by upper(name)
having count(*) > 1
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

DTS job to copy

I created a DTS package to copy a database from one server to another. I want to change the collation of the existing database to insenseitive. During the set up of the DTS package I didn't check off collation , so I assume my destination database will ov
erride the source. I had an error after and and the process finished.
[microsoft][odbc sql serverdriver][sql server]user or role "user_name"already exists in the current database.
what happened, and how can I correct it.
I would guess you either already had a user in the database or you have
two users in the source database which only differ in case and are
considered the same user in the destination.
Either don't copy users or get rid of the duplicates
try in the source
select upper(name)
from sysusers
group by upper(name)
having count(*) > 1
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

DTS job to copy

I created a DTS package to copy a database from one server to another. I want to change the collation of the existing database to insenseitive. During the set up of the DTS package I didn't check off collation , so I assume my destination database will override the source. I had an error after and and the process finished
[microsoft][odbc sql serverdriver][sql server]user or role "user_name"already exists in the current database
what happened, and how can I correct it.I would guess you either already had a user in the database or you have
two users in the source database which only differ in case and are
considered the same user in the destination.
Either don't copy users or get rid of the duplicates
try in the source
select upper(name)
from sysusers
group by upper(name)
having count(*) > 1
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTS job hung ! Please help

I create a DTS package that copy data from oracle to SQL 2000. In the package, I have 2 connections, 1 transform data task, and 1 execute SQL task to truncate the table before copying the data. The DTS works by right clicking and EXECUTING.
however, it does not work when I run it from a job. The job keeps saying
"Executing..." and it runs for hours when it only takes a few seconds to run using DTS package

DTS package owner is "sa"
Job owner is "sa"

What did I do wrong? Any suggestions are greatly appreciated.

thank youCan't say just yet. Can you check on the following, just to see where the package is hanging?

1) Which version of the package has been scheduled? If you are not sure, read up on the DTSRUN utility, and change the command that Enterprise Mangler automatically garbages up for you.

2) Has the package connected to SQL Server? (Use sp_who).

3) Has the SQL Server table been successfully truncated?

4) Has the package connected to Oracle?

Had the package just errored out after the truncate table, I would think that you are missing the Oracle SQLNET installation on the server. With a hang, you likely have a test textbox popping up waiting for someone to click OK.|||Thank you for your reply.

The connection to SQL is working because the table got truncated. I am not sure how to test if the connection to Oracle is working. I did install ORacle client on the server and define ODBC data source to connect to the ORacle database. The DTS package runs if I right click on the package and select Execute.

Any thing else I should check? Thanks|||Try this, then: Log on the server as the SQL Agent service account, and run the package from the command line. Use the one in the job, to be certain you get the right command. See if anything comes up.

DTS Import/Export Wizard database copy problem

Hi

I'm experiencing problems copying a SQL 2000 database from my host to a local SQL Server.

I choose the Data Source OK, using SQL Server Authentication and the username and password used to setup my hosted database.

Then I specify the destination, my local server. I'm using my sa account and password and creating a <new> database (doesn't matter if I use the same db name as the source or a different one, I'm still getting an error).

I then choose "Copy objects and data between SQL Server databases", then take all defaults on the "Select Objects to Copy screen", i.e. create destination objects drop objects first and include dependent objects); creat data (replacing existing data); Copy all objects and use default options.

The copy appears to be creating a script OK (runs up to 100%), then looks to start copying objects. It gets to approx 20% and reports that it "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". When I click on the Executing Package entry it tells me:

[Microsoft][ODBC SQL Server Driver][SQL Server]The login already has an account under a different user name.

I can't seem to find any reference to this error in my books or on Google (usually a great source of info). Can't help feeling I'm doing something stupid. Would be very, very greatful if someone out there could give me a pointer in the right direction.

Thanks,
Keith.On the "Select Objects to Copy screen" I would uncheck the "Use default options" box at the bottom, click "Options..." and uncheck "Copy database users and database roles". This should take care of the problem.

Terri

DTS Import Wizard fails

I am trying to copy updated tables from a DB on a remote SQL server to
my pc using import wizard. I choose "copy objects and data between SQL
Server databases". On the screen "Select objects to copy", all of the
options are unchecked/blank including the script location. Clicking
"select objects" causes the wizard (and enterprise manager) to crash.
An error message refers to mmc.exe. Any ideas?Hi

This sounds like an error with EM itself, is this a one off or re-creatable?
You may want to check that the client tools are up-to-date. To avoid network
problems, it may be easier to restore a backup to a copy database and then
do the updates.

John

"SkyBlue" <aajjww@.postmaster.co.uk> wrote in message
news:60c756c.0407300546.664df6ba@.posting.google.co m...
> I am trying to copy updated tables from a DB on a remote SQL server to
> my pc using import wizard. I choose "copy objects and data between SQL
> Server databases". On the screen "Select objects to copy", all of the
> options are unchecked/blank including the script location. Clicking
> "select objects" causes the wizard (and enterprise manager) to crash.
> An error message refers to mmc.exe. Any ideas?

dts import agent

Hi group
I want to copy a DB from one Sql Server 2000 SP3 to antother Sql 2000 Sp3.
Im using the DTS Import agent in Enterprise manager. After a few minutes
Ive got an error Message telling me that the copy process failed.
The Log file of the Enterprise Manager is empty. Any Idea what happened? The
Database has a size of 900 Mb, is that a problem?
Is there another solution to copy the db?
Backup and than restore is not possible because the database I want to copy
use replication.
Any ideas?
thx for any help
BjrnBjrn,
I don't follow why backup and restore is prevented in your situation by the
presence of replication (publisher or subscriber should be fine) - can you
explain more.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
I have tried it.
I have build a new DB on my destination Sql Server. Than I have restored the
Databse.
Now I have a dead Databse. Enterprise Manager displays the Icon in grey not
in yellow and behind the Database Name is "loading" written.
I cant delete it, I cant show any properties nothing.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:eTNFzlCyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Bjrn,
> I don't follow why backup and restore is prevented in your situation by
the
> presence of replication (publisher or subscriber should be fine) - can you
> explain more.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>|||I think this is not related at all to replication - I'd guess that you have
restored the database using no_recovery.
Try running "restore database yourdbname with recovery".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hey Paul
Thx for your help.
I dont know what is different but now I have a working restored DB.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:u0Y7eFGyGHA.4092@.TK2MSFTNGP04.phx.gbl...
> I think this is not related at all to replication - I'd guess that you
have
> restored the database using no_recovery.
> Try running "restore database yourdbname with recovery".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>