dts retrieves 0 rows when scheduled

Hi all
After scheduling a dts, which retrieves data from a priority-tabula server
(using the tabula propriatary odbc driver) , no rows are transfared.
There is no error massage i can trace sql server or in any of its logs, the
job just passes gracefully by the dts data transfare tasks, which all return
0 rows..
If I execute the dts manually all is well...
Both the sql server and sqlserver agent user accounts are the domain
administrator.
Any idea will be greatly appriciated!
I've been fighting this phantom phenomena for a very l o n g time and still
have no clue.
Thank you
Rea
what is the result if you schedule your package in a Windows scheduled tasks
instead-of SQL Agent?
have you try to change the DTS properties to make sur the thread run in the
same process? (sometimes if this option is not checked, there is some
problems to pass the identity to the components)
"Rea Peleg" <rea_p@.afek.co.il> a crit dans le message de
news:eWbHCLWTEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Hi all
> After scheduling a dts, which retrieves data from a priority-tabula
server
> (using the tabula propriatary odbc driver) , no rows are transfared.
> There is no error massage i can trace sql server or in any of its logs,
the
> job just passes gracefully by the dts data transfare tasks, which all
return
> 0 rows..
> If I execute the dts manually all is well...
> Both the sql server and sqlserver agent user accounts are the domain
> administrator.
> Any idea will be greatly appriciated!
> I've been fighting this phantom phenomena for a very l o n g time and
still
> have no clue.
> Thank you
> Rea
>

dts retrieves 0 rows when scheduled

Hi all
After scheduling a dts, which retrieves data from a priority-tabula server
(using the tabula propriatary odbc driver) , no rows are transfared.
There is no error massage i can trace sql server or in any of its logs, the
job just passes gracefully by the dts data transfare tasks, which all return
0 rows..
If I execute the dts manually all is well...
Both the sql server and sqlserver agent user accounts are the domain
administrator.
Any idea will be greatly appriciated!
I've been fighting this phantom phenomena for a very l o n g time and still
have no clue.
Thank you
Reawhat is the result if you schedule your package in a Windows scheduled tasks
instead-of SQL Agent?
have you try to change the DTS properties to make sur the thread run in the
same process? (sometimes if this option is not checked, there is some
problems to pass the identity to the components)
"Rea Peleg" <rea_p@.afek.co.il> a crit dans le message de
news:eWbHCLWTEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Hi all
> After scheduling a dts, which retrieves data from a priority-tabula
server
> (using the tabula propriatary odbc driver) , no rows are transfared.
> There is no error massage i can trace sql server or in any of its logs,
the
> job just passes gracefully by the dts data transfare tasks, which all
return
> 0 rows..
> If I execute the dts manually all is well...
> Both the sql server and sqlserver agent user accounts are the domain
> administrator.
> Any idea will be greatly appriciated!
> I've been fighting this phantom phenomena for a very l o n g time and
still
> have no clue.
> Thank you
> Rea
>

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 Package for AS/400 to SQL Server

Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

ThanksYes, we had this exact same issue. There are several steps you will need to take to resolve it:

1. Check the version of IBM Client Access that you are running on your SQL server. We installed V5R1 with the latest service pack. The latest service pack is critical because the original version of V5R1 has a bug that limits the size of the connection library to 9 characters (it's supposed to be 10).

2. Make sure that your SQL Agent is running as a user account (preferably a Domain User). Make sure it is NOT running as the local system account. Verify this by right-clicking on the SQL Server Agent icon under the management folder of your SQL Server (in EM) and then clicking on Properties. The Service Startup Account information should be filled in.

3. Set up an account on the AS/400 with the EXACT same name as the service account for SQL Agent. The password must also be EXACTLY the same (nb, at least on our AS/400, passwords are limited to 10 characters; the password for your SQL Agent account must conform to the same limitation).

4. When you set up the ODBC DSN connection to the AS/400 on your SQL Server, be sure to specify to use Windows Authentication (no prompting).

Doing all of this will ensure:

1. That your server will connect to the AS/400 without the password prompt
2. That rebooting your server will not impact the state of the connection.

Bear in mind that if you change the password for the SQL Agent account, then you will need to change it on the AS/400 as well.

I hope that this helps.

Hugh Scott

Originally posted by lossman
Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

Thanks

DTS Import Slow from As/400

I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jmWhat's the data in on the AS/400?|||JDE.. But the file isn't vanilla JDE.. Actually, I just create a File using query first, then I use DTS to import/translate..|||Can't you dump the data on the 400 then just copy and load it?|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm

COULD BE YOUR NETWORK SERVICES, CHECK OUT HOW FAST IS WORKING YOUR NETWORK, IF YOU ARE WORKING ON WINDOWS, CHECK OUT TERMINAL SERVICES|||are there any tranformations?
how is your package transforming the data?
are you using activex operations to perform the x-foms?

activex is single threaded in dts that could slow down operations

what is happpening on the SQL Side once you import the data
are those tables indexed and do they have enabled constraints on them?

constraint checking can slow down an import
so can an index load.
Disable your constraints and indexes before you perfom a bulk load|||Thanks for all the suggestions..
I will look into each of them..
To answer one of the questions, yes I'm using terminal services..
thx
jm|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm

We pull data out of the AS/400 here and there are two areas which will be the cause of performance problems.

1)The speed of the network connection.

2)the size of the tables you are transforming.

Not much you can do apart from increase your network bandwith and the cards on the SQL Server and the AS/400 (I think we have Gigabit Ethernet cards on each end)

For every thousand rows is takes approx three seconds - so if you are around this then you're doing OK.