issue with script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with script

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-27 : 19:50:23

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim fso
Dim f
dim objCN
dim objCMD
dim objRS
Dim file_modified_date

strConnectionString = "Provider=SQLOLEDB.1;Password=data$;Persist Security Info=True;User ID=data_migration;Initial Catalog=import_export;Data Source=10.50.220.139\REALM"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("N:\Test FilePath")

For Each file In f.Files

'File Name
'-----------------
file_name= file.Name
'msgbox file_name

'File Location
'---------------------
file_location =file.path
'msgbox file_location

'Length of File
'------------------------
split_extension = Split(File.Name, ".", -1, vbBinaryCompare)
length_of_file_name = Len(split_extension(0))
'msgbox length_of_file_name

'File Extension
'------------------------
file_extension =split_extension(1)
'msgbox file_extension

'Doc Abbreviation
'----------------------------
file_abbreviation =Right(split_extension(0),length_of_file_name-10)
'msgbox file_abbreviation

'File Modified Date
'------------------------------
file_modified_date = file.DateLastModified
msgbox file_modified_date

set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open strConnectionString
objCMD.ActiveConnection = objCN
objCMD.CommandText = "test"
objCMD.CommandType = 4 'adCmdStoredProc
objCMD.Parameters.Append objCMD.CreateParameter("@file_name",200,1,100,file_name)
objCMD.Parameters.Append objCMD.CreateParameter("@file_location",200,1,7000,file_location)
objCMD.Parameters.Append objCMD.CreateParameter("@file_extension",200,1,20,file_extension)
objCMD.Parameters.Append objCMD.CreateParameter("@file_abbreviation",200,1,20,file_abbreviation)
objCMD.Parameters.Append objCMD.CreateParameter("@file_modified_date",135,1,8,file_modified_date)
Set objRS = objCMD.Execute
set objRS = nothing
set objCMD = nothing
set objCN = nothing

Next

Main = DTSTaskExecResult_Success
End Function

Here is the Vbscript for getting information of a file in a particular directory.now I have an
issue where there can be multiple folders and subfolder in there.how can I search and get those
information.How to use a recursive function in vbscript for DTS

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-27 : 21:30:24

This is t-sql?
From an sp you would just xp_cmdshelll a dir /s command.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources