Solved

Dynamic sql query that use Nvarchar and exceed 4000 characters

Posted on 2016-11-16
22
23 Views
Last Modified: 2016-11-17
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
Comment
Question by:Harreni
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41890050
how is your variable declared? have you tried nvarchar(max)?
1
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890053
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
 

Author Comment

by:Harreni
ID: 41890135
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
 

Author Comment

by:Harreni
ID: 41890180
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890586
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
 

Author Comment

by:Harreni
ID: 41890932
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890941
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
 

Author Comment

by:Harreni
ID: 41890944
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890947
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
 

Author Comment

by:Harreni
ID: 41890958
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890976
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Harreni
ID: 41890987
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
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
ID: 41890994
Use NVARCHAR (MAX) -- It can store up to 2GB of data.

Is the other issue fixed ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891014
Even If I define my query as NVRACHAR, It will be truncated
Where did you see the truncation?
0
 

Author Comment

by:Harreni
ID: 41891017
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41891021
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
 

Author Comment

by:Harreni
ID: 41891022
Thanks Vitor.
If I use NVARCHAR, The truncation started after the character number 3970.
If I use VARCHAR, No truncation is there.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41891026
Can you please provide the entire query so that we can look?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41891027
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41891029
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41891032
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
 

Author Closing Comment

by:Harreni
ID: 41891038
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now