Error Message Unclosed quotation mark after the character string

0
votes

I got this error message when I executed the query.

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''

My code:

DECLARE @imgLogo VARCHAR(50)
DECLARE @savPicture VARBINARY(MAX)
DECLARE @strPerson VARCHAR(50) = 'Max Verstappen'

SET @imgLogo = 'SET @SavPicture = (SELECT * FROM OPENROWSET (BULK N''C:\Program Files\Microsoft SQL Server\140\Couriers\ + TRIM (cast('+ @strPerson + '''as char(30))) + .jpg'

EXEC (@imgLogo)

Can someone help me out?

2
c#sql-serverstringquotation-marks
Looks like you might have 1 extra than needed in BULK N'''' – sr28
The error is pretty self explanatory. My advice is to declare a few additional variables. The way I see it you need @basePath = N'C:\Program Files\Microsoft SQL Server\140\Couriers\', @imageName = TRIM (cast('''+ @strPersoon + '''as char(30))) + .jpg and then you'll have something like `SET @imgLogo = 'SET @SavPicture= (SELECT * FROM OPENROWSET (BULK @basePath + @imageName))'. Most probably the quotes are not correct here but this approach is easier to isolate the problem. – Leron_says_get_back_Monica
Debugging is part of programmer's job. Have you tried to PRINT @imgLogo before actually executing it? – Eric

2 Answers

2
votes

Run the your code with a PRINT statement like below and you will see that you have the length of @imgLogo set to 50. So your string is getting truncated. This is a quick example of how to debug dynamic SQL when you are not sure what is getting to the search engine:

DECLARE @imgLogo VARCHAR(50)
DECLARE @savPicture VARBINARY(MAX)
DECLARE @strPerson VARCHAR(50) = 'Max Verstappen'

SET @imgLogo = 'SET @SavPicture = (SELECT * FROM OPENROWSET (BULK N''C:\Program Files\Microsoft SQL Server\140\Couriers\ + TRIM (cast('+ @strPerson + '''as char(30))) + .jpg'

PRINT @imgLogo
0
votes

I fix it, it marks that error because there were a few characters with no comma or extra ). I also fix the other message that It will occur if you don't specify the SINGLE_NCLOB. The only issue that I give it to you its the problem with the scalar variable. If you like it please give me a vote, I solve what you ask, a little more but not everything.

DECLARE @imgLogo VARCHAR(200)
DECLARE @SavPicture VARBINARY(MAX)
DECLARE @strPerson VARCHAR(50) = 'Max Verstappen'

SET @imgLogo = 'SET @SavPicture = (SELECT * FROM OPENROWSET (BULK N''C:\Program Files\Microsoft SQL Server\140\Couriers\' + TRIM(cast(+ @strPerson as char(30))) + '.jpg'',SINGLE_NCLOB) as imagefile)'

EXEC (@imgLogo)