Solved

Get sql server to execute a dos script

Posted on 2011-09-27
8
296 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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