| Author |
Topic |
|
majnoon
Starting Member
26 Posts |
 Posted - 2003-11-12 : 03:50:44
|
| Hi:I have a DTS package which queries a table and outputs a flat txt file to a local drive. The package includes a small activex script which picks up the file changes its name to include a datetime stamp and moves it to a network drive, script as follows'**********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main() dtDate = FormatDateTime(Now, 1) Set FSO = CreateObject("Scripting.FileSystemObject") FSO.CopyFile "D:\ProductsDatabase\Strategix OutPut\ArticleMaster.txt", "I:\Ops\EDM\Purchasing\Article Master Zips\ArticleMaster " & dtDate & ".txt" Set FSO = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionWhen I execute the package manually it works fine, however when I schedule it it reports a failure stating that the path cannot be found. I also tried using the network drive servers friendly name, and IP address and again same failure is reported.Additionally, I tried getting the query to output to the network drive using BCP and the xp_cmdshell extended procedure, but it fails telling me that the path cannot be found.Can anyone please help??Wishing you a peaceful journey |
|
|
majnoon
Starting Member
26 Posts |
Posted - 2003-11-12 : 05:23:06
|
| Hi:One futher thing I neglected to mention, but the network I am using is a novell, not a microsoft networkWishing you a peaceful journey |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-12 : 08:31:03
|
| Dig in direction of permission of SQL Server Agent to write into that shared folder. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-12 : 08:36:33
|
| My guess is actually that drive I: doesn't exist for SQL Server Agent. Use a UNC path instead (i.e. \\netwareserv\ops\EDM\ etc )Damian |
|
|
|
majnoon
Starting Member
26 Posts |
Posted - 2003-11-13 : 04:09:35
|
| Hi:I have tried using the mapped drive letter, the UNC and IP but still the same error.Additionally, wouldn't the error come back as access denied if it were write permissions for SQL Server Agent??Wishing you a peaceful journey |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-13 : 05:39:35
|
| Just a wild guess.Maybe the file (that to be copied to) still not exists whenthe ActiveX script is starting? Have you proper workflow(On Success) between the two tasks?Don't mind the fact it works if started manually. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-13 : 14:56:43
|
| When it runs as a scheduled job, it see it's mapping as itself, the server. When you run it, it sees your mapping...I keep an identical mapping on my client so it looks like the server...Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-13 : 15:02:42
|
| majnoon, troubleshoot the problem on the database server, meaning execute the package while logged onto the console of the database server. Grab the path from your code, and put it in Start..Run. If you get the error there, it is because the server can't see that path. This is due to what Brett mentioned.Tara |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-13 : 16:07:47
|
| I am in doubt.. In fact you say:when you start a package manually (guess by right click on it in EM, then Execute)the script from an ActiveX Script Task is sent to your CLIENT machine and thenit is executed by local Script Engine..Impossibile.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-13 : 16:29:46
|
| No that isn't what is meant. It uses the client's drives, whether they be local or networked. Go ahead and test it for yourself, you'll see. Shouldn't say impossible until you've tested it. I do this type of thing almost weekly. I start on my machine using my drives, then when I'm ready to move the package to the database server, I modify the path information.Tara |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-13 : 17:04:35
|
quote:
then when I'm ready to move the package to the database server
???Why it should be moved??majnoon's package is already in msdb (and always was there).. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-13 : 17:07:22
|
| I shouldn't have said move. I should have said when I am ready to schedule it. The point remains:When you execute a package in DTS designer from your machine, it uses the client's drives, not the database servers. When you execute it through a job or in DTS designer from the server, it uses the server's drives.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-13 : 18:08:23
|
| Yes! Tara is absolutely correct. I have a specific ODBC driver installed on a client machine, and can use a certain DTS with that ODBC driver through that client, but the package cannot execute on the server since it doesn't have it installed.- Jeff |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-14 : 08:46:57
|
quote:
In fact you say:when you start a package manually (guess by right click on it in EM, then Execute)the script from an ActiveX Script Task is sent to your CLIENT machine and thenit is executed by local Script Engine..Impossibile..
Possible.. possible.. Moreover, not only a script is sent to the client machine butthe entire package, just for executing namely in the client machine environment.Thanks, Tara!! |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-14 : 09:00:05
|
| Extra-over, even if package is sheduled it is sent tothe client (out of SQL Server side) for executing butin this case the client and the sql server share thesame machine.. |
|
|
|
|
ActiveX Script