SQL Userdefined function to open files

Question

I would like to create a user defined function / store procedure or another SQL mechanism that when passed a filename will read the file contents into a variable for later processing. The mechanism must work within a transaction.

My plan was to use a user defined function however the OPENROWSET function doesn't allow me to pass a variable in as the filename so I need to execute it within a "sp_executesql" statement and the function become non-deterministic.

Are the any workarounds I can user to get around non-deterministic function?

Where is that filename? Is it on the client or the SQL server? — paparazzo, Oct 15 '13 at 15:37

score 0 · Answer 1 · answered Oct 15 '13 at 15:07

Yes, you can use so called "CLR Functions" functions that invoked by SQL Server from DLL provided by developer. See details on http://technet.microsoft.com/en-us/library/ms189876.aspx

But note, that deal with file from server-side is not scale-able solution with big security issues.

score 0 · Answer 2 · answered Oct 15 '13 at 15:20

The CLR option will get you exactly what you want (see @Dewfy's comments) but another option is to do a bulk insert, if your data allows for it. It is not scale-able either, issues with multiple users doing this at once, but maybe your situation allows for this??

SQL Userdefined function to open files

2 Answers2