Databases
StackOverflow2013
Postsdbo
POSQL Server: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR stored procedure?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR stored procedure?
    primarykey
    data
    text
    <p>I ask this question as a followup of <a href="https://stackoverflow.com/questions/2720054/how-to-upload-files-and-store-them-in-a-server-local-path-when-ms-sql-server-allo">this question</a>.</p> <p>A solution that uses bcp and xp_cmdshell, that is not my desired solution, has been posted <a href="https://stackoverflow.com/questions/828749/ms-sql-server-2005-write-varbinary-to-file-system">here</a>.</p> <p>I am new to c# (since I am a Delphi developer) anyway I was able to create a simple CLR stored procedure by following a tutorial.</p> <p>My task is to move a file from the client file system to the server file system (the server can be accessed using remote IP, so I cannot use a shared folder as destination, this is why I need a CLR stored procedure).</p> <p>So I plan to: </p> <ol> <li>store from Delphi the file in a varbinary(max) column of a temporary table</li> <li>call the CLR stored procedure to create a file at the desired path using the data contained in the varbinary(max) field</li> </ol> <p>Imagine I need to move C:\MyFile.pdf to Z:\MyFile.pdf, where C: is a harddrive on local system and Z: is an harddrive on the server. C is in New York, Z is in London and there is no VPN between them, just https connection.</p> <p>I provide the code below (not working) that someone can modify to make it work? Here I suppose to have a table called MyTable with two fields: ID (int) and DATA (varbinary(max)). Please note it doesn't make a difference if the table is a real temporary table or just a table where I temporarly store the data. I would appreciate if some exception handling code is there (so that I can manage an "impossible to save file" exception).</p> <p>I would like to be able to write a new file or overwrite the file if already existing.</p> <pre><code>[Microsoft.SqlServer.Server.SqlProcedure] public static void VarbinaryToFile(int TableId) { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); SqlCommand command = new SqlCommand("select data from mytable where ID = @TableId", connection); command.Parameters.AddWithValue("@TableId", TableId); // This was the sample code I found to run a query //SqlContext.Pipe.ExecuteAndSend(command); // instead I need something like this (THIS IS META_SYNTAX!!!): SqlContext.Pipe.ResultAsStream.SaveToFile('z:\MyFile.pdf'); } } </code></pre> <p><strong>(one subquestion is: is this approach correct or there is a way to directly pass the data to the CLR stored procedure so I don't need to use a temp table?)</strong></p> <p>If the subquestion's answer is No, could you describe the approach of avoiding a temp table? So is there a better way then the one I describe above (=temp table + Stored procedure)? A way to directly pass the dataastream from the client application to the CLR stored procedure? (my files can be any size but also very big)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USLaBracca
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR stored procedure?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONoone can help more than this?
      singulars
      1. POSQL Server: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR stored procedure?
      1. USLaBracca
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload