File stream object Help needed !!!

Question

File stream object Help needed !!!

baabhu

SSCertifiable

Points: 6205
More actions
September 11, 2011 at 9:12 pm

#257151

We have a filestream object enabled in one the tables. And due to some production issue, we need to extract the file stream object and review it. What is the best approach to do that.
Please let me know if you need any information.
Thanks.
Table name: Document
Column name: DocumentDetailvarbinaryno-1

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ahmed_b72 Ten Centuries Points: 1251 More actions · Answer 1

I managed to handle similar issue before by creating an ODBC connection to the SQL Server, create a linked table in MS ACCESS and then create a form based on the table. That was quick method for me.

BTW, to manage the contents of a FILESTREAM object in MS ACCESS form, just right-click on its corresponding item.

Note: this method works well for reading but for uploading the documents into the FILESTREAM colmun, I faced some troubles.

Good luck!

SQLALX SSC Eights! Points: 956 More actions · Answer 2

You might want to export single FILESTREAM column data from the table.

This blog ( Export binary data from FILESTREAM column ) might be helpful.

Thanks,

SQLALX

http://www.sqlmgmt.com

baabhu SSCertifiable Points: 6205 More actions · Answer 3

Thank you everyone for the suggestion, is there any method from SQLserver to extract the file steam object. Since this a production environment, executing the codes may be bit challenging.

We don't have any access to it too.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Are you in for some Powershell ?

I am using Quests free PowerGUI and this works like a charm !

You will have to download and copy/paste Chad Miller's great Invoke-SQLCMD2 from Downloaded from: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/

Paste it where I indicated in this following script

<# Export FileStreamData to folder

ALZDBA

# http://www.sqlservercentral.com/Forums/Topic1173159-391-1.aspx?

#>

#######################

<# Downloaded Invoke-Sqlcmd2 from: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/

#>

COPY/PASTE THE FUNCTION HERE function Invoke-Sqlcmd2 {....}

} #Invoke-Sqlcmd2

# This is the actual active code, just using Invoke-Sqlcmd2 to fetch the data

####################################################

Clear-Host

$tb = Invoke-Sqlcmd2 -ServerInstance YourSQLInstance -Database Adventureworks2008 -Query 'Select FileName, Document from [Production].[Document] where document is not null ' -as 'DataTable'

#$tb.rows.count

for ($i=0;$i -lt $tb.rows.count;$i++) {

$TheDoc = $tb.Rows[$i].Document

$TargetFile = $('c:\temp\BU\{0}' -f $tb.Rows[$i].FileName )

#$TheDoc.Length

[System.IO.File]::WriteAllBytes($TargetFile,$TheDoc)

}

Write-Host $("[{0}] documents exported" -f $tb.rows.count )

Maybe someone can enhance it using a SQLDatareader, but I just wanted to post a working script at this time. :Whistling:

It uses a datatable, which is known to be slower then a datareader object :blush:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me