Solved

Get sql server to execute a dos script

Posted on 2011-09-27
8
276 Views
Last Modified: 2012-05-12
Hi,

I want to get sql server to kick off a bat file which i can do ok by setting up a job to run the o/s command "exec c:\test.bat".The problem is that this file copies a file out of a mapped networked drive which sql cannot see. Is there anywhere to just kick of the bat file on the o/s and get sql to ignore  what its doing or how to i get sql to see the mapped network drive

thanks
H
0
Comment
Question by:hraja77
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 36709942
sp_cmdshell
0
 
LVL 1

Author Comment

by:hraja77
ID: 36709982
sorry to make it clear i use:

xp_cmdshell 'c:\test.bat'

the test.bat is:

copy z:\CSS_db_%yyyymmdd%1349.BAK D:\mssql\mssql\Restore\cssprod.bak

but my output is:


Job 'Restore CSS Reporting' : Step 1, 'Copy Production CSS' : Began Executing 2011-09-27 15:21:18

output                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
C:\WINDOWS\system32>echo wscript.echo year(date) & right(100 + month(date),2) & right(100+day(date),2)   1>"C:\WINDOWS\TEMP\dateparts.vbs"
(null)
C:\WINDOWS\system32>for /F "tokens=1 delims=" %a in ('cscript //nologo "C:\WINDOWS\TEMP\dateparts.vbs"') do set yyyymmdd=%a
(null)
C:\WINDOWS\system32>set yyyymmdd=20110927
(null)
C:\WINDOWS\system32>copy z:\CSS_db_201109271349.BAK D:\mssql\mssql\Restore\cssprod.bak
The system cannot find the drive specified.
(null)
0
 
LVL 18

Expert Comment

by:x-men
ID: 36710018
the user running the service has to have permission on the share.

create a sql jog, give permissions for the user that runs the SQLAGENT service, on the share
0
 
LVL 1

Author Comment

by:hraja77
ID: 36710140
hi it just says that the agent is running under a local system account ?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Expert Comment

by:x-men
ID: 36710172
0
 
LVL 1

Author Comment

by:hraja77
ID: 36710202
hi
that link implies we shouldn't use a network account ??

Microsoft recommends that you do not use the Network Service account for the SQL Server or the SQL Server Agent services if an account with lesser privileges is available, because Network Service is a shareable account.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 36710318
It's true, the windows account running the .bat file must have permission to access the share (as well as some files on the C: and D: drives).  Check the Services Control Panel to see what windows account is assigned to the SQLAGENT service.


In addition, the account must have the share mapped to the Z: drive for that copy syntax to work. You can avoid this step by using UNC syntax.  It goes like this:


copy \\servername\sharename\CSS_db_%yyyymmdd%1349.BAK D:\mssql\mssql\Restore\cssprod.bak



UNC's are very useful for making bat files portable.  When different machines have different drive letters mapped to the shares, the UNC syntax still works.





Sure you can run the bat file from the OS.  Just logon and run it.  Or schedule it.  
 
0
 
LVL 18

Assisted Solution

by:x-men
x-men earned 250 total points
ID: 36710477
not the NETWORK SERVICES account, create normal user account (beaware of the password wxpiration policies) and assign it to the AGENT service; then give the permissions to the share.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now