Capturing file name and date/time of it.

Question

Capturing file name and date/time of it.

SQLIndia

Ten Centuries

Points: 1073
More actions
January 31, 2005 at 8:57 am

#165883

Hi Folks
I have this problem as I new for it.. That is
I need to capture filename and it's date and time into variables. Then use this information in order to carry further process. for example, I need to load the source data files depend by confirming file name and it's date and time.
I am writing this code in store procedure..
Thanks in advance
singhsonny@hotmail.com
SqlIndia

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Digs SSCertifiable Points: 7333 More actions · Answer 1

I dont think TSQL has windows API functions like Vb6.0, you may have to build TSQL and load info from client interface ( vb/Access).

Exporting by script from SQL table

http://vyaskn.tripod.com/programming_faq.htm

How to save the output of a query/stored procedure to a text file using T-SQL? <top>

T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:

From command prompt:

osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"

From T-SQL:

EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'

Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.

BCP and Data Transformation Services (DTS) let you export table data to text files.

hoo-t SSChampion Points: 12713 More actions · Answer 2

Here's a snippet of code from one of my procedures. My filenames include the date in the name, so thats all I need. However, by modifying the DIR command, you can get the file date as well. Load it into a single column table as I have here. Then you can parse the filename and filedate from the single column using string functions such as substring(), and either work with them at that point or load them into a second temporary table.

Declare

@path varchar(128),

@command varchar(128)

Create Table #file_table

( #file_name varchar(100) null)

set @path = 'X:\SQL_Backups\'

-- build/execute command to populate temporary table

select @command = 'master..xp_cmdshell ' + '"' + 'dir ' + @path + '/b /A:-D' + '"'

print @command

insert #file_table

exec (@command)

select * from #file_table

drop table #file_table

-- Steve

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

This might be better accomplished by a DTS package instead of a stored procedure.

An ActiveX script task in DTS can instantiate a Scripting.FileSystemObject or other COM component to inspect the filesystem and file properties, then call the relevant stored procedure and pass the necessary parameetrs.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

The code snippet above that uses xp_cmdshell is a common tool. I've used it for years for a variety of things. Just a couple of things to remember about it. It's command line and result set are both limited to 255 characters ... go figure ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

SQLIndia Ten Centuries Points: 1073 More actions · Answer 5

Thanks Guys.. It is really understandble..

PW: is this possible if you provide sample example of DTS package that you mentioned in your answer??

hoo-t: where can find more about dir command with example so i can play with it.

and any of you know BCP method for it..

thanks again

swarn

SqlIndia

hoo-t SSChampion Points: 12713 More actions · Answer 6

For more info about the DIR command, shell out to the command prompt (Start/Programs/Accessories/Command Prompt) on your pc and type HELP DIR . It'll give you syntax and options.

There is a good book that goes into more detail about command prompt commands and syntax... "Windows NT Shell Scripting" by Tim Hill, published by New Riders Publishing, ISBN 1-57870-047-7.

Steve