T-SQL for creating a text file with with this filename format - Month(MM)Day(dd)Year(yyyy).Log

Question

T-SQL for creating a text file with with this filename format - Month(MM)Day(dd)Year(yyyy).Log

Agile

Ten Centuries

Points: 1153
More actions
June 27, 2010 at 8:40 pm

#97056

Guys, Is there a T-SQL that I can use to create a text file and with .Log file extension?
The process would be, I will get the data from a view, so for example the output of the view are:
0009510948A2010062560805I
0009510948B2010062562101O
0010502005B2010062660728O
0010502005A2010062561853I
0002008137B2010062561935O
0020051048B2010062660752O
0020051048A2010062562019I
0002008038A2010062560803I
0002008038B2010062561915O
so these data will be saved on a specific location (ex. D:\DataFile) as textfile with this filename format - Month(MM)Day(dd)Year(yyyy).Log
ex. 06282010.Log
What I tried so far is this BCP
bcp "sec.dbo.view_temptms" out "E:\testingofTMS.Log" -f -fbcp.fmt -c -P"."
And obviously I did not get the correct output. (ex. 06282010.Log)
because of the filename format (MM/dd/yyyy) - (I don't know how to make it dynamic. ) 🙁
and also I dont know if it's possible to include this BCP to my stored procedure. 🙁
If there's no other way, I will be force to create a seperate program just to create this log file. (vb.net application)
Please help..
Thanks
Morris
Thanks,
Morris

Viewing 15 posts - 1 through 15 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Agile (6/27/2010)
Guys, Is there a T-SQL that I can use to create a text file and with .Log file extension?
The process would be, I will get the data from a view, so for example the output of the view are:
0009510948A2010062560805I
0009510948B2010062562101O
0010502005B2010062660728O
0010502005A2010062561853I
0002008137B2010062561935O
0020051048B2010062660752O
0020051048A2010062562019I
0002008038A2010062560803I
0002008038B2010062561915O
so these data will be saved on a specific location (ex. D:\DataFile) as textfile with this filename format - Month(MM)Day(dd)Year(yyyy).Log
ex. 06282010.Log
What I tried so far is this BCP
bcp "sec.dbo.view_temptms" out "E:\testingofTMS.Log" -f -fbcp.fmt -c -P"."
And obviously I did not get the correct output. (ex. 06282010.Log)
because of the filename format (MM/dd/yyyy) - (I don't know how to make it dynamic. ) 🙁
and also I dont know if it's possible to include this BCP to my stored procedure. 🙁
If there's no other way, I will be force to create a seperate program just to create this log file. (vb.net application)
Please help..
Thanks
Morris

Can you use xp_CmdShell or not? I mean, which environment are you executing the BCP program from? Are you doing it from a CMD window or using xp_CmdShell or ????

--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)

Agile Ten Centuries Points: 1153 More actions · Answer 2

Thanks for the reply, Im using it in Cmd Prompt. (As .bat file)

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Agile (6/27/2010)
Thanks for the reply, Im using it in Cmd Prompt. (As .bat file)

Ok... and which date would you like to use? Today's date or something else? If something else, please be explicit.

--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)

Agile Ten Centuries Points: 1153 More actions · Answer 4

sorry for that... I have to get yesterday date.

Thanks

Morris

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Agile (6/27/2010)
sorry for that... I have to get yesterday date.
Thanks
Morris

In that case, I'd use sqlcmd to write out the correct bcp command to another batch file and then execute that other batch file. It's a whole lot easier than a VB Script or a Power Shell job.

Post back if you can't get it to work. I just don't want to take all the fun out of it for you. 🙂

--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)

Agile Ten Centuries Points: 1153 More actions · Answer 6

DECLARE @fn varchar(8000)

SELECT @fn = 'E:\'+ CAST(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(dd,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(yyyy,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) +'.Log'

DECLARE @cmd varchar(8000)

SELECT @cmd = 'bcp "sec.dbo.view_temptms" out ' + @fn + ' -f -fbcp.fmt -c -P"."'

EXEC master..xp_cmdshell @cmd

So far this is what I did, Yes it has an output of formatted filename as per requirement.

Problems are:

1. On the format of month, so for this month june it should be 06 not just 6.

2. I'd change my script, I'm not getting the data in views anymore, I just inserted the data to a variable (#FinalData), I have no idea if it's possible to call it in bcp, When I google it this what I found

EXEC xp_cmdshell 'BCP tempdb.dbo.##temptable OUT flatfilename ...'

,

I tried this and it doesnt work.

Please advice..

Thanks

Morris

Thanks,
Morris

Agile Ten Centuries Points: 1153 More actions · Answer 7

At last I did it!, but If anyone see improvements on this script, please do tell me.

DECLARE @fn varchar(8000)

SELECT @fn = CASE

WHEN LEN(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))))=1 THEN 'E:\'+ '0' + CAST(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(dd,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(yyyy,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) +'.Log'

WHEN LEN(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))))=2 THEN 'E:\'+ CAST(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(dd,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(yyyy,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) +'.Log'

END

DECLARE @cmd varchar(8000)

SELECT @cmd = 'bcp "##LogWithNightShift" out ' + @fn + ' -f -fbcp.fmt -c -P"."'

EXEC master..xp_cmdshell @cmd

Thanks

Morris

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Agile (6/28/2010)
At last I did it!, but If anyone see improvements on this script, please do tell me.
DECLARE @fn varchar(8000)
SELECT @fn = CASE
WHEN LEN(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))))=1 THEN 'E:\'+ '0' + CAST(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(dd,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(yyyy,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) +'.Log'
WHEN LEN(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))))=2 THEN 'E:\'+ CAST(DatePart(mm,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(dd,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) + CAST(DatePart(yyyy,DateAdd(d,-1,DateAdd(d, DateDiff(d, 0, GetDate()), 0))) AS varchar) +'.Log'
END
DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp "##LogWithNightShift" out ' + @fn + ' -f -fbcp.fmt -c -P"."'
EXEC master..xp_cmdshell @cmd
Thanks
Morris

Very cool, Morris. Thanks for the feedback.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 9

I would suggest the following:

declare @MyDate datetime,

@fn varchar(100),

@cmd varchar(200)

set @MyDate = DateAdd(day, -1, getdate())

set @fn = 'E:\' +

right('00' + convert(varchar(2), month(@MyDate)), 2) +

right('00' + convert(varchar(2), day(@MyDate)),2) +

convert(char(4), year(@MyDate)) + '.Log'

SELECT @cmd = 'bcp "##LogWithNightShift" out ' + @fn + ' -f -fbcp.fmt -c -P"."'

print @cmd

-- EXEC master..xp_cmdshell @cmd

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Agile Ten Centuries Points: 1153 More actions · Answer 10

Thanks wayne for the suggestion, Im just wondering, Because when I use your solution, I did not declare variable for Date. Is there a difference in performance on doing that? Or just for the purpose of readability of the codes?

declare @fn varchar(100),

@cmd varchar(200)

set @fn = 'E:\' +

right('00' + convert(varchar(2), month(DateAdd(day, -1, getdate()))), 2) +

right('00' + convert(varchar(2), day(DateAdd(day, -1, getdate()))),2) +

convert(char(4), year(DateAdd(day, -1, getdate()))) + '.Log'

SELECT @cmd = 'bcp "##LogWithNightShift" out ' + @fn + ' -f -fbcp.fmt -c -P"."'

print @cmd

--EXEC master..xp_cmdshell @cmd

Thanks,
Morris

WayneS SSC Guru Points: 95460 More actions · Answer 11

Agile (6/28/2010)
Thanks wayne for the suggestion, Im just wondering, Because when I use your solution, I did not declare variable for Date. Is there a difference in performance on doing that? Or just for the purpose of readability of the codes?

Well, you're doing 3 date manipulations, while I'm only doing one. If this was processing a lot of records, my method would win over yours. However, both will be extremely fast. And since this is just being executed one time, from a performance standpoint it won't make a difference.

IMO, in the readability / maintainability, I think my method wins out there also.

But, your method is not wrong.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Agile Ten Centuries Points: 1153 More actions · Answer 12

Thanks for the answer. I'll take note of that.

Morris

Thanks,
Morris

vince.chittenden Newbie Points: 5 More actions · Answer 13

WayneS (6/28/2010)
I would suggest the following:
declare @MyDate datetime,
@fn varchar(100),
@cmd varchar(200)
set @MyDate = DateAdd(day, -1, getdate())
set @fn = 'E:\' +
right('00' + convert(varchar(2), month(@MyDate)), 2) +
right('00' + convert(varchar(2), day(@MyDate)),2) +
convert(char(4), year(@MyDate)) + '.Log'
SELECT @cmd = 'bcp "##LogWithNightShift" out ' + @fn + ' -f -fbcp.fmt -c -P"."'
print @cmd
-- EXEC master..xp_cmdshell @cmd

If the month function never returns a zero-length string (and it never will in this case, AFAICS), then why not '0' instead of '00'?

The second '0' appears to be totally redundant:

set @fn = 'E:\' +

right('0' + convert(varchar(2), month(@MyDate)), 2) +

right('0' + convert(varchar(2), day(@MyDate)),2) +

convert(char(4), year(@MyDate)) + '.Log'

Vinn

Less is more

WayneS SSC Guru Points: 95460 More actions · Answer 14

vince.chittenden (6/29/2010)
If the month function never returns a zero-length string (and it never will in this case, AFAICS), then why not '0' instead of '00'?

You are correct, this would work.

I just got into the habit years ago that to ensure I had two characters, to always specify two.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2