For Nvarchar(Max) I am only getting 4000 characters in TSQL?
Solution 1
You have declared this as nvarchar(max) which allows 2GB of data so it will store 2GB.
What is happening:
- The datatype is not yet nvarchar(max) until assignment to @sql1
- Before that, it's a collection of strings, each less than 4000 (constants)
- You are concatenating short constants with short variables (short = < 4000)
- So you have 4000 characters put into @sql1
So, you have make sure you have nvarchar(max) on the right hand side.
One idea. The 2nd line concatenates nvarchar(max) with a constant = nvarchar(max)
SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...
....
It's no different to the integer division that happens in every langauge.
declare @myvar float
set @myvar = 1/2 --gives zero because it's integer on the right
Operator precedence (infers datatype precedence) is always "assignment" last... why should unicode strings in SQL Server be any different?
Solution 2
Update: gbn's answer is right, and I was wrong. As MSDN points out, nvarchar(max) supports up to 2^31-1 bytes of data, stored as UCS-2 (2 bytes per character, plus 2 for BOM). Your problem seems to be with string concatenation, not data type limits.
That said, if you're using it to build a SQL string, why not use VARCHAR? Do you have field names that aren't representable by the database's native character set (usually Latin-1)?
Finally -- you could simplify your entire problem by just not using dynamic SQL in your stored procedure. Create some table-valued functions that take your where-clause strings and return tables, and then just JOIN them in your procedure. As a bonus it will almost certainly be much faster, since at very least the database will be able to cache the SP body as a prepared statement.
Nguyen Anh Duc
Software developer from just outside Sydney. Enjoy sport and music in my spare time.
Updated on January 09, 2020Comments
-
Nguyen Anh Duc over 4 years
This is for SS 2005.
Why I am i only getting 4000 characters and not 8000?
It truncates the string @SQL1 at 4000.
ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, '' As AllocationDate, '' As AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [VenueCategories].[Category] + '' Allocations'' AS ReportHeader, ljs.AbbreviationCode AS PrevWeekCampaign FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs ON VenuePanels.PanelID = ljs.PanelID) INNER JOIN (SELECT VenueInfo.VenueID, VenuePanels.PanelID, VenueInfo.VenueName, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, ljs2.AbbreviationCode AS PrevWeekCampaign FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs2 ON VenuePanels.PanelID = ljs2.PanelID WHERE ' + @alldate + ' AND ' + @where + ') ljs3 ON VenueInfo.VenueID = ljs3.VenueID WHERE (((VenuePanels.PanelID)<>ljs3.[PanelID] And (VenuePanels.PanelID) Not In (SELECT PanelID FROM CampaignAllocations WHERE ' + @alldateprevweek + ')) AND ' + @where + ') UNION ALL SELECT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, ljs.AbbreviationCode AS PrevWeekCampaign FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs ON VenuePanels.PanelID = ljs.PanelID WHERE ' + @alldate + ' AND ' + @where Select @SQL1
-
ZygD over 14 yearsThis does not answer the question: @sql1 is declared as nvarchar(max) which allow 2GB.
-
ZygD over 14 years@Daniel: yes, or varchar, or real SQL as you mentioned.
-
Peter PitLock over 11 yearsdoes not work for me unfortunately. The claim is thus that just be making the nvarchars equal to nvarchar(max) , it will work?
-
gotqn over 10 years+1 But I am founding this very foolish. Let's say OK for MS, there is no sense to use/allocated (MAX) size, in case you do not need it, but when you need it (for example the case above, which is my case too) it is stupid for me to cut the string.