Problem with Execute Process Tasks
-
I've added an Execute Process Task to one of my DTS Package, which executes .bat script, which executes itself an .exe program which sends an e-mail.
But it never works, except if I re-select the .bat script before executing the package.
And even if the script hasn't been modified.
It doesn't seem to be an issue of rights, since it's the same problem even if it's executed interactively (instead of being batched).
Has anyone got an explanation ?
-
I'm not certain. Is the bat file on a network drive and do you refer to it using a mapped drive letter (this may cause problems)? Does the Win32 process have the entire path for the batch file? I would wonder if the "Current folder" has changed from running the dts package in different sessions.
I hope this helps. I've never used this task.
Russ
Russel Loski, MCSE Business Intelligence, Data Platform
-
The .Bat file is on the local drive of my server.
I refer it with its entite path and name (c:\scripts\lot2.bat)
The strange thing is that nothing seems to have changed when I re-select the file, so why does it works after that ?
By the way, SQL Server and SQL Server Agent are both running under the Administrator account, and yes, this account has a full control of this directory and its content...
-
Does the package have a Dynamic Properties task or ActiveX Script task that execute prior to the Execute Process Task ?
If so, have you verified that neither of these are modifying the executable path property at runtime ?
It is very common DTS practice to have packages that modify themselves at runtime so that they can adapt to whatever server and filesystem they are running on. This practive means that what you see (and manually set) in the DTS designer at design time will not necessarily be the same as what happens at runtime.
-
No there's nothing like that.
Besides, I've tried to create a new package including only this Execute Process Task, and it still won't work...
By the way, there's another Execute Process Task in the original package, at the beginning of it (a .bat script which copies file from one directory to another one) and this one works fine.
-
Can you use Execute SQL task and xp_cmdshell to execute the bat file?
I am not sure it will work but at least it worths a try.
-
You helped me solve the problem because when you run a shell with "xp_cmdshell" you have a log.
So the problem was : the .bat script is refering an .exe program but without specifying its directory since they are in the same one.
But SQL Server doesn't choose this directory, it uses its default directory (except if you just selected the .bat file, then its directory becomes the current directory, then it works).
So I modified the .bat script, replacing relative directories by absolute directories and it works.
To sum it up, let's say the problem comes from the fact that, unlike in windows shorcuts, you can't specify a directory for the script you execute.
Thanks to you all for your help
Benoit.
-
You seemed to know the problem. So why did you post to ask for help?
-
Not at all, Loner !
As I said, I've been able to solve the problem with the help of what you told me to do...
In my last post, I tried to analyze where the problem came from, because I thought it could interest the people who tried to help me. But it sure doesn't mean I knew that before !
-
You can use relative paths if you put a pushd command or a cd command at the top line switching the directory location. Like so:
pushd C:\scripts
start MyExe.exe
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I want to know if you don't use xp_cmdshell, what can you do to solve the problem ? Can you use execute process task ?
I am trying to do something liked this now and I don't want to use xp_cmdshell. If you use .exe file, do you need to put in user id and password?
-
Yes the solutions provided (absolute paths, or pushd command) work fine also with the Execute Process Task.
By the way, thank you to Robert Davis : the pushd command (which was absolutely unknown to me) is very useful in many ways.
-
Do you need to put in Log in ID and Password in the bat or exe file ?
Are you a SQL Server deverloper ro DBA?
-
Are you talking about the SQL Server Authentification ?
This one is set into the DTS Task.
If you launch the DTS Task from a bat file, then yes, you put in log id & password into the file.
Concerning your last question, i'm only a project manager who often develops things with DTS which i consider as a very useful tool.
-
If you launch the DTS Package from a bat file, and your SQL Server login is using Windows authentication, you can use the /E switch for DTSRun to use a trusted connection. If executing the bat file via Windows task scheduler, you can specify the Windows account to use for the execution of the task, so you don't even have to be logged in to run it.
Otherwise, use the /P and /U switches of DTSRun to pass in the login information. I prefer to use task scheduler in these cases because it stores the password encrypted. You can encrypt the entire command line yourself and use the /Z switch to specify that it is encrypted.
Benoit, you're welcome.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply