MFC ODBC Stored Procedure Parameters with Unicode Characters

Question

The Stored Procedure is like this

CREATE PROCEDURE [dbo].[InsertItemsSPRoc] 
-- Add the parameters for the stored procedure here
@File_Name nvarchar(400) =  NULL,   -- file name to be inserted
@Path_File_Name nvarchar(400) = NULL,   -- path file name to be inserted

    -- insert in the Media_Files FILETABLE
    -- set the specific table holder
    set @sql = N'insert into Media_Files (name, file_stream) (SELECT ''' + @File_Name + ''', * FROM OPENROWSET(BULK N''' + @Path_File_Name + ''', SINGLE_BLOB) AS FileData)'

    -- execute the combined statement
    exec(@sql)

The Stored Procedure is called through a MFC statement and is executing nicely when the passing parameters are ANSI, i.e. English letters. The problem arises when the user selects a file with file name made up of Unicode letters like "Ένα Δείγμα.mp4" (definitely looks Greeks to me) where the exemption I get is like below:

Cannot bulk load because the file "D:??? ??????.mp4" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

Apparently the ODBC driver is passing gibberish or something.

I have tried to debug/execute the same stored procedure using the same parameters using MSSM Studio and there is no problem.

How can I pass parameters in a stored procedure?

Answer 1

I would guess that you are passing the parameter as varchar and not as nvarchar.

There is also an error in your stored procedure:

   (SELECT ''' + @File_Name  

 

There is an N missing there, so you get a varchar literal, which means that characters outside the codepage will be replaced by fallback characters.

You should also have a handling of single quotes in the name, so that the procedure is not open to SQL injection.

Then again, if this is a filetable, isn't simpler to move the file into the fileshare outside SQL Server?

Answer 2

Hi @Jimmy ,

Welcome to Microsoft Q&A!

You could try to add code page after bulk statement as below:

CODEPAGE = '65001' -- Unicode  

If above is still not working, you could refer Erland Sommarskog's article for more details.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.