0

I have element table and want to upload word file to varbionary(max) column. But it's not working. I can't use escape character to resolve this problem.

Do you have any idea?

create table element
(element_id varchar(20) not null,
document_id varchar(20) not null,
student_id varchar(20) not null,
element_name varchar(50),
element_contents varbinary(max),
supervisor_comment varchar(200),
CONSTRAINT PK_Element PRIMARY KEY (element_id, document_id)
);

-----------------------------------------------------------

CREATE PROCEDURE AddElement
    @ElementID nvarchar(20),
    @DocumentID nvarchar(20),
    @StudentID nvarchar(20),
    @ElementName nvarchar(50),
    @ElementPath nvarchar(50)
AS
BEGIN

insert into element(element_id,document_id,student_id,element_name,element_contents) 
    select @ElementID, @DocumentID, @StudentID, @ElementName, BulkColumn 
    FROM OPENROWSET(BULK N'@ElementPath',SINGLE_BLOB) as SRC;

END


------------------------------------------------------------

execute AddElement @ElementID='e001', @DocumentID='d001',@StudentID='20150901',@ElementName='Control Plan',@ElementPath='c:/control_plan.docx'

Msg 4860, Level 16, State 1, Procedure AddElement, Line 21
Cannot bulk load. The file "@ElementPath" does not exist.
haya
  • 1
  • 1
  • 1
    Possible duplicate of [Using a Variable in OPENROWSET Query](http://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query) – Liesel Jul 14 '16 at 00:47

1 Answers1

0

I think the reason is simply the use of your variable:

N'@ElementPath'

will be interpreted as String "@ElementPath" and with this as filename - which is not correct...

Tyron78
  • 4,117
  • 2
  • 17
  • 32