SQL*Loader launch from Web Service

[RedKev]

Hi guys,

I've built a small class to take in a filename (.csv), launch SQL*Loader & push it into a specific table. This class works fine in my windows app but when I try to run it from a web service I get the following error with the control file:

SQL*Loader-500: Unable to open file (control.ctl)
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command.

(bear in mind the details being used for the web service & windows app ARE THE EXACT SAME).

Code to launch the process is as follows:

Dim psi As System.Diagnostics.Process = New System.Diagnostics.Process
psi.StartInfo.FileName = SQLLoader
psi.StartInfo.UseShellExecute = False
psi.StartInfo.RedirectStandardOutput = True
psi.StartInfo.RedirectStandardError = True
psi.StartInfo.Arguments = file
psi.Start()
psi.WaitForExit()


Any help at all would be appreciated, as this is quite urgent.

 

[cjard]

I'd start with looking into why the loader cant open its control.ctl file - is it there? in use? permissions?

A usefil Sysinternals tool called filemon will help

 

[RedKev]

I'd start with looking into why the loader cant open its control.ctl file - is it there? in use? permissions?

A usefil Sysinternals tool called filemon will help

Hi cjard,

I've managed to launch SQLLoader when the .exe & files are on the local disk. It seems to be a strange permissions issue because it can launch sqlLoader but cannot access the .ctl file, even tho they are in the same directory with the same permissions.

Alas, I don't think it is SQLLoader specifc. Cheers.

 

[cjard]

In situations like this, you normally find that there is some working-directory assumption gone wrong, or that the process which started the EXE doesnt have the rights to access the file - remember, if a web service is executing the loader, any permissions it does/nt have affect all work the loader does

 

[RedKev]

Hey cjard,

This is a weird one, I have the web service impersonating myself (the same user who kicks off the SQL*Loader in the app), also the Web service doesn't seem to have a problem opening the loader.exe only reading the control file:

"SQL*Loader-500: Unable to open file (\\xxxx\xxxxxxx$\S Systems\SQLLDR\Margin.ctl)
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command."

Any ideas?!?

 

[cjard]

Copy the file to the local disk - network services typically run under a user account that is restricted from accessing some network resources without explicit permission.. Its for this reason that people with FTP servers struggle to serve files of network drives - the service is prevented from accessing it by windows.

If you ascertain this is the problem you can look into granting access to the file

 

[RedKev]

Copy the file to the local disk - network services typically run under a user account that is restricted from accessing some network resources without explicit permission.. Its for this reason that people with FTP servers struggle to serve files of network drives - the service is prevented from accessing it by windows.

If you ascertain this is the problem you can look into granting access to the file

Hey cjard,

That has solved the problem, got any pointers on how to resolve it? I need it running on a network drive. I'm kind of baffled because the web service has impersonation turned on so SQL*Loader must run under it's own user once it starts.

Thanks for all your help!

 

[cjard]

I dont, directly.. but as mentioned its a problem that comes up a lot because things like FTP servers cannot serve from shared/network drives.. Have a look to see what the recommended course of action is..

Perhaps your app can copy the file from the network to c:\temp every time it runs, if you can find no other solution

 

[Javier]

Hi i have a problem... i got an SQL Loader process executing well in a Web Sevice but it only get some records into the table, specifically 1425, there are over 400.000 records on my plain text file. What it can be? Thanks in advance!

 

[cjard]

What's the pattern? Is it the first 1425? Are there an even number of records missing (like, it does 1 then skips 280, then does 1 then skips 280)

 

[Javier]

Hi, thanks for your reply, my SQL Loader is working fine with my data text file if i doble click directly on the server .bat file, but when i execute in C# using the web service the .bat executes, the process is in the processes window, but my cmd stop, continue showing in the screen in black so i finish the process and when i finish it in my C# code where i have a "process" declared for that, the output shows me the SQL loader cmd lines, with no errors and only to 1425, always it says that it has execute just to 1425, doesn´t mather if i wait 1 minute or 1 hour in stoping the cmd. Sorry about my english...

I´m trying Sunopsis now but thanks in advance!