Hi,

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