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?
Advertisement
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.