• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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
0
Harreni
Asked:
Harreni
  • 9
  • 8
  • 4
  • +1
3 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
how is your variable declared? have you tried nvarchar(max)?
1
 
Pawan KumarDatabase ExpertCommented:
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)
पवन
0
 
HarreniAuthor Commented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HarreniAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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 !
0
 
HarreniAuthor Commented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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 !!
0
 
HarreniAuthor Commented:
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)
0
 
Pawan KumarDatabase ExpertCommented:
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 !!
0
 
HarreniAuthor Commented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
HarreniAuthor Commented:
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??
0
 
Pawan KumarDatabase ExpertCommented:
Use NVARCHAR (MAX) -- It can store up to 2GB of data.

Is the other issue fixed ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Even If I define my query as NVRACHAR, It will be truncated
Where did you see the truncation?
0
 
HarreniAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
HarreniAuthor Commented:
Thanks Vitor.
If I use NVARCHAR, The truncation started after the character number 3970.
If I use VARCHAR, No truncation is there.
0
 
Pawan KumarDatabase ExpertCommented:
Can you please provide the entire query so that we can look?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
That's why I asked where did you see the truncation. It may be a limitation of the application. Even SSMS doesn't show all characters (by default only show first 256 characters). But that doesn't mean that all information isn't stored. NVARCHAR(MAX) should let you store more than 6K of characters.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, when you are setting a value for a NVARCHAR variable always use N before the string:
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = N'INSERT INTO '
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

1
 
HarreniAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 9
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now