Using an UNC name in wScript.Shell

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  April 19, 2007 at 2:23 pm

  #68362

  I'm trying to run a program from within a stored procedure.  The program is not located on the same server as the database the stored procedure is in.  So when I try to run the following code, the stored procedure hangs, and never executes the sp_oamethod step.  Our network person suggested that I make the following change, but that doesn't help either.

   

  DECLARE @obj    INT 

  DECLARE @ProgramToRun VARCHAR(255)

   

  SET @ProgramToRun = 'Run("\\myserver.test.system\test_exec\FlatFileBuildTest.exe")'

   

  EXEC sp_oacreate 'wScript.Shell', @obj out

  EXEC sp_oamethod @obj, @ProgramToRun

  EXEC sp_oadestroy @obj

   

  Our network person suggested that I make the following change, but that doesn't help either:

   

  DECLARE @obj    INT 

  DECLARE @ProgramToRun VARCHAR(255)

   

  SET @ProgramToRun = 'Run("'">\\myserver\test_exec\FlatFileBuildTest.exe")'

   

  EXEC sp_oacreate 'wScript.Shell', @obj out

  EXEC sp_oamethod @obj, @ProgramToRun

  EXEC sp_oadestroy @obj

   

  If, however, I put the executable on the same server as the database that holds the stored procedure, the program executes.  This is what the code looks like.

   

  DECLARE @obj    INT 

  DECLARE @ProgramToRun VARCHAR(255)

   

  SET @ProgramToRun = 'Run("c:\test_exec\FlatFileBuildTest.exe")'

   

  EXEC sp_oacreate 'wScript.Shell', @obj out

  EXEC sp_oamethod @obj, @ProgramToRun

  EXEC sp_oadestroy @obj

   

  Now as near as I can tell, the only difference between the logic that works and the logic that doesn't is the location of the executable.  I'm beginning to think that you can't run wScript.Shell against anything that isn't on the computer that you're running the process on, but I don't seem to be able to find confirmation of that.  Can you tell me if that's the case?

   

  Thanks,

  Mattie

   

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  April 19, 2007 at 4:37 pm

  #701595

  Does the account that SQL Server is running under have permission to the file on the other server?

  ---

  And then again, I might be wrong ...
  David Webb

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  April 20, 2007 at 6:26 am

  #701695

  David,

  Thank you so much for responding, and I apologize in advance for the ignorance I'm about to display.  I don't know how to tell what account SQL Server is running under (I'm just a developer), and our network expert tells me 'I don't know SQL', and of course, we don't have a DBA.  (I hope Sergiy isn't reading this; I know my posts are going to cause him to have a stroke one day.)

  So if you'd be willing to tell me how to find out what account SQL Server is running under (do I look in EM, or Services?), and then where to check on the destination server for that account, I promise never to ask those two questions again.

  By asking about permissions (which was my first thought), you seem to be implying that referencing a remote server in this manner is possible, yet in every site I could find on the web, I never saw that syntax used, everything used drive names.

  I really appreciate your help on this.

  Mattie

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  April 20, 2007 at 8:54 am

  #701746

  ...what account SQL Server is running under...

  In EM

  Locate the server

  Right click on the server

  Select properties

  Select the Security tab

   Startup service account will tell you which account sql is running under

  If System account is selected then you will no be able to access the network, otherwise check permissions as David stated.

  Also make sure that the program does not produce any prompts etc as this may cause the job to pause (not sure on this)

  Far away is close at hand in the images of elsewhere.
  Anon.

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  April 20, 2007 at 10:10 am

  #701771

  Hi David, thanks for responding.

  There is a specific startup service account, it is not the system account.  (For those of you following along at home, be aware that to find this out, you have to view this tab from the server.  It does not display at another machine.)

  When I check the security on the executable (inherited from the folder), it shows that same user account has full control, and just to be sure, I gave full control to Everyone.  No difference.  Is there somewhere else I should be looking on the server that has the executable.  Is there another file (wscript.exe?) that needs permission.  I tried giving Full Control to Everyone on wscript.exe, and that didn't help.  (I have since removed Full Control.)

  There aren't any prompts, so that's not an issue.  The code runs just fine when it runs from the server the database is on, and I use a drive letter in the path. 

  Mattie