How to get a size of the file in SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to get a size of the file in SQL?

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:01:34

Hello All,

Is there anyway I can get size of the file, located on a local drive, in Transact-SQL?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 09:03:03

Yes. But SSIS is better suited for this.

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:06:58

quote:
Originally posted by Peso

Yes. But SSIS is better suited for this.

E 12°55'05.63"
N 56°04'39.26"

and what would SSIS be and how can I use it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 09:07:37

use xp_getfiledetails if you're in sql 2000. its not available in sql 2005

http://forums.databasejournal.com/showthread.php?t=41753

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:11:58

quote:
Originally posted by visakh16

use xp_getfiledetails if you're in sql 2000. its not available in sql 2005

http://forums.databasejournal.com/showthread.php?t=41753

I'm doing it in SQL 2005 so is it worth a try or it won't work?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-19 : 09:14:37

use clr integration for this. create a sporc that will do this in C# or VB and use it SQL.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:18:59

quote:
Originally posted by spirit1

use clr integration for this. create a sporc that will do this in C# or VB and use it SQL.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

that sounds kind of difficult and long winded process for me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 09:22:24

[code]CREATE TABLE #Files
(
row VARCHAR(400)
)

INSERT #Files
(
row
)
EXEC master..xp_cmdshell 'DIR C:\TEMP\*.zip /s'

SELECT fileName,
fileSize
FROM (
SELECT SUBSTRING(row, 37, 400) AS fileName,
REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize
FROM #Files
) AS d
WHERE fileSize NOT LIKE '%[^0-9]%'

DROP TABLE #Files[/code]

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:28:08

quote:
Originally posted by umertahir

quote:
Originally posted by visakh16

use xp_getfiledetails if you're in sql 2000. its not available in sql 2005

http://forums.databasejournal.com/showthread.php?t=41753

I'm doing it in SQL 2005 so is it worth a try or it won't work?

I tried above and I get the following error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:41:56

quote:

Originally posted by Peso

CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR C:\TEMP\*.zip /s'

SELECT	fileName,
	fileSize
FROM	(
		SELECT	SUBSTRING(row, 37, 400) AS fileName,
			REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize
		FROM	#Files
	) AS d
WHERE	fileSize NOT LIKE '%[^0-9]%'

DROP TABLE	#Files

E 12°55'05.63"
N 56°04'39.26"

I have tried the above code modified as below by just giving the file name to it:


CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat /s'

SELECT	fileName,
	fileSize
FROM	(
		SELECT	SUBSTRING(row, 37, 400) AS fileName,
			REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize
		FROM	#Files
	) AS d
WHERE	fileSize NOT LIKE '%[^0-9]%'

Result:
(12 row(s) affected)
fileName fileSize
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:45:36

and when i run the command in command prompt, it works fine:

C:\>DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat /s
Volume in drive D is DATA
Volume Serial Number is A0F5-E65D

Directory of D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol

10/10/2008 16:28 30,739,850 test.dat
1 File(s) 30,739,850 bytes

Total Files Listed:
1 File(s) 30,739,850 bytes
0 Dir(s) 4,864,012,288 bytes free

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 09:46:21

Try this first and post back the result

CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'

SELECT * FROM #Files

DROP TABLE #Files

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 09:47:26

Fixed. You had comma as separator for file size

CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR C:\TEMP\aapr.zip /s'

SELECT	fileName,
	CAST(fileSize AS BIGINT) AS fileSize
FROM	(
		SELECT	SUBSTRING(row, 37, 400) AS fileName,
			REPLACE(REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), ''), ',', '') AS fileSize
		FROM	#Files
	) AS d
WHERE	fileSize NOT LIKE '%[^0-9]%'

DROP TABLE	#Files

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:48:43

quote:
Originally posted by Peso

Try this first and post back the result
CREATE TABLE #Files
(
row VARCHAR(400)
)

INSERT #Files
(
row
)
EXEC master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'

SELECT * FROM #Files

DROP TABLE #Files

E 12°55'05.63"
N 56°04'39.26"

Result

(9 row(s) affected)
row
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Volume in drive D is DATA
Volume Serial Number is A0F5-E65D
NULL
Directory of D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol
NULL
10/10/2008 04:28 PM 30,739,850 test.dat
1 File(s) 30,739,850 bytes
0 Dir(s) 4,864,012,288 bytes free
NULL

(9 row(s) affected)

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 09:51:27

quote:

Originally posted by Peso

Fixed. You had comma as separator for file size

CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR C:\TEMP\aapr.zip /s'

SELECT	fileName,
	CAST(fileSize AS BIGINT) AS fileSize
FROM	(
		SELECT	SUBSTRING(row, 37, 400) AS fileName,
			REPLACE(REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), ''), ',', '') AS fileSize
		FROM	#Files
	) AS d
WHERE	fileSize NOT LIKE '%[^0-9]%'

DROP TABLE	#Files

E 12°55'05.63"
N 56°04'39.26"

sorry, no affect..result is still blank:

(12 row(s) affected)
fileName fileSize
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------

(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:02:32

Can you run this again

CREATE TABLE	#Files
		(
			row VARCHAR(400)
		)

INSERT	#Files
	(
		row
	)
EXEC	master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'

SELECT * FROM #Files

DROP TABLE #Files

And post the results using [ code ] tags? You can click # button and paste the result between the [ code ] tags.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:04:16

You will have to alter the SUBSTRING start and length parameter values.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-19 : 10:05:03

I saved a vbs file to C:\temp\fs1.vbs
Here is vbs file contents:

Dim ArgObj, var1
Set ArgObj = WScript.Arguments
Set WshShell = WScript.CreateObject("WScript.Shell")

var1 = ArgObj(0)
filename=var1
Set fso = CreateObject("Scripting.FileSystemObject")
set mainfile=fso.GetFile(filename)
Wscript.Echo mainfile.Size

Then run following tsql:
exec master..xp_cmdshell 'Cscript.exe //NOLOGO C:\temp\fs1.vbs "C:\temp\test.html"'

Obvously you could save this as Proc and pass file name in.

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 10:06:05

quote:
Originally posted by Peso

You will have to alter the SUBSTRING start and length parameter values.

E 12°55'05.63"
N 56°04'39.26"

Would I have to alter the substring everytime I look for some new file?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:11:06

No. This is a one time operation, beacuase for some reason the SUBSTRING parameter values differs from your locale to my locale.

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-19 : 10:14:00

quote:
Originally posted by Peso

No. This is a one time operation, beacuase for some reason the SUBSTRING parameter values differs from your locale to my locale.

E 12°55'05.63"
N 56°04'39.26"

Ok no problem, thanks for pointing me to the right direction though. cheers

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources