Exaption when insert VARBINARY data using stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hello there..

I have a stored procedure that I am using to store varbinary data, so if the file is small almost 50mb to 150mb it works fine, but the problem when I try to store bigger than 300 mb, it doesn't work and shows message says:

Quote:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The stored procedure is:

SQL

PROCEDURE AddData
    @FileContent varbinary(MAX)
AS
    INSERT INTO [AttachmentTable] 
    (
        [ContentFile]
    )
    VALUES 
    (
        @FileContent
    )

The parameter is send from c# as byte[]

What can I do to solve this problem?

Thanks in advance.

What I have tried:

I searched many websites but no answers.

Posted 5-May-20 14:21pm

Jassom

Updated 7-May-20 6:12am

Richard Deeming

v3

Add a Solution

Comments

phil.o 5-May-20 20:22pm

Please, don't shout :)

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)

phil.o · Answer 1 · 2020-05-05T14:35:00

Solution 1

Usually, it is not recommended to store files, especially large ones, in your database. Databases are not optimized to store huge binary files. Instead, you shoud store the path to the file in the database, and leave the file itself to the regular file system.
This is in fact a very common questionning, here are a couple of links relative to that matter:
database - Storing Images in DB - Yea or Nay? - Stack Overflow[^]
File System vs. Database - dzone[^]

Posted 5-May-20 14:35pm

phil.o

Comments

Jassom 5-May-20 21:18pm

thank you for reply.

The server is located on the same client machine so I don't worry about database size .. etc. also the reason of why I want to store files in database is becuase the data is sensitive and I don't want others see them outside database.

MadMyche 5-May-20 22:30pm

Have you used any of the analytic tools in SQL Server or the Performance Monitor in Windows to see what is happening with memory & CPU usage?

Jassom 6-May-20 16:58pm

Thank you for reply.
I use the Performance Monitor in Windows to see what is happening, CPU performance is working normal, memory gets full about 95% when storing files up to 250MB.

MadMyche 6-May-20 19:12pm

Well, that about answers your question: your system is running out of resources when you try to do this.
The resolution is simple: Don't do. Encrypt the file and save it as a file, and have a reference in your table for it

Dave Kreskowiak 5-May-20 22:40pm

Then encrypt the files and decrypt them when you need them.

Really, Storing a 300MB file in the database is a bad idea no matter how you look at it.

Take a look at the following and see what it can do for you.
Binary Large Object (Blob) Data (SQL Server) - SQL Server | Microsoft Docs[^]

Jassom 6-May-20 16:55pm

Thank you for reply.
Encrypt files and decrypt them is also a good idea ..