Re: problem with Execute SQL Task

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@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23VOeg8xPFHA.2788@xxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxx> wrote in message news:n3570r@xxxxxxxxx:
>
>> 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
>


.

Relevant Pages

  • Re: Newbee DTS Question
    ... Have a look in BOL (Books Online) ... >> also do this in DTS ... >> If you use a linked server then you can write the query to extract rows ... >> If you have read things into a Global Variable you can use it in a query ...
    (microsoft.public.sqlserver.dts)
  • Re: Newbee DTS Question
    ... Your situation sounds more suited to replication than DTS however, ... If you use a linked server then you can write the query to extract rows ... If you have read things into a Global Variable you can use it in a query ...
    (microsoft.public.sqlserver.dts)
  • Re: output query results to file
    ... You can also use the Jet OLEDB driver as a linked server. ... >> I am looking for a way to output the results of a query to a text file. ... > Can I do this via statements in my query or do I have to use DTS? ...
    (microsoft.public.sqlserver.server)
  • Re: Newbee DTS Question
    ... > also do this in DTS ... Use a linked server ... > If you have read things into a Global Variable you can use it in a query ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS - passing variable from one connection to the other connec
    ... Assuming we are still using DTS to do this, either options in your email (1 ... The 'select' query is in the ... 'Source' tab of the Transform Data Task properties. ... > what happened between times A and B on the source server. ...
    (microsoft.public.sqlserver.dts)