maverick0728
asked on
t-sql procedure to insert image with path from parameter
I am trying to execute this stored procedure to insert. The parameter @pImagePath is being passed in (for example) is: \\server\image.png
This procedure produces the error:
Msg 402, Level 16, State 1, Procedure spWIPImageInsert, Line 24
The data types nvarchar and varbinary(max) are incompatible in the add operator.
How can I insert this image?
Here's the procedure code:
This procedure produces the error:
Msg 402, Level 16, State 1, Procedure spWIPImageInsert, Line 24
The data types nvarchar and varbinary(max) are incompatible in the add operator.
How can I insert this image?
Here's the procedure code:
ALTER PROCEDURE [dbo].[spWIPImageInsert]
@pUserName varchar(50),
@pMachineName varchar(50),
@pPartNum varchar(50),
@pImagePath varbinary(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQL_ERROR INT
SET @SQL_ERROR = 0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
SET @SQL = N'INSERT INTO [dbo].[WIPImage]([FromUserName], [MachineName], [PartNum], [Image]) SELECT ''' + @pUserName + ''', ''' + @pMachineName + ''', ''' + @pPartNum + ''', BulkColumn FROM OPENROWSET(BULK '''+@pImagePath+''', SINGLE_BLOB) AS IMAGE'
EXEC (@SQL)
SELECT @SQL_ERROR = @@ERROR
IF @SQL_ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
ASKER
I am calling this from a c# program - passing the file path as a parameter. When should the conversion of that path to varbinary occur - before the stored procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works; however the image can't be viewed in a ssrs report.
ASKER
You were correct; I just found an permissions error with the SSRS report accessing the image. You are right; thanks for your help.
@pImagePath varbinary(MAX)
>>>
@pImagePath nvarchar(MAX)