asked on

BCP command in batch file not working through dbms scheduler

Hi,
We are encountering a peculiar problem. We have a batch file which does a nightly data extract from different databsaes. We have a BCP command in this which extracts data from a SQL server database.

The batch file, when run manually does all jobs and the BCP command runs fine.

Be when scheduled through Oracle Scheduler, all commands except this BCP fail.

Any pointers would be greatly appreciated.

Thanks
Ganesh

Guy Hengel [angelIII / a3]

what are all these other commands?
what errors?

or do you want to say by " all commands except this BCP fail."
all command run fine, except this BCP fails?

anyhow, what error do you get?

mganesh

ASKER

Sorry for the typo. Let me clarify:
'All commands except the BCP run fine'

There is no error as such. The job is shown as completed but the resultant files are not created, meaning the data is not extracted.
But when run manually, or through Windows Scheduler (tested right now), works fine.

Regards,
Ganesh

Guy Hengel [angelIII / a3]

ok, can you show the syntax you used for the bcp?

mganesh

ASKER

Here is ta small file which we used for testing, this has the same behaviour, works in windows scheduler but not through oracle scheduler:

echo starting >> D:\bcp_test.log
cd C:\Program Files\Microsoft SQL Server\90\Tools\binn\
SET USERNAME >> D:\bcp_test.log --------------this line too is giving an error level of 1
echo %ErrorLevel% >> D:\bcp_test.log
BCP "SELECT <field list> FROM <table_name> WHERE <column_name1>='value1' AND <column_name2>='value2'" QUERYOUT D:\mydir\output.TRA -S <credentials> -T -c -t -e BCP_Error.txt
echo %ErrorLevel% >> D:\bcp_test.log --------------------the error level is shown as 1
echo ending >> d:\bcp_test.log

All outputs appear in the log file except the output of BCP command

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mganesh

ASKER

The OS account under which Oracle runs is a trusted account on the SQL server.
Another important observation is,
the SET USERNAME >> D:\bcp_test.log command also gives an error code of 1
which may indicate that the problem area is at the local side, on the command level.

nmcdermaid

Does the oracle scheduler account have rights to create the output files?

mganesh

ASKER

We changed the scheduler user from system to sys and the problem was solved.
Accept Angellll's solution, but not sure why the SET command gave the error too?