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