Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
Ok,
So I haz lots of scripts that are generated by a team in my organisation which need to then be processed via SQLPlus Basically we/me get loads of emails with the script location @Z:/aFolder/aScript.sql; We then have to process them individually by opening up SQLPlus and pasting the file location in there ;" I have been looking into a way to automate this via either c# or VBA I think that the Windows Script Host Object Model option via VBA is a good one Example: Option Explicit Sub SQLPlus(strFilePath) Dim WShell As New WshShell WShell.Run "sqlplus " & strFilePath End Sub Sub test() Call ;") End Sub Only issue is that I get an error from passing that last file location in the "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" Am I missing a special character or something from this? I pass the @ sign into the string but its not recognised on the command line?? Any input greatly appreciated, thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
On Jan 30, 10:52*am, Scott Spence wrote:
Ok, So I haz lots of scripts that are generated by a team in my organisation which need to then be processed via SQLPlus Basically we/me get loads of emails with the script location @Z:/aFolder/aScript.sql; We then have to process them individually by opening up SQLPlus and pasting the file location in there ;" I have been looking into a way to automate this via either c# or VBA I think that the Windows Script Host Object Model option via VBA is a good one Example: Option Explicit Sub SQLPlus(strFilePath) * * Dim WShell As New WshShell * * WShell.Run "sqlplus " & strFilePath End Sub Sub test() * * Call ;") End Sub Only issue is that I get an error from passing that last file location in the "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" Am I missing a special character or something from this? I pass the @ sign into the string but its not recognised on the command line?? Any input greatly appreciated, thanks. Sometimes enclosing paths in strings helps. Maybe you can try Sub SQLPlus(strFilePath) Dim WShell As New WshShell WShell.Run "sqlplus " & strFilePath & """" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
Good idea John, tried it and the window just closes immediately
Thanks for the suggestion :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
Is the sqlplus window supposed to stay open ?
Is there an "exit" at the end of the script you're calling ? Tim On Jan 30, 9:05*am, Scott Spence wrote: Good idea John, tried it and the window just closes immediately Thanks for the suggestion :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
Yes and yes :)
Are these questions going to help you answer my question? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
If there's an exit at the end of your script then that would explain
why the sqlplus window closed. If you comment that out does it stay open? Tim On Jan 31, 7:21*am, Scott Spence wrote: Yes and yes :) Are these questions going to help you answer my question? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
It doesn't even open the script in SQLPlus
Like I said in the main post I get an error: "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" I have validated the location and file exist, it just looks like the parameter isn't passed in correctly for some reason If I just paste in the file location: @Z:/aFolder/aScript.sql; It runs fine but when I try pass it I get the error message without the @ in the command string "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
On Jan 31, 11:26*am, Scott Spence wrote:
It doesn't even open the script in SQLPlus Like I said in the main post I get an error: "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" I have validated the location and file exist, it just looks like the parameter isn't passed in correctly for some reason If I just paste in the file location: @Z:/aFolder/aScript.sql; It runs fine but when I try pass it I get the error message without the @ in the command string "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" Try Dim WShell As New WshShell WShell.Run "sqlplus " & " " & strFilePath End Sub The only addition from your first script is to put a space between the sql server path and the script that you are trying to execute -John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
There's already a space at the end of the server location
*WShell.Run "sqlplus "* But just to humour you I have added the extra space and I still get the same error Thanks anyway :) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
There's already a space at the end of the server location
*WShell.Run "sqlplus "* But just to humour you I have added the extra space and I still get the same error Thanks anyway :) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
Ok, this is now resolved [in a fashion]
What I'm doing is passing the file location parameter to a .bat file So I have my .bat file OracleSQL.bat which contains this code: @echo off sqlplus @%1 exit; I can now shell out to the batch file with the file parameter: Dim strBatchName As String strBatchName = "C:\location\of\bat\OracleSQL.bat Z:/aFolder/aScript.sql" Shell strBatchName Done :) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQLPlus script location using Windows Script Host Object Model
On Feb 2, 6:36*am, Scott Spence wrote:
Ok, this is now resolved [in a fashion] What I'm doing is passing the file location parameter to a .bat file So I have my .bat file OracleSQL.bat which contains this code: @echo off sqlplus @%1 exit; I can now shell out to the batch file with the file parameter: Dim strBatchName As String strBatchName = "C:\location\of\bat\OracleSQL.bat Z:/aFolder/aScript.sql" Shell strBatchName Done :) Glad that you found a work-around. I suspect that the problem lies in the area of permissions. The fact that you can type a string at the command prompt and have it work but not have identitical behavior when you use the same string with the script host run method suggests that something is preventing the script-host from accessing certain files. Either that or the directory that the script-host is trying to run the command in is a different directory from that where the .bat file runs -- though you seem to be passing fully-qualified paths so I don't see why that should be an issue. As an aside - have you checked out Power Shell yet? I have been dabbling in it recently and it really does seem to be more powerful than VBScript - although I haven't yet figured out how to call it seemlessly from VBA. A less kludgy solution to your problem would probably be possible with Power Shell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Windows Script Host in Excel VBA | Excel Programming | |||
PASSING PARAMETER FORM DOS script to Excel | Excel Programming | |||
Passing Data to a Line in a VBA Script | Excel Programming | |||
Pass a variable back to the host script? | Excel Programming | |||
Use Windows Script to run Windows Explorer Search? | Excel Programming |