T-Sql: Question mark symbols are displaying while retrieving the japanese string using the dynamic sql.

I have a japanese string in of the localized database table. There is a function called get_str() to retrieve the value for the specific key in this case i am retrieving the japanese string.

     Please find the below two scenarios both are trying to retrieve the same values but in different way of execution at the end the results are varies.

Scenario 1: Used dynamic sql query to return the service pack value which in turn returns the question mark symbol (????)

Scenario 2:
        Used the direct sql statement and able to get the proper output.

Please suggest.
Please find the below two scenarios both are trying to retrieve the same values but in different way of execution at the end the results are varies.
 
Scenario 1: Used dynamic sql query to return the service pack value which in turn returns the question mark symbol (????)
 
Scenario 2:
        Used the direct sql statement and able to get the proper output. 
 
 
Scenario1:
---------------
declare @unknown nvarchar(255)
declare @culture_info nvarchar( max)
declare @sql nvarchar(max)
declare @ServicePackLevel nvarchar(200)
 
set @culture_info = 'ja'
set @ServicePackLevel = 'service pack'
SET @unknown = [dbo].get_str(2003, @culture_info)  holds the japanese string 'Çü¿
³'
set @sql =  'select  CASE 
    WHEN d.[wmi_scan_result] = 0
      THEN d.[operating_system_service_pack]
    ELSE
       COALESCE (d.[operating_system_service_pack],
                 ' +[dbo].quotestring(@unknown) + ')
  END AS ''Servicepack'' from devices d where device_number
in(''1724B5B7-5A35-498F-914B-AF6CAD557DFF'', ''DC7FE0A1-A2B7-42BE-9DB5-02EE3E782785'')'
 
 
exec (@sql)
 
result:
Result:
ServicePack
--------------
?????
?????
 
 
Scenario2:
-------------
declare @unknown nvarchar(255)
declare @culture_info nvarchar( max)
declare @sql nvarchar(max)
declare @ServicePackLevel nvarchar(200)
 
set @culture_info = 'ja'
set @ServicePackLevel = 'service pack'
SET @unknown = [dbo].get_str(2003, @culture_info)  holds the japanese string 'Çü¿
³'
 
select  CASE 
    WHEN d.[wmi_scan_result] = 0
      THEN d.[operating_system_service_pack]
    ELSE
       COALESCE (d.[operating_system_service_pack],
                   [dbo].quotestring(@unknown) )
  END AS   'service pack'   from devices d where device_number
in('1724B5B7-5A35-498F-914B-AF6CAD557DFF', 'DC7FE0A1-A2B7-42BE-9DB5-02EE3E782785')
 
 
Result:
ServicePack
--------------
'Çü¿
³'
'Çü¿
³'
 
 
Any help on why the results are varies from 2 scenarios? Also observed  the collation is set to  SQL_Latin1_General_CP1_CI_AS

Open in new window

kiransvmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
Try this:

declare @unknown nvarchar(255)
declare @culture_info nvarchar( max)
declare @sql nvarchar(max)
declare @ServicePackLevel nvarchar(200)
 
set @culture_info = 'ja'
set @ServicePackLevel = 'service pack'
SET @unknown = [dbo].get_str(2003, @culture_info)  holds the japanese string 'Çü¿
³'
set @sql =  'select  CASE
    WHEN d.[wmi_scan_result] = 0
      THEN d.[operating_system_service_pack]
    ELSE
       COALESCE (d.[operating_system_service_pack],
                 N' +[dbo].quotestring(@unknown) + ')
  END AS ''Servicepack'' from devices d where device_number
in(''1724B5B7-5A35-498F-914B-AF6CAD557DFF'', ''DC7FE0A1-A2B7-42BE-9DB5-02EE3E782785'')'
exec (@sql)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kiransvmAuthor Commented:
got a good solution by with in short period of time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.