Hi Cetin,
No, I didn't verify - I trust the author. Looks like he made a mistake Viv pointed us.
>>Just in case - may be important to know about this little problem
>>
>>
http://beyondrelational.com/blogs/jacob/archive/2010/02/18/why-do-excel-2007-prompt-for-repair-when-opening-a-document-stored-in-sql-server-2008-filestream-column.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+ExploringBeyondRelational+%28Exploring+Beyond+Relational+.......%29>>
>
>Naomi,
>Did you check the validity of that blog entry before sending? I don't know VB and thus what he is doing wrong but I tried writing my code with C# and also VFP and I can't verify such a thing exists. FileStream works perfectly well and w/o any such errors. Here is C# code (both bufferings work - I mean all at once or in chunks):
>
>
>using System;
>using System.Collections.Generic;
>using System.Linq;
>using System.Text;
>using System.Data.SqlClient;
>using System.IO;
>using System.Data.SqlTypes;
>
>namespace ConsoleApplication1
>{
> class Program
> {
> static void Main(string[] args)
> {
> using (SqlConnection connection = new SqlConnection(
> @"server=.\SQL2008;Trusted_Connection=Yes;Database=NorthpOle"))
> {
> connection.Open();
> SqlCommand command = new SqlCommand("", connection);
>
> SqlTransaction tran = connection.BeginTransaction(
> System.Data.IsolationLevel.ReadCommitted);
> command.Transaction = tran;
>
> command.CommandText =
> "select Top(1) DocumentData.PathName(),"
> + " GET_FILESTREAM_TRANSACTION_CONTEXT () from Documents";
> using (SqlDataReader reader = command.ExecuteReader())
> {
> while (reader.Read())
> {
> // Get the pointer for the file
> string path = reader.GetString(0);
> byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
>
> // Create the SqlFileStream
> SqlFileStream fileStream = new SqlFileStream(path,
> (byte[])reader.GetValue(1),
> FileAccess.Read,
> FileOptions.SequentialScan, 0);
>
> //byte[] buffer = new byte[fileStream.Length];
> //Console.WriteLine("FS length: {0}",fileStream.Length);
> //int bytesRead = fileStream.Read(buffer, 0, (int)fileStream.Length);
> //Console.WriteLine("Bytes read: {0}",bytesRead);
>
> //FileStream fs = new FileStream(@"c:\temp\xl2007X.xlsx", FileMode.CreateNew);
> //BinaryWriter w = new BinaryWriter(fs);
>
> //w.Write(buffer);
>
> //w.Close();
> //fs.Close();
>
> FileStream fs = new FileStream(@"c:\temp\xl2007X.xlsx", FileMode.CreateNew);
> BinaryWriter w = new BinaryWriter(fs);
>
> byte[] buffer = new byte[8192];
> Console.WriteLine("FS length: {0}",fileStream.Length);
> int totRead = 0;
> int bytesRead;
> while ((bytesRead = fileStream.Read(buffer, 0, 8192)) > 0)
> {
> totRead += bytesRead;
> w.Write(buffer, 0, bytesRead);
> }
> Console.WriteLine("Bytes read: {0}",totRead);
>
> w.Close();
> fs.Close();
>
> Console.WriteLine("Done");
> fileStream.Close();
> }
> }
> tran.Commit();
> }
> }
> }
>}
>
>And here is VFP sample anyway that also works perfectly well:
>
Local loCon As adodb.Connection, lorS As adodb.Recordset
>
>loCon = Createobject('Adodb.Connection')
>loCon.ConnectionString='Provider=SQLNCLI10.0;Server=.\SQL2008;Trusted_connection=yes;Database=NorthpOle'
>loCon.Open()
>
>lorS = loCon.Execute('select top 1 * from Documents')
>
>Local ix
>ix=0
>Do While !lorS.Eof
> luValue = lorS.Fields('DocumentData').Value
> ix = m.ix + 1
> Strtofile(m.luValue, 'c:\temp\xl2007'+Padl(m.ix,2,'0')+'.xlsx')
> lorS.MoveNext
>Enddo
>lorS.Close()
>loCon.Close()
>
Cetin
If it's not broken, fix it until it is.
My Blog