SP2-0332 error: cannot create spool file.

We want to use Windows Task Scheduler to schedule and execute several DB jobs using a .bat file. The DB is Oracle. A sql query needs to be scheduled to run twice a day to create and populate a temp table, then 3 Csystal reports need to be run getting data from the temp table. The sequence is:

1. Run SQL script to create  and populate the staging table;
2. Run report A when the staging table is created and populated; ie. the report can't be run until it receveid the status of the SQL script is completed;
3. Refreshed/repopulated the staging table when repot A is completed. ie. upon receiving the status of the report is compelted;
4. Run report B when receveid the status of the SQL script is completed;
5. Run reprot C when  receveid the status of the report B is completed.

I created a .bat file to exec. the sql file successfully when I ran it in my local drive.

However, when I ran it in the server I first got the SP2-0333 "bad character" error. It was caused by the space in the server path. So I changed the .bat file to the following:

@ECHO OFF
subst g: "C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86"
sqlplus pinprd/pintst_pin05@xm_pintst @"C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\AT_Sql.sql"

Now I get the SP2-0332 error: cannot create spool file.

I can't change the space in the path as that's what the server path is, and the spool file can't be place anywhere else as it will be used there.

Is there any work around?

Thanks.
HKBoyzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveGTRCommented:
Check out this for information and a possible work around:

http://www.orafaq.com/forum/t/11222/0/

Good Luck,
Steve
0
GuruGaryCommented:
You can try something like:

@ECHO OFF
subst g: C:\Progra~1\Crystal Decisions\Enterp~1\win32_x86
sqlplus pinprd/pintst_pin05@xm_pintst @C:\Progra~1\Crysta~1\Enterprise 9\win32_x86\AT_Sql.sql
0
SteveGTRCommented:
Another possibility is to use the 8.3 file name. Use the /X switch to get it:

dir /X "C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86"

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

GuruGaryCommented:
Oops ... mised a space in the first line:

@ECHO OFF
subst g: C:\Progra~1\Crysta~1\Enterp~1\win32_x86
sqlplus pinprd/pintst_pin05@xm_pintst @C:\Progra~1\Crysta~1\Enterprise 9\win32_x86\AT_Sql.sql
0
GuruGaryCommented:
Okay, one more try (hopefully no spaces this time):

@ECHO OFF
subst g: C:\Progra~1\Crysta~1\Enterp~1\win32_x86
sqlplus pinprd/pintst_pin05@xm_pintst @C:\Progra~1\Crysta~1\Enterp~1\win32_x86\AT_Sql.sql

Steve's solution of the "dir /x" will get you the exact path, but chances are that it will work out to be what is listed above.
0
HKBoyzAuthor Commented:
GuruGary
My .bat file have exactly the same lines you put here. And that's how I got the SP2-0332 error. My spool statements in the SQL file is:

spool g:at_spool.txt
  select 1 from dual;
  spool off
0
SteveGTRCommented:
I'd recommend just using the 8.3 file names. That is how others with this problem has gotten around the problem. That means, removing the subst and just referencing the 8.3 file name in sqlplus.
0
SteveGTRCommented:
0
HKBoyzAuthor Commented:
Steve
I tried your suggestion too, but still getting the same error. I did look at the link you sent and that's how I came up with using subst.  But instead of getting SP2-0333 now I get SP2-0332
0
SteveGTRCommented:
So you are no longer using the subst and you are just doing something like this and getting the error?

@echo off

sqlplus pinprd/pintst_pin05@xm_pintst @C:\Progra~1\Crysta~1\Enterp~1\win32_x86\AT_Sql.sql

0
HKBoyzAuthor Commented:
This is what I have in the .bat file:
@ECHO OFF
subst g: "C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86"
sqlplus pinprd/pintst_pin05@xm_pintst @"C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\AT_Sql.sql"

and this is what I have in SQL file:

......
.....

spool g:at_spool.txt
  select 1 from dual;
  spool off


And I am getting SP2-0332.
0
SteveGTRCommented:
I'd recommend trying something like the following:

This is what I have in the .bat file:
@ECHO OFF

sqlplus pinprd/pintst_pin05@xm_pintst @"C:\Progra~1\Crysta~1\Enterp~1\win32_x86\AT_Sql.sql"

...

spool C:\Progra~1\Crysta~1\Enterp~1\win32_x86\
  select 1 from dual;
  spool off
0
HKBoyzAuthor Commented:
Steve
I tired and get the SP3-0333 again.
0
SteveGTRCommented:
How about changing this command:

spool C:\Progra~1\Crysta~1\Enterp~1\win32_x86\

to

spool C:\Progra~1\Crysta~1\Enterp~1\win32_x86\at_spool.txt

If this still fails, can you post the batch processing that receives the error and using the changes I suggested?
0
HKBoyzAuthor Commented:
This is what I have in the .bat file:
@ECHO OFF
sqlplus pinprd/pintst_pin05@xm_pintst @"C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\AT_Sql.sql"

and this is what I have in SQL file:

......
.....

spool "C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\at_spool.txt"
  select 1 from dual;
  spool off

same error SP3-0333.
0
SteveGTRCommented:
Use the 8.3 file names in both places.

Change:

C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\

To:

C:\Progra~1\Crysta~1\Enterp~1\win32_x86\

In both places.
0
HKBoyzAuthor Commented:
Sorry but what's 8.3 file names? And what's the difference between

C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\

and

C:\Progra~1\Crysta~1\Enterp~1\win32_x86\

What are those "~"??
0
SteveGTRCommented:
The 8.3 names are the old DOS equivalants of the long DOS names that can include spaces.

Drop down to the DOS prompt and type in the following commands:

dir "C:\Program Files"

dir "C:\Progra~1"

They produce that same results.

Using the 8.3 names you can get around limitations in programs where they can't handle file names with spaces and longer than 8 characters for the file name and 3 characters for the extension.
0
HKBoyzAuthor Commented:
Thanks for the explanations. I changed to C:\Progra~1\Crysta~1\Enterp~1\win32_x86\ in both files. Now I get this:

SP2-0332: Cannot create spool file.
0
SteveGTRCommented:
I think you'll want to use the following change:

C:\Progra~1\Crysta~1\Enterp~1\win32_~1\

Verify that that path is correct by doing the following at the command prompt:

dir "C:\Progra~1\Crysta~1\Enterp~1\win32_~1"

It should list the directory and it's contents.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKBoyzAuthor Commented:
Oh well......
I got it!!
I removed the dir path in the spool (now just spool at_spool.txt) then it works!!

Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft DOS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.