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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
brejkConnect With a Mentor Commented:
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
 
kiransvmAuthor Commented:
got a good solution by with in short period of time.
0
All Courses

From novice to tech pro — start learning today.