File Access and table-valued functions

re: File Access and table-valued functions

William Stacey — Wed, 11 May 2005 16:18:23 GMT

Thanks for the sample. Would it be possible to post the client side code that calls this method and chunks file to the client file? Also, maybe you could post the reverse too. Uploading file to SQL using this method. Thanks much!
--
William Stacey [MVP]

re: File Access and table-valued functions

sqlclr — Thu, 12 May 2005 19:56:48 GMT

Hi William,

Can you give us a detailed description of your scenario? We would be glad to help you with a sample if we understand your needs better.

Thanks,
Sashi

re: File Access and table-valued functions

sqlclr — Fri, 13 May 2005 00:33:49 GMT

William,
Chunking the returned contents of this TVF (or for that matter any LOB value from the server) should be possible by writing an ADO.NET program that sets the CommandBehavior property of the SqlCommand to "SequentialAccess". If you execute such a command, the resultant SqlDataReader will load LOB data as a stream. You can then use the GetBytes or GetChars method to specify a byte location to start the read operation, and a limited buffer size for the data being returned. If you think you still need a sample, let us know.

As for the other direction, it is unfortunately not possible to "stream" a LOB value as an input parameter into SQL Server from the client as an argument of a stored procedure. (The client will buffer the entire value before sending it to the server).

Balaji Rathakrishnan
Microsoft SQL Server

re: File Access and table-valued functions

William Stacey — Mon, 16 May 2005 01:04:56 GMT

Yes please, would still like a client sample. There is something similar in a MSDN article (I think), but would like to see another implementation in the context of your server sample. The sample case could be this: You have a message row in sql db. Message has subject, body, and can have 0 or more attachments. Get message row and all attachments. Attachment reading should be stream based so not to load large files into mem on client. GetAttachments(string[] attachmentIDs) could be a seperate call instead of grabbing attachments with the message. Thanks for the feedback so far. 2005 looks great!

re: File Access and table-valued functions

William Stacey — Mon, 16 May 2005 01:15:14 GMT

Also..
It seems to me like explict support for .Net Streams could be supported and would make this kind of thing natural for the client side consumer. The SQLClient could create and hook-up the stream (i.e. NetworkStream) based on the reply results. Would seem possible to do both read and write streams. This may need to happen outside the normal SP context - not sure, but think it can be done (all most things.) Naturally, I could probably do this kind of thing with DIME and WSE 2.0 (or 3.0 and MTOM) if WSE was supported from SQLCLR (is it or will it?) Thanks again!

re: File Access and table-valued functions

Timur — Tue, 07 Mar 2006 00:55:36 GMT

Is it possible to create similar function with OLAP SQL Server 2005 access.

re: File Access and table-valued functions

Vikrant — Mon, 21 Aug 2006 19:25:46 GMT

The same way as you loaded a document. is there a way read data from a table and write to a document in filesystem?

Using table-valued functions (TVF) in SQL Server

SQL Server Users & tools (SSQA) — Fri, 29 Jun 2007 11:18:42 GMT

I was looking for more information on Table-Valued Functions (TVF) in SQL Server in attempting to perform

Interesting post about Table Valued Functions

Niels SQL Server Blog — Sun, 26 Aug 2007 21:00:54 GMT

Yesterday I wrote about that the SQLCLR team had started blogging, and today I saw their first "real"