Solved

Get sql server to execute a dos script

Posted on 2011-09-27
8
285 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 1

Author Comment

by:hraja77
ID: 36710140
hi it just says that the agent is running under a local system account ?
0
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 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