check a file size - 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 2005 Forums

Transact-SQL (2005)

check a file size

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-12 : 17:24:25

anyone got a good idea on how i can check the size of a file on the sql server?

lets say the filename is: c:\order\order3744.zip

i need to see if it's over a certain size.

thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 02:56:06

create table #t(files varchar(1000))
Insert into #t
EXEC master..xp_cmdshell 'dir c:\order\order3744.zip'

select substring(size_files,charindex(' ',size_files)+1 ,len(size_files))as backup_file,cast(substring(size_files,1,charindex(' ',size_files)-1) as money)/1024/1024 as file_size_MB from
(
select files,ltrim(substring(files,patindex('%[AP]M%',files)+2,len(files))) as size_files
from #t where files like '%ZIP'
) as t
drop table #t

Madhivanan

Failing to plan is Planning to fail

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-13 : 10:17:04

actually wrote a stored proc based off that concept... i found some stuff on the net a while after i posted.

check her out.


ALTER procedure [dbo].[filesize]
(@filesize int, @filename varchar(50))
as
declare @doscommand varchar(50), @size varchar(30),@s varchar(100), @i int
set @doscommand = 'dir c:\orders\'+@filename
CREATE TABLE #tempList (Files VARCHAR(500))
INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL @doscommand
DELETE #tempList WHERE Files LIKE '%<dir>%' OR Files LIKE ' %' OR Files IS NULL
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))
UPDATE #tempList SET files =LTRIM(files)
set @size = (SELECT LEFT(files,PATINDEX('% %',files)) AS Size FROM #tempList)
select @s = @SIZE
	select @i = patindex('%,%', @s)
	while @i > 0
	begin
		select @s = replace(@s, substring(@s, @i, 1), '')
		select @i = patindex('%,%', @s)
	end
	set @filesize = @s
	return @filesize

i'm trying to use cmdshell to run a cscript now, but i keep getting permission denied, any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources