SQL Server Sproc Large String Parameter?

Question

This is not exactly the same as passing a huge string parameter to stored procedure ....

I have a SQL Server 2008 sproc that takes an id int and a large string and inserts it into a table. The sproc is called by a .NET 4.0 code, which does a File.ReadAllText into a string and then sends it to the sproc. The source of the string is a text file. The thought of future reading many 100mb files all day into an immutable "use once" strings and then sending it to SQL Server sounds memory-wasteful on the C# side.

What is a smarter way to stream the text from disk to the sproc? I can change the current Varchar(Max) parameter to anything that makes better sense.

All ideas appreciated.

Thanks.

Answer 1

I see three options for your situation:

1. Keep your existing design
2. Use OPENROWSET in the SP
3. Filestream

Item one is best if the files are remote from the server and have unique names.

Item two will take the work off the c# side but you will have to deal with read permissions for SQL on the file, naming convention and file cleanup.

Item three is the current best pratice for SQL Server 2008. There are numerous HOW2 articles to follow. This choice will allow SQL to manage the file while keeping the file on NTFS storage