FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath

uBuilder.Port = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)
Return fWebRequest
End Function
End Classsql

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath

uBuilder.Port = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)
Return fWebRequest
End Function
End Class

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath

uBuilder.Port = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)
Return fWebRequest
End Function
End Class

FTP Task - Delete remote files always fails

Hello,

I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.

Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.

I receive the same message for each of the files that was downloaded:

Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.

The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.

Dan

Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.

I see there is one other unanswered post about a problem remotly deleting a file, but no answers.

|||

I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.

Any suggestions or other information would be welcome.

|||

I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b

I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.

As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.

|||

Were you able to delete a single file using the FTP Task without using a wildcard?

Donald Farmer

|||

I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).

SSIS package "Package.dtsx" starting.

Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.

The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||

I am getting exactly the same problem,

howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.

any help on either will be appreciated.

Terry

|||

What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?

|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.

Jarret|||

Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.

Out of curiousity, what is the OS of your server? Mine is running on SCO.

|||

In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):

FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password

Then use the following code within the Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If

'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)

Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

Private Function FileExists(ByVal fileName As String) As Boolean

Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If

Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try

Return retVal

End Function

Private Sub DeleteFile(ByVal fileName As String)

Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)

Return fWebRequest

End Function

End Class

|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.

[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .|||

Still this script is not helping as its throwing exception back... Has anyone found other workaround please?

Regards,
paddy

|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?

Arjun|||

Hello Arjun,

I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...

Thanks,
paddy

FTP Task - Delete remote files always fails

Hello,

I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.

Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.

I receive the same message for each of the files that was downloaded:

Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.

The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.

Dan

Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.

I see there is one other unanswered post about a problem remotly deleting a file, but no answers.

|||

I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.

Any suggestions or other information would be welcome.

|||

I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b

I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.

As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.

|||

Were you able to delete a single file using the FTP Task without using a wildcard?

Donald Farmer

|||

I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).

SSIS package "Package.dtsx" starting.

Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.

The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||

I am getting exactly the same problem,

howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.

any help on either will be appreciated.

Terry

|||

What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?

|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.

Jarret
|||

Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.

Out of curiousity, what is the OS of your server? Mine is running on SCO.

|||

In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):

FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password

Then use the following code within the Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If

'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)

Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

Private Function FileExists(ByVal fileName As String) As Boolean

Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If

Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try

Return retVal

End Function

Private Sub DeleteFile(ByVal fileName As String)

Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)

Return fWebRequest

End Function

End Class

|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.

[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .
|||

Still this script is not helping as its throwing exception back... Has anyone found other workaround please?

Regards,
paddy

|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?

Arjun
|||

Hello Arjun,

I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...

Thanks,
paddy

FTP Task - Delete remote files always fails

Hello,

I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.

Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.

I receive the same message for each of the files that was downloaded:

Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.

The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.

Dan

Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.

I see there is one other unanswered post about a problem remotly deleting a file, but no answers.

|||

I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.

Any suggestions or other information would be welcome.

|||

I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b

I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.

As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.

|||

Were you able to delete a single file using the FTP Task without using a wildcard?

Donald Farmer

|||

I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).

SSIS package "Package.dtsx" starting.

Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.

The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||

I am getting exactly the same problem,

howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.

any help on either will be appreciated.

Terry

|||

What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?

|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.

Jarret|||

Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.

Out of curiousity, what is the OS of your server? Mine is running on SCO.

|||

In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):

FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password

Then use the following code within the Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If

'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)

Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

Private Function FileExists(ByVal fileName As String) As Boolean

Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If

Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try

Return retVal

End Function

Private Sub DeleteFile(ByVal fileName As String)

Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)

Return fWebRequest

End Function

End Class

|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.

[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .|||

Still this script is not helping as its throwing exception back... Has anyone found other workaround please?

Regards,
paddy

|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?

Arjun|||

Hello Arjun,

I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...

Thanks,
paddy

sql

FTP Task - Delete remote files always fails

Hello,

I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.

Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.

I receive the same message for each of the files that was downloaded:

Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.

The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.

Dan

Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.

I see there is one other unanswered post about a problem remotly deleting a file, but no answers.

|||

I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.

Any suggestions or other information would be welcome.

|||

I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b

I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.

As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.

|||

Were you able to delete a single file using the FTP Task without using a wildcard?

Donald Farmer

|||

I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).

SSIS package "Package.dtsx" starting.

Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.

The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||

I am getting exactly the same problem,

howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.

any help on either will be appreciated.

Terry

|||

What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?

|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.

Jarret|||

Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.

Out of curiousity, what is the OS of your server? Mine is running on SCO.

|||

In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):

FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password

Then use the following code within the Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If

'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)

Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

Private Function FileExists(ByVal fileName As String) As Boolean

Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If

Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try

Return retVal

End Function

Private Sub DeleteFile(ByVal fileName As String)

Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)

Return fWebRequest

End Function

End Class

|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.

[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .|||

Still this script is not helping as its throwing exception back... Has anyone found other workaround please?

Regards,
paddy

|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?

Arjun|||

Hello Arjun,

I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...

Thanks,
paddy

FTP Task - Delete remote files always fails

Hello,

I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.

Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.

I receive the same message for each of the files that was downloaded:

Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.

The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.

Dan

Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.

I see there is one other unanswered post about a problem remotly deleting a file, but no answers.

|||

I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.

Any suggestions or other information would be welcome.

|||

I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b

I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.

As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.

|||

Were you able to delete a single file using the FTP Task without using a wildcard?

Donald Farmer

|||

I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).

SSIS package "Package.dtsx" starting.

Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.

The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.

.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||

I am getting exactly the same problem,

howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.

any help on either will be appreciated.

Terry

|||

What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?

|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.

Jarret
|||

Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.

Out of curiousity, what is the OS of your server? Mine is running on SCO.

|||

In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):

FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password

Then use the following code within the Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If

'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)

Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

Private Function FileExists(ByVal fileName As String) As Boolean

Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If

Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try

Return retVal

End Function

Private Sub DeleteFile(ByVal fileName As String)

Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName

Try

Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)

Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)

Return fWebRequest

End Function

End Class

|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.

[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .
|||

Still this script is not helping as its throwing exception back... Has anyone found other workaround please?

Regards,
paddy

|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?

Arjun
|||

Hello Arjun,

I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...

Thanks,
paddy

FTP Delete task in SQL agent

I have a FTP task created using SSIS that need to delete lot of files in a directory. This task is working fine if I'm Executing the SSIS package manualy but not working if I'm scheduling the SSIS package in SQL agent job.
Anybody have faced this kind if situation is there any solution for this?.

have you gotten a resolution on this problem? I am having the same issue on a FTP send task and it works fine when I run it manually but it bombs out when I schedule it.

Let me know... Thanks

|||

Error messages really help in these situations guys.

-Jamie

|||

I actually found the answer for my problem here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356558&SiteID=1

I just made the protection level to "DontSaveSensitive" and when the SQL Server agent ran the package it ran properly.

Hope it helps :)

From BAD To WORSE: A Disasterous Delete

Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
with my smaller data extract, I have had a recurrence of the problem of the
mysterious deletion of ALL RECORDS, but now with the smaller data extract.
But I have a better sense now of what may have CAUSED the problem (I think).
I had just used SQL Server Enterprise Manager to Change the Data Type of two
columns from char to nvarchar. I was then re-running a query. Thereafter,
in noticed that ALL ROWS of the table seemed to be missing.
Perhaps I am SPOILED by the way MS Access handles changes to a datastructure
with data records in place.
DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
ALTERED? This seems like a rather radical treatment to the data within a
table if this is the case.
Now I seem to have lost NOT ONLY the original larger 9 million record data
table, but also the smaller 350,000 record data extract.
Maybe I should just throw in the towel!
Any suggestions are appreciated!!
Of course you backed up your database, prior to making major changes to
tables, correct?
Whew..thank goodness.
And no, generally records are not deleted. However, behind the scenes, it's
probably renaming the table, creating a new structure, then inserting the
records, then dropping and renaming the temp table.
Did you check if the data was there immediately after changing the column
datatype?
Jeff
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of
the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I
think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of
two
> columns from char to nvarchar. I was then re-running a query.
Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a
datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within
a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>
|||> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED?
No. You have either hit a bug, or something went very wrong. But without a repro, it is hard for us
to comment any further. Most of us prefer to execute TSQL statements from Query Analyzer for these
types of changes as it gives us better control, and we know exactly what commands are executed...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bishop" <ugradfrnd@.aol.com> wrote in message news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of two
> columns from char to nvarchar. I was then re-running a query. Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>
|||Enterprise Mangler often does a table shuffle under the covers when making
changes to tables. You can see exactly what it intends to do by saving the
change script before saving changes. As Tibor points out, many of us prefer
to do things via T-SQL because we know exactly what is happening. EM is a
good tool, but sometimes the way it wants to do something is not the way I
want it done.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of
the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I
think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of
two
> columns from char to nvarchar. I was then re-running a query.
Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a
datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within
a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>
|||> Enterprise Mangler
First time I've seen that one. LOL...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
> Enterprise Mangler often does a table shuffle under the covers when making
> changes to tables. You can see exactly what it intends to do by saving the
> change script before saving changes. As Tibor points out, many of us prefer
> to do things via T-SQL because we know exactly what is happening. EM is a
> good tool, but sometimes the way it wants to do something is not the way I
> want it done.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> the
> think).
> two
> Thereafter,
> datastructure
> a
>
|||Thank you, too, Tibor!
DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
trick. ENterprise Manager was simply misreporting the record count!
As I use the Wizards and tools to do certain tasks, I also tend to learn
more about the underlying SQL, which I will no doubt apply directly from
TSQL when I am more knowledgable and experienced.
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
IS
> No. You have either hit a bug, or something went very wrong. But without a
repro, it is hard for us
> to comment any further. Most of us prefer to execute TSQL statements from
Query Analyzer for these
> types of changes as it gives us better control, and we know exactly what
commands are executed...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
work[vbcol=seagreen]
the[vbcol=seagreen]
extract.[vbcol=seagreen]
think).[vbcol=seagreen]
two[vbcol=seagreen]
Thereafter,[vbcol=seagreen]
datastructure[vbcol=seagreen]
IS[vbcol=seagreen]
within a[vbcol=seagreen]
data
>
|||> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
Yes, EM picks up the row count from sysindexes which isn't reliable. Good catch by Geoff.

> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
Good plan. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bishop" <ugradfrnd@.aol.com> wrote in message news:eQGc0mcvEHA.3272@.TK2MSFTNGP12.phx.gbl...
> Thank you, too, Tibor!
> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
> Thanks again!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
> IS
> repro, it is hard for us
> Query Analyzer for these
> commands are executed...
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> work
> the
> extract.
> think).
> two
> Thereafter,
> datastructure
> IS
> within a
> data
>
|||I see this when people talk about using EM for data manipulation, because
that's what it does to your data.
John, I suggest you start learning basic SQL soon. EM is a good tool for
working with your objects, but NOT for working with the data itself.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
> First time I've seen that one. LOL...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
> First time I've seen that one. LOL...
>
Really? Wow. :-)

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/

From BAD To WORSE: A Disasterous Delete

Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
with my smaller data extract, I have had a recurrence of the problem of the
mysterious deletion of ALL RECORDS, but now with the smaller data extract.
But I have a better sense now of what may have CAUSED the problem (I think).
I had just used SQL Server Enterprise Manager to Change the Data Type of two
columns from char to nvarchar. I was then re-running a query. Thereafter,
in noticed that ALL ROWS of the table seemed to be missing.
Perhaps I am SPOILED by the way MS Access handles changes to a datastructure
with data records in place.
DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
ALTERED? This seems like a rather radical treatment to the data within a
table if this is the case.
Now I seem to have lost NOT ONLY the original larger 9 million record data
table, but also the smaller 350,000 record data extract.
Maybe I should just throw in the towel!
Any suggestions are appreciated!!Of course you backed up your database, prior to making major changes to
tables, correct?
Whew..thank goodness.
And no, generally records are not deleted. However, behind the scenes, it's
probably renaming the table, creating a new structure, then inserting the
records, then dropping and renaming the temp table.
Did you check if the data was there immediately after changing the column
datatype?
Jeff
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of
the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I
think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of
two
> columns from char to nvarchar. I was then re-running a query.
Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a
datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within
a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>|||> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED?
No. You have either hit a bug, or something went very wrong. But without a repro, it is hard for us
to comment any further. Most of us prefer to execute TSQL statements from Query Analyzer for these
types of changes as it gives us better control, and we know exactly what commands are executed...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bishop" <ugradfrnd@.aol.com> wrote in message news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of two
> columns from char to nvarchar. I was then re-running a query. Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>|||Enterprise Mangler often does a table shuffle under the covers when making
changes to tables. You can see exactly what it intends to do by saving the
change script before saving changes. As Tibor points out, many of us prefer
to do things via T-SQL because we know exactly what is happening. EM is a
good tool, but sometimes the way it wants to do something is not the way I
want it done.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
> with my smaller data extract, I have had a recurrence of the problem of
the
> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
> But I have a better sense now of what may have CAUSED the problem (I
think).
> I had just used SQL Server Enterprise Manager to Change the Data Type of
two
> columns from char to nvarchar. I was then re-running a query.
Thereafter,
> in noticed that ALL ROWS of the table seemed to be missing.
> Perhaps I am SPOILED by the way MS Access handles changes to a
datastructure
> with data records in place.
> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED? This seems like a rather radical treatment to the data within
a
> table if this is the case.
> Now I seem to have lost NOT ONLY the original larger 9 million record data
> table, but also the smaller 350,000 record data extract.
> Maybe I should just throw in the towel!
> Any suggestions are appreciated!!
>|||Your explanation makes SENSE and yes, it appears that I was checking
immediately after the change. (Bear in mind that I am still feeling my way
along.)
The indication that the records were DELETED is that Server Enterprise
Manager reports that the table has ZERO Rows. I had made the change in Data
Type in Server Enterprise Manager and then was making notes and documenting
what I had done. I went back to note the record count (which I had
previously found was conveniently reported in the Table Properties in Server
Enterprise Manager and it gave me the sensation that ALL of the data had
been deleted.
Now, I am thinking that it is doing precisely as you describe. I was able
to use the Import and Export Data tool to COPY all of the data from that
table to another table in a different database, which reports that it has
387,825 records (CORRECT). The original table STILL says in the Properties
that it has ZERO records, but that table has several Indices that are
probably being rebuilt.
But, the larger database from last night STILL SAYS that it has ZERO records
nine hours after the records appeared to all be deleted. But it has nine
million records and more than a few indices. And I am running this SQL
Evaulation on an older 866 MHz single processor system with only 512 Mbs of
memory.
Is there some way to monitor the things that SQL is doing in the BACKGROUND
to these tables?
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ORaVrEcvEHA.2584@.TK2MSFTNGP10.phx.gbl...
> Of course you backed up your database, prior to making major changes to
> tables, correct?
> Whew..thank goodness.
> And no, generally records are not deleted. However, behind the scenes,
it's
> probably renaming the table, creating a new structure, then inserting the
> records, then dropping and renaming the temp table.
> Did you check if the data was there immediately after changing the column
> datatype?
> Jeff
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
work
> > with my smaller data extract, I have had a recurrence of the problem of
> the
> > mysterious deletion of ALL RECORDS, but now with the smaller data
extract.
> >
> > But I have a better sense now of what may have CAUSED the problem (I
> think).
> > I had just used SQL Server Enterprise Manager to Change the Data Type of
> two
> > columns from char to nvarchar. I was then re-running a query.
> Thereafter,
> > in noticed that ALL ROWS of the table seemed to be missing.
> >
> > Perhaps I am SPOILED by the way MS Access handles changes to a
> datastructure
> > with data records in place.
> >
> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
IS
> > ALTERED? This seems like a rather radical treatment to the data
within
> a
> > table if this is the case.
> >
> > Now I seem to have lost NOT ONLY the original larger 9 million record
data
> > table, but also the smaller 350,000 record data extract.
> >
> > Maybe I should just throw in the towel!
> >
> > Any suggestions are appreciated!!
> >
> >
>|||Run DBCC UPDATEUSAGE to correct the row count display in Enterprise Mangler.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:%231Z4HYcvEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Your explanation makes SENSE and yes, it appears that I was checking
> immediately after the change. (Bear in mind that I am still feeling my
way
> along.)
> The indication that the records were DELETED is that Server Enterprise
> Manager reports that the table has ZERO Rows. I had made the change in
Data
> Type in Server Enterprise Manager and then was making notes and
documenting
> what I had done. I went back to note the record count (which I had
> previously found was conveniently reported in the Table Properties in
Server
> Enterprise Manager and it gave me the sensation that ALL of the data had
> been deleted.
> Now, I am thinking that it is doing precisely as you describe. I was able
> to use the Import and Export Data tool to COPY all of the data from that
> table to another table in a different database, which reports that it has
> 387,825 records (CORRECT). The original table STILL says in the
Properties
> that it has ZERO records, but that table has several Indices that are
> probably being rebuilt.
> But, the larger database from last night STILL SAYS that it has ZERO
records
> nine hours after the records appeared to all be deleted. But it has nine
> million records and more than a few indices. And I am running this SQL
> Evaulation on an older 866 MHz single processor system with only 512 Mbs
of
> memory.
> Is there some way to monitor the things that SQL is doing in the
BACKGROUND
> to these tables?
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ORaVrEcvEHA.2584@.TK2MSFTNGP10.phx.gbl...
> > Of course you backed up your database, prior to making major changes to
> > tables, correct?
> >
> > Whew..thank goodness.
> >
> > And no, generally records are not deleted. However, behind the scenes,
> it's
> > probably renaming the table, creating a new structure, then inserting
the
> > records, then dropping and renaming the temp table.
> >
> > Did you check if the data was there immediately after changing the
column
> > datatype?
> >
> > Jeff
> > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > > Last night, I posted the message "HELP!: A Disasterous Delete"
(Friday,
> > > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
> work
> > > with my smaller data extract, I have had a recurrence of the problem
of
> > the
> > > mysterious deletion of ALL RECORDS, but now with the smaller data
> extract.
> > >
> > > But I have a better sense now of what may have CAUSED the problem (I
> > think).
> > > I had just used SQL Server Enterprise Manager to Change the Data Type
of
> > two
> > > columns from char to nvarchar. I was then re-running a query.
> > Thereafter,
> > > in noticed that ALL ROWS of the table seemed to be missing.
> > >
> > > Perhaps I am SPOILED by the way MS Access handles changes to a
> > datastructure
> > > with data records in place.
> > >
> > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
> IS
> > > ALTERED? This seems like a rather radical treatment to the data
> within
> > a
> > > table if this is the case.
> > >
> > > Now I seem to have lost NOT ONLY the original larger 9 million record
> data
> > > table, but also the smaller 350,000 record data extract.
> > >
> > > Maybe I should just throw in the towel!
> > >
> > > Any suggestions are appreciated!!
> > >
> > >
> >
> >
>|||> Enterprise Mangler
First time I've seen that one. LOL...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
> Enterprise Mangler often does a table shuffle under the covers when making
> changes to tables. You can see exactly what it intends to do by saving the
> change script before saving changes. As Tibor points out, many of us prefer
> to do things via T-SQL because we know exactly what is happening. EM is a
> good tool, but sometimes the way it wants to do something is not the way I
> want it done.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
>> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
>> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my work
>> with my smaller data extract, I have had a recurrence of the problem of
> the
>> mysterious deletion of ALL RECORDS, but now with the smaller data extract.
>> But I have a better sense now of what may have CAUSED the problem (I
> think).
>> I had just used SQL Server Enterprise Manager to Change the Data Type of
> two
>> columns from char to nvarchar. I was then re-running a query.
> Thereafter,
>> in noticed that ALL ROWS of the table seemed to be missing.
>> Perhaps I am SPOILED by the way MS Access handles changes to a
> datastructure
>> with data records in place.
>> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
>> ALTERED? This seems like a rather radical treatment to the data within
> a
>> table if this is the case.
>> Now I seem to have lost NOT ONLY the original larger 9 million record data
>> table, but also the smaller 350,000 record data extract.
>> Maybe I should just throw in the towel!
>> Any suggestions are appreciated!!
>>
>|||Thank you, Geoff! That did the trick! The data WAS there all along.
It is more than a little disorienting though, when one is tired and
ill-acquainted with SQL and a data table seems to LOSE all of its records!
But these test runs are all about learning the ideosyncracies of a product
before trying to use it in production!
The problem appears to have been between the chair and the keyboard all
along!!
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e9Z36acvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Run DBCC UPDATEUSAGE to correct the row count display in Enterprise
Mangler.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
> news:%231Z4HYcvEHA.2016@.TK2MSFTNGP15.phx.gbl...
> > Your explanation makes SENSE and yes, it appears that I was checking
> > immediately after the change. (Bear in mind that I am still feeling my
> way
> > along.)
> >
> > The indication that the records were DELETED is that Server Enterprise
> > Manager reports that the table has ZERO Rows. I had made the change in
> Data
> > Type in Server Enterprise Manager and then was making notes and
> documenting
> > what I had done. I went back to note the record count (which I had
> > previously found was conveniently reported in the Table Properties in
> Server
> > Enterprise Manager and it gave me the sensation that ALL of the data had
> > been deleted.
> >
> > Now, I am thinking that it is doing precisely as you describe. I was
able
> > to use the Import and Export Data tool to COPY all of the data from that
> > table to another table in a different database, which reports that it
has
> > 387,825 records (CORRECT). The original table STILL says in the
> Properties
> > that it has ZERO records, but that table has several Indices that are
> > probably being rebuilt.
> >
> > But, the larger database from last night STILL SAYS that it has ZERO
> records
> > nine hours after the records appeared to all be deleted. But it has
nine
> > million records and more than a few indices. And I am running this SQL
> > Evaulation on an older 866 MHz single processor system with only 512 Mbs
> of
> > memory.
> >
> > Is there some way to monitor the things that SQL is doing in the
> BACKGROUND
> > to these tables?
> >
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:ORaVrEcvEHA.2584@.TK2MSFTNGP10.phx.gbl...
> > > Of course you backed up your database, prior to making major changes
to
> > > tables, correct?
> > >
> > > Whew..thank goodness.
> > >
> > > And no, generally records are not deleted. However, behind the scenes,
> > it's
> > > probably renaming the table, creating a new structure, then inserting
> the
> > > records, then dropping and renaming the temp table.
> > >
> > > Did you check if the data was there immediately after changing the
> column
> > > datatype?
> > >
> > > Jeff
> > > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > > news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > > > Last night, I posted the message "HELP!: A Disasterous Delete"
> (Friday,
> > > > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
> > work
> > > > with my smaller data extract, I have had a recurrence of the problem
> of
> > > the
> > > > mysterious deletion of ALL RECORDS, but now with the smaller data
> > extract.
> > > >
> > > > But I have a better sense now of what may have CAUSED the problem (I
> > > think).
> > > > I had just used SQL Server Enterprise Manager to Change the Data
Type
> of
> > > two
> > > > columns from char to nvarchar. I was then re-running a query.
> > > Thereafter,
> > > > in noticed that ALL ROWS of the table seemed to be missing.
> > > >
> > > > Perhaps I am SPOILED by the way MS Access handles changes to a
> > > datastructure
> > > > with data records in place.
> > > >
> > > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE
COLUMN
> > IS
> > > > ALTERED? This seems like a rather radical treatment to the data
> > within
> > > a
> > > > table if this is the case.
> > > >
> > > > Now I seem to have lost NOT ONLY the original larger 9 million
record
> > data
> > > > table, but also the smaller 350,000 record data extract.
> > > >
> > > > Maybe I should just throw in the towel!
> > > >
> > > > Any suggestions are appreciated!!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you, too, Tibor!
DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
trick. ENterprise Manager was simply misreporting the record count!
As I use the Wizards and tools to do certain tasks, I also tend to learn
more about the underlying SQL, which I will no doubt apply directly from
TSQL when I am more knowledgable and experienced.
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
IS
> > ALTERED?
> No. You have either hit a bug, or something went very wrong. But without a
repro, it is hard for us
> to comment any further. Most of us prefer to execute TSQL statements from
Query Analyzer for these
> types of changes as it gives us better control, and we know exactly what
commands are executed...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bishop" <ugradfrnd@.aol.com> wrote in message
news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
work
> > with my smaller data extract, I have had a recurrence of the problem of
the
> > mysterious deletion of ALL RECORDS, but now with the smaller data
extract.
> >
> > But I have a better sense now of what may have CAUSED the problem (I
think).
> > I had just used SQL Server Enterprise Manager to Change the Data Type of
two
> > columns from char to nvarchar. I was then re-running a query.
Thereafter,
> > in noticed that ALL ROWS of the table seemed to be missing.
> >
> > Perhaps I am SPOILED by the way MS Access handles changes to a
datastructure
> > with data records in place.
> >
> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
IS
> > ALTERED? This seems like a rather radical treatment to the data
within a
> > table if this is the case.
> >
> > Now I seem to have lost NOT ONLY the original larger 9 million record
data
> > table, but also the smaller 350,000 record data extract.
> >
> > Maybe I should just throw in the towel!
> >
> > Any suggestions are appreciated!!
> >
> >
>|||> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
Yes, EM picks up the row count from sysindexes which isn't reliable. Good catch by Geoff.
> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
Good plan. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bishop" <ugradfrnd@.aol.com> wrote in message news:eQGc0mcvEHA.3272@.TK2MSFTNGP12.phx.gbl...
> Thank you, too, Tibor!
> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
> Thanks again!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
>> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
> IS
>> > ALTERED?
>> No. You have either hit a bug, or something went very wrong. But without a
> repro, it is hard for us
>> to comment any further. Most of us prefer to execute TSQL statements from
> Query Analyzer for these
>> types of changes as it gives us better control, and we know exactly what
> commands are executed...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Bishop" <ugradfrnd@.aol.com> wrote in message
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
>> > Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
>> > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
> work
>> > with my smaller data extract, I have had a recurrence of the problem of
> the
>> > mysterious deletion of ALL RECORDS, but now with the smaller data
> extract.
>> >
>> > But I have a better sense now of what may have CAUSED the problem (I
> think).
>> > I had just used SQL Server Enterprise Manager to Change the Data Type of
> two
>> > columns from char to nvarchar. I was then re-running a query.
> Thereafter,
>> > in noticed that ALL ROWS of the table seemed to be missing.
>> >
>> > Perhaps I am SPOILED by the way MS Access handles changes to a
> datastructure
>> > with data records in place.
>> >
>> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
> IS
>> > ALTERED? This seems like a rather radical treatment to the data
> within a
>> > table if this is the case.
>> >
>> > Now I seem to have lost NOT ONLY the original larger 9 million record
> data
>> > table, but also the smaller 350,000 record data extract.
>> >
>> > Maybe I should just throw in the towel!
>> >
>> > Any suggestions are appreciated!!
>> >
>> >
>>
>|||I see this when people talk about using EM for data manipulation, because
that's what it does to your data.
John, I suggest you start learning basic SQL soon. EM is a good tool for
working with your objects, but NOT for working with the data itself.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
>> Enterprise Mangler
> First time I've seen that one. LOL...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Enterprise Mangler often does a table shuffle under the covers when
>> making
>> changes to tables. You can see exactly what it intends to do by saving
>> the
>> change script before saving changes. As Tibor points out, many of us
>> prefer
>> to do things via T-SQL because we know exactly what is happening. EM is
>> a
>> good tool, but sometimes the way it wants to do something is not the way
>> I
>> want it done.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "John Bishop" <ugradfrnd@.aol.com> wrote in message
>> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
>> Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
>> October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
>> work
>> with my smaller data extract, I have had a recurrence of the problem of
>> the
>> mysterious deletion of ALL RECORDS, but now with the smaller data
>> extract.
>> But I have a better sense now of what may have CAUSED the problem (I
>> think).
>> I had just used SQL Server Enterprise Manager to Change the Data Type of
>> two
>> columns from char to nvarchar. I was then re-running a query.
>> Thereafter,
>> in noticed that ALL ROWS of the table seemed to be missing.
>> Perhaps I am SPOILED by the way MS Access handles changes to a
>> datastructure
>> with data records in place.
>> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
>> IS
>> ALTERED? This seems like a rather radical treatment to the data
>> within
>> a
>> table if this is the case.
>> Now I seem to have lost NOT ONLY the original larger 9 million record
>> data
>> table, but also the smaller 350,000 record data extract.
>> Maybe I should just throw in the towel!
>> Any suggestions are appreciated!!
>>
>>
>|||"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:OBZZrjcvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Thank you, Geoff! That did the trick! The data WAS there all along.
SQL Server is really good about not deleting stuff it shouldn't. :-)
> It is more than a little disorienting though, when one is tired and
> ill-acquainted with SQL and a data table seems to LOSE all of its records!
It's worse when you realize you just copied the EMPTY DB on top of the
production DB. THEN realized you had no backups. :-)
> But these test runs are all about learning the ideosyncracies of a product
> before trying to use it in production!
Good plan.
> The problem appears to have been between the chair and the keyboard all
> along!!
>
PEBKAC :-)
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e9Z36acvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> > Run DBCC UPDATEUSAGE to correct the row count display in Enterprise
> Mangler.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > news:%231Z4HYcvEHA.2016@.TK2MSFTNGP15.phx.gbl...
> > > Your explanation makes SENSE and yes, it appears that I was checking
> > > immediately after the change. (Bear in mind that I am still feeling
my
> > way
> > > along.)
> > >
> > > The indication that the records were DELETED is that Server Enterprise
> > > Manager reports that the table has ZERO Rows. I had made the change
in
> > Data
> > > Type in Server Enterprise Manager and then was making notes and
> > documenting
> > > what I had done. I went back to note the record count (which I had
> > > previously found was conveniently reported in the Table Properties in
> > Server
> > > Enterprise Manager and it gave me the sensation that ALL of the data
had
> > > been deleted.
> > >
> > > Now, I am thinking that it is doing precisely as you describe. I was
> able
> > > to use the Import and Export Data tool to COPY all of the data from
that
> > > table to another table in a different database, which reports that it
> has
> > > 387,825 records (CORRECT). The original table STILL says in the
> > Properties
> > > that it has ZERO records, but that table has several Indices that are
> > > probably being rebuilt.
> > >
> > > But, the larger database from last night STILL SAYS that it has ZERO
> > records
> > > nine hours after the records appeared to all be deleted. But it has
> nine
> > > million records and more than a few indices. And I am running this
SQL
> > > Evaulation on an older 866 MHz single processor system with only 512
Mbs
> > of
> > > memory.
> > >
> > > Is there some way to monitor the things that SQL is doing in the
> > BACKGROUND
> > > to these tables?
> > >
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ORaVrEcvEHA.2584@.TK2MSFTNGP10.phx.gbl...
> > > > Of course you backed up your database, prior to making major changes
> to
> > > > tables, correct?
> > > >
> > > > Whew..thank goodness.
> > > >
> > > > And no, generally records are not deleted. However, behind the
scenes,
> > > it's
> > > > probably renaming the table, creating a new structure, then
inserting
> > the
> > > > records, then dropping and renaming the temp table.
> > > >
> > > > Did you check if the data was there immediately after changing the
> > column
> > > > datatype?
> > > >
> > > > Jeff
> > > > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > > > news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > > > > Last night, I posted the message "HELP!: A Disasterous Delete"
> > (Friday,
> > > > > October 29, 2004 1:20 AM). This morning, as I pressed ahead with
my
> > > work
> > > > > with my smaller data extract, I have had a recurrence of the
problem
> > of
> > > > the
> > > > > mysterious deletion of ALL RECORDS, but now with the smaller data
> > > extract.
> > > > >
> > > > > But I have a better sense now of what may have CAUSED the problem
(I
> > > > think).
> > > > > I had just used SQL Server Enterprise Manager to Change the Data
> Type
> > of
> > > > two
> > > > > columns from char to nvarchar. I was then re-running a query.
> > > > Thereafter,
> > > > > in noticed that ALL ROWS of the table seemed to be missing.
> > > > >
> > > > > Perhaps I am SPOILED by the way MS Access handles changes to a
> > > > datastructure
> > > > > with data records in place.
> > > > >
> > > > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE
> COLUMN
> > > IS
> > > > > ALTERED? This seems like a rather radical treatment to the data
> > > within
> > > > a
> > > > > table if this is the case.
> > > > >
> > > > > Now I seem to have lost NOT ONLY the original larger 9 million
> record
> > > data
> > > > > table, but also the smaller 350,000 record data extract.
> > > > >
> > > > > Maybe I should just throw in the towel!
> > > > >
> > > > > Any suggestions are appreciated!!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
> > Enterprise Mangler
> First time I've seen that one. LOL...
>
Really? Wow. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||John,
There is a situation where you can end up with deleted data from Enterprise
Manager. If you increase a constraint level in a table using Enteprise
Manager, and then apply the script that the table designer creates, you can
lose data.
For example, if you change a column constraint from NULL to NOT NULL, and
the data has some NULLs in it, the resulting script, when you run it, will
begin a transaction
rename the original table
create the new table with NOT NULL
insert the data from the renamed table into the new table
the insert will fail, because of NULLs in the data
the error is not trapped
the renamed table is dropped
the transaction is committed.
... and voila! a new table with no data in it.
So I'm with Kalen, don't use Entperprise Manager to edit table structures.
Ron
--
Ron Talmage
SQL Server MVP
"John Bishop" <ugradfrnd@.aol.com> wrote in message
news:OBZZrjcvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Thank you, Geoff! That did the trick! The data WAS there all along.
> It is more than a little disorienting though, when one is tired and
> ill-acquainted with SQL and a data table seems to LOSE all of its records!
> But these test runs are all about learning the ideosyncracies of a product
> before trying to use it in production!
> The problem appears to have been between the chair and the keyboard all
> along!!
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e9Z36acvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> > Run DBCC UPDATEUSAGE to correct the row count display in Enterprise
> Mangler.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > news:%231Z4HYcvEHA.2016@.TK2MSFTNGP15.phx.gbl...
> > > Your explanation makes SENSE and yes, it appears that I was checking
> > > immediately after the change. (Bear in mind that I am still feeling
my
> > way
> > > along.)
> > >
> > > The indication that the records were DELETED is that Server Enterprise
> > > Manager reports that the table has ZERO Rows. I had made the change
in
> > Data
> > > Type in Server Enterprise Manager and then was making notes and
> > documenting
> > > what I had done. I went back to note the record count (which I had
> > > previously found was conveniently reported in the Table Properties in
> > Server
> > > Enterprise Manager and it gave me the sensation that ALL of the data
had
> > > been deleted.
> > >
> > > Now, I am thinking that it is doing precisely as you describe. I was
> able
> > > to use the Import and Export Data tool to COPY all of the data from
that
> > > table to another table in a different database, which reports that it
> has
> > > 387,825 records (CORRECT). The original table STILL says in the
> > Properties
> > > that it has ZERO records, but that table has several Indices that are
> > > probably being rebuilt.
> > >
> > > But, the larger database from last night STILL SAYS that it has ZERO
> > records
> > > nine hours after the records appeared to all be deleted. But it has
> nine
> > > million records and more than a few indices. And I am running this
SQL
> > > Evaulation on an older 866 MHz single processor system with only 512
Mbs
> > of
> > > memory.
> > >
> > > Is there some way to monitor the things that SQL is doing in the
> > BACKGROUND
> > > to these tables?
> > >
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ORaVrEcvEHA.2584@.TK2MSFTNGP10.phx.gbl...
> > > > Of course you backed up your database, prior to making major changes
> to
> > > > tables, correct?
> > > >
> > > > Whew..thank goodness.
> > > >
> > > > And no, generally records are not deleted. However, behind the
scenes,
> > > it's
> > > > probably renaming the table, creating a new structure, then
inserting
> > the
> > > > records, then dropping and renaming the temp table.
> > > >
> > > > Did you check if the data was there immediately after changing the
> > column
> > > > datatype?
> > > >
> > > > Jeff
> > > > "John Bishop" <ugradfrnd@.aol.com> wrote in message
> > > > news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> > > > > Last night, I posted the message "HELP!: A Disasterous Delete"
> > (Friday,
> > > > > October 29, 2004 1:20 AM). This morning, as I pressed ahead with
my
> > > work
> > > > > with my smaller data extract, I have had a recurrence of the
problem
> > of
> > > > the
> > > > > mysterious deletion of ALL RECORDS, but now with the smaller data
> > > extract.
> > > > >
> > > > > But I have a better sense now of what may have CAUSED the problem
(I
> > > > think).
> > > > > I had just used SQL Server Enterprise Manager to Change the Data
> Type
> > of
> > > > two
> > > > > columns from char to nvarchar. I was then re-running a query.
> > > > Thereafter,
> > > > > in noticed that ALL ROWS of the table seemed to be missing.
> > > > >
> > > > > Perhaps I am SPOILED by the way MS Access handles changes to a
> > > > datastructure
> > > > > with data records in place.
> > > > >
> > > > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE
> COLUMN
> > > IS
> > > > > ALTERED? This seems like a rather radical treatment to the data
> > > within
> > > > a
> > > > > table if this is the case.
> > > > >
> > > > > Now I seem to have lost NOT ONLY the original larger 9 million
> record
> > > data
> > > > > table, but also the smaller 350,000 record data extract.
> > > > >
> > > > > Maybe I should just throw in the towel!
> > > > >
> > > > > Any suggestions are appreciated!!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>