September 29, 2005 at 7:40 am
I am trying to automate the archiving of certain pages from a SQL stored procedure, I am writing the MHT (single file archives) to the file system.
I found this VB example, but can not see how to use this from T-SQL,
Sub makemht()'MakeMHT.vbs
Const adSaveCreateNotExist = 1Const adSaveCreateOverWrite = 2
Const adTypeBinary = 1 'Binary data
Const adTypeText = 2 '(Default) Text data
Set objMessage = CreateObject("CDO.Message")objMessage.CreateMHTMLBody URL
SaveToFile objMessage, DiskFile
Sub SaveToFile(Msg, Fn)Dim Strm, Dsk
Set Strm = CreateObject("ADODB.Stream")Strm.Type = adTypeText
Strm.Charset = "US-ASCII"
Strm.Open
Set Dsk = Msg.DataSource
Dsk.SaveToObject Strm, "_Stream"
Strm.SaveToFile Fn, adSaveCreateOverWrite
End Sub
October 3, 2005 at 9:20 pm
Since this VBScript calls COM Objects.. inorder to do this in T-SQL; You would need to use the sp_OACreate methods to create references to the object as local variables.. then use the sp_OAMethod to call the methods, or sp_OAGetProperty to get properties returned.
Check out Books Online... this is not a very difficult task. Your stored proc will most likely need two input params; @URL and @OutputFileName... etc...
HTH,
-Mike Gercevich
October 4, 2005 at 2:41 am
Thanks Mike, I have read this but am still a little stuck
October 5, 2005 at 7:36 pm
OK... If you really cannot find a better way of doing this... (like calling a VB Console App that does it much cleaner and faster, or even calling a vbScript via xp_cmdshell)...
let me try to whip out some code here on the fly... won't guarantee syntax error free; but this should get you started:
DECLARE @Stream int
DECLARE @Object int
DECLARE @data int
DECLARE @Sresult int
DECLARE @Oresult int
DECLARE @Dresult int
DECLARE @URL varchar(255)
DECLARE @FileName varchar(255)
SET @URL = 'http://www.msn.com'
SET @FileName = 'C:\test.mht'
--Create the object
PRINT 'Create Message Object'
EXEC @Oresult = sp_OACreate 'CDO.Message', @Object OUT
IF @Oresult <> 0 GOTO Error_Handler
PRINT 'Open URL'
EXEC @Oresult = sp_OAMethod @Object, 'CreateMHTMLBody', NULL, @URL, 0
IF @Oresult <> 0 GOTO Error_Handler
Print 'Create Stream Object'
EXEC @Sresult = sp_OACreate 'ADODB.Stream', @Stream OUT
IF @Sresult <> 0 GOTO Error_Handler
EXEC @Sresult = sp_OASetProperty @Stream, 'Type', 2
IF @Sresult <> 0 GOTO Error_Handler
EXEC @Sresult = sp_OASetProperty @Stream, 'CharSet', 'US-ASCII'
IF @Sresult <> 0 GOTO Error_Handler
PRINT 'Open Stream Object'
EXEC @Sresult = sp_OAMethod @Stream, 'Open'
IF @Sresult <> 0 GOTO Error_Handler
EXEC sp_OAGetProperty @Object, 'DataSource', @data OUT
EXEC sp_OAMethod @data, 'SaveToObject', @Stream, '_Stream'
EXEC sp_OAMethod @Stream, 'SaveToFile', NULL, @FileName, 2
EXEC @DResult = sp_OADestroy @data
IF @Dresult <> 0 GOTO Error_Handler
EXEC @OResult = sp_OADestroy @Object
IF @Oresult <> 0 GOTO Error_Handler
EXEC @Sresult = sp_OADestroy @Stream
IF @Sresult <> 0 GOTO Error_Handler
GOTO End_App
Error_Handler:
If @Oresult <> 0
BEGIN
Print 'Object Errors:'
EXEC sp_OAGetErrorInfo @Object
END
IF @Sresult <> 0
BEGIN
Print 'Stream Errors:'
EXEC sp_OAGetErrorInfo @Stream
END
IF @Dresult <> 0
BEGIN
Print 'Data Errors:'
EXEC sp_OAGetErrorInfo @data
END
End_App:
Print 'Done'
Yikes!
-Mike Gercevich
October 6, 2005 at 5:48 pm
Mike,
This looks and executes very well, but the files created are always empty any ideas why?
Tony the most grateful
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply