?
Solved

Batch file execution problem

Posted on 2005-05-04
33
Medium Priority
?
1,169 Views
Last Modified: 2008-01-09
I have two systems A and B which are in the same nerwork.
I have written a stored procedure in  data base on System A to execute a batch file on system B using the xp_cmdshell Stored procedure.
The batch file executes the commands but when the command "e:" in batch file executes it goes to the E: command prompt of System A. I need to go to command prompt of System B.

Please let me know how to achieve this.

The code in Stored procedure on System A is:

EXEC master..xp_cmdshell '\\System B\E\Express\Jobs\batchfile.bat'

Batch File Code(on System B):
E:
CD MyFolder
Batcfile2.bat param1


I am not able to execute batch file 2.bat on E drive of system B from System A.
Batchfile2.bat can execute only under E:\Myfolder as it is dependent on some settings.

Request you to kindly provide some solution.
0
Comment
Question by:ivega
  • 16
  • 14
  • 2
  • +1
33 Comments
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13929530
One option would be to dynamically map the drive letter in the batch file.

Batch File Code(on System B):

@echo off

setlocal

set unc=\\System B\E
set drv=

for /f "tokens=2" %%a in ('net use * "%unc%" ^| findstr " is now connected to "') do set drv=%%a

if "%drv%"=="" echo Could not map to %unc%&goto :EOF

%drv%
cd /d %drv%\MyFolder
call Batcfile2.bat param1

net use %drv% /del

Good Luck,
Steve
0
 
LVL 5

Expert Comment

by:burningmace
ID: 13929789
You can alter the program so that you access the E drive via a shared folder.
For example, share the folder as MyDrive... then set the batch program to access the file via \\ServerName\MyDrive\file.ext

You might have to enable "Allow users to alter my files/folders" in the sharing options... not sure if Windows bypasses that setting if you access the shared folder locally using the server name in the path.
0
 

Author Comment

by:ivega
ID: 13930006
Hi Steve,

It worked. Thanks a Lot. But I need to give login and password during mapping in net use command. Also i need to give persistent as yes. Each time I need same drive like g:.

Please give me syntax.

I believe the below code searches for next available drive. Please correct me if I am wrong.
for /f "tokens=2" %%a in ('net use * "%unc%" ^| findstr " is now connected to "') do set drv=%%a

Once again thanks a lot.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 5

Expert Comment

by:burningmace
ID: 13930047
In regards to my above comment, I did some testing...
You can simply do the following:

\\ServerName\MySharedFolder\2.bat param1

Unfortunately, all commands execute on the computer which runs the batch file in the first place. If you want to read from or write to any files, they have to be in that shared folder or one of its subfolders.

For example...

cd Logs
del yesterday.log
rename today.log yesterday.log

Would have to become...

cd \\ServerName\MySharedFolder\Logs\
del yesterday.log
rename today.log yesterday.log

Also, you have to allow files to be edited/deleted via the shared in this instance.

If you want to do things like shutdowns, the commands are executed locally, so this would shut down the computer which ran the batch file.
Have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;317371&sd=tech to see how you could turn off a computer remotely.
Of course, you probably don't want to do that, I wanted to show you that certain commands can be executed remotely.

If you've got any questions, don't hesitate to ask!
0
 

Author Comment

by:ivega
ID: 13930193
Hi burningmace,

Thanks for the solution. The other solution actually just suits our requirement.
Thank you very much.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13930353
You could do two things.

1) Run the SQL server service under a user account that has rights to the machine (instead of SYSTEM).

2) The other is as you said to supply a username and password. To do that you'd modify the for statement:

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username ^| findstr " is now connected to "') do set drv=%%a

To add the persistent switch you'd do this:

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username /persistent:YES ^| findstr " is now connected to "') do set drv=%%a

If you always want to use G: you'd replace the asterick (*) with g: like so:

for /f "tokens=2" %%a in ('net use g: "%unc%" password /user:username /persistent:YES ^| findstr " is now connected to "') do set drv=%%a



0
 

Author Comment

by:ivega
ID: 13931349
Hi Steve,

Sorry to bug you again. I am getting error could not map to \\System B\E. I gave correct values to user name and password and I executed the commands in  batch files multiple times. Below are the commands.
@echo off

setlocal

set unc=\\System B\E
set drv=

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username /persistent:YES ^| findstr " is now connected to "') do set drv=%%a

if "%drv%"=="" echo Could not map to %unc%&goto :EOF

%drv%
cd /d %drv%\MyFolder
call Batcfile2.bat param1

net use %drv% /del

Please help.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13931761
I was wondering about your UNC. Shouldn't it be:

\\System B\E$

E$ would be the share for the E drive.

A good test would be to try to do a dir statement from a cmd session on your machine:

dir "\\System B\E$"

Try this:

@echo off

setlocal

set unc=\\System B\E$
set drv=

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username ^| findstr " is now connected to "') do set drv=%%a

if "%drv%"=="" echo Could not map to %unc%&goto :EOF

%drv%
cd /d %drv%\MyFolder
call Batcfile2.bat param1

net use %drv% /del

I took the persistent out of the net use. Since you are removing the net use drive letter there is no need to persist it on reboot.
0
 

Author Comment

by:ivega
ID: 13931865
Actually e drive is not shared for all. So it is \\System\E.
Commands above worked fine. But  Whenever I executed the commands for mapping it gave me a new drive letter.g,h,i...........................w.
Now I get System 85 error.

System error 85 has occurred.
The local device name is already in use.

How can I fix and prevent this in future.
Please help.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13931878
When you use the net use command from the command line what do you receive?

net use * "%unc%" password /user:username

0
 

Author Comment

by:ivega
ID: 13931931
I get the below error message.

There are no available drive letters left.

More help is available by typing NET HELPMSG 3920.
0
 
LVL 30

Accepted Solution

by:
SteveGTR earned 500 total points
ID: 13932831
Sounds like you've overloaded the drive letters. Type net use to see the mappings. Delete mappings that are not necessary. My guess is that in developing this process with the persistent option you've assigned all the drive letters for the system account.

The best way to do this is by modifying your batch file or creating another one and executing it from Query Analyzer.

@echo off

net use >c:\netuse.dat

Then inspect the c:\netuse.dat.

Create another batch file to remove the drive letters that are not necessary and execute it from Query Analyzer.

@echo off

net use g: /del
net use h: /del
etc...

You might want to put some debugging code in your original batch file processing to assure that the net use /del line is being called:

@echo off

setlocal

set unc=\\System B\E$
set drv=

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username ^| findstr " is now connected to "') do set drv=%%a

if "%drv%"=="" echo Could not map to %unc%&goto :EOF

echo [%date%:%time%] Drive letter %drv% assigned.>>c:\netuse.log

%drv%
cd /d %drv%\MyFolder
call Batcfile2.bat param1

echo [%date%:%time%] Deleting drive letter %drv% assigned.>>c:\netuse.log
net use %drv% /del >>c:\netuse.log

0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13932833
Change the unc to \\System B\E in the last portion of code.
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 13934291
FYI - I can't make any improvements to the batch file, SteveGTR's code is as impeccable as ever!

I find the PushD and PopD commands very helpful when dealing with the thorny issue of drive letters.
To use them, specify your Net Use command without a drive letter, so that you are authenticated but not mapping a drive.
Then use
      PushD \\server\share\path1\path2
This maps the first free drive (counting back from Z:) and changes to the directory specified.
When you're finished you just use
      PopD
to unmap the drive and take you back to the directory you were in initially.

You can also nest PushD's and PopD's to change to multiple directories (and back again).

HTH,

Wallsy
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13935446
Wallsy, I didn't know you could do that. Pretty cool. Its like a net use on a stack. The only problem I could see here is that there is no way to provide username/password information to override the default authentication.
0
 

Author Comment

by:ivega
ID: 13937298
Hi Steve,


Thank you for helping me. THe problem I am facing now is that when deleting drive letters g: , h: through net use is i get the below message:

The network connection could not be found.
More help is available by typing NET HELPMSG 2250.

the net use.bat does not show these drive letters i.e.mappings.

But when I hard code these drive letters in my batch file and try to execute I get the
below message
Could not map to \\exptrain\E
i.e.
System 85 error
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13937628
The problem could be that the drive you are removing is the current drive. Try this:

@echo off

setlocal

set unc=\\System B\E
set drv=

pushd .

for /f "tokens=2" %%a in ('net use * "%unc%" password /user:username ^| findstr " is now connected to "') do set drv=%%a

if "%drv%"=="" echo Could not map to %unc%&goto :EOF

echo [%date%:%time%] Drive letter %drv% assigned.>>c:\netuse.log

%drv%
cd /d %drv%\MyFolder
call Batcfile2.bat param1

popd

echo [%date%:%time%] Deleting drive letter %drv% assigned.>>c:\netuse.log
net use %drv% /del >>c:\netuse.log

Can you also clear the netuse.log file and include it in your next post?

Thanks :)
Steve
0
 

Author Comment

by:ivega
ID: 13937977
I am not able to map. Same system 35 error at time of mapping.
It says drive letters are already in use. Log filies does not have these drive letters.
I cannot delete these drive letters nor map to these letters.
Should I restart the server.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13938245
Did you try to create the batch files to identify the net use drives and run them via Query Analyser as I suggested above? See my post with the timestamp  05/04/2005 10:32PM EST.
0
 

Author Comment

by:ivega
ID: 13938456
netuse.dat does not show any of these drive letters or the \\System B\E mapping details.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13938721
So when you create a batch file and run it from Query Analyzer:

@echo off

net use >c:\netuse.dat

c:\netuse.dat does not contain the drive mappings? What does it contain? Remember, you must run it from within Query Analyzer via xp_cmdshell.
0
 

Author Comment

by:ivega
ID: 13939736
The contents of  netuse.dat:
New connections will not be remembered.


Status       Local     Remote                    Network

-------------------------------------------------------------------------------
Unavailable  B:        \\System C\e$                Microsoft Windows Network
The command completed successfully.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13940115
So when you create a batch file ran run it from Query Analyzer:

@echo off

net use * "\\System B\E" password /user:username >c:\netuse.dat 2>&1

What does c:\netuse.dat contain?
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13940212
What account is the MSSQLServer service running under? The is in Control Panel --> Administrative Tools --> Services, MSSQLServer --> Action --> Properties --> Log On.

Is it Local System Account or This account (another user). My SQL server service runs under my account. I tried setting it to Local System Account and the "EXEC master..xp_cmdshell '\\System B\E\Express\Jobs\batchfile.bat" got an access denied.
0
 

Author Comment

by:ivega
ID: 13940752
It is running under this account.
0
 

Author Comment

by:ivega
ID: 13940801
@echo off

net use * "\\System B\E" password /user:username >c:\netuse.dat 2>&1

The batch file containing the above code gives the below output:


There are no available drive letters left.

More help is available by typing NET HELPMSG 3920.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13941502
I don't now what "this" account is... I searched on the message you are receiving and found the following:

http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=15580

I tried to back up my databases to a file on a network share and restore the databases on the secondary server. I tried to use the Net Use command to create the share on the target server, as in

NET USE * \\PS5\C /USER:ONEDEV\{domain id}
      {password} /PERSISTENT:NO

But I keep getting the message There are no available drive letters left. Enterprise Manager shows many available drives. How can I fix this problem?

Just back up your databases directly to the Uniform Naming Convention (UNC) path. SQL Server doesn't see mapped drive letters the same way a user does. You need to grant the SQL Server service account full control permissions on the share \\PS5\C, make a new share, or make the SQL Server service account a member of the administrators group on machine PS5.



0
 

Author Comment

by:ivega
ID: 13941784
This account: admin account.

SQL service account is running in admin account and the same account has full control on the  share.
Still not working and  i get same error.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13941906
How about trying to set the SQL Server service to run under your account. You'll have to restart the service before do the test.
0
 

Author Comment

by:ivega
ID: 13946207
I cannot do that. server has to run under the same admin account as their are other jobs running in that account.
Any alternatives is highly appreciated.
Thanks.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13946623
For test purposes.
0
 

Author Comment

by:ivega
ID: 13949776
it is working after server reboot. Just one last help from you.
The drive letter assigned is not getting deleted because when the batch file executes the delete command it requests Yes/ no from user to delete the drive as it says that still some connections are open.
How to pass yes and thus delete the assigned drive letter.
since this batch file is executed from query analyser, how to include yes during drive deletion.

0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 13950754
My post from 05/05/2005 11:39AM EST has a push . and pop that should have taken care of that. Either that our you could just change the drive to c: before doing the net use /del.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of my most closely kept secrets is revealed in this discussion How to output text on the same line This question was recently posted in EE by Simon336697 (http://www.experts-exchange.com/Programming/Languages/Scripting/Shell/Batch/Q_2459…
Being a system administrator some time we require to do things remotely, one of them is installing software. Here I am going to tell you how to install software through wmic (Windows management instrument console). I am not at all saying that this i…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question