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" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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? |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 2005http://forums.databasejournal.com/showthread.php?t=41753 |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
umertahir
Posting Yak Master
154 Posts |
|
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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
that sounds kind of difficult and long winded process for me. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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, fileSizeFROM ( SELECT SUBSTRING(row, 37, 400) AS fileName, REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize FROM #Files ) AS dWHERE fileSize NOT LIKE '%[^0-9]%'DROP TABLE #Files[/code] E 12°55'05.63"N 56°04'39.26" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 2005http://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 1SQL 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. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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, fileSizeFROM ( SELECT SUBSTRING(row, 37, 400) AS fileName, REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize FROM #Files ) AS dWHERE 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, fileSizeFROM ( SELECT SUBSTRING(row, 37, 400) AS fileName, REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), '') AS fileSize FROM #Files ) AS dWHERE fileSize NOT LIKE '%[^0-9]%' Result:(12 row(s) affected)fileName fileSize---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(0 row(s) affected) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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\hol10/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 |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-19 : 09:46:21
|
Try this first and post back the resultCREATE TABLE #Files ( row VARCHAR(400) )INSERT #Files ( row )EXEC master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'SELECT * FROM #FilesDROP TABLE #Files E 12°55'05.63"N 56°04'39.26" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-19 : 09:47:26
|
Fixed. You had comma as separator for file sizeCREATE 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 fileSizeFROM ( SELECT SUBSTRING(row, 37, 400) AS fileName, REPLACE(REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), ''), ',', '') AS fileSize FROM #Files ) AS dWHERE fileSize NOT LIKE '%[^0-9]%'DROP TABLE #Files E 12°55'05.63"N 56°04'39.26" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 resultCREATE TABLE #Files ( row VARCHAR(400) )INSERT #Files ( row )EXEC master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'SELECT * FROM #FilesDROP 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-E65DNULL Directory of D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\holNULL10/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 freeNULL(9 row(s) affected) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 sizeCREATE 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 fileSizeFROM ( SELECT SUBSTRING(row, 37, 400) AS fileName, REPLACE(REPLACE(REPLACE(SUBSTRING(row, 18, 19), CHAR(160), ''), CHAR(32), ''), ',', '') AS fileSize FROM #Files ) AS dWHERE 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) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-19 : 10:02:32
|
Can you run this againCREATE TABLE #Files ( row VARCHAR(400) )INSERT #Files ( row )EXEC master..xp_cmdshell 'DIR D:\EvaluationsUnit\FertilityIndexAnimalModelMixFiles\hol\test.dat'SELECT * FROM #FilesDROP 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" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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, var1Set ArgObj = WScript.Arguments Set WshShell = WScript.CreateObject("WScript.Shell")var1 = ArgObj(0) filename=var1Set fso = CreateObject("Scripting.FileSystemObject") set mainfile=fso.GetFile(filename)Wscript.Echo mainfile.SizeThen 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. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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? |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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" |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
Next Page
|