Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 30th 12, 04:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default 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   Report Post  
Old January 30th 12, 05:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 274
Default 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   Report Post  
Old January 30th 12, 06:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default 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   Report Post  
Old January 31st 12, 12:46 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2005
Posts: 114
Default 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   Report Post  
Old January 31st 12, 04:21 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default Passing SQLPlus script location using Windows Script Host Object Model

Yes and yes

Are these questions going to help you answer my question?


  #6   Report Post  
Old January 31st 12, 04:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2005
Posts: 114
Default 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   Report Post  
Old January 31st 12, 05:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default 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   Report Post  
Old January 31st 12, 07:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 274
Default 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   Report Post  
Old February 1st 12, 10:54 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default 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   Report Post  
Old February 1st 12, 10:57 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 16
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Windows Script Host in Excel VBA jasontferrell Excel Programming 10 November 4th 09 10:27 PM
PASSING PARAMETER FORM DOS script to Excel BruceWyne Excel Programming 4 November 3rd 06 04:02 AM
Passing Data to a Line in a VBA Script Danny Young Excel Programming 1 October 12th 06 06:14 AM
Pass a variable back to the host script? mb Excel Programming 2 October 29th 05 07:02 PM
Use Windows Script to run Windows Explorer Search? Ian Elliott[_3_] Excel Programming 0 January 12th 04 06:03 PM


All times are GMT +1. The time now is 05:08 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017