Harreni
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
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
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
Output
(No column name)
पवन
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
Output
(No column name)
पवन
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.
Also, I set some variables inside that SQL query using nvarchar because I set Arabic words in those variables.
ASKER
Here is a part from my SQL query "not a complete query":
My issue is with @Yes and @No variables beside the length of my 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)
My issue is with @Yes and @No variables beside the length of my query
try this
Hope it helps !
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)
Hope it helps !
ASKER
Thanks Pawan,
still same issue exists, Arabic words represented as ???
Here is the result of print command:
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
Can you please provide column with their data types of table tb_Test_Services_ImportedB yWizard_As Is_Saad_Co rrectRecor ds_IDs
Specifically in which value of OutsideBranchesToProvideTh eService is inserted.
Trial
Hope it helps !!
Specifically in which value of OutsideBranchesToProvideTh
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
Hope it helps !!
ASKER
AgencyName nvarchar(MAX)
ServiceID nvarchar(MAX)
ServiceArabicName nvarchar(MAX)
OutsideBranchesToProvideTh eService 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)
ServiceID nvarchar(MAX)
ServiceArabicName nvarchar(MAX)
OutsideBranchesToProvideTh
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..
Hope it helps !!
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)
Hope it helps !!
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:
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
try,,, Issue was with this line -- it was Varchar(max) DECLARE @ChangingAsIsColumnsToTheE quivelantV aluesSQL 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)
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??
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Even If I define my query as NVRACHAR, It will be truncatedWhere did you see the truncation?
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.
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.
Can you post your full query. The other one is working fine on my machine.
ASKER
Thanks Vitor.
If I use NVARCHAR, The truncation started after the character number 3970.
If I use VARCHAR, No truncation is there.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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.
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.