Link to home
Start Free TrialLog in
Avatar of maverick0728
maverick0728Flag for United States of America

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:
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

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

just use

@pImagePath varbinary(MAX)

>>>

@pImagePath nvarchar(MAX)
Avatar of maverick0728

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
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That works; however the image can't be viewed in a ssrs report.
You were correct; I just found an permissions error with the SSRS report accessing the image.  You are right; thanks for your help.