dts appears cached when run as job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

dts appears cached when run as job

Author

Topic

aseab
Starting Member

8 Posts

Posted - 2003-11-18 : 11:23:11

HELPPPPPPP!!!!

I've got a bit of a wierd one....my dts package will execute perfectly if i execute it from design view or by right clicking the package name and chosing execute...but when i have set it up as a scheduled job it seems to use a previous version of the dts...

ie. My dts consists of a connection, a sql task and an active x script which sends an email...the sql script passes parameters to the activex script by using global variable as a rowset..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 12:30:48

How did you schedule it? Try running the DTS package in a cmd window using dtsrun.exe. SQL Server does save multiple versions of a package, but I've never heard of a scheduling problem with it.

Tara

aseab
Starting Member

8 Posts

Posted - 2003-11-18 : 13:04:21

quote:
Originally posted by tduggan

How did you schedule it? Try running the DTS package in a cmd window using dtsrun.exe. SQL Server does save multiple versions of a package, but I've never heard of a scheduling problem with it.

Tara

Hi, i scheduled it through the Management/Jobs/New Job/
i haven't tried running it from a command window..i'll try that in the morning...i did use dtsrun though in the step part of the job..
like this...dtsrun /S servername /N package name /E

thanks
Ang

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 14:03:19

I realize where you scheduled jobs. But did you go through the scheduler wizard for the DTS package or did you manually type in the dtsrun.exe command in the job step. If you went through the wizard, maybe it picked the wrong version (could be a bug). So in that case, manually create dtsrun.exe command. For which switches to pass it, check BOL.

Tara

aseab
Starting Member

8 Posts

Posted - 2003-11-18 : 16:52:44

quote:
Originally posted by tduggan

I realize where you scheduled jobs. But did you go through the scheduler wizard for the DTS package or did you manually type in the dtsrun.exe command in the job step. If you went through the wizard, maybe it picked the wrong version (could be a bug). So in that case, manually create dtsrun.exe command. For which switches to pass it, check BOL.

Tara

Thanks Tara but i did create it manually and i got the switch information from BOL...It's a really strange problem...I even tried deleting all the previous versions to see if that would have any effect...I didn't mention before but its sql server 7 i'm using...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-18 : 19:09:39

quote:
it seems to use a previous version of the dts...

Sorry, aseab.. It sounds pretty funny. What does it mean: seems?
Maybe this is your case: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30499

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 19:21:51

Since you deleted all of the previous versoins of the DTS package, I'm going to go out on a limb and say that the job is running exactly what the DTS package is supposed to do. How do you know it isn't? If you are executing the DTS package manually from your client machine and it works there, you also need to do this on the database server. If you get an error, this means that you are probably referring to something that the database server can not get to. Troubleshoot the problem on the database server but not through the scheduled job, meaning go to the console and execute it manually.

Tara

aseab
Starting Member

8 Posts

Posted - 2003-11-19 : 04:07:25

Hi peop...I had already looked at the forum topic you mentioned stoad but I don't get any errors...

I know the package isn't running properly because i have time stamped it...I have added the time and date to the rowset that is passed to the activex script..The script adds this date and time to the email subject. When executed manually the correct time is passed...However when executed by the job i get yesterday's date and a time of 10:59...If I make changes to the query task ie I add a new field and rebuild the package..And then schedule the job..It works first time...but after that it brings back a repeat of that...

any help would be much appreciated

Ang

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-19 : 06:35:03

I think more information is required here. Maybe it is your code that is "cached" and not the dts, as you have already eliminated previous versions of the package from the equation.

Maybe you should post the code that is being executed if there is any.

There could be any number of reasons why your email has a funny date. Maybe the computer it is running on has the date set wrongly? or your mail server does?

-------
Moo. :)

aseab
Starting Member

8 Posts

Posted - 2003-11-19 : 06:57:00

Hi thanks for the help everyone...I'm pretty sure that this is some sort of permissions prob for sql agent. I logged onto the server and recreated the package with the sql agent login and got the cached data irrespective of how i executed it....surely someone out there must have had this problem before...

cheers
ang

aseab
Starting Member

8 Posts

Posted - 2003-11-19 : 07:20:21

hi peops again...

here is the content of my dts

EXECUTE SQL TASK IN DTS

SELECT dbo.TestAppointments.AppID, dbo.TestAppointments.RegNo,
dbo.TestAppointments.Proprietor, dbo.TestAppointments.TestDate,

dbo.TimeSlots.TimeSlotStart, (CAST(GETDATE() AS varchar(22))) AS DATE1
FROM
dbo.TestAppointments INNER JOIN
dbo.TimeSlots ON dbo.TestAppointments.TimeSlotID = dbo.TimeSlots.TimeSlotID
WHERE (left(DATEADD(Day, 14, GETDATE()),10) = left(dbo.TestAppointments.TestDate,10)) and dbo.TestAppointments.TestType = '6 Month'

I create an output parameter global variable here called MOTInfo and pass the rowset results from the above

then i have an on success workflow

then i have the ACTIVE SCRIPT below:

Function Main()
dim countr
dim RS

set RS = DTSGlobalVariables("MotInfo").value
If RS.recordcount <> 0 then
Dim objMail
Set objMail = CreateObject("CDONTS.NewMail")
objMail.From ="xxxxx"
objMail.Subject = "Taxi MOTTest Reminder " & RS.Fields(5).value
objMail.To = "xxxxxxx"

If RS.RecordCount <> 0 then
strBody = "The Taxi's listed below are due a 6 Month Test in 14 days" & vbcrlf & vbcrlf
for countr =1 to RS.RecordCount
strBody = strBody & "REGISTRATION NO: " & RS.Fields(1).value & vbcrlf &" PROPRIETOR: " & RS.Fields(2).value & vbcrlf & " DATE: " & RS.Fields(3).value & vbcrlf & " TIME: " & RS.Fields(4).value & vbcrlf & vbcrlf

RS.MoveNext
Next
End If

objMail.Body = strBody
objMail.Send
set objMail = nothing
set RS = nothing

Main = DTSTaskExecResult_Success

End If

End Function

thanks again

Ang

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources