Determine 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

General SQL Server Forums

New to SQL Server Programming

Determine file size.

Author

Topic

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 22:18:01

Hi all,

Does any one know how to determine file size using SQL stored Procedure or a function?

My requirement is that I need to kill a file from a specific directory if the file size is 0 kb or 1 kb

Any Ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 22:29:21

[code]exec master..xp_cmdshell 'dir filename.ext'[/code]

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 22:37:21

Hi KH

Thanks for the reply but what does that do as I got the following errors.

1 'c:\asdf.txt' is not recognized as an internal or external command,
2 operable program or batch file.
3 NULL

I am executing the following command

exec master..xp_cmdshell 'c:\asdf.txt'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 22:44:42

quote:
Originally posted by asbharadwaj

Hi KH

Thanks for the reply but what does that do as I got the following errors.

1 'c:\asdf.txt' is not recognized as an internal or external command,
2 operable program or batch file.
3 NULL

I am executing the following command

exec master..xp_cmdshell 'c:\asdf.txt'

You might try running the command that khtan gave you:
exec master..xp_cmdshell 'dir filename.ext'

instead of
exec master..xp_cmdshell 'c:\asdf.txt'

Notice the "DIR"?

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 22:45:35

create a temp table and insert the result of dir into the temp table and extract the file size from there


create table #temp
(
	result	varchar(100)
)

insert into #temp exec master..xp_cmdshell 'dir c:\asdf.txt'

EDIT : Please look up BOL and see what is the SP xp_cmdshell for.

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 22:49:17

How would that solve my prob?

I am getting the following details if I execute as is

Volume in drive C has no label.
Volume Serial Number is 94DE-A8F9
NULL
Directory of C:\WINNT\system32
NULL
File Not Found
NULL

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 22:53:56

What is the command you executed?

CODO ERGO SUM

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 22:57:44

quote:
Originally posted by Michael Valentine Jones

What is the command you executed?

CODO ERGO SUM

exec master..xp_cmdshell 'dir c:\asdf.txt'

and I get this

Volume in drive C has no label.
Volume Serial Number is 94DE-A8F9
NULL
Directory of c: NULL
File Not Found
NULL

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 23:03:05

It is telling you the file does not exist.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 23:12:56

quote:
Originally posted by asbharadwaj

quote:
Originally posted by Michael Valentine Jones

What is the command you executed?

CODO ERGO SUM

exec master..xp_cmdshell 'dir c:\asdf.txt'

and I get this

Volume in drive C has no label.
Volume Serial Number is 94DE-A8F9
NULL
Directory of c: NULL
File Not Found
NULL

The result you post and the command does not match.

if you do a dir c:\asdf.txt, the result should show

 Directory of c:\

Check that the file is located in the correct folder.

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 23:15:27

quote:
Originally posted by Michael Valentine Jones

It is telling you the file does not exist.

CODO ERGO SUM

But the file exists

and is 1kb in size.I am sorry..I know its tiring but...please help me

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 23:18:01

quote:
Originally posted by khtan

quote:
Originally posted by asbharadwaj

quote:
Originally posted by Michael Valentine Jones

What is the command you executed?

CODO ERGO SUM

exec master..xp_cmdshell 'dir c:\asdf.txt'

Hi KH

the file exists.I see it physically and the size is 1kb
and I get this

Volume in drive C has no label.
Volume Serial Number is 94DE-A8F9
NULL
Directory of c: NULL
File Not Found
NULL

The result you post and the command does not match.

if you do a dir c:\asdf.txt, the result should show
Directory of c:\

Check that the file is located in the correct folder.

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 23:19:20

go to command prompt C Drive and run this


dir C:\asdf.txt

what is the output ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 23:19:47

quote:
Originally posted by asbharadwaj

quote:
Originally posted by Michael Valentine Jones

It is telling you the file does not exist.

CODO ERGO SUM

But the file exists and is 1kb in size.I am sorry..I know its tiring but...please help me

I doubt that.

What output does this command show you?

exec master..xp_fileexist 'c:\asdf.txt'

CODO ERGO SUM

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 23:32:11

I Get this

File exists file is a directory Parent directory exists
0 0 1

But the file is physically there

I can see that

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 23:35:52

The file is not in that directory on the SQL Server.

You are most likely making the mistake of thinking that the SQL Server is looking at your C drive. It isn't. It is looking at it's own C drive.

CODO ERGO SUM

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-30 : 23:37:43

quote:
Originally posted by Michael Valentine Jones

The file is not in that directory on the SQL Server.

You are most likely making the mistake of thinking that the SQL Server is looking at your C drive. It isn't. It is looking at it's own C drive.

CODO ERGO SUM

Precisely...I think you can read minds...using telepathy???

...Thats what I was thinking...then in that case how do I read the file on C:\ but not SQL server c:\

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 23:42:01

You will have to give it the UNC path:
\\SERVERNAME\SHARE\FILENAME

The SQL Server command shell account will have to have access to that share and the directory.

I will leave it to you to figure the rest out.

CODO ERGO SUM

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-31 : 00:21:37

Thanks a million

This worked
exec master..xp_fileexist '\\server01\testfolder\asdf.txt'

but this is taking long time(more than 5 minutes and still running)
exec master..xp_cmdshell '\\server01\testfolder\asdf.txt'

and the worst thing is I am not able to stop the query and and when I try to stop it says 'Attempting to cancel query...Please wait'

is it normal...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 00:25:09

you missed out the DIR in the xp_cmdshell again ?

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-31 : 00:29:00

quote:
Originally posted by khtan

you missed out the DIR in the xp_cmdshell again ?

KH

Sorry...

It works fine with DIR...thanks to all...once again...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources