Too many arguments passed to Stored Procedure storing image

I am trying to store a JPG image into a MSSQL Server 2014. The image path was being passed into a stored procedure and part of the process within the proc is to store the image into a VARBINARY(MAX) field (I cannot change schema). My first attempt was within the same proc like this:

DECLARE @pictureSql VARCHAR(300)

SET @pictureSql = 'INSERT INTO PictureBinary
(PictureId, BinaryData)
SELECT ' + CAST(@ImgId AS Varchar) +', * FROM OPENROWSET(BULK ''' + 
@ImgPath1 + ''', SINGLE_BLOB) rs'

EXEC(@pictureSql)

Even though the command that came from the PRINT() statement executed as expected I ran into problems executing it within the stored proc. It was a VARCHAR conversion error that nearly caused the laptop to take a flying lesson, but part of the research I was doing suggested breaking this bit out into a separate stored procedure. Without really understanding the logic of the reasoning and in desperation I followed this path. Same problem.

Then I changed the stored proc to accept the binary data and pass it in via a calling app. I based it on this.

(stripped down version)

ALTER PROCEDURE [dbo].[usp_InsertImage] @ProductId INT, 
@imagePath NVARCHAR(300), 
@mimeType VARCHAR(40), 
@seoFilename NVARCHAR(300), 
@altTag NVARCHAR(300), 
@titleTag NVARCHAR(300), 
@ImgData VARBINARY(MAX)
AS
BEGIN


    DECLARE @Id INTEGER

    SET @imagePath = REPLACE(@imagePath, '''', '''''')
    SET @seoFilename = REPLACE(@seoFilename, '''', '''''')
    SET @altTag = REPLACE(@altTag, '''', '''''')
    SET @titleTag = REPLACE(@titleTag, '''', '''''')

    DECLARE @ImgId INT

    INSERT INTO dbo.Picture
    (MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew, VirtualPath)
    VALUES (@mimeType, @seoFilename, @altTag, @titleTag, 0, NULL )

    SELECT @ImgId = SCOPE_IDENTITY()

    INSERT INTO dbo.PictureBinary (PictureId, BinaryData)   
VALUES (@ProductId, @ImgData)

    INSERT dbo.Product_Picture_Mapping (ProductId, PictureId, DisplayOrder) 
VALUES (@ProductId, @ImgId, 1)

    SELECT @id;
END

It is called from another SP like this:

DECLARE @childResult INT

EXEC @childResult = dbo.usp_InsertImage  
@ProductId = @ProductID, 
@imagePath  = @ImgPath1, 
@mimeType = 'image/jpeg', 
@seoFilename =  @SeoFilename1, 
@altTag = @ProductName, 
@titleTag = @ProductName, 
@ImgData = @ImgBinary1

Have also tried unnamed params:

EXEC @childResult = dbo.usp_InsertImage  
@ProductID, 
@ImgPath1, 
N'image/jpeg', 
@SeoFilename1, 
@ProductName, 
@ProductName, 
@ImgBinary1

No matter what I try I get the same error:

Procedure or function usp_InsertImage has too many arguments specified.

I have got to a point where I am not so sure I am even approaching this is the right way, it seems way more irritating than it should be. Either that or I am being a total plank and missing something obvious, its a 50/50 chance.

This is just dev code but can someone tell me if this approach is correct?

Answer

The error message suggests that you are connected to a different database (or different instance) where the definition of the procedure hasn’t been updated. Please check your connection strings (and make sure nothing you’re using is doing anything like AttachDbFileName – which means it is using its own, different copy of the database file). See this and this for more details.

You can confirm your calling code is not connecting to a different database by:

changing the procedure definition to have too many required arguments, and see if the error message changes.
change the name of the procedure, and see if your existing code still finds it.
create a copy of the procedure, with a different name, and change your code to see if it finds the new object.

Advertisement

Answer