?
Solved

SP2-0332 error: cannot create spool file.

Posted on 2006-04-13
21
Medium Priority
?
2,253 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:HKBoyz
  • 10
  • 8
  • 3
21 Comments
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16448777
Check out this for information and a possible work around:

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

Good Luck,
Steve
0
 
LVL 10

Assisted Solution

by:GuruGary
GuruGary earned 240 total points
ID: 16448790
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16448795
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 10

Assisted Solution

by:GuruGary
GuruGary earned 240 total points
ID: 16448832
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
 
LVL 10

Assisted Solution

by:GuruGary
GuruGary earned 240 total points
ID: 16448848
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
 

Author Comment

by:HKBoyz
ID: 16448927
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16448942
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16448946
0
 

Author Comment

by:HKBoyz
ID: 16448947
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16448994
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
 

Author Comment

by:HKBoyz
ID: 16449022
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16449079
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
 

Author Comment

by:HKBoyz
ID: 16449135
Steve
I tired and get the SP3-0333 again.
0
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16449178
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
 

Author Comment

by:HKBoyz
ID: 16449202
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16449249
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
 

Author Comment

by:HKBoyz
ID: 16449309
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
 
LVL 30

Assisted Solution

by:SteveGTR
SteveGTR earned 1760 total points
ID: 16449520
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
 

Author Comment

by:HKBoyz
ID: 16449554
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
 
LVL 30

Accepted Solution

by:
SteveGTR earned 1760 total points
ID: 16449580
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
 

Author Comment

by:HKBoyz
ID: 16449603
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 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