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. 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. > > ----------------------------------------------------------------------------- > > -- ----------------------------------------------------------------------------- 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. -----------------------------------------------------------------------------
