Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Get sql server to execute a dos script

Posted on 2011-09-27
8
292 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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