scheduled dts return's 0 rows..

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 in 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
Rea,
to test if this is an authentication issue, can you log onto the SQL Server box as the same Domain user that the SQL Server Agent uses and then manually run the DTS package. This should be physically done on the server box. I'm expecting that this will re
turn zero records, but let's see... . If it doesn't return any rows, then try running the job manually as you reported in your first post and again log in as the SQL Server Agent's login. This will tell us if it is related to the existence of network shar
es etc.
HTH,
Paul Ibison

scheduled dts return's 0 rows..

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 in 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
ReaRea,
to test if this is an authentication issue, can you log onto the SQL Server
box as the same Domain user that the SQL Server Agent uses and then manually
run the DTS package. This should be physically done on the server box. I'm
expecting that this will re
turn zero records, but let's see... . If it doesn't return any rows, then tr
y running the job manually as you reported in your first post and again log
in as the SQL Server Agent's login. This will tell us if it is related to th
e existence of network shar
es etc.
HTH,
Paul Ibison

scheduled dts fails but runs when done manually

I'm a newbie to sql2000. i have some jobs that copy stuff from a non microsoft database(titanium) to SQL2000. i use the propriety odbc drivers provided by titanium. when run manually in enterprize manager,it works. but scheduled as a job it fails with the message "System cannot find the specified File" i've read up and tried microsofts suggestions ie ensuring that the sql server agent has rights to the folders used etc. i've also tried puttin in the the dts owner and user passwords. each time it fails with the same error.
any one out there who can help?:confused:I'm a SQL2K newbie and I've experienced a similar problem.

When you run the DTS pkg manually, it runs on your client machine;
when you run it as a job it runs on the SQL server.

If you can run the DTS pkg manually on the SQL server (from its console), do that. You should get more details on the error. Most likely
something is missing from the PATH environment variable on the SQL
server (and you can compare to what you have for this on your client
machine).

Good luck!

Jeff|||Jeff's on the right track. When you develop a DTS package remotely, it remembers the paths based on where you developed it. You would either need to
- develop the package locally (on the server), in which case it would not run interactively from a remote machine, or

- use a unc path, ie. \\servername\sharename\filename. In this case, it should run interactively or scheduled.

Steve|||I'd also add to check permissions. You need to keep in mind that the job on the server is not going to run as "you" but as the sql server agent, so it maynot have the same authorization to access drives and shares as you do. That's the one that always catches me.

Scheduled DTS + odbc with drive mapping

Hi, thanks for reading my message.

My company is running SQL Server 2000, and I need to have a DTS scheduled to run every night that extracts data from our other TimberLine database. The connection to this Timberline database is done through an ODBC which uses a mapped drive to the local database directory of the database.

Now, this mapping is performed only when somebody logs onto the server, therefore manually running the DTS is no problem.

The problem is that when I schedule the DTS, the system does not know where to find the local database directory of the TimberLine system, because the local database dir is not mapped to a drive. Therefore I get an error called "TimberLine ODBC drive is not activated".. This is because the ODBC cannot find the local dbase dir, because the drive is not mapped..

Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?Did you try UNC file path?

Why don't you have the drive mapped?

And if it's a database, aren't you using named pipes or tcp/ip?|||Originally posted by sirorange
Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?

If the Workstation you are running this from is a Windows NT/2000/XP box, you could login, let the login script map the drive, and then lock the computer. You can lock the computer by pressing CTRL + ALT + DEL and then clicking "Lock Computer."

This will not log you out, so your drives will still be present.|||1)configure the sql agent service to run under domain/user and not local system, create a sql job that execute the DTS using dtsrun utility, schedule the job.
or
2) run the DTS from windows schedule task as batch command or any other script|||Originally posted by sirorange
Hi, thanks for reading my message.

My company is running SQL Server 2000, and I need to have a DTS scheduled to run every night that extracts data from our other TimberLine database. The connection to this Timberline database is done through an ODBC which uses a mapped drive to the local database directory of the database.

Now, this mapping is performed only when somebody logs onto the server, therefore manually running the DTS is no problem.

The problem is that when I schedule the DTS, the system does not know where to find the local database directory of the TimberLine system, because the local database dir is not mapped to a drive. Therefore I get an error called "TimberLine ODBC drive is not activated".. This is because the ODBC cannot find the local dbase dir, because the drive is not mapped..

Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?

create a batch file with following

REM DELETE EXISTING MAP
net use z: /delete
REM CREATE Z: MAP DRIVE
net use z: \\server\path /user:YOUR_NETWORK_ID password /delete

Use a os command step in SQL AgentJob and call above batch file in it.
and run it as first step before DTS step

amit

Schedule task DB connection

I need to know how to pass parameters that could tell an executable through the task scheduler, to initiate the ODBC connection first and execute the executable. I do not want to leave a PC, server, terminal server session or similar mechanism running and logged into DB prior to the scheduled task running. This keeps the ODBC connection initialized and available for the Import executable to use when it runs (as a scheduled task). However, that will not be a reliable, long term solution for automating the import as the session would be killed if the server is rebooted, somebody may accidentally close it or a number of other scenarios could cause it to get closed.

Question How do you make the ODBC connection to the database when running the schedule task function, prior to the software executable running. I am using the scheduled function to automactically excute a xml import every 10 minutes. My software keeps stating no db connection. I have looked everywhere and can not find how to tell in this schedule task function command line how to connect to the DB prior to running the process. Does any one know how to fully automating the connection to a ODBC prior to running the executable.

thanks techbk

Are you using any SQL Server tools here?