I'm trying to use the Execute SQL Task to call up a batch store procedure
that takes typically 1 hour to run. This batch procedure involves retriving
data from linked servers and some heavy data processing.

If I were to run this batch SP from query analyser there's no problems but
everytime i run it from DTS it fails. Am I missing something here? Is there
a timeout period for Execute SQL task?

the error i get involves some smalldatetime conversion error... This SP
takes 1 input parameter with Nvarchar(15) as data type.... my global
variable is a string format....

can anyone help me out with some suggestions? Thanks in advance

I kinda found out that the error is caused by a datetime conversion function
i called in the batch SP. In that function i convert my string date time
'dd/mm/yyyy' into a smalldatetime format. This runs fine in QA but fails
when called from DTS.

my global variable is String datatype and 'dd/mm/yyyy' is nvarchar(15)

can someone help me out with this?




"Nestor" <n3570r (AT) yahoo (DOT) com> wrote

Would following this article help?

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

Also.

Be careful about who the user is. SQL Server will look at their
language settings to determine how it thinks they are sending the value
to it.



"Nestor" <n3570r (AT) yahoo (DOT) com> wrote

I found out that the problem for the failure is due to a linked server query
i did in my batch query.

If this DTS package is run manually it works but when i schedule it to run
periodically it fails. I read from

http://support.microsoft.com/?kbid=269074

that there are some permission issues with scheduled DTS and I saw from the
logfiles that the permission is denied when i query from a linked server....

Can someone help me with this?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The article explains it all really.

If the owner of the job is a sysadmin then the guy executing the package
is the same guy as the SQL Server Agent account. If the owner is not a
sysadmin then the package runner is the proxy account.

This means that if the owner is a sysadmin and the agent service account
is the local system account then you will not be able to see resource
off the local bos for example.


Allan



"Nestor" <n3570r (AT) yahoo (DOT) com> wrote

got that fixed.. thanks Allan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote