February 8, 2008 at 4:08 am
Hello everybody,
I decided to post here because I don't find the soluce to my problem.
I have the following table :
CREATE TABLE [dbo].[BroadCastReporting](
[BroadCastReportingID] [bigint] NOT NULL,
[ConfirmationID] [bigint] NULL,
[DealID] [bigint] NULL,
[SubmitDate] [datetime] NOT NULL,
[GenerationDate] [datetime] NULL,
[SendingDate] [datetime] NULL,
[ResendingDate] [datetime] NULL,
[GUID_DocumentName] [uniqueidentifier] NULL,
[DocumentPivot] [xml] NULL,
[FinalDocument] [varbinary](max) NULL,
[BiztalkDocument] [xml] NULL,
[BroadCastStatus] [int] NOT NULL,
[ObjectTypeID] [smallint] NOT NULL CONSTRAINT [DF__BroadCast__Objec__023D5A04] DEFAULT ((1)),
CONSTRAINT [PK_BroadCastReporting] PRIMARY KEY CLUSTERED
(
[BroadCastReportingID] ASC
)
----------------------------------------------------------------------
In this table are written pdf, text or word documents that are sent by mail, fax or others channels via Biztalk. The documents were inserted by ours applications (C#). But, this table is now too big (20 Gb) and we decide to change the way to store them. We are going to write new documents on a file server.
The application was modified and we want to write the documents stored in the FinalDocument column before deleting from the DB, and by SQL scripting, without developing C# batch.
An example of what is stored in the column (only the beggining of a word file) :
0xD0CF11E0A1B11AE10000000000000000000000000000000021000...
I tried some scripts but it was not good.
Here are one of the various and numerous attempts I tried :
-----------------------------------------------------------------------
[font="Verdana"]SET NOCOUNT ON
DECLARE @RetCode int , @TargetFile int , @FileHandle int
DECLARE @FilePath VARCHAR(255)
DECLARE @Extension VARCHAR(10)
DECLARE @DataToWrite nvarchar(max)
-- Data to extract
SELECT @DataToWrite = FinalDocument
FROM BroadCastReporting WHERE BroadCastReportingID=216
-- Extension
SELECT @Extension = RTRIM(Biztalkdocument.value ('(//AttachedDocument/FileType)[1]','varchar(10)'))
FROM BroadCastReporting WHERE BroadCastReportingID=216
-- Buildind the path of the file
SELECT @FilePath = 'c:\tmp\' + CAST(BroadCastReportingID AS VARCHAR(10)) + '_' + CAST(DealID AS VARCHAR(10)) + '.'+ @Extension
FROM BroadCastReporting WHERE BroadCastReportingID=216
-- Instancing OLE Object
EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @TargetFile OUTPUT
IF (@@ERROR|@RetCode > 0 Or @TargetFile < 0)
RAISERROR ('Unenable to create the ouptput file' , 16 , 1)
--ELSE
-- PRINT 'Instance created'
EXECUTE sp_OAMethod @TargetFile , 'OpenTextFile' , @FileHandle OUTPUT , @FilePath , 2 , 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR ('Unenable to open output file.' , 16 , 1)
--ELSE
-- PRINT 'file accessible'
EXECUTE sp_OAMethod @FileHandle , 'Write', NULL, @DataToWrite
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Unenable to write into the output file ' , 16 , 1)
--ELSE
-- PRINT 'Write complete'
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' , NULL
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Unenable to close the output file' , 16 , 1)
--ELSE
-- PRINT 'Output file closed'
-- Kill the OLE ole objetc instance
EXEC sp_OADestroy @TargetFile[/font]
----------------------------------------------------------------------
I get no result or a file with unreadable content.
I think there is a solution by using suitable primitive of the sp_OAMethod but there's no help to this
methods and about its parameters, or a suitable CAST to write in the file the content of my varbinary
column.
Thanks for your answers.
February 8, 2008 at 9:48 am
A SQL statement would allow to give only a delivery to the integration department.
If I must develop some C# code to read each row of the table and write the content of the column into a file, it will take a long time (I think).
Nevertheless, I wrote a stored procedure to "clean" the table.
I would like to have only one stored procedure to do all these tasks.
Thanks for your replies
February 8, 2008 at 10:13 am
You need to stream this out with ADO. It doesn't need to be C# code, simple VBScript or Powershell could help here.
http://support.microsoft.com/kb/258038
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=7354
http://andrewu.co.uk/clj/load_and_save_file_helper_functions/
February 8, 2008 at 10:34 am
Hy,
Unfortunately, it is the reply I was afraid of.
It is not possible to do it with SQL statement, even with the sp_OAxxx methods.
I thank you for your answer.
David
PS : I'm going to search for a soluce and if I find something, I will post it here.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply