Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get sql server to execute a dos script

Posted on 2011-09-27
8
Medium Priority
?
312 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
[X]
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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 1000 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 1000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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