Thanks.
Looks complicated. I think it would be easier to write a simple C# application. Is a shame that one has to resort to this as it makes selling this concept much harder. Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 6396 4285 (Monday, Wednesday,Friday) Phone : +618 9346 4372 (Tuesday, Thursday) Mobile: 0423 540 825 E-Mail : [email protected]; [email protected] The contents of this e-mail transmission outside of the WAGHS network are intended solely for the named recipient's), may be confidential, and may be privileged or otherwise protected from disclosure in the public interest. The use, reproduction, disclosure or distribution of the contents of this e-mail transmission by any person other than the named recipient(s) is prohibited. If you are not a named recipient please notify the sender immediately. From: [email protected] [mailto:[email protected]] On Behalf Of Stuart Kinnear Sent: Sunday, 13 May 2012 10:15 AM To: ozDotNet Subject: Re: Selecting data stored using FileStream in SQl Server Hi Peter, Further to the idea of using BCP in lieu of C# code, I tried a format file: perhaps there is something wrong in the mapping. (see http://msdn.microsoft.com/en-us/library/ff719290(v=sql.105).aspx) <?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> <RECORD> <FIELD ID="1" xsi:type="NCharTerm" TERMINATOR="\t\0"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="BLOB" xsi:type="SQLVARYBIN"/> </ROW> </BCPFORMAT> Here is a snippet of SQL code: --ALTER DATABASE Audit --ADD FILEGROUP Audit_fg_filestream CONTAINS FILESTREAM --GO --ALTER DATABASE Audit --ADD FILE --( -- NAME= 'fs_filestream', -- FILENAME = 'C:\Data\SQL Server\Filestream\audit' --) --TO FILEGROUP Audit_fg_filestream --GO --CREATE TABLE dbo.BLOB ( -- ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, -- BLOB VARBINARY(MAX) FILESTREAM NULL --) DECLARE @img AS VARBINARY(MAX) -- Load the image data delete FROM dbo.BLOB SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK 'C:\Document\Walking\Castlemaine Daylesford\20120324_102659.jpg', SINGLE_BLOB ) AS x DECLARE @ID UNIQUEIDENTIFIER SET @ID = NEWID() INSERT INTO dbo.BLOB (ID, BLOB) VALUES (@ID, @img) SELECT ID, BLOB FROM dbo.BLOB WHERE ID = @ID SELECT BLOB.PathName() FROM dbo.BLOB WHERE ID = @ID declare @sql varchar(8000) select @sql = 'bcp "SELECT TOP 1 BLOB FROM audit.dbo.BLOB" queryout c:\Temp\sysobjects.jpg -n -T -f c:\BCPfmt.txt -S ECHIDNA\STANDARD' exec master.. xp_cmdshell @sql DECLARE @img AS VARBINARY(MAX) -- Load the image data delete FROM dbo.BLOB SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK 'C:\Document\Walking\Castlemaine Daylesford\20120324_102659.jpg', SINGLE_BLOB ) AS x DECLARE @ID UNIQUEIDENTIFIER SET @ID = NEWID() INSERT INTO dbo.BLOB (ID, BLOB) VALUES (@ID, @img) SELECT ID, BLOB FROM dbo.BLOB WHERE ID = @ID SELECT BLOB.PathName() FROM dbo.BLOB WHERE ID = @ID declare @sql varchar(8000) select @sql = 'bcp "SELECT TOP 1 BLOB FROM audit.dbo.BLOB" queryout c:\Temp\sysobjects.jpg -n -T -f c:\BCPfmt.txt -S ECHIDNA\STANDARD' exec master.. xp_cmdshell @sql Regards, Stuart On 13 May 2012 11:18, Stuart Kinnear <[email protected]> wrote: You can get stuff in very easily using the plethora of examples, however using T-SQL to get the record out is not possible, perhaps because of the need to isolate the field value and deal with it separately. If you were to create a clr function using the info available for c# code then you would have means to do it via SQL server. I played with the idea of using bcp, but it corrupted the binary. Perhaps as bcp expects a tabulated output it adds termination values to the file . eg. declare @sql varchar(8000) select @sql = 'bcp "SELECT TOP 1 blob FROM mydatabase.dbo.Myblob" queryout c:\Temp\Mydump.txt -n -T -S MyServer\MyInstance' exec master.. xp_cmdshell @sql this might work for you if the records were plain text. Regards, Stuart On 12 May 2012 23:33, Peter Maddin <[email protected]> wrote: I have found examples of how to use T-SQL to insert a file as a Filestream blob in SQL Server 2008. I have not been successful at finding out how to use just T-SQL to select the same blob data to a local file. All the examples I have seen are code based (C# etc.). Is it possible just to use T-SQL to persist a FileStream blob to a local file? Regards Peter Maddin -- ---------------------------------------------------------------------------- - Stuart Kinnear Mobile: 040 704 5686 <tel:040%20704%205686> . Office: 03 9589 6502 <tel:03%209589%206502> SK Pro-Active! Pty Ltd acn. 81 072 778 262 PO Box 6117 Cromer, Vic 3193. Australia Business software developers. SQL Server, Visual Basic, C# , Asp.Net, Microsoft Office. ---------------------------------------------------------------------------- - -- ---------------------------------------------------------------------------- - Stuart Kinnear Mobile: 040 704 5686. Office: 03 9589 6502 SK Pro-Active! Pty Ltd acn. 81 072 778 262 PO Box 6117 Cromer, Vic 3193. Australia Business software developers. SQL Server, Visual Basic, C# , Asp.Net, Microsoft Office. ---------------------------------------------------------------------------- -
