File name with changing date - 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)

File name with changing date

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-02-13 : 11:09:54

Hi i have a file name which has the date as part of it... example asd_20070201.log
how can i handle this in SQL as the file name will keep changing.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-13 : 11:24:50

Don't be afraid to tell us what you are trying to do.

CODO ERGO SUM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-13 : 11:25:06

What do you mean by Handling it in SQL?
What you do with that file?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-02-13 : 11:31:43

Sorry, its a extract file.. that i get each day so it would have the file_name_date.log as its name. i need to get the contents of this file into my sql db. I am thinking about using the active x script in the DTS but am not to sure were to start..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-13 : 11:36:23

Then you can build file name and store it in some global variable. You can use this global variable later to refer to the file.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2007-02-20 : 07:49:24

You could also rename the file to a fixed name and create your DTS transforms to use that name.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-02-20 : 23:21:23

Sample only... you may have to change the Bulk Insert to include a format file or whatever... but you get the point...

DECLARE @FileName CHAR(16)
DECLARE @SQL      VARCHAR(8000)
    SET @FileName = 'asd_'+CONVERT(CHAR(8),GETDATE(),112)+'.log'

--PRINT @FileName

SET @SQL = '
BULK INSERT dbnamehere.dbo.tablenamehere
   FROM '''+@FileName+'''
   WITH 
      (
         FIELDTERMINATOR = ''|'',
         ROWTERMINATOR = ''|\n''
      )'

EXEC (@SQL)

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources