Arabic text in sp_sqlexec insert return question mark

Records are imported into table #FileRecords and ##TableInsert are dynamic tables, it can be table A, table B(which justify the reason of using exec sp_sqlexec). When Arabic wording going through sp_sqlexec, it will insert ??? instead of the Arabic wording. Is there anything I am not aware of, beside of encoding and N in front of the string?

DECLARE @SQL nvarchar(max) = ''
IF OBJECT_ID('tempdb..#FileRecords') IS NOT NULL
    DROP TABLE #FileRecords
CREATE TABLE #FileRecords(
    Field1 nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

INSERT INTO #FileRecords (Field1)
SELECT N'محمد'

SELECT * FROM #FileRecords

SET @SQL = N'IF OBJECT_ID(''tempdb..##TableInsert'') IS NOT NULL
    DROP TABLE ##TableInsert
CREATE TABLE ##TableInsert(
    Field1 nvarchar(1000) COLLATE Arabic_CI_AI_KS_WS
    )
    
    INSERT INTO ##TableInsert
     '

SET @SQL = @SQL + (SELECT N'SELECT N'''+Field1+'''' FROM #FileRecords FOR XML PATH(''))

SET @SQL = @SQL +N'
SELECT * FROM ##TableInsert'

exec sp_sqlexec @SQL

Output: Above code output

Answer

Use sp_executesql instead.

I think sp_execsql is the older version and doesn’t support Unicode.

If I’m not mistaken ( somebody will correct me If I’m wrong) but sp_sqlexec is not supported anymore.

Advertisement

Answer