Using sql to execute a batch file

Is it possible to use sql to execute a batch file? I would like to
execute the following "C:\BTW\bartend.exe /f=C:\BTW\Toolbox\Formats
\carnum.btw /p", 6

Thanks,

Matt

Re: Using sql to execute a batch file

You can look at xp_cmdshell. Note that no program run that way can
open any sort of window, or prompt the "user" for anything at all.

You may also want to look into running it as a job.

Roy Harvey
Beacon Falls, CT

On Thu, 09 Aug 2007 15:44:36 -0000, mcolson
wrote:

>Is it possible to use sql to execute a batch file? I would like to
>execute the following "C:\BTW\bartend.exe /f=C:\BTW\Toolbox\Formats
>\carnum.btw /p", 6
>
>Thanks,
>
>Matt

Re: Using sql to execute a batch file, xp_cmdshell

On Aug 9, 1:26 pm, Roy Harvey wrote:
> You can look at xp_cmdshell. Note that no program run that way can
> open any sort of window, or prompt the "user" for anything at all.
>
> You may also want to look into running it as a job.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 09 Aug 2007 15:44:36 -0000, mcolson
> wrote:
>
> >Is it possible to use sql to execute a batch file? I would like to
> >execute the following "C:\BTW\bartend.exe /f=C:\BTW\Toolbox\Formats
> >\carnum.btw /p", 6
>
> >Thanks,
>
> >Matt

When I use the xp_cmdshell to try and run the bat file, i get an
output saying access denied. It should be noted that I am using sql
server 2005 express. Is this why. When I try to copy the file to
another location, i get 0 copied. I am able to dir the directory just
fine.

Re: Using sql to execute a batch file, xp_cmdshell

On Thu, 09 Aug 2007 21:23:07 -0000, mcolson
wrote:

>When I use the xp_cmdshell to try and run the bat file, i get an
>output saying access denied. It should be noted that I am using sql
>server 2005 express. Is this why. When I try to copy the file to
>another location, i get 0 copied. I am able to dir the directory just
>fine.

I am not familiar with Express, but I don't think it is any different
in this area. What is important to remember is that any command run
through xp_cmdshell is running under the account designated for that,
and that is the account that needs the permissions. Running
xp_cmdshell 'set' and inspecting the results should tell you the
account being used.

While it does not appear to be your problem it is worth noting also
that xp_cmdshell runs in a context without any mapped drives, so any
files on a network drive must be referenced using a UNC path of the
form \\servername\sharename\folder...

Roy Harvey
Beacon Falls, CT

Re: Using sql to execute a batch file, xp_cmdshell

On Aug 10, 3:23 pm, Roy Harvey wrote:
> On Thu, 09 Aug 2007 21:23:07 -0000, mcolson
> wrote:
>
> >When I use the xp_cmdshell to try and run the bat file, i get an
> >output saying access denied. It should be noted that I am using sql
> >server 2005 express. Is this why. When I try to copy the file to
> >another location, i get 0 copied. I am able to dir the directory just
> >fine.
>
> I am not familiar with Express, but I don't think it is any different
> in this area. What is important to remember is that any command run
> through xp_cmdshell is running under the account designated for that,
> and that is the account that needs the permissions. Running
> xp_cmdshell 'set' and inspecting the results should tell you the
> account being used.
>
> While it does not appear to be your problem it is worth noting also
> that xp_cmdshell runs in a context without any mapped drives, so any
> files on a network drive must be referenced using a UNC path of the
> form \\servername\sharename\folder...
>
> Roy Harvey
> Beacon Falls, CT

Sql is setup as a Network Service. I could change it to a local user,
but would prefer not to. I'm trying to setup a credentials named
"Matt". In here I have entered the login and password for an accepted
user of the file. How do I tell the stored procedure to use this
credential when I am running the bath file?

Thanks,

Matt

Re: Using sql to execute a batch file, xp_cmdshell

On Wed, 15 Aug 2007 17:28:16 -0000, mcolson
wrote:

>Sql is setup as a Network Service. I could change it to a local user,
>but would prefer not to. I'm trying to setup a credentials named
>"Matt". In here I have entered the login and password for an accepted
>user of the file. How do I tell the stored procedure to use this
>credential when I am running the bath file?

I have no idea, sorry.

Roy Harvey
Beacon Falls, CT

Re: Using sql to execute a batch file, xp_cmdshell

mcolson (mcolson1590@gmail.com) writes:
> Sql is setup as a Network Service. I could change it to a local user,
> but would prefer not to. I'm trying to setup a credentials named
> "Matt". In here I have entered the login and password for an accepted
> user of the file. How do I tell the stored procedure to use this
> credential when I am running the bath file?

Have a look at xp_cmdshell and sp_xp_cmdshell_proxy_account in Books
Online. By setting up a proxy account and not run as sysadmin, you may
be able to work around the issue.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx