Wednesday, June 10, 2015

VB script to send an ftp file after exporting the file with smartconnect


Dim  currentdate as Date= Date.Now()
Dim  DateStamp as String=currentdate.Year.ToString() + currentdate.Month.ToString().PadLeft(2,"0") + currentdate.Day.ToString().PadLeft(2,"0")
Dim TimeStamp as String=currentdate.Hour.ToString().PadLeft(2,"0") + currentdate.Minute.ToString().PadLeft(2,"0") + currentdate.Second.ToString().PadLeft(2,"0")

Const username As String = “username”
Const password As String = “password”
Const localFile As String = “\\localshare\filename.txt"

Dim remoteFile As String = “/ftpfolder/filename_" + DateStamp + ".txt”
Dim archiveFile as String = “\\archiveshare\filename_" + DateStamp + "_" + TimeStamp  +  ".txt"


Const host As String = “ftp://username:password@ftpipaddress"
Dim operation as string
Dim buffLength As Integer = 2048
Dim buff(buffLength - 1) As Byte
Try
 If not File.Exists(localFile)   then
return false
end if

Dim URI As String = host & remoteFile

operation="create ftp web request"
Dim ftp As System.Net.FtpWebRequest = CType(System.Net.FtpWebRequest.Create(URI), System.Net.FtpWebRequest)

ftp.Credentials = New System.Net.NetworkCredential(username, password)
ftp.KeepAlive = False
ftp.UseBinary = True
ftp.Timeout = 20000
ftp.Method=System.Net.WebRequestMethods.FTP.UploadFile

operation="getting fileinfo for local file"
Dim _FileInfo As New System.IO.FileInfo(localFile)
ftp.ContentLength = _FileInfo.Length

operation="opening filestream"
Dim _FileStream As System.IO.FileStream = _FileInfo.OpenRead()

operation="getrequeststream"
Dim _Stream As System.IO.Stream = ftp.GetRequestStream()

Dim contentLen As Integer = _FileStream.Read(buff, 0, buffLength)

Do While contentLen <> 0
operation="writing stream"
_Stream.Write(buff, 0, contentLen)
contentLen = _FileStream.Read(buff, 0, buffLength)
Loop

operation="closing"
_Stream.Close()
_Stream.Dispose()
_FileStream.Close()
_FileStream.Dispose()

System.IO.File.Move(localFile,archiveFile )

return true
Catch ex As Exception
'MessageBox.Show("operation=" + operation + "  " + ex.ToString(), "Upload Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
return false
End Try

SQL 2022 TSQL snapshot backups!

  SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...