1

I am running a Stored procedure to export data to an Excel sheet. Issue is that The column headers are being displayed at the bottom or they are missing at times.

What am I missing here ?

SQL Server Version

Microsoft SQL Server 2016 (RTM-CU7) (KB4024304) - 13.0.2210.0 (X64) Jul 16 2017 17:56:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'rptSalesAndReturnBCP' AND Type = 'P')
BEGIN
    DROP PROC [dbo].[rptSalesAndReturnBCP] 
END

GO
CREATE PROC [dbo].[rptSalesAndReturnBCP]
    @StartDate DATETIME
    ,@EndDate DATETIME
    ,@BusinessChannelUID INT = NULL
    ,@DistributorUID INT = NULL
    ,@GEmployeeUID INT = NULL
    ,@GAgentUID INT = NULL
    ,@SalesOrgID NVARCHAR(50) = NULL
    ,@Output_Number INT OUTPUT
    ,@Output_FileName VARCHAR(MAX) OUTPUT
AS
BEGIN TRY
DECLARE @Query NVARCHAR(MAX)    

    --Create User Session Tempory table     
    DECLARE @TableName VARCHAR (255) 
    SET @TableName='##'  + REPLACE(CAST(NEWID() AS VARCHAR(MAX)) +'_TableRSA','-','');
    PRINT @TableName
---------------------------------------------------------------------------------------------------------------------------------

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    -----------------------------------------------------------------------------------
    --  IsItemBulk1QtyEnabled Filter

    DECLARE @IsItemBulk1QtyEnabled BIT

    SELECT @IsItemBulk1QtyEnabled = BC.IsItemBulk1QtyEnabled
    FROM BusinessChannel BC
    WHERE BC.UID = @BusinessChannelUID
    -----------------------------------------------------------------------------------
    -- NSM, RSM, ASM, ASE login filter

    DECLARE @mpt_DesignationEnum INT = 0, @mpt_DesignationUID INT

    SELECT @mpt_DesignationEnum = EB.mpt_DesignationEnum, @mpt_DesignationUID = EB.mpt_DesignationUID
    FROM EmployeeBusinessChannel EB 
    WHERE EB.BusinessChannelUID = @BusinessChannelUID 
        AND EB.EmployeeUID = @GEmployeeUID

    -----------------------------------------------------------------------------------
    -- Sales Org

    IF (@SalesOrgID IS NULL 
        OR @SalesOrgID = '-1'
        OR NOT EXISTS(SELECT COUNT(1) FROM BusinessChannelSalesOrg WHERE BussinessChannelUID = @BusinessChannelUID GROUP BY BussinessChannelUID HAVING COUNT(1) > 1))
    BEGIN
        SET @SalesOrgID = NULL;
    END

    -----------------------------------------------------------------------------------
    -- Agency Manager

    DECLARE @IsAgencyManagerFilter BIT = 0
    CREATE TABLE #AgencyUIDs (UID INT)  

    IF (@mpt_DesignationEnum = 90)
    BEGIN
        SET @IsAgencyManagerFilter = 1;

        INSERT INTO #AgencyUIDs
        SELECT P.ParentUID
        FROM ParentChildLink P
            INNER JOIN Agent A ON P.ChildUID = A.UID
        WHERE A.EmployeeUID = @GEmployeeUID
            AND P.mpt_TypeEnum = 3
            AND P.ExpiryDate IS NULL
            AND A.ExpiryDate IS NULL
    END

    -----------------------------------------------------------------------------------
    -- Sales Reps

    DECLARE @IsSalesRepFilter BIT = 0
    CREATE TABLE #SalesRepUIDs (UID INT PRIMARY KEY);

    IF EXISTS(SELECT 1 FROM MultipurposeTag WHERE UID = @mpt_DesignationUID AND Flag2 = 1)
    BEGIN
        SET @IsSalesRepFilter = 1;

        INSERT INTO #SalesRepUIDs
        SELECT DISTINCT emp_SalesRepUID
        FROM EmployeeSalesRep
        WHERE ExpiryDate IS NULL
            AND emp_ParentUID = @GEmployeeUID
            AND (@SalesOrgID IS NULL OR SalesOrg = @SalesOrgID);
    END

    -----------------------------------------------------------------------------------
    -- Distributors

    CREATE TABLE #DistributorUIDs (DistributorUID INT PRIMARY KEY)
    DECLARE @IsDistributorFilter BIT = 0

    IF @DistributorUID > 0
    BEGIN
        SET @IsDistributorFilter = 1;
        INSERT INTO #DistributorUIDs VALUES (@DistributorUID)
    END
    ELSE IF (@IsSalesRepFilter = 0 AND (@mpt_DesignationEnum = 5 OR @mpt_DesignationEnum = 6 OR @mpt_DesignationEnum = 7 OR @mpt_DesignationEnum = 8))
    BEGIN
        SET @IsDistributorFilter = 1;

        INSERT INTO #DistributorUIDs(DistributorUID)
        SELECT ED.DistributorUID
        FROM EmployeeDistributor ED 
        WHERE ED.EmployeeUID = @GEmployeeUID
            AND ED.BusinessChannelUID = @BusinessChannelUID
    END

    -----------------------------------------------------------------------------------

    SET @Query= 'SELECT  * INTO  '+@TableName+'
    FROM
    (
            SELECT D.Name AS Distributor
            , D.ID AS DistributorID
            , RE.Description AS Region
            , TE.Description AS Territory
            , T.ID AS TxnID
            , ISNULL(T.TxnInvoiceSerialNo,''-'') AS SerialNo
            , T.Date AS Date 
            , DATEPART(yyyy,T.Date) AS Year
            , DATEPART(mm,T.Date) AS Month
            , DATEPART(dd,T.Date) AS Day
            , O.Name AS Outlet
            , O.ID AS OutletID
            , ISNULL(MPT2.MiscData,''-'') AS OutletType
            , ISNULL(MPT3.MiscData,''-'') AS OutletGroup
            , CASE WHEN (E.FirstName + '' '' + E.LastName) IS NULL THEN ''* UNASSIGNED *'' ELSE E.FirstName + '' '' + E.LastName END + '' - '' + A.MiscData  as Agent   
            , CASE WHEN (ASM.FirstName + '' '' + ASM.LastName) IS NULL THEN ''* UNASSIGNED *'' ELSE ASM.FirstName + '' '' + ASM.LastName END as ASMName                 
            , CASE WHEN R.UID IS NULL THEN R2.Description ELSE R.Description END AS Route
            , ISNULL(C1.Description, '') AS Category1
            , ISNULL(C2.Description, '') AS Category2
            , ISNULL(C3.Description, '') AS Category3
            , ISNULL(C4.Description, '') AS Category4
            , ISNULL(C5.Description, '') AS Category5
            , I.ID AS ItemID
            , I.Description AS Item
            , ISNULL(MPT1.MiscData,''-'') AS Team
            , '' AS Reason
            , SO.Description AS SalesOrgName
            , I.UnitsPerBulk1Pack
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN TL.UnitQty / I.UnitsPerBulk1Pack 
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN 0 END AS Cases -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN TL.UnitQty % I.UnitsPerBulk1Pack 
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN TL.UnitQty END AS Units -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN TL.FreeQty / I.UnitsPerBulk1Pack
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN 0 END AS FreeCases -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN TL.FreeQty % I.UnitsPerBulk1Pack
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN TL.FreeQty END AS FreeUnits -- Cases Not Allowed
            , (TL.UnitQty * I.Volume / 1000000) AS Tonnage
            , TL.UnitPrice AS Price
            , TL.GrossValue AS GrossValue
            , TL.LineDisc AS GrossValue
            , TL.AdditionalDisc AS AdditionalDisc
            , CASE WHEN O.mpt_TypeEnum = 1 THEN (ISNULL(TL.GrossValue,0)) - (ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) + ((ISNULL(TL.NetSaleValue,0) - (ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0))) * (ISNULL(T.CompanyDiscPct,0) / 100)) + (ISNULL(TL.GrossValue,0) -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) )) * (ISNULL(T.DocDiscPct,0) / 100)) 
                   WHEN O.mpt_TypeEnum = 2 THEN (ISNULL(TL.GrossValue,0) -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) )) END  AS NetValue 
            , (ISNULL(TL.GrossValue,0) -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0)  + ISNULL(TL.AdditionalGroupDisc,0)  )) * (ISNULL(T.DocDiscPct,0) / 100) AS Discount
            , ISNULL(TL.GroupDiscPart,0) AS GroupDiscPart
            , ISNULL(TL.AdditionalDisc,0) AS AddiLineDisc
            , ISNULL(TL.AdditionalGroupDisc,0) AS AddiGroupDisc
            , (TL.NetSaleValue - (TL.GroupDiscPart + TL.AdditionalGroupDisc)) * (ISNULL(T.CompanyDiscPct,0) / 100) AS CompanyLineDisc
            , AR1.Description AS Town
            , AR2.Description AS Area
            , AR3.Description AS BusinessArea
            , ''Sales'' AS TypeTxn
            , CASE WHEN TL.mpt_TypeEnum = 1 THEN ''Normal Sales'' ELSE ''Free Sales'' END AS LineType
            , CASE WHEN O.mpt_TypeEnum = 1 THEN ''Normal'' WHEN O.mpt_TypeEnum = 2 THEN ''Direct'' END AS OutletBusinessType
            , MPT4.MiscData AS Type
            , CASE WHEN IPL1.UnitListPrice IS NULL THEN 0 ELSE IPL1.UnitListPrice END AS p1
            , CASE WHEN IPL2.UnitListPrice IS NULL THEN 0 ELSE IPL2.UnitListPrice END AS p2
            , CASE WHEN IPL3.UnitListPrice IS NULL THEN 0 ELSE IPL3.UnitListPrice END AS p3
            , AG.Name AS AgencyName 
            , CASE WHEN ISNULL(T.IsQuatationCompleted,0) = 0 AND T.mpt_TypeEnum = 58 THEN ''Quotation'' ELSE ''Normal Invoice'' END as InvoiceType
        FROM TxnInvoice T
            INNER JOIN TxnInvoiceLine TL ON T.UID = TL.TxnInvoiceUID AND T.SiteUID = TL.TxnInvoice_SiteUID
            INNER JOIN Item I ON TL.ItemUID = I.UID
            LEFT OUTER JOIN Agency AG ON I.AgencyUID = AG.UID
            LEFT OUTER JOIN Call C ON T.Call_SiteUID = C.SiteUID AND T.CallUID = C.UID
            LEFT OUTER JOIN 
            ( 
                SELECT MAX(R.RouteUID) AS RouteUID, R.OutletUID 
                FROM RouteOutlet R
                WHERE R.ExpiryDate IS NULL
                GROUP BY R.OutletUID
            ) KK ON T.OutletUID = KK.OutletUID 
            LEFT OUTER JOIN Route R ON C.RouteUID = R.UID
            LEFT OUTER JOIN Route R2 ON KK.RouteUID = R2.UID
            LEFT OUTER JOIN SalesOrg SO ON SO.ID = T.SalesOrgID
            LEFT JOIN Category C1 ON I.CategoryUID2 = C1.UID
            LEFT JOIN Category C2 ON I.CategoryUID3 = C2.UID
            LEFT JOIN Category C3 ON I.CategoryUID4 = C3.UID
            LEFT JOIN Category C4 ON I.CategoryUID5 = C4.UID
            LEFT JOIN Category C5 ON I.CategoryUID6 = C5.UID
            LEFT JOIN Outlet O ON T.OutletUID = O.UID
            LEFT JOIN MultiPurposeTag MPT1 ON I.MPTSubTypeUID = MPT1.UID
            LEFT JOIN MultiPurposeTag MPT2 ON O.mpt_TypeUID = MPT2.UID
            LEFT JOIN MultiPurposeTag MPT3 ON O.mpt_GroupUID = MPT3.UID
            LEFT JOIN Area AR1 ON O.AreaUID = AR1.UID
            LEFT JOIN Area AR2 ON AR1.ParentUID = AR2.UID
            LEFT JOIN Area AR3 ON O.BusinessAreaUID = AR3.UID
            LEFT JOIN Agent A ON T.AgentUID = A.UID
            INNER JOIN Distributor D ON T.DistributorUID = D.UID
            INNER JOIN EmployeeDistributor ED ON D.UID = ED.DistributorUID
            INNER JOIN EmployeeBusinessChannel EBC ON ED.EmployeeUID = EBC.EmployeeUID AND EBC.mpt_DesignationEnum = 6 -- ASM
            LEFT OUTER JOIN Employee ASM ON EBC.EmployeeUID = ASM.UID
            LEFT OUTER JOIN Area RE ON D.RegionUID = RE.UID
            LEFT OUTER JOIN Area TE ON D.BusinessAreaUID = TE.UID
            LEFT JOIN ItemPriceList IPL1 ON TL.ItemPriceListUID1 = IPL1.UID
            LEFT JOIN ItemPriceList IPL2 ON TL.ItemPriceListUID2 = IPL2.UID
            LEFT JOIN ItemPriceList IPL3 ON TL.ItemPriceListUID3 = IPL3.UID
            LEFT JOIN Employee E ON T.EmployeeUID = E.UID
            LEFT JOIN MultiPurposeTag MPT4 ON T.mpt_TypeUID = MPT4.UID
        WHERE T.mpt_TypeEnum IN ( 21 , 44, 14, 1)
            AND T.ExpiryDate IS NULL
            AND T.SubmittedDate IS NOT NULL
            AND T.Date >= @StartDate
            AND T.Date <= @EndDate
            AND D.BusinessChannelUID = @BusinessChannelUID
            AND (@SalesOrgID IS NULL OR SO.ID = @SalesOrgID)
            AND (@IsDistributorFilter = 0 OR D.UID IN (SELECT DistributorUID FROM #DistributorUIDs))
            AND (@IsAgencyManagerFilter = 0 OR I.AgencyUID IN (SELECT UID FROM #AgencyUIDs))
            AND (@IsSalesRepFilter = 0 OR T.EmployeeUID IN (SELECT UID FROM #SalesRepUIDs))

        UNION ALL

        SELECT D.Name AS Distributor
            , D.ID AS DistributorID
            , RE.Description AS Region
            , TE.Description AS Territory
            , T.ID AS TxnID
            , ISNULL(T.SerialNo,''-'') AS SerialNo
            , T.Date AS Date
            , DATEPART(yyyy,T.Date) AS Year
            , DATEPART(mm,T.Date) AS Month
            , DATEPART(dd,T.Date) AS Day
            , O.Name AS Outlet
            , O.ID AS OutletID
            , ISNULL(MPT2.MiscData,''-'') AS OutletType
            , ISNULL(MPT3.MiscData,''-'') AS OutletGroup
            , CASE WHEN (E.FirstName + '' '' + E.LastName) IS NULL THEN ''* UNASSIGNED *'' ELSE E.FirstName + '' '' + E.LastName END + '' - '' + A.MiscData  as Agent   
            , CASE WHEN (ASM.FirstName + '' '' + ASM.LastName) IS NULL THEN ''* UNASSIGNED *'' ELSE ASM.FirstName + '' '' + ASM.LastName END as ASMName
            , CASE WHEN R.UID IS NULL THEN R2.Description ELSE R.Description END AS Route
            , ISNULL(C1.Description, '') AS Category1
            , ISNULL(C2.Description, '') AS Category2
            , ISNULL(C3.Description, '') AS Category3
            , ISNULL(C4.Description, '') AS Category4
            , ISNULL(C5.Description, '') AS Category5
            , I.ID AS ItemID
            , I.Description AS Item
            , ISNULL(MPT1.MiscData,''-'') AS Team
            , MPT1.MiscData AS Reason
            , SO.Description AS SalesOrgName
            , I.UnitsPerBulk1Pack
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN -TL.UnitQty / I.UnitsPerBulk1Pack 
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN 0 END AS Cases -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN -TL.UnitQty % I.UnitsPerBulk1Pack 
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN -TL.UnitQty END AS Units -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN -TL.FreeQty / I.UnitsPerBulk1Pack
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN 0 END AS FreeCases -- Cases Not Allowed
            ,CASE WHEN @IsItemBulk1QtyEnabled = 1 THEN -TL.FreeQty % I.UnitsPerBulk1Pack
                  WHEN @IsItemBulk1QtyEnabled = 0 THEN -TL.FreeQty END AS FreeUnits -- Cases Not Allowed
            , -(TL.UnitQty * I.Volume / 1000000) AS Tonnage
            , TL.UnitPrice AS Price
            , -TL.GrossValue AS GrossValue
            , -TL.LineDisc AS LineDisc
            , -TL.AdditionalDisc AS AdditionalDisc
            , CASE WHEN O.mpt_TypeEnum = 1 THEN -(ISNULL(TL.GrossValue,0) - (ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) + (TL.GrossValue -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) )) * (ISNULL(T.DocDiscPct,0) / 100) + (ISNULL(TL.NetReturnValue,0) * (ISNULL(T.CompanyDiscPct,0) / 100)))) 
                   WHEN O.mpt_TypeEnum = 2 THEN -(TL.GrossValue -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0)   + ISNULL(TL.GroupDiscPart,0) + ISNULL(TL.AdditionalGroupDisc,0) )) END  AS NetValue 
            , -(ISNULL(TL.GrossValue,0) -(ISNULL(TL.LineDisc,0) + ISNULL(TL.AdditionalDisc,0) + ISNULL(TL.GroupDiscPart,0)  + ISNULL(TL.AdditionalGroupDisc,0) )) * (ISNULL(T.DocDiscPct,0) / 100) AS Discount
            , -ISNULL(GroupDiscPart,0) AS GroupDiscPart
            , -ISNULL(TL.AdditionalDisc,0) AS AddiLineDisc
            , -ISNULL(TL.AdditionalGroupDisc,0) AS AddiGroupDisc
            , -((TL.NetReturnValue - (TL.GroupDiscPart + TL.AdditionalGroupDisc)) * (ISNULL(T.CompanyDiscPct,0) / 100)) AS CompanyLineDisc
            , AR1.Description AS Town
            , AR2.Description AS Area
            , AR3.Description AS BusinessArea
            , CASE WHEN T.mpt_TypeEnum <> 35 THEN ''Return'' ELSE ''Replacement'' END AS TypeTxn
            , CASE WHEN ISNULL(TL.FreeQty,0) > 0 THEN ''Free Return'' ELSE ''Normal Return'' END AS LineType
            , CASE WHEN O.mpt_TypeEnum = 1 THEN ''Normal'' WHEN O.mpt_TypeEnum = 2 THEN ''Direct'' END AS OutletBusinessType
            , MPT4.MiscData AS Type
            , CASE WHEN IPL1.UnitListPrice IS NULL THEN 0 ELSE IPL1.UnitListPrice END AS p1
            , CASE WHEN IPL2.UnitListPrice IS NULL THEN 0 ELSE IPL2.UnitListPrice END AS p2
            , CASE WHEN IPL3.UnitListPrice IS NULL THEN 0 ELSE IPL3.UnitListPrice END AS p3
            , AG.Name AS AgencyName 
            , '' as InvoiceType
        FROM TxnReturn T
            INNER JOIN TxnReturnLine TL ON T.UID = TL.TxnReturnUID AND T.SiteUID = TL.TxnReturn_SiteUID
            INNER JOIN MultiPurposeTag MPT1 ON TL.mpt_ReturnReasonUID = MPT1.UID
            INNER JOIN Item I ON TL.ItemUID = I.UID
            LEFT OUTER JOIN Agency AG ON I.AgencyUID = AG.UID
            LEFT OUTER JOIN Call C ON T.Call_SiteUID = C.SiteUID AND T.CallUID = C.UID
            LEFT OUTER JOIN 
            ( 
                SELECT MAX(R.RouteUID) AS RouteUID,R.OutletUID 
                FROM RouteOutlet R
                WHERE R.ExpiryDate IS NULL
                GROUP BY R.OutletUID
            ) KK ON T.OutletUID = KK.OutletUID 
            LEFT OUTER JOIN Route R ON C.RouteUID = R.UID
            LEFT OUTER JOIN Route R2 ON KK.RouteUID = R2.UID
            LEFT OUTER JOIN SalesOrg SO ON SO.ID = T.SalesOrgID
            LEFT JOIN Category C1 ON I.CategoryUID2 = C1.UID
            LEFT JOIN Category C2 ON I.CategoryUID3 = C2.UID
            LEFT JOIN Category C3 ON I.CategoryUID4 = C3.UID
            LEFT JOIN Category C4 ON I.CategoryUID5 = C4.UID
            LEFT JOIN Category C5 ON I.CategoryUID6 = C5.UID
            LEFT JOIN Outlet O ON T.OutletUID = O.UID
            LEFT JOIN MultiPurposeTag MPT2 ON I.MPTSubTypeUID = MPT2.UID
            LEFT JOIN Area AR1 ON O.AreaUID = AR1.UID
            LEFT JOIN Area AR2 ON AR1.ParentUID = AR2.UID
            LEFT JOIN Area AR3 ON O.BusinessAreaUID = AR3.UID
            INNER JOIN Agent A ON T.AgentUID = A.UID
            LEFT JOIN MultiPurposeTag MPT3 ON O.mpt_TypeUID = MPT3.UID
            LEFT JOIN MultiPurposeTag MPT4 ON O.mpt_GroupUID = MPT4.UID
            LEFT JOIN ItemPriceList IPL1 ON TL.ItemPriceListUID1 = IPL1.UID
            LEFT JOIN ItemPriceList IPL2 ON TL.ItemPriceListUID2 = IPL2.UID
            LEFT JOIN ItemPriceList IPL3 ON TL.ItemPriceListUID3 = IPL3.UID
            INNER JOIN Distributor D ON T.DistributorUID = D.UID
            INNER JOIN EmployeeDistributor ED ON D.UID = ED.DistributorUID
            INNER JOIN EmployeeBusinessChannel EBC ON ED.EmployeeUID = EBC.EmployeeUID AND EBC.mpt_DesignationEnum = 6 -- ASM
            LEFT OUTER JOIN Employee ASM ON EBC.EmployeeUID = ASM.UID
            LEFT OUTER JOIN Area RE ON D.RegionUID = RE.UID
            LEFT OUTER JOIN Area TE ON D.BusinessAreaUID = TE.UID
            LEFT JOIN Employee E ON A.EmployeeUID = E.UID
            LEFT JOIN MultiPurposeTag MPT5 ON T.mpt_TypeUID = MPT5.UID
        WHERE 
            T.mpt_TypeEnum IN (57,2,45, 35/*Replacement*/)
            AND T.ExpiryDate IS NULL
            AND T.SubmittedDate IS NOT NULL
            AND T.Date >= @StartDate
            AND T.Date <= @EndDate
            AND (@IsDistributorFilter = 0 OR D.UID IN (SELECT DistributorUID FROM #DistributorUIDs))
            AND (@BusinessChannelUID IS NULL OR D.BusinessChannelUID = @BusinessChannelUID)
            AND (@SalesOrgID IS NULL OR SO.ID = @SalesOrgID)
            AND (@IsAgencyManagerFilter = 0 OR I.AgencyUID IN (SELECT UID FROM #AgencyUIDs))
            AND (@IsSalesRepFilter = 0 OR T.EmployeeUID IN (SELECT UID FROM #SalesRepUIDs))

)'

------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_executesql 
            @Query
            , N'@StartDate DATETIME, @EndDate DATETIME, @BusinessChannelUID INT, @DistributorUID INT, @SalesOrgID NVARCHAR(50), @IsItemBulk1QtyEnabled BIT
            , @mpt_DesignationEnum INT, @mpt_DesignationUID INT, @IsAgencyManagerFilter BIT, @IsSalesRepFilter BIT, @IsDistributorFilter BIT'
            ,@StartDate = @StartDate
            ,@EndDate = @EndDate
            ,@BusinessChannelUID = @BusinessChannelUID
            ,@DistributorUID = @DistributorUID
            --,@GEmployeeUID = @GEmployeeUID
            --,@AgencyUID = @AgencyUID
            ,@SalesOrgID = @SalesOrgID
            ,@IsItemBulk1QtyEnabled = @IsItemBulk1QtyEnabled
            ,@mpt_DesignationEnum = @mpt_DesignationEnum
            ,@mpt_DesignationUID = @mpt_DesignationUID
            ,@IsAgencyManagerFilter = @IsAgencyManagerFilter
            ,@IsSalesRepFilter = @IsSalesRepFilter
            ,@IsDistributorFilter = @IsDistributorFilter
------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @server Varchar (100)
    SET @server = 'SERVERNAME'
    DECLARE @FileName VARCHAR(255)
    DECLARE @ToDayDate VARCHAR(12)
    SELECT @ToDayDate = CONVERT(VARCHAR(10),GETDATE(),120)
    SET @Output_FIleName = CAST(NEWID() AS VARCHAR(MAX))+ '_SalesAndReturnsRpt' + '.xls'

      DECLARE @FilePath VARCHAR (4000)
      SET @FilePath = '\\SERVERNAME\BCPDumpFiles\' + @Output_FIleName  ;

    DECLARE @ExcSql Varchar (Max)
        DECLARE @dbName Varchar (100)
        SET @dbName = 'master'
        DECLARE @sql varchar (8000)
        SET @sql = ' SELECT * FROM ' + @TableName ;
        --exec(@sql)

        DECLARE @dbName1 Varchar (100)
        SELECT  @dbName1 = @dbName
        SELECT  @dbName = 'use ' + @dbName + ';'

        --Prepare Columns as excel compatible 
        DECLARE @columnNames varchar (8000), @columnConvert varchar (8000), @tempSQL varchar (8000)

        SELECT  @columnNames = COALESCE(@columnNames + ',', '') + '"' + column_name + '"',
        @columnConvert = COALESCE(@columnConvert + ',', '') + 'convert(nvarchar(4000),'
            + '[' + column_name + ']' + CASE
                WHEN data_type IN ('datetime', 'smalldatetime') THEN ',121'
                WHEN data_type IN ('numeric', 'decimal') THEN ',128'
                WHEN data_type IN ('float', 'real', 'money', 'smallmoney') THEN ',2'
                WHEN data_type IN ('datetime', 'smalldatetime') THEN ',120' ELSE ''
            END + ') as ' + '[' + column_name + ']'
        FROM tempdb.INFORMATION_SCHEMA.Columns
        WHERE table_name = @TableName

        print @columnNames
        print @TableName
            --Prepare sql to bcp 
        SELECT  @sql = 'select ' + @columnNames + ' into '+@TableName+'_2 from (select ' + @columnConvert + ', 
            ''2'' as temp##SortID         
                from '+@TableName+' union all select ''' + REPLACE(@columnNames, ',', ''', ''') + ''', 
                    ''1'') t order by temp##SortID'        
        EXEC (@sql)
        print @sql
        SET @sql = 'SELECT * FROM '+ @TableName +'_2'
        PRINT @Sql
        exec(@sql)

        SET @sql = 'bcp " select * from '+@TableName+'_2 order by [Year] asc "  queryout "' + @FilePath + '" -U uno -P uno   -c -T -S ' + @server   
        PRINT @Sql
        EXEC master..xp_cmdshell @sql

        ------------------------------------------------------------------------------------------
    --  SET  @Query = N'DROP TABLE ' + @TableName ;
        --EXEC sp_sqlexec @Query
        --SET  @Query = N'DROP TABLE ' + @TableName + '_2' ;
        --EXEC sp_sqlexec @Query    

        SET @OutPut_Number = 1
END TRY
BEGIN CATCH
    DECLARE @ErrorDescription VARCHAR(1000) = 'ERROR - ' + ERROR_MESSAGE() RAISERROR(@ErrorDescription,16,1)
    SET @OutPut_Number = 2
    --SELECT @ErrorDescription
END CATCH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.