September 26, 2005 at 1:24 pm
I have a batch file that has the following commands in it...
net use p: /d
net use p: \\sername\sharename
When I run the batch file on its own, either by double-clicking on it or through the command prompt, it works fine...but when i try to run the batch file as an operating command in a SQL job, it fails with the following error...
net use p: /dThe network connection could not be found.
More help is available by typing NET HELPMSG 2250.
C:\WINNT\system32>net use p:
\\servername\sharename System error 85 has occurred. The local device name is already in use.
For some reason, it can't see p: to disconnect it.
I get this same problem on SQL 7 and SQL 2000, NT 4 and Windows 2000. It used to work and the only thing I can think of that might have changed is Active Directory and Windows Updates.
Does anyone have any ideas on this?
Thanks!!
September 26, 2005 at 1:47 pm
Dunno,
But I never use drive definitions for network resources in batch files etc.
Use the UNC \\Servername\sharname. instead.
September 27, 2005 at 1:35 am
Hi.
Something obvious I know, but Is the same user / server used for the batch run as the manual exec ?
I have made this mistake previously, and tried to run the drive map on a server with no access / connection to the target drive. Also using a signon with insufficient access to change drive mappings on the particular server.
September 27, 2005 at 5:46 am
Hi,
I've had to do similar things, due to different domains that don't have trust. below see a piece of code that is used - note there are some UGLIES here, like a hardcoded password - this is only used in test.
******code*****
set nocount on
DECLARE @new_backup_folder varchar(250)
DECLARE @sql varchar(250)
DECLARE @NetUseSQL varchar(250)
DECLARE @NetDisSQL varchar(250)
DECLARE @dateformat char(8)
DECLARE @rc int
DECLARE @drivename varchar(255)
DECLARE @localbackupDir varchar(250)
DECLARE @DBID INT
DECLARE @DBNAME VARCHAR(128)
SET @DBNAME = DB_NAME()
SET @DBID = DB_ID()
SET @rc = 0
set @localbackupDir = 'd:\backups'
set @NetUseSQL = N'master.dbo.xp_cmdshell ''net use O: \\AServerName\scrubbed_backups$ /user:dev\devsql_temp letmein'''
set @NetDisSQL =N'master.dbo.xp_cmdshell ''net use O: /d'''
exec (@NetUseSQL)
set @drivename = 'o:\'
create table #fileexists (fileexist int, FileIsDir int,ParDirEx int)
insert into #fileexists (fileexist,FileIsDir,ParDirEx) exec master..xp_fileexist @drivename
if (select FileIsDir+ParDirEx from #fileexists) <> 2
begin
print '*** ERROR ***'
print '*** '+@drivename+' Not Mapped - please review and fix ***'
set @rc = 1
goto Error
end
delete from #fileexists
insert into #fileexists (fileexist,FileIsDir,ParDirEx) exec master..xp_fileexist @localbackupDir
if (select FileIsDir+ParDirEx from #fileexists) <> 2
begin
print '*** ERROR ***'
print '*** '+@localbackupDir+' Local Backup Directory Not Found - please review and fix ***'
set @rc = 1
goto Error
end
select @dateformat = convert(char(4),datepart(year,getdate()))
+substring(convert(char(3),(datepart(month,getdate()) +100) ),2,2)
+substring(convert(char(3),(datepart(day,getdate()) +100) ),2,2)
select @new_backup_folder = @drivename+@dateformat
select @new_backup_folder
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\Contact*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\Redbaron*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\Sessionhandler*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\Tilos*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\veccase*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\filetransfer*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\centaur*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\CDV*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
set @localbackupDir = 'c:\db_backups'
select @sql=N'master.dbo.xp_cmdshell '' XCOPY /Z /Y '+@new_backup_folder+'\GCMS*.bak '+@localbackupDir+'\*.bak'''
select @sql
exec (@sql)
error:
exec (@NetDisSQL)
drop table #fileexists
if (@rc <> 0 )
RAISERROR ('Copying Source Backups Failed', 16, 1, @DBID, @DBNAME)
GO
******code*****
September 28, 2005 at 5:35 am
The account the sql server runs under must have sufficient rights to execute these commands, whilst you might have permission it doesn't mean the server user account has. I'd look to this as your first step. I do lots of cross server stuff at o/s level from sql server but sometimes the permissions for the accounts can be a pain to set up. W2003 has more lockdown on share permissions than W2k so what worked in w2k might not work with w2003 - if that helps at all?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 28, 2005 at 6:57 am
thanks for all the tips so far...i indeed had a look at the permissions for the account that sql server uses and that part is ok. what seems to happen is the first command that disconnects the p: drive if it's mapped, doesn't work. then when it gets to the second command that is supposed to re-map the p: drive it gives the error that it's still in use (because obviously it is)
i've done some playing around and if i make two batch files and run them in two steps...it sort of works. step 1 disconnects the drive, then step 2 re-mapos the drive but even though it's mapped the drive, the step never 'completes' the job just says executing until i cancel it.
September 28, 2005 at 10:06 am
Since your very first post I was watching that situation, because you're using the
net use p: /d
which assumes that the [P:] drive is not longer used or open by any processes.
However there is a workaround. You can use:
net use p: /d /y
The /y option implies a YES, which means that you want to disconnect the drive no matter if it is still in use.
Actually, this is not a workaround, THIS SHOULD SOLVE YOUR PROBLEM, but I prefer to look it as a workaround, because you have to be sure that there is not any relevant process accesing the mapped drive, at that time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply