SQL Server Performance Forum – Threads Archive

SELECT FROM FILE THAT HAS DATE ON END OF FILE NAME

Each month a file is created that contains the date at the end of the file. I would like to create a stored procedure that would obtain the correct file and use it in a from statement. example of the file name is perpetual1106. The next month it would be perpetual1206.

---

Based on the current date, or should it scan the files and simply choose the most recent one?

---

You can build a date string as you require:<br /><br />SELECT SUBSTRING(CONVERT(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,getdate(),1),1,2) + SUBSTRING(CONVERT(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,getdate(),1),7,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />

---

The problem is that the file is the from file. Example abc1106 – 1106 being the month year. Each month the file name is changed to the month year of the file. select * from abc1106

---

Is your file name format is fixed? and Is suffixed year and moth corresponds to the current moth and year then you can follow the Haywood method… If not only option is xp-cmdshell with dir command combination because no more xp-getfiledetails in 2005….
Ex:
drop table #file
create table #file (name varchar(200))
insert #file exec master..xp-cmdshell ‘dir c: /T:W /A:-D /b’
delete from #file where name is NUll
select * from #file where name like …
Mohammed U.

---

]]>