Loading a Series of Flat Files
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ajones/load.asp
.
-
Good article. Always like articles with code that I can play with
One comment I would make though is that I'd eliminate the GOTO's by using positive logic. eg:
IF @rtn = 0
begin
do something
if @rtn = 0
begin
do something more
end
else
begin
flag error
end
end
else
begin
flag error
endAlso, is it my browser settings or your formatting that double spaces all the lines?
Edited by - phillcart on 10/31/2002 02:39:37 AM
--------------------
Colt 45 - the original point and click interface
-
Good article - I agree that it's good to see the code as well. Perhaps it ought to be downloadable, and have a version with your error checking as this is very important.
It would be interesting to write a VB app, using ADO to load the data, to see which would be fastest.
Personally I would use VB, but I'm a little biased !
thanks
John, UK
-
Thanks for your comments - some answers: -
I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.
I think the double space formatting is my fault!
With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.
Regards,
Andy Jones
.
-
My biggest project of the last 2 years has been a process that automatically logs and imports files that are uploaded to our server from stores. This process handles both transaction and customer files from 4 different POS systems, each with their own file format. The files also come compressed/archived in .ZIP, .Tar, or .tar.Z formats. (I skipped a lot of details, it gets even more 'interesting'.)
For my solution, I used 3 DTS packages. The first one has an ActiveX script that uses the file system object to loop through the files in the directory, logs any new ones, and performs any extraction that is necessary. This happens every 5 minutes.
The second package, which is executed every 30 minutes, goes through the log (a table) searching for files that haven't imported, and calls the third package for each file that needs to be imported, passing the filename via a global variable.
That's the basics of the process. I personally like the DTS method better because of flexibility, error handling capability, and logging. Whenever an error occurs, I get an email telling me where it occurred, and if it happens in an ActiveX script, the email includes the line of code where the error occurred. I also prefer to keep my DTS packages in SQL Server, its simpler to access them, and they're always backed up.
One final advantage to the way I do it, everything I use is documented. May not be well-documented, but at least there's something there. You want to be careful about using undocumented stuff, especially since it may not always be there.
Anyway, there's my 2-cents worth. If anyone is interested in more information, I'll be happy to provide. My process works; that's gotta be worth something.
James C Loesch
James C Loesch -
quote:
Thanks for your comments - some answers: -I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.
I think the double space formatting is my fault!
With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.
Regards,
Andy Jones
You can still keep the error logging in one place. In my example where I've said flag error this could set an error flag and/or build an error message. Then at the end of the proc you'd just check to see if an error occurred and then log it.
I had a bad experience with goto's as a child and have been mortally afraid of them ever since
Thanks
Phill Carter
Edited by - phillcart on 10/31/2002 4:11:12 PM
--------------------
Colt 45 - the original point and click interface -
Interesting solution. Thanks for bringing those undocumented xp's to light. 🙂
Personally, I'd do a couple things different. It's probably the programmer in me, but I'd replace sp_GetFiles, and sp_FolderFileExist with functions. Then instead of working with ##Temp tables, your curser would be based on
select * from dbo.GetFiles(@myfolder)
I'd also use BCP instead of DTS to load the files. Again, personal preference.
(Johnhind, I'd put money on dts bulk load or bcp over any vb solution.)
Thanks again,
John
-
Good point about using a UDF. With regard to using BCP, my original requirement used files which didn't have a consistent number of fields on each line e.g.: -
1,2
1,2,3
1
etc...
I couldn't get BCP / Bulk insert to work with these types of files, so that was the reason behind using DTS.
Thanks for the reply.
Regards,
Andy Jones
.
-
Andy,
Must be fixed width fields, otherwise you could spedify the delimeter in the BCP command. In the case of fixed width files, you could bcp into a generic table:
create table Import
( id int identity primary key,
data varchar(4000)
)
Then parse the data field as needed in sql. Just an option.
John
-
I have been doing this for years with stored procs, BCP, and a few control tables. Multiple Import sources, multiple import structures within the same file, going to multiple tables determined by structure of data line.
I was very interested in seeing it done using DTS, excellent article....
-
I am currently doing this same thing in SQL 7 and SQL 2K. Please read whole article.
Our older process uses DTS and BCP to load data and process it. This process also uses TEMP tables (I know BAD BAD BAD) I have rewritten that particular piece of the code.
I have created several different VB apps that look for the text files and write a record into SQL (ADO using stored-procedure). If the file is found it writes a flag stating TRUE else FALSE.
I then have the DTS packages scheduled to run around each other that look in the log to see if the file is there, if we have processed today, etc.. If all conditions are TRUE then we perform the INSERT.
I have created a LINKED SERVER to handle the text files. I have also created a schema.ini file to handle column names and data types. This way SQL handles ALL processing.
The old process ran on average 1 hour 30 to 1 hour 50 minutes. Using the LINKED SERVER and with more validations the process takes 5 minutes 20 seconds.
just a different way of looking at the issue.
I can post sample code if you would like to look at it.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I think we would be interested to see how you do it !
John
-
quote:
I think we would be interested to see how you do it !John
John,
please find the code below. I have provided the VB6 code, the DTS package, and a couple of stored-procedures. This is a lot of data to weed through and may help you. Please note that you can command line extracts for .tar, .Z, .tar.Z.
If I have missed anything please let me know and I will provide it to you.
Thanks,
AJ Ahrens
////////////////////////////
// Code to create LINKED SERVER
exec sp_addlinkedserver
@server = 'txtSrvr',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet (Text IISAM)',
@datasrc = 'c:\MyDir\',
@provstr = 'Text'
go
exec sp_addlinkedsrvlogin 'txtSrvr', false, NULL, 'admin'
go
--SELECT * FROM [txtSrvr]...[TextFile#txt]
go
// Code for DTS package to determine if daily process ready
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\aahrens.GBSNT001\Desktop\Daily Disp Process Loader.bas
'Package Name: Daily Disp Process Loader
'Package Description:
'Generated Date: 11/12/2002
'Generated Time: 9:24:28 AM
'****************************************************************
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "Daily Disp Process Loader"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection As DTS.Connection2
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("SQLOLEDB.1")
oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "P2C"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Locale Identifier") = 1033
oConnection.ConnectionProperties("Prompt") = 4
oConnection.ConnectionProperties("General Timeout") = 0
oConnection.ConnectionProperties("Use Procedure for Prepare") = 1
oConnection.ConnectionProperties("Auto Translate") = True
oConnection.ConnectionProperties("Packet Size") = 4096
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.ConnectionProperties("Workstation ID") = "PLB1"
oConnection.Name = "PLB1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 0
oConnection.Catalog = "P2C"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSCreateProcessTask_1"
oStep.Description = "Check for DONE file"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSCreateProcessTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a precedence constraint for steps defined below
Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint = oStep.precedenceConstraints.New("DTSStep_DTSCreateProcessTask_1")
oPrecConstraint.StepName = "DTSStep_DTSCreateProcessTask_1"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task DTSTask_DTSCreateProcessTask_1 (Check for DONE file)
Call Task_Sub1(goPackage)
'------------- call Task_Sub2 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)
Call Task_Sub2(goPackage)
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving
package line
Set goPackage = Nothing
Set goPackageOld = Nothing
End Sub
'------------- define Task_Sub1 for task DTSTask_DTSCreateProcessTask_1 (Check for DONE file)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.CreateProcessTask2
Set oTask = goPackage.Tasks.New("DTSCreateProcessTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSCreateProcessTask_1"
oCustomTask1.Description = "Check for DONE file"
oCustomTask1.ProcessCommandLine = "K:\DONEFileCheck.exe"
oCustomTask1.SuccessReturnCode = 0
oCustomTask1.Timeout = 0
oCustomTask1.TerminateProcessAfterTimeout = False
oCustomTask1.FailPackageOnTimeout = True
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
'------------- define Task_Sub2 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)
Public Sub Task_Sub2(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask2.Description = "Execute SQL Task: undefined"
oCustomTask2.SQLStatement = "spCheckIfDailyRunReady"
oCustomTask2.ConnectionID = 1
oCustomTask2.CommandTimeout = 0
oCustomTask2.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
End Sub
// CODE for stored-procedure that will fire off daily process if all requirements are TRUE
CREATE PROCEDURE spCheckIfDailyRunReady
/*
Created:10/03/2002
Created By:AJ Ahrens - SQL DBA AT&T Labs x73375
Purpose:Determine whether or not to process Daily DISP job
*/
AS
IF (SELECT MAX(CONVERT(VARCHAR, LastRunDateTime, 101))
FROM tblDailyLog WHERE JobId = 1) = CONVERT(VARCHAR, GETDATE(), 101)
-- Above determines Daily has been processed today
BEGIN
SELECT 'Daily already processed today'
RETURN(0)
END
ELSE
BEGIN
IF (SELECT MAX(DailyDoneId) -- Ensures we receive only 1 record back
FROM tblDailySearchLog TS
WHERE TS.JobId = 1 AND FoundYN = -1 AND DATEDIFF(n, SearchDateTime, GETDATE()) < 30) IS NULL
-- Above WHERE clause is to determine whether DONE file found within last (n) minutes
BEGIN
SELECT 'DONE file not found'
RETURN(0)
END
ELSE
BEGIN
INSERT INTO tblDailyLog (JobId, LastRunDateTime) VALUES (1, GETDATE())
EXEC msdb..sp_start_job 'Daily Disp' -- Executes the job Daily Disp
END
END
GO
// CODE to perform pull from LINKED SERVER
CREATE PROCEDURE spA_DISPDailyRaw_Sessions
/*
Created:10/22/2002
Created By:AJ Ahrens - SQL DBA AT&T Labs x3375
Location:SERVER
Purpose:* IMPORT on a daily basis DISP daily records
* replaces old DTS package <Daily Sessions Import>
*/
@JobId NUMERIC
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwDuplicateRaw_Sessions]') and
OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwDuplicateRaw_Sessions]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwRaw_SessionsInSessions]') and
OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwRaw_SessionsInSessions]
TRUNCATE TABLE dbo.Raw_Sessions
If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions_Temp')
DROP INDEX Raw_Sessions.IX_RawSessions_Temp
If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions')
DROP INDEX Raw_Sessions.IX_RawSessions
If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions1')
DROP INDEX Raw_Sessions.IX_RawSessions1
INSERT INTO Raw_Sessions
(Account, Userid, Service, Sub_Account, Origin_Country, Billing_Country, Resource, Resource_Qty,
Session_Begin, Session_End, System_Id, Dest_Net_Flag, Dnis, Modem_Speed, Isdn_Seconds,
Isdn_Channels, Record_Cut, Chassis_Id, Port_Number, Disconnect_Type, Nas_Port_Type, Input_Bytes,
Output_Bytes, Input_Packets, Output_Packets, Nas_Ip, Client_Ip, Client_Port_Dnis, Caller_Id)
SELECT
UPPER(Account), Userid, Service, Sub_Account, Origin_Country, Billing_Country, Resource, Resource_Qty,
LEFT(Session_Begin, 23) Session_Begin, LEFT(Session_End, 23) Session_End, System_Id,
Dest_Net_Flag, Dnis, Modem_Speed, Resource_Qty * ISNULL(Isdn_Channels, 0), Isdn_Channels, Record_Cut,
ISNULL(Chassis_Id, '00000000'), ISNULL(Port_Number, '000'), ISNULL(Disconnect_Type, '0'),
ISNULL(Nas_Port_Type, '0'), ISNULL(Input_Bytes, '0'), ISNULL(Output_Bytes, '0'),
ISNULL(Input_Packets, '0'), ISNULL(Output_Packets, '0'), ISNULL(Nas_Ip, '255.255.255.255'),
ISNULL(Client_Ip, '255.255.255.255'), ISNULL(Client_Port_Dnis, '19999999999'), Caller_Id
FROM txtSrvr...[P2CAny#txt]
-- Create temporary index for deletion process performance increase
If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions_Temp')
CREATE INDEX [IX_RawSessions_Temp]
ON [dbo].[Raw_Sessions]([Account], [Userid], [Resource], [Session_Begin],
[Session_End], [System_Id], [Chassis_Id], [Port_Number]) ON [PRIMARY]
If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions')
CREATE INDEX [IX_RawSessions] ON [dbo].[Raw_Sessions]([Account]) ON [PRIMARY]
If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions1')
CREATE INDEX [IX_RawSessions1] ON [dbo].[Raw_Sessions]([Resource]) ON [PRIMARY]
-- Removes any old data from table from prior run(s) if any
DELETE FROM tblDailyDuplicateCounts WHERE JobId = @JobId AND CONVERT(VARCHAR, RunDateTime, 101) = CONVERT(VARCHAR,
GETDATE(), 101)
-- inserts basic information into count table for tracking purposes
INSERT INTO tblDailyDuplicateCounts (JobId, RunDateTime, RecordsLoadedIntoRawSessions)
SELECT @JobId, GETDATE(), COUNT(*)
FROM Raw_Sessions
EXEC dbo.spCreateRawSessionViews
GO
// CODE for VB6 executable
// normal module
Option Explicit
Global bRanToday As Boolean
Global sServerName As String
Global sConn As String
Global iJobId As Integer
Sub Main()
'' Created: 10/16/2002
'' Created By: AJ Ahrens - SQL DBA AT&T Labs x3375
'' Location: K$\root of servers
'' Purpose: Provide small efficient way of checking for DONE file to start _
Daily DISP process
'' --
'' Modified: 10/21/2002
'' Modified By: AJ Ahrens - SQL DBA AT&T Labs x3375
'' Location: SAB
'' Purpose: Provide comments
Dim sFile As String
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim iFound As Integer
Dim clsCheck As New clsCheckIfRanToday
Dim sTest As String
Dim sFilePath As String
On Error GoTo ErrorTrap
iFound = 0 '' Default to state that DONE file has not been found
iJobId = 1 '' Daily Disp Job Id
sFilePath = "\Inetpub\ftproot\P2C\P2Cany.ftp.done"
Select Case Environ$("ComputerName") '' Part of internal functions that determines _
PC/Server name
Case Is = "DISP2", "PLB1", "DISP5"
sFile = Dir("K:" & sFilePath) '' Path to where DONE file should be
sServerName = Environ$("ComputerName")
Case Else
sFile = Dir("\\164.120.88.81\K$" & sFilePath)
'' Default if not ran from Server is to be DISP2 _
IP address
sServerName = "164.120.88.81" '' Default Server name to be DISP2 IP address
End Select
sConn = "Driver={SQL Server};Server=" & sServerName & ";db=P2C;trusted_connection=true"
'' Creates connection string to be used for ADO _
connections
clsCheck '' Class module that checks to see if process _
already ran today
If bRanToday = True Then Exit Sub '' If process has already ran get out without _
updating Search log
If sFile <> "" Then '' Code to determine IF file found update _
flag to state same
iFound = -1
End If
Set adoConn = New ADODB.Connection '' Performs late-binding routine
With adoConn '' Opens ADO connection to server
.ConnectionString = sConn
.CommandTimeout = 0
.Open
End With
Set adoCmd = New ADODB.Command '' performs late-binding routine
With adoCmd '' Opens ADO command to INSERT search attempt
.ActiveConnection = adoConn '' establishes what OPEN ADO connection to use
.CommandType = adCmdText '' Determines what type of command to perform
.CommandText = "INSERT INTO P2C.dbo.tblDailySearchLog (SearchDateTime, JobId, FoundYN) " & _
"VALUES (GetDate(), " & iJobId & ", " & iFound & ")"
'' SQL command string here
.Execute '' Tells system to perform SQL command
End With
CleanUp:
Set adoCmd = Nothing '' Destroys ADO Command object (There is no _
CLOSE option)
If adoConn.State = adStateOpen Then '' Checks to ensure the ADO Connection is _
OPEN before CLOSE
adoConn.Close
End If
Set adoConn = Nothing '' Destroys ADO Connection regardless of _
above code
Exit Sub
ErrorTrap:
If Err.Number = 438 Then '' Isolates non-critical error from error-trap
Err.Clear
Resume Next
Else
MsgBox "Error: " + Str(Err.Number) + " Description: " + Err.Description
End If
GoTo CleanUp '' Ensures that object clean-up is performed
End Sub
// CODE for VB6 executable
// Class module
Private Function fDailyAlreadyProcessedToday()
Dim adoCon As ADODB.Connection
Dim adoCom As ADODB.Command
Dim adoRs As ADODB.Recordset
Set adoCon = New ADODB.Connection '' Performs late-binding routine
With adoCon '' Opens ADO connection with connection string from _
SUB MAIN()
.ConnectionString = sConn
.CommandTimeout = 0
.Open
End With
Set adoCom = New ADODB.Command '' Performs late-binding routine
With adoCom '' Prepares ADO command object with Connection _
and SQL command
.CommandText = "SELECT MAX(CONVERT(VARCHAR, LastRunDateTime, 101)) " & _
"FROM P2C.dbo.tblDailyLog WHERE JobId = " & iJobId
.CommandType = adCmdText
.ActiveConnection = adoCon
End With
Set adoRs = New ADODB.Recordset '' Performs late-binding routine
With adoRs '' Establishes cursor at CLIENT-SIDE in case _
record count req'd
.CursorLocation = adUseClient
End With
Set adoRs = adoCom.Execute '' Opens ADO redordset by EXECUTE of _
ADO command object
If "'" & adoRs.Fields(0) & "'" = "''" Then
bRanToday = False '' Routine to ensure program doesn't blow
GoTo Cleaner
Exit Function '' up in case 1st time run
End If
If CDate(adoRs.Fields(0)) = Date Then '' Checks ADO Recordset info to determine _
if process ran today
bRanToday = True '' If process ran today/ or not flags as such
Else
bRanToday = False
End If
Cleaner:
If adoRs.State = adStateOpen Then
adoRs.Close '' Closes ADO recordset
End If
Set adoRs = Nothing '' Destroys ADO recordset
Set adoCom = Nothing '' Destroys ADO command object
If adoCon.State = adStateOpen Then
adoCon.Close '' Closes ADO connection
End If
Set adoCon = Nothing '' Destroys ADO connection
End Function
Private Sub Class_Initialize()
fDailyAlreadyProcessedToday '' Runs PRIVATE function above
End Sub
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Andy,
THANK YOU, THANK YOU, THANK YOU!!!!!
In the bowels of your code, I finally found what I've not been able to find anywhere else... a replacement for using xp_CmdShell (security risk) to run a DIR command.
I knew that xp_DirTree would create a list of sub-directories off a given path but I didn't know it would produce a level controled list of files, as well (although I thought it should because it also uses XPStar.dll) which is why I kept looking). Even the SQL "Black Book" didn't have the 'level' and 'filesflag' switches that you incorporated into your code.
So even though your article is over 4 years old, I just wanted to say "Thanks"... you've made my job a heck of a lot easier ("Look Ma! No DTS!!! YIPPEEE!!!")
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply