How do I get the file size of a text file on my local drive through SQL

Question

1.60/5 (2 votes)

See more:

I would like to know how to retrieve the size of a text file present on my drive in SQL.

What I have tried:

I have tried openrowset and my query goes like (select datalength(bulkcolumn) from openrowset(bulk 'C:\a.txt', single_clob) a)

But when I supply a variable instead of static path, the query does not compile.

Posted 27-Jun-16 6:05am

Member 12573208

Updated 28-Jun-16 1:26am

Add a Solution

Comments

Afzaal Ahmad Zeeshan 27-Jun-16 12:22pm

Why would you use SQL for this purpose when you can easily get the size in C# program.

ZurdoDev 27-Jun-16 16:04pm

Because sometimes you need the result in SQL.

Afzaal Ahmad Zeeshan 27-Jun-16 16:14pm

In such sometimes cases it is better to do things that you at least know the basics of. If you know nothing of the field you are trying these somethings on, results are always confusing. Such as in this case. In such cases my recommendation is to learn the basics instead of doing the advanced stuff. Just an opinion.

ZurdoDev 27-Jun-16 16:17pm

A simple example. From a sql stored procedure you execute another stored procedure that outputs a file to the system. You check file size to make sure it exported correctly and if not you then return something from sql to the caller.

There are very real and very good use cases for wanting to see files from Sql.

Afzaal Ahmad Zeeshan 27-Jun-16 16:22pm

Doesn't, File.WriteAllText(data); (in C#) work?

I mean, there are many ways you can check how data is being passed. You can get the data from readers and write it to the file streams from there too. Of course stored procedures can be fast, due to a bit caching and other stuff, sometimes memory is also a problem. :-)

Maybe, I am a bit more fan of KISS but however I cannot grasp this idea. Sorry Ryan.

ZurdoDev 27-Jun-16 16:24pm

For some reason you think that C# is involved here. A dba likely knows nothing about C# nor ever needs to.

Afzaal Ahmad Zeeshan 27-Jun-16 16:26pm

That is exactly why I can't grasp this idea and thus I likely didn't answer the problem.

ZurdoDev 27-Jun-16 16:29pm

Some people's entire jobs are just to write sql. They never use C# nor is the sql they generate used by sql, it's all kept within SQL itself.

Afzaal Ahmad Zeeshan 27-Jun-16 16:30pm

My warm regards to them. :-)

Cheers.

Sergey Alexandrovich Kryukov 27-Jun-16 18:18pm

:-)

Member 12573208 27-Jun-16 12:23pm

I have to use sql. That is the requirement. I already have the c# exe which checks the file size

ZurdoDev 27-Jun-16 16:03pm

1. Reply to the comment so that the user is notified.
2. You have code that works so all you have to do is debug it and figure out why your variable is causing a syntax error. Should be pretty simple.

Sergey Alexandrovich Kryukov 27-Jun-16 12:27pm

It has nothing to do with SQL. SQL "knows" nothing about any drives or file systems. Of course, you can store information on files in your SQL, exactly as any other information.
—SA

ZurdoDev 27-Jun-16 16:04pm

OP's code does work.

Sergey Alexandrovich Kryukov 27-Jun-16 16:18pm

Which code?..
—SA

ZurdoDev 27-Jun-16 16:20pm

select datalength(bulkcolumn) from openrowset(bulk 'C:\a.txt', single_clob) a

That will return the bytes of a.txt.

Sergey Alexandrovich Kryukov 27-Jun-16 18:18pm

Ah... thank you for the note. I did not realize that...
—SA

jgakenhe 27-Jun-16 13:15pm

You can using a DBMS, such as Oracle and SQL Server. You should be able to google it. I came up with this with no effort: http://stackoverflow.com/questions/2349552/using-t-sql-to-query-a-file-system-folder

CHill60 28-Jun-16 6:34am

Worth posting as the solution to get this out of the unanswered list :-)

Daniel Jones 28-Jun-16 0:25am

check out the following link: http://stackoverflow.com/questions/7952406/get-each-file-size-inside-a-folder-using-sql

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jgakenhe · Answer 1 · 2016-06-28T01:26:00

Per recommendation above, I'll post my tip/solution to get the ball rolling.

Stack has a nice example of the xp_cmdshell at:
[^]
Using the example you'll first need to configure the xp_cmdshell using sp_configure as shown below (if you don't have permissions, you'll need a System Admin or DBA to run it for you):

SQL

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

Now you can run xp_cmdshell.

SQL

EXECUTE master.dbo.xp_cmdshell 'DIR "C:\YourDirectory" /A-D /B'

It works nice. It shows up in the results window in the familiar tabular format.