Non-Duplcating filenames - 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

General SQL Server Forums

New to SQL Server Administration

Non-Duplcating filenames

Author

Topic

Jmunoz69
Starting Member

6 Posts

Posted - 2009-10-20 : 13:09:09

I need to create multiple daily flat files and they must not overwrited each other. What do I need to do to edit this logic?

here is the logic used

Set @Filename = (
SELECT ('C:\Reports_Out\ReturnShip.2009.'+
substring(convert(varchar,getdate(), 101),1,2)+ '' + substring(convert(varchar,getdate(), 101),4,2)+
''+ substring(convert(varchar,getdate(), 101),7,4) + '.txt' ) as Filename )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 13:46:13

Here's what I use to get the datetime stamp: REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

Add your prefix to it and you should be good.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jmunoz69
Starting Member

6 Posts

Posted - 2009-10-20 : 14:08:20

Thanks Tara

This worked great, Can logic be modifed so that the filename does not include timestamp, but add "_1" when it encounters the same filename?

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 15:19:03

Please post a data example of what you want to make it clear. Someone should be able to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jmunoz69
Starting Member

6 Posts

Posted - 2009-10-20 : 15:22:54

Thanks Tara

Would like filenames to follow this naming convention:

ReturnShip.2009.10202009.txt

ReturnShip.2009.10202009_1.txt

ReturnShip.2009.10202009_2.txt

I need to create multiple daily flat files and they must not overwrited each other. What do I need to do to edit this logic?

here is the logic used

Set @Filename = (
SELECT ('C:\Reports_Out\ReturnShip.2009.'+
substring(convert(varchar,getdate(), 101),1,2)+ '' + substring(convert(varchar,getdate(), 101),4,2)+
''+ substring(convert(varchar,getdate(), 101),7,4) + '.txt' ) as Filename )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 16:08:48

Here you go:


DECLARE @s varchar(50), @i smallint

SET @i = 1

SET @s = 'ReturnShip.' + CONVERT(char(4), YEAR(GETDATE())) + '.' + REPLACE(CONVERT(char(10), GETDATE(), 101), '/', '') 

SET @s =  CASE WHEN @i > 0 THEN @s + '_' + CONVERT(varchar(3), @i) ELSE @s END

SET @s = @s + '.txt'

PRINT @s

Just change @i to whatever increment you are on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jmunoz69
Starting Member

6 Posts

Posted - 2009-10-20 : 16:27:50

Perfect

Very much appreciative of your help

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 17:01:15

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources