Hi,
all I want to do is kick off a windows executable if a particular file exists. I have managed to come up with a script from the internet ( Huge thanks to nigelrivett.net) which actually checks for the file and then prints a message based on whether it's there or not.
My problem is trying to get the last part

What I have so far is :
****************

declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'

declare @i int
declare @File varchar(1000)

select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1

from here is my botched attempt

declare @FileName1 varchar(1000)
declare @path varchar (1000)
exec master..sp_execute
@path = 'c:\'
@FileName1= 'test.bat'
else
print 'no'

********************

I have played around with sp_execute but I just can't get it through the gate. I have just reacted to the error messages and tried to sort it out as I went but no joy.
In case anyone caqn't guess from the copied code, I know Zero about SQL but have been given at task to make something work.

If anyone could help me I would be eternally grateful !

Thanks

Jeff

To make it go, you'll need to use another XP, something likeAs they used to say in the margins of old maps, "There be dragons here"... Be careful to test your process thoroghly, since you won't always get exactly what you expect when you run things this way due to the fact that xp_cmdshell runs in an odd security context and an unusual machine context. This gets really complicated to explain, so I'll skip the gory details for now, other than to repeat: test carefully.

If (when?) you have more questions, feel free to post them too.

-PatP

Thanks Pat !!
Don't worry, I will test it thoroughly as I don't want to be the one explaining to the MD what happened to our database

After a bit of work I am a little closer but I think I didn't ask the correct question in the first place.
I actually want to kick off an installed program. I have created batch files that if clicked on manually do what I want. Trying to get the script to kick of the batch file seems to be somewhat harder.

*********************************
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'

declare @i int
declare @File varchar(1000)

select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
EXECUTE master.dbo.xp_cmdshell 'c:\test.bat'

else
print 'nothing there'

********************************
goes to kick off (in this case for testing) Outlook Express.

What I get in the results pane of query analyser is :
********************************
NULL
C:\WINDOWS\system32>call c:\kickoff.bat
NULL
C:\WINDOWS\system32>c:\progra~1\outloo~1\msimn.exe
NULL
C:\WINDOWS\system32>rem dir c: >dir.txt
NULL
C:\WINDOWS\system32>pause
Press any key to continue . . .
NULL
**********************************

Which is every step in the batch files..unfortunately OE doesn't launch.

thanks

Jeff

Ah, but it does launch Outhouse Express! The problem is that you can't see it! If you check the processes tab in the Windows Task Manager.

SQL Server runs as a service. All of the programs that are launched via xp_cmdshell run in the context of the service. There isn't a GUI interface for services (you have to write an SCP or Service Control Program to give a service a GUI).

You really want something quite different than xp_cmdshell. Check out xp_sendmail to see if that will do what you want.

For what it is worth, this thread has drifted a long way from being "on topic" in the SQL forum. I'd strongly suggest that either Tony move this thread for us, or you just start a new thread in the SQL Server forum.

-PatP

Pat, sorry about being off topic .. I deliberated a while before posting here as I thought it was related more to scripting sql , point noted

I saw that OE was running ... I didn't know why the damned thing wasn't showing though!!!
The actual program I want to launch is the LaunchIt Utility that will launch a Navision code unit/form if the presence of a file has been detected ( which will be FTP'd in ).I will schedule the job to run every X hours and it will check for the file's existance and then go on if required.
I have tried to get a batch file to kick off ( which works fine if just double clicked ) but obviously there is an issue with the security context when used with an extended stored procedure.

thanks hugely for your help !

Jeff

Ok, now we do yet another "about face" one this one!

If what you really want is to fire off Launch-It in order to actaully launch an Navision form, then I'd take still another route! I'd schedule Launch-It as a SQL Agent Job, then launch that job within your code. There is a recent thread in the MS-SQL forum that explains this SQL Agent / sproc process in some detail.

-PatP

Thanks pat,
I wouldn't say an About Face as I did mention "goes to kick off (in this case for testing) Outlook Express." My sincere apologies for any misunderstanding as I (obviously) know no scripting as it's not part of my usual job. In fact, this is the first time in over a year.

I will try to find that thread, Thanks !!