Inserting filestream data type into SQL server 2008 from VBA

Hey all

I want to insert a record into sql server 2008 containing filestream data. I have already put in the code that saves the email as a .msg. This will allow me to save and retrieve it like any other file.

The issue I am having is with calling the actual stored proc from within Outlook 2007 VBA.

The code below is what I have done. It does not throw up any errors but also does not insert into the database

I tested the connection and that is fine(I did a manual insert into a test table).

It seems I can't do this manually because the insert in the stored proc makes use of openrowset and bulk.

In the code I save the email to a fixed directory and then run the stored proc with that file. That is why I placed the filepath(@File_Con) as the parameter.

Am I supposed to specify the parameters beforehand(Like value and size, etc.)?

Please advise on the correct method in doing this.

Thank you

VBA outlook:
----------------------
    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")
    
    With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "InsertFile"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@File_Con", adVarChar, adParamInput, 100, "C:\Email.msg")
    .Parameters.Append .CreateParameter("@File_Desc", adVarChar, adParamInput, 50, "Email")
    .Parameters.Append .CreateParameter("@File_Ext", adVarChar, adParamInput, 10, ".msg")
    .Execute
    End With
 
'The stored proc insert: 
-----------
	DECLARE @SQL varchar(max);
 
   SET @SQL = 'INSERT INTO OpenBLOBDB.dbo.Files (FileID,FileDesc,FileExt,FileContents)
    SELECT  NEWID(),''' + @File_Desc +  ''',''' +  @File_Ext + ''', BulkColumn FROM Openrowset( Bulk ''' + @File_Con + ''',SINGLE_BLOB) AS blob';
   
    exec sp_sqlexec @SQL

Select allOpen in new window