DTS Problem in stored proc

Hi all,
I'm having a problem I was hoping someone out there could shed some light on
. I have a stored procedure in SQL server 2000 that opens a cursor on a tab
le that contains a list of files on my heard drive. It then loops through t
he cursor, and first copies the file to a sub folder using xp_cmdshell. It
then moves the same file to a location and renames it to specific name that
a DTS package will import from (also done using xp_cmdshell). I am then exe
cuting the DTS package using DTSRun and xp_cmdshell. That is pretty straigh
t forward. Now to my problem. All this works for a random number of files,
and then it hangs. A few times it can process all files, but usually not.
To debug it, I figured I could create a log file for the DTS package (by us
ing the /L option), but by doing that, the problem disappeared. One thing I
have observed is that when it hangs, the CMD.EXE process is not terminating
. Does anyone have any explanation for this? I know I could just leave t
he logging, but I am curious why logging it also fixes it. FYI, the reason
I'm using a DTS package to load the text file instead of BULK INSERT is that
I have a rowterminator of CR and not CRLF, and I haven't found any document
ations stating that it even supports CR as the rowterminator. Also, I can't
stop the query analyzer after it hangs, so I have to stop the SQL server fo
r it to stop, and the error message that shows in the query analyzer then is
as follow.
Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
I assume that could have to do with the fact that I stopped the SQL server,
but maybe not, and it might be helpful to someone out there.
Any help would be much appreciated,
TKhi,
At the outset I would create a log for that DTS (Package
properties->Logging) and after that I would create another one (partial log)
inside the PUMP task (Transform Data Task Properties->Options->Exception Fil
e)
current location: alicante (es)
"Tor" wrote:

> Hi all,
> I'm having a problem I was hoping someone out there could shed some light on. I h
ave a stored procedure in SQL server 2000 that opens a cursor on a table that contai
ns a list of files on my heard drive. It then loops through the cursor, and first c
opi
es the file to a sub folder using xp_cmdshell. It then moves the same file
to a location and renames it to specific name that a DTS package will import
from (also done using xp_cmdshell). I am then executing the DTS package us
ing DTSRun and xp_cmdshell.
That is pretty straight forward. Now to my problem. All this works for a r
andom number of files, and then it hangs. A few times it can process all fi
les, but usually not. To debug it, I figured I could create a log file for
the DTS package (by using
the /L option), but by doing that, the problem disappeared. One thing I hav
e observed is that when it hangs, the CMD.EXE process is not terminating.
Does anyone have any explanation for this? I know I could just leave the
logging, but I am curious why logging it also fixes it. FYI, the reason I'm
using a DTS package to load the text file instead of BULK INSERT is that I
have a rowterminator of CR and not CRLF, and I haven't found any documentati
ons stating that it even su
pports CR as the rowterminator. Also, I can't stop the query analyzer after it hangs, so I
have to stop the SQL server for it to stop, and the error message that shows in the query
analyzer then is as follow.
> Query cancelled by User
> [Microsoft][ODBC SQL Server Driver]Operation canceled
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
> I assume that could have to do with the fact that I stopped the SQL server
, but maybe not, and it might be helpful to someone out there.
> Any help would be much appreciated,
> TK|||Thanks Enric. I tried all that, and I am getting the same result. When I
log the DTS package (not by the /L option but via the property of the
package), it works too. When I'm specifying only the exception file, it
does hang, but it states that all executions up to then were completed. I
don't even know if the problem is the actual DTS package, but if it wasn't,
why would logging it fix the problem? Could the calls to xp_cmdshell hang?
It is leaving a CMD.EXE running when it hangs after all, and I do know it is
one CMD.EXE and not several processes that come and go because of the PID.
I even tried to move the COPY and MOVE commands to a BAT file, and call the
BAT file from the stored proc, but no difference. Do you, or anyone else,
have any other ideas I can try?
Thanks,
TK
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:A42A33E7-C4C7-4BEC-B470-4077F404E9B8@.microsoft.com...
> hi,
> At the outset I would create a log for that DTS (Package
> properties->Logging) and after that I would create another one (partial
> log)
> inside the PUMP task (Transform Data Task Properties->Options->Exception
> File)
> --
> current location: alicante (es)
>
> "Tor" wrote:
>
> logging, but I am curious why logging it also fixes it. FYI, the reason
> I'm using a DTS package to load the text file instead of BULK INSERT is
> that I have a rowterminator of CR and not CRLF, and I haven't found any
> documentations stating that it even supports CR as the rowterminator.
> Also, I can't stop the query analyzer after it hangs, so I have to stop
> the SQL server for it to stop, and the error message that shows in the
> query analyzer then is as follow.sqlsql

DTS Package

Hello everyone,
I am wondering if anyone can shed any light on a DTS issue that I am
facing. At my company we are using multiple DTS packages to extract
data from a 3rd party database using their ODBC driver. Every so often
a couple of the DTS packages fail - which can prove to be a real pain -
as I and a colleague have to check these processes around 7am every
morning (becuase some of the packages can take an hour + to complete)
to ensure that they have all worked as expected.
Now, when a DTS package fails I am sent an e-mail notification from
SQLServer.
E.g.
JOB RUN: 'PurchaseControl' was run on 07/06/2006 at 06:00:00
DURATION: 0 hours, 7 minutes, 46 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 64
(PurchaseControl). The last step to run was step 1 (PurchaseControl).
This is OK as it tells me the package that fails. However, I cannot see
why it failed, there is no runtime information regarding the error that
occured. Now, if I run a DTS package manually through SQL Server 2000
Enterprise Manager and a step in the DTS package fails it tells me
why...
E.g.
[Kerridge][KISAM ODBC Driver]error reading pkt body
Is it possible to obtain this type of information from scheduled DTS
packages, so that I can examine what are the common problems within the
packages?
I'd really, really appreciate help on this one, if anyone can spare the
time.
Thanks in advance,
Craig.Hi Craig,
Yes, within the package properties you have a logging tab where you can set
the package to an error file.
Ray
"crgsmrt@.googlemail.com" wrote:

> Hello everyone,
> I am wondering if anyone can shed any light on a DTS issue that I am
> facing. At my company we are using multiple DTS packages to extract
> data from a 3rd party database using their ODBC driver. Every so often
> a couple of the DTS packages fail - which can prove to be a real pain -
> as I and a colleague have to check these processes around 7am every
> morning (becuase some of the packages can take an hour + to complete)
> to ensure that they have all worked as expected.
> Now, when a DTS package fails I am sent an e-mail notification from
> SQLServer.
> E.g.
> JOB RUN: 'PurchaseControl' was run on 07/06/2006 at 06:00:00
> DURATION: 0 hours, 7 minutes, 46 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 64
> (PurchaseControl). The last step to run was step 1 (PurchaseControl).
>
> This is OK as it tells me the package that fails. However, I cannot see
> why it failed, there is no runtime information regarding the error that
> occured. Now, if I run a DTS package manually through SQL Server 2000
> Enterprise Manager and a step in the DTS package fails it tells me
> why...
> E.g.
> [Kerridge][KISAM ODBC Driver]error reading pkt body
> Is it possible to obtain this type of information from scheduled DTS
> packages, so that I can examine what are the common problems within the
> packages?
> I'd really, really appreciate help on this one, if anyone can spare the
> time.
> Thanks in advance,
> Craig.
>|||rb wrote:
> Hi Craig,
> Yes, within the package properties you have a logging tab where you can se
t
> the package to an error file.
> Ray
> "crgsmrt@.googlemail.com" wrote:
>
Hi Ray,
Thanks for that. However, I cannot appear to locate this 'logging tab'.
Would you mind telling me where to locate it. The closest item I have
found is on the 'Notifications' tab in the Management>>Jobs section.
However, I do not believe this is what you are referring to.
Regards,
Craig.|||rb wrote:
> Hi Craig,
> Yes, within the package properties you have a logging tab where you can se
t
> the package to an error file.
> Ray
> "crgsmrt@.googlemail.com" wrote:
>
The information that gets dumped into the Event Log is just as useless
too...
SQL Server Scheduled Job 'PointOfSale'
(0xE2A9D65E6F5BB04C9C6116CE1F07AA65) - Status: Failed - Invoked on:
2006-06-06 03:00:02 - Message: The job failed. The Job was invoked by
Schedule 61 (PointOfSale). The last step to run was step 1
(PointOfSale).|||Thanks Ray,
I've found it already.
Craig.
rb wrote:
> Hi Craig,
> Yes, within the package properties you have a logging tab where you can se
t
> the package to an error file.
> Ray
> "crgsmrt@.googlemail.com" wrote:
>