Link to home
Start Free TrialLog in
Avatar of Harreni
HarreniFlag for Saudi Arabia

asked on

Dynamic sql query that use Nvarchar and exceed 4000 characters

Hi Experts,

I have a dynamic Sql query that exceed 4000 characters long so I cannot use Nvarchar with it,  and when I set variables to Arabic words it represents words like this N'???'
so, how to overcome this issue with an example.

Thanks a lot.
Harreni
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

how is your variable declared? have you tried nvarchar(max)?
USE NVARCHAR(MAX) -- It can store upto 2GB data.

N is "national" means Unicode - means in Nvarchar we can store data from multiple languages like chineese, hindi, korean, etc

Example

DECLARE @Var AS NVARCHAR(MAX)  = N'पवन'
SELECT @Var

Open in new window


Output

(No column name)
पवन
Avatar of Harreni

ASKER

Yes, I define the SQL query using nvarchar but its begin truncated so I use varchar instead "  I confirm this by using print command".

Also, I set some variables inside that SQL query using nvarchar because I set Arabic words in those variables.
Avatar of Harreni

ASKER

Here is a part from my SQL query "not a complete query":

DECLARE @DumpDate NVARCHAR(MAX)
DECLARE @AsIsTable NVARCHAR(MAX);
DECLARE @AsIs_CorrectRecords_IDsTable NVARCHAR(MAX);
DECLARE @Yes NVARCHAR(100); 
DECLARE @No NVARCHAR(100); 
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL VARCHAR (MAX);

SET @Yes = N'نعم'
SET @No = N'لا'
SET @AsIs_CorrectRecords_IDsTable = N'tb_Test_Services_ImportedByWizard_AsIs_'+ @DumpDate +'_CorrectRecords_IDs'
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = 'INSERT INTO ' + @AsIs_CorrectRecords_IDsTable +
' SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N''False'' THEN ' + @No + '' 
WHEN N''True'' THEN ' + @Yes + ' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription

FROM ' + @AsIsTable + ' As R';
PRINT @ChangingAsIsColumnsToTheEquivelantValuesSQL
EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window


My issue is with @Yes and @No variables beside the length of my query
try this

DECLARE @DumpDate NVARCHAR(MAX)
DECLARE @AsIsTable NVARCHAR(MAX);
DECLARE @AsIs_CorrectRecords_IDsTable NVARCHAR(MAX);
DECLARE @Yes NVARCHAR(100); 
DECLARE @No NVARCHAR(100); 
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL VARCHAR (MAX);

SET @Yes = N'نعم'
SET @No = N'لا'

SET @AsIs_CorrectRecords_IDsTable = N'tb_Test_Services_ImportedByWizard_AsIs_'+ @DumpDate +'_CorrectRecords_IDs'
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = 'INSERT INTO ' + @AsIs_CorrectRecords_IDsTable +
' SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N''False'' THEN ' + @No + ' 
WHEN N''True'' THEN ' + @Yes + ' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM ' + @AsIsTable + ' As R';
PRINT @ChangingAsIsColumnsToTheEquivelantValuesSQL
EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window


Hope it helps !
Avatar of Harreni

ASKER

Thanks Pawan,
still same issue exists, Arabic words represented as ???

Here is the result of print command:

INSERT INTO tb_Test_Services_ImportedByWizard_AsIs_Saad_CorrectRecords_IDs 
SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N'False' THEN ?? 
WHEN N'True' THEN ??? END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM [dbo].[tb_Test_Services_ImportedByWizard_AsIs_Saad] 
 As R

Open in new window

Can you please provide column with their data types of table tb_Test_Services_ImportedByWizard_AsIs_Saad_CorrectRecords_IDs

Specifically in which value of OutsideBranchesToProvideTheService is inserted.

Trial

CREATE TABLE tryArabic
(
	N NVARCHAR(MAX)
)
GO

DECLARE @try AS VARCHAR(10) =  N'Yes'
DECLARE @Yes NVARCHAR(100); 
DECLARE @No NVARCHAR(100)

SET @Yes = N'نعم'
SET @No = N'لا'

INSERT INTO tryArabic
SELECT CASE @try WHEN 'Yes' THEN @Yes ELSE @No END

GO

DECLARE @try AS VARCHAR(10) =  N'No'
DECLARE @Yes NVARCHAR(100); 
DECLARE @No NVARCHAR(100)

SET @Yes = N'نعم'
SET @No = N'لا'

INSERT INTO tryArabic
SELECT CASE @try WHEN 'Yes' THEN @Yes ELSE @No END

SELECT * FROM tryArabic

Open in new window


Hope it helps !!
Avatar of Harreni

ASKER

AgencyName                                                      nvarchar(MAX)
ServiceID                                                      nvarchar(MAX)
ServiceArabicName                                      nvarchar(MAX)
OutsideBranchesToProvideTheService      nvarchar(MAX)
DependingOnOtherAgency                      nvarchar(3000)
SupportingOtherAgency                              nvarchar(3000)
ArabicHyperLink                                              nvarchar(MAX)
EnglishHyperLink                                             nvarchar(MAX)
YouTubeLink                                                     nvarchar(MAX)
ServiceManual                                             nvarchar(MAX)
ServiceForm                                                     nvarchar(MAX)
ServiceArabicDescription                             nvarchar(MAX)
Pls try..

DECLARE @DumpDate NVARCHAR(MAX)=''
DECLARE @AsIsTable NVARCHAR(MAX)=''
DECLARE @AsIs_CorrectRecords_IDsTable NVARCHAR(MAX)=''
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL VARCHAR (MAX) = ''

SET @AsIs_CorrectRecords_IDsTable = N'tb_Test_Services_ImportedByWizard_AsIs_'+ @DumpDate +'_CorrectRecords_IDs'

SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = 

'INSERT INTO ' + @AsIs_CorrectRecords_IDsTable +
' SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N''False'' THEN ' + 'N' + '''نعم''' + ' WHEN N''True'' THEN ' + 'N' + '''لا''' + ' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM ' + @AsIsTable + ' As R'

PRINT @ChangingAsIsColumnsToTheEquivelantValuesSQL
EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window


Hope it helps !!
Avatar of Harreni

ASKER

Thanks Pawan.

Since your SQL query has defined as VARCHAR, it will not display or store your NVARCHAR variables that you put it inside it as you want (Arabic, Chinese, etc) languages , it will keep it as ???

Here's the print command result:
INSERT INTO tb_Test_Services_ImportedByWizard_AsIs_Saad_CorrectRecords_IDs 
SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N'False' THEN N'???' WHEN N'True' THEN N'??' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM [dbo].[tb_Test_Services_ImportedByWizard_AsIs_Saad] As R

Open in new window

try,,, Issue was with this line -- it was Varchar(max) DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL NVARCHAR (MAX)=N''

DECLARE @DumpDate NVARCHAR(MAX) = N''
DECLARE @AsIsTable NVARCHAR(MAX) = N''
DECLARE @AsIs_CorrectRecords_IDsTable NVARCHAR(MAX) = N''
DECLARE @Yes NVARCHAR(100) = N''
DECLARE @No NVARCHAR(100)= N''
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL NVARCHAR (MAX)=N''

SET @Yes = N'نعم'
SET @No = N'لا'

SET @AsIs_CorrectRecords_IDsTable = N'tb_Test_Services_ImportedByWizard_AsIs_'+ @DumpDate +'_CorrectRecords_IDs'
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = 'INSERT INTO ' + @AsIs_CorrectRecords_IDsTable +
' SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N''False'' THEN N' + '''' + @No + '''' + ' WHEN N''True'' THEN N' + '''' + @Yes + '''' + ' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM ' + @AsIsTable + ' As R';
PRINT @ChangingAsIsColumnsToTheEquivelantValuesSQL
--EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window

Avatar of Harreni

ASKER

I know this from the beginning and I mentioned it in my question.
Even If I define my query as NVRACHAR, It will be truncated because the query is around 6000 characters, so how to overcome this??
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Even If I define my query as NVRACHAR, It will be truncated
Where did you see the truncation?
Avatar of Harreni

ASKER

Try to store a query that is around 6000 characters inside a NVARCHAR variable and see if it will be presented fully in a print command or NO. Also, set some special languages (Arabic, Chinese) varibables with NVARCHAR inside it to understand my concern.

The issue NOT fixed yet.
USE NVARCHAR(MAX) to store the query -- It can store more than 6000 characters.

Can you post your full query. The other one is working fine on my machine.
Avatar of Harreni

ASKER

Thanks Vitor.
If I use NVARCHAR, The truncation started after the character number 3970.
If I use VARCHAR, No truncation is there.
Can you please provide the entire query so that we can look?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
And you can also try concatenate by blocks instead of have one single command line for concatenation:
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = N'INSERT INTO ' + @AsIs_CorrectRecords_IDsTable +
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = @ChangingAsIsColumnsToTheEquivelantValuesSQL + N' SELECT
 AgencyName
,ServiceID
,ServiceArabicName
,(Case OutsideBranchesToProvideTheService WHEN N''False'' THEN N' + '''' 
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = @ChangingAsIsColumnsToTheEquivelantValuesSQL + @No + N'''' + N' WHEN N''True'' THEN N' + '''' + @Yes + '''' + ' END) As OutsideBranchesToProvideTheService 
,DependingOnOtherAgency
,SupportingOtherAgency
,ArabicHyperLink
,EnglishHyperLink
,YouTubeLink
,ServiceManual
,ServiceForm
,ServiceArabicDescription
FROM ' + @AsIsTable + ' As R';
PRINT @ChangingAsIsColumnsToTheEquivelantValuesSQL
--EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window

Avatar of Harreni

ASKER

Thanks a lot Pawan and Vitor.
Yes, as Mr. Vitor It will NOT present the whole query in print command because of software limitation perhaps, but it will work and store it as you need.