Using an UNC name in wScript.Shell

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

David Webb-CDS

SSCoach

Points: 17398

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 · Answer 2

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: 65111 More actions · Answer 3

...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 · Answer 4

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