[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

BCP command in batch file not working through dbms scheduler

Posted on 2007-08-10
8
Medium Priority
?
967 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:mganesh
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19674987
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?
0
 
LVL 3

Author Comment

by:mganesh
ID: 19674999
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19675006
ok, can you show the syntax you used for the bcp?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 3

Author Comment

by:mganesh
ID: 19675035
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
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 19675067
-T means trusted connectivity... is the account under which the oracle scheduler runs a trusted acccount on the sql server ?
0
 
LVL 3

Author Comment

by:mganesh
ID: 19675173
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 19682861
Does the oracle scheduler account have rights to create the output files?
0
 
LVL 3

Author Comment

by:mganesh
ID: 19690084
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?

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question