Trying to troubleshoot .....one line of SQL at a time.....

Trying to check one line of code at a time in the Query below. I am trying to see where the SELECT breaks down in dumping to the TEMP table called WeeklyTemp.

Can someone identify where I keep going wrong here. I either get the following errors.
String or binary data would be truncated.
USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_MAC_RAMS]    Script Date: 10/17/2013 10:05:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_MAC_RAMS]  
 
	(
--		@Cus_no_Start		varchar(12), 
--		@Cus_no_End			varchar(12),
--		@Rams_Cus_no_Start	varchar(12),
--		@Rams_Cus_no_End	varchar(12),
		@DB_Include			int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

	) AS

-- for testing
-- drop table #weeklytemp
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'
-- end testing
/* note:
8 columns
WW = col 1 report_sort = 10
EW = col 2 report_sort = 20
OR = col 3 report_sort = 30
AK = col 4 report_sort = 40
MW = col 5 report_sort = 185
NC = col 6 report_sort = 50
MI = col 7 report_sort = 110
FS = col 8 report_sort = 70
*/
----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
--If @Cus_no_Start <> '*' and @Cus_no_End <> '*'  
--BEGIN
-- set @Cus_no_Start			= right('000000000000' + @Cus_no_Start,12)
-- set @Cus_no_End			= right('000000000000' + @Cus_no_End,12)
--END

/***************************************************************************
-- Taking out the selection for RAMS customers
If @Rams_Cus_no_Start <> '*' and @Rams_Cus_no_End <> '*' 
BEGIN 
  set @Rams_Cus_no_Start	= right('000000000000' + @Rams_Cus_no_Start,12)
  set @Rams_Cus_no_End		= right('000000000000' + @Rams_Cus_no_End,12)
END
*****************************************************************************/

-- print @Cus_no_Start
-- print @Cus_no_End
----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate 	= CurrWeekEndDate, 
   @CurrToDateYTD 		= CurrToDateYTD,
   @CurrFromDateYTD		= CurrFromDateYTD,
   @CurrFromDate 		= CurrFromDate,
   @CurrToDate 			= CurrToDate, 
   @CurrToDateYTD 		= CurrToDateYTD,
   @CurrWeekBeginDate 	= CurrWeekBeginDate,
   @PriorWeekBeginDate 	= PriorWeekBeginDate,
   @PriorWeekEndDate 	= PriorWeekEndDate,
   @PriorFromDateYTD 	= PriorFromDateYTD,
   @PriorToDateYTD 		= PriorToDateYTD,
   @PriorFromDate 		= PriorFromDate,
   @PriorToDate 		= PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK) 

declare @CurrentWeek decimal(8,0), 
		@NumberOfWeeks int, 
		@CurrToDate_Period tinyint, 
		@CurrToDateYTD_FiscalYear smallint
declare @CurrToDate_pSDt datetime, 
		@CurrToDate_pSDt7 datetime

select 
	@CurrentWeek 				= CurrentWeek,
	@NumberOfWeeks 				= NumberOfWeeks,
	@CurrToDate_Period 			= CurrToDate_Period,
	@CurrToDateYTD_FiscalYear	= CurrToDateYTD_FiscalYear,
	@CurrToDate_pSDt			= CurrToDate_PeriodStartDate,
	@CurrToDate_pSDt7			= CurrToDate_PeriodStartDate+(7*vwFiscalDates.NumberOfWeeks-1)

from dbo.vwFiscalDates  


----------------------------------------------------------------------------------------------------
--  below is pulling data from macola into temp table weeklytemp
----------------------------------------------------------------------------------------------------

CREATE TABLE #weeklytemp
  (
    [data_source] varchar(12),--  what database is the data coming from  macola or rams?
	[RAMS_ID] VARCHAR (6) Null
/*	[CurrWeekEndDate] [int] NULL ,
	[CurrToDate] [int] NULL ,
	[CurrToDateYTD] [int] NULL ,
	[CurrentWeek] [decimal](8, 0) NULL ,
	[NumberOfWeeks] [int] NULL ,
	[CurrToDate_Period] [tinyint] NULL ,
	[CurrToDateYTD_FiscalYear] [smallint] NULL ,
	[Terr_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ID_Title] [Varchar] (40),
	[Report_Sort] [int] NULL ,
	[Terr] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustNo] [varchar] (30) 	COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	
	[TerrAdopted] [Varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Adopted_Sort] [int] NULL ,
	[WeekNumber] [int] NULL ,
	[Week1_EndDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TICKET_NUMBER] varchar (10) NULL,
	[Item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RETURN_REASON_CODE] varchar (6) null,
	[FINAL_PRICE] decimal (38,2) null,
	[TOTAL_DELIV_QUANTITY] int null,
	[Week_Sls_Amt] [decimal](38, 2) NULL ,
	[Week_Cost_Amt] [decimal](38, 2) NULL,
	[Date_id] [datetime] null,
	[qty_to_ship] varchar (10) null,
	[inv_dt] int,   
	[prod_cat] Varchar (12), --left([prod_cat],2) int, --as ProdCatNo,
	[prod_desc] varchar (32),
    [CurrWeekBeginDate] int null,
    [PriorWeekBeginDate] int Null,
    [PriorWeekEndDate] Int Null,
    [CurrFromDate] Int Null,
    [PriorFromDate] Int Null,
    [PriorToDate] Int Null,
    [CurrFromDateYTD] Int Null,
    [PriorFromDateYTD] Int Null,
    [PriorToDateYTD] Int Null,
    [qty_return_to_stk] Int null
    )
   */
--------------------------------------------------------
--RAMS Section
-------------------------------------------------------

--if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both Macola and RAMS data
--BEGIN

insert  into #weeklytemp

	SELECT DISTINCT
    	    
	    'Rams',
		CM.CM_SELLZONE
/*		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek as CurrentWeek,
		@NumberOfWeeks as NumberOfWeeks,
		@CurrToDate_Period as CurrToDate_Period,
		@CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
		CUST_ID.ID_DESC as Terr_name,
		CUST_ID.ID_Title as ID_Title, 
		Rpt_TerritoryAdopted.Report_Sort, 
		SUBSTRING(CUST_ID.ID_DESC, 3, 2) AS Terr,
		OH.OH_CUSTOMER_NUMBER,
		CM.CM_FULLNAME,
		Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
		Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
		Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
		FLOOR((CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt,112))/7+1) AS WeekNumber,
		CONVERT(char(8), @CurrToDate_pSDt+(7*FLOOR(CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt)/7+1)-1),112) AS Week1_EndDate,
		OD_TICKET_NUMBER,
		OD.OD_PRODUCT as Item_no,
		OD.OD_RETURN_REASON_CODE,
		OD.OD_FINAL_PRICE,
		OD.OD_TOTAL_DELIV_QUANTITY, 
		Week_Sls_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) -- Sale or BuyBack
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Week_Cost_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) 																											
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END),
	     OH.OH_delivery_date,
	     OD_TOTAL_DELIV_QUANTITY,
	     CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
		 @PriorWeekBeginDate AS PriorWeekBeginDate,
		 @PriorWeekEndDate AS PriorWeekEndDate,
		 @CurrFromDate as CurrFromDate,
		 @PriorFromDate AS PriorFromDate,
		 @PriorToDate AS PriorToDate,
		 @CurrFromDateYTD AS CurrFromDateYTD,
		 @PriorFromDateYTD AS PriorFromDateYTD,
		 @PriorToDateYTD AS PriorToDateYTD,
		 qty_return_to_stk = 0
*/		 
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
				ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
				AND CM.CM_ACCT_GLPOSTS = '000004'
				--AND left(CM.CM_SELLZONE,1) = '4'
				--AND CM.CM_CUST_SUB_TYPE <> 3
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
				ON CM.CM_SELLZONE = CUST_ID.IDVALUE
				AND CUST_ID.ID_TITLE = N'Sale Zone'
		INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD	
				ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER	
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
				ON PD.PRD_PRODUCT = OD.OD_PRODUCT
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
				ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
				AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = Terr
		LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
						ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
						AND Rpt_TerritoryAdopted.End_Item_no
		
--		WHERE 
--			(CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrToDate_pSDt AND @CurrToDate_pSDt7)	
			-- OH.OH_SALE_TYPE = 'O'
			-- AND (CUST_ID.ID_TITLE = N'Sale Zone')
          
 --GROUP BY   PR_ID.ID_DESC,
            --OD.OD_PRODUCT,
            --CM.CM_SELLZONE,
            --CUST_ID.IDVALUE,
            --CUST_ID.ID_TITLE,
            --PD.PRD_R_BRANDNAME

--END

----------------------------------------------------
--End RAMS Section
----------------------------------------------------

---------------------------------------------------------------------------------------------
	---   MACOLA   SECTION
--------------------------------------------------------------------------------------------
	
 
/*If (@DB_Include = 0 or @DB_Include =2 ) -- macola data
-- if @DB_Include = 0  -- macola data
Begin
insert  into #weeklytemp

SELECT DISTINCT
     
      'Macola',
     ARCUSFIL_SQL.Terr, -- correlates with the RAMS ID in RAMS DB
--    Rpt_ReportingDates.CurrWeekEndDate AS CurrWeekEndDate,
--    Rpt_ReportingDates.CurrToDate AS CurrToDate,
--   Rpt_ReportingDates.CurrToDateYTD AS CurrToDateYTD,
      @CurrWeekEndDate as CurrWeekEndDate, 
      @CurrToDate as CurrToDate, 
      @CurrToDateYTD as CurrToDateYTD, 
      @CurrentWeek AS CurrentWeek,
      @NumberofWeeks AS NumberofWeeks,
      @CurrToDate_Period AS CurrToDate_Period,
      @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
      Rpt_Territory.Terr_Name,
      '0',
      Rpt_OELINHST_Terr.Report_Sort,
      Rpt_OELINHST_Terr.Terr,
      Rpt_OELINHST_Terr.cus_no ,
      ARCUSFIL_SQL.cus_name,
      Rpt_Territory.Terr_id,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort AS Adopted_Sort,
      '0', -- ARCUSFIL_SQL.weeknumber AS WeekNumber,
      '0', -- ARCUSFIL_SQL.week1_enddate,
      '0', -- ARCUSFIL_SQL.ticket_number,
      Rpt_OELINHST_Terr.item_no,
      '0', -- ARCUSFIL_SQL.OD_RETURN_REASON_CODE,
      '0.00', -- ARCUSFIL_SQL.OD_FINAL_PRICE,
      Rpt_OELINHST_Terr.qty_to_ship,-- dup below needs to match [TOTAL_DELIV_QUANTITY] in table
      Rpt_OELINHST_Terr.sls_amt,-- Weekly_Sales_Amt
      '0.00',-- Weekly_Cost_Amt 
      ' ', -- Date_ID set to Null per Phil Lavene
      Rpt_OELINHST_Terr.qty_to_ship,
      Rpt_OELINHST_Terr.inv_dt, 
      IMITMIDX_SQL.prod_cat,
      IMCATFIL_SQL.prod_cat_desc,
/*    Rpt_ReportingDates.CurrWeekBeginDate AS CurrWeekBeginDate,
      Rpt_ReportingDates.PriorWeekBeginDate AS PriorWeekBeginDate,
      Rpt_ReportingDates.PriorWeekEndDate AS PriorWeekEndDate,
     Rpt_ReportingDates.CurrFromDate AS CurrFromDate,
     Rpt_ReportingDates.PriorFromDate AS PriorFromDate,
     Rpt_ReportingDates.PriorToDate AS PriorToDate,
     Rpt_ReportingDates.CurrFromDateYTD AS CurrFromDateYTD,
     Rpt_ReportingDates.PriorFromDateYTD AS PriorFromDateYTD,
     Rpt_ReportingDates.PriorToDateYTD AS PriorToDateYTD,*/
--  Replacing above with same as in RAMS Select
      @CurrWeekBeginDate AS CurrWeekBeginDate,
      @PriorWeekBeginDate AS PriorWeekBeginDate,
      @PriorWeekEndDate AS PriorWeekEndDate,
      @CurrFromDate as CurrFromDate,
      @PriorFromDate AS PriorFromDate,
      @PriorToDate AS PriorToDate,
      @CurrFromDateYTD AS CurrFromDateYTD,
      @PriorFromDateYTD AS PriorFromDateYTD,
      @PriorToDateYTD AS PriorToDateYTD,
      Rpt_OELINHST_Terr.qty_return_to_stk
     
 

FROM
    { oj (((DATA_04.dbo.Rpt_OELINHST_Terr Rpt_OELINHST_Terr LEFT OUTER JOIN DATA_04.dbo.Rpt_Territory Rpt_Territory ON
        Rpt_OELINHST_Terr.Terr = Rpt_Territory.Terr_Id)
     LEFT OUTER JOIN DATA_04.dbo.ARCUSFIL_SQL ARCUSFIL_SQL ON
        Rpt_OELINHST_Terr.cus_no = ARCUSFIL_SQL.cus_no)
	 INNER JOIN DATA_04.dbo.Rpt_ReportingDates Rpt_ReportingDates ON
        Rpt_OELINHST_Terr.inv_dt >= Rpt_ReportingDates.PriorFromDateYTD
            )
     LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        Rpt_OELINHST_Terr.item_no = IMITMIDX_SQL.item_no}
     INNER JOIN DATA_04.dbo.IMCATFIL_SQL IMCATFIL_SQL ON
             IMITMIDX_SQL.prod_cat = IMCATFIL_SQL.prod_cat
WHERE
     (Rpt_OELINHST_Terr.inv_dt between @CurrFromDateYTD and @CurrToDateYTD or
     Rpt_OELINHST_Terr.inv_dt between @PriorFromDateYTD and @PriorToDateYTD) -- Changed by RB was @PriorFromDate and @PriorToDate
END

*/
----------------------------------------------------
-- final select
----------------------------------------------------

select * from #weeklytemp

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
its hard for us to determine as we don't have the table structure.
what you can do is comment out one varchar field on your temp table and the corresponding column on your insert statement and run the sp. if no error, uncomment them and comment the next one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That is what I was trying to do and it gives me the error of :

Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_MAC_RAMS, Line 53
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_MAC_RAMS, Line 145
Incorrect syntax near the keyword 'BEGIN'.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Just found that you have already commented all the columns except the first two. Can you increase the size of [RAMS_ID]  from VARCHAR (6) to varchar(200) and test
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I am going through it again with the " -- " in each row.

If it comes back and none of the rows fail and it looks like it is alright but when run I get the
"String or binary data would be truncated. "
What do you suggest I try next...?
0
Aneesh RetnakaranDatabase AdministratorCommented:
>>I am going through it again with the " -- " in each row.
that's because most of the  rows except the first two are commented with /*.. */

Did you try increasing the size of the second column as I suggested
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I could not get past the error code
Incorrect syntax near the keyword 'declare'
Incorrect syntax near the keyword 'BEGIN'

I thought there was a quicker way but I would have increased the size of the column but in running with it commented out still brought back the error.
0
Aneesh RetnakaranDatabase AdministratorCommented:
your original code is already attached to this post, use that or else you can post your current code here . I wish I have remote access to your PC, that would be easier
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Found it it was PRD_DESC1.....should have been 40 characters wide instead of 32. Thanks for the support.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Persistance and waiting for the query to finish. Thanks
One row at a time. I am glad there were not 100 rows. Whew
0
Aneesh RetnakaranDatabase AdministratorCommented:
even if with 100 columns, you have to do the same approach, probably wont do it one at a time, instead will go for half at a time
0
PortletPaulfreelancerCommented:
>>instead will go for half at a time
yep,
do top half, if that's ok,
it's in the bottom half, do half of that, etc.

if the top half is NOT ok, then do top quarter, if that's OK
it's in the next quarter, do half of that, etc

much faster, it's a boolean approach :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.