Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic sql query that use Nvarchar and exceed 4000 characters

Posted on 2016-11-16
22
Medium Priority
?
108 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 70

Expert Comment

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

Expert Comment

by:Pawan Kumar
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

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

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 35

Assisted Solution

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

Is the other issue fixed ?
0
 
LVL 52

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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 1000 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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 52

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 the fundamental information of how to create a table.

916 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