Running Command Line call from stored procedure?

[ghost2]

Hello all. I have a batch file that I want to call based on criteria in a stored procedure. How do I within an IF statement switch to the command shell and run command shell lines? Also is there a way to pass into one of the command shell lines a variable? I have a program that auto emails through command shell and within double quotes I have a message. I need to pass in a variable into that message here is the command shell line for example:

postie.exe -mx -host:%DNS_SRV% -to:test@bubble.net -from:%computername%@bubble.com -s:"Confirmed Orders File Issue" -msg:"Confirmed Order Issue Order Number [THIS IS WHERE I WANT TO PASS IN A VARIABLE FOR ORDER NUMBER]."

 

[JayKusch]

I am passing in the invoice number as a string, you may have to do some formatting if you pass an Int in. Here is the initial code I beleive will get you started!!!

declare @SQL_Command VarCHar(500)
declare @ImpInvoice VarChar(10)

SET @ImpInvoice = 'XX04122004'
SET @SQL_Command = 

'EXEC master..xp_cmdshell ' + '''' + 

'postie.exe -mx -host:%DNS_SRV% -to:test@bubble.net -from:%computername%@bubble.com -s:"Confirmed Orders File Issue" -msg:"Confirmed Order Issue Order Number ' + @ImpInvoice

+ ''''

print @SQL_Command

EXEC (@SQL_Command)

Thanks

J. Kusch

 

[ghost2]

Jay if I have a bat file, how can I get that bat file code into my procedure? For example here is the bat file code which has some single quotes in it. How can I run this code within a stored procedure? Thanks so much for your help. Then I will pass how you showed the variable.


for /f "delims=: tokens=2" %%I in ('ipconfig /all^|find /I "DNS Servers"') do call :SETDNS %%I
@echo DNS server is %DNS_SRV%
goto ONE

:SETDNS
if NOT {%1}=={} (
set DNS_SRV=%1
@goto :EOF
) ELSE goto :EOF

ONE

c:
cd C:\Program Files\Postie
postie.exe -mx -host:%DNS_SRV% -to:test@bubble.com -from:%computername%@bubble.com -s:"Web Order Processing" -msg:"Web Order Processing has completed on SQL Server."

 

[JayKusch]

I see nothing in you bat file that is using a user supplied parameter ... If this is the case ... can you not just fire off the bat file "as is" within the store procedure? Something like ...

EXEC master..xp_cmdshell 'C:\MyBat.bat'
[code]

If this is not the case ... direct me to the parameter and there are ways of creating this bat on the fly too!


Thanks

J. Kusch

 

[ghost2]

I do need a parameter in the msg piece like in my first posting.

 

[JayKusch]

Then you can fire it off from w/in the SP as I posted above ...

EXEC master..xp_cmdshell 'C:\MyBat.bat'

Thanks

J. Kusch

 

[ghost2]

Can I pass a parameter that way though with just calling the bat file?

 

[JayKusch]

Where in your bat example is the parameter? If you can point it out I may be able to work something.

Thanks

J. Kusch

 

[ghost2]

Here it is resent with the actual bat I think I took another example:

for /f "delims=: tokens=2" %%I in ('ipconfig /all^|find /I "DNS Servers"') do call :SETDNS %%I
@echo DNS server is %DNS_SRV%
goto ONE

:SETDNS
if NOT {%1}=={} (
set DNS_SRV=%1
@goto :EOF
) ELSE goto :EOF

ONE

c:
cd C:\Program Files\Postie
postie.exe -mx -host:%DNS_SRV% -to:test@bubble.net -from:%computername%@bubble.com -s:"Confirmed Orders File Issue" -msg:"Confirmed Order Issue Order Number [THIS IS WHERE I WANT TO PASS IN A VARIABLE FOR ORDER NUMBER]."