[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

problems embedding sql script into dos batch file

Posted on 2009-04-20
6
Medium Priority
?
742 Views
Last Modified: 2013-11-10
Hi there,

I have this batch file I am trying to embed a SQL script into.  I would call the label :sql which will then export to the %temp% folder a completed sql script (which will be variablized the reason to embed this script) because the batch file will ask questions and export the values into the sql script.

The issue is i get this error:


CALLING...
DELETE FROM #Report where sourcecode LIKE 'COPTEMPshipfinrptname.SQL
DELETE FROM #Report where sourcecode LIKE 'DO NOTTEMPshipfinrptname.SQL
DELETE FROM #Report where sourcecode LIKE 'HETTEMPshipfinrptname.SQL
DELETE FROM #Report where sourcecode LIKE 'watchTEMPshipfinrptname.SQL
                CONVERT(VARCHAR(20),@PcntEmails)+'TEMPshipfinrptname.SQL


This is a copy from the screen.  What confuses me is the syntax LOOKS correct.  I mean there doesnt APPEAR to be any special characters and I can not see a reason for this to happen. IF there were any special characters I would THINK it would be around:
ECHO DELETE FROM #Report where sourcecode IN ('86') >> %TEMP%\%ship%_%finrptname%.SQL

Sorry just not seeing it, any help is appreciated.  There is a second spot at the end of the embedded sql script causing the same issue.

Thanks!


:SQL
ECHO use winoasis > %TEMP%\%ship%_%finrptname%.SQL
ECHO SET NOCOUNT ON >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET ANSI_WARNINGS OFF >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#playertrips]')) drop table #playertrips >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#TotalTwin]')) drop table #TotalTwin >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Rem_TotalTwin]')) drop table #Rem_TotalTwin >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Rem_MaxAge]')) drop table #Rem_MaxAge >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#REM_Pnts]')) drop table #REM_Pnts >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#REM_lastplaydate]')) drop table #REM_lastplaydate >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#REM_enrolldate]')) drop table #REM_enrolldate >> %TEMP%\%ship%_%finrptname%.SQL
ECHO IF EXISTS       (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Report]')) drop table #Report >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   declare             @Verbosity       int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @vstartdate      datetime, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @venddate       datetime, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @vmonth                      datetime, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @TODAY                      datetime, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @casino                       varchar(10), >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @varmonths     int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @SUMonths     int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @tpoints           int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @tstart             int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @astart             int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @MaxAge                     int >> %TEMP%\%ship%_%finrptname%.SQL
ECHO. >> %TEMP%\%ship%_%finrptname%.SQL
ECHO /*            QUALIFIERS GO HERE */ >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @Verbosity       = '0'                  -- 0=No Verbosity 1=Verbose Display >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @VARMonths   = '18'                -- Maximum # Months to look for play???? >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @SUMonths     = '999'              -- Maximum Number months back on Sign up Date >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     @MaxAge         = '999'              -- Maximum Age to Market >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     @tpoints           = '-999999'        -- Minimum Points Available to qualify >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     @tstart             = '499'              -- Minimum Theo to qualify >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     @astart             = '499'              -- Minimum Actual to qualify >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO /*            DO NOT EDIT BELOW */ >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT             @Casino           = (SELECT LTRIM(RTRIM(RIGHT(DESCRIPTION, LEN(DESCRIPTION)-9))) FROM GEN_CASINO (nolock)) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @VENDDATE       = (SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @VSTARTDATE            = (SELECT DATEADD(dd, -DAY(DATEADD(m,1,@VENDDATE)), DATEADD(m,-@VarMonths,@VENDDATE))) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @VMONTH                   = (SELECT DATEADD(dd, -DAY(DATEADD(m,1,@VENDDATE)), DATEADD(m,-@SUMonths,@VENDDATE))) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO create table #playertrips >> %TEMP%\%ship%_%finrptname%.SQL
ECHO ( playerid            int, totaltrips      int ) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO. >> %TEMP%\%ship%_%finrptname%.SQL
ECHO insert into #playertrips (playerid,totaltrips) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select meta_id, count(meta_id) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO from cds_stattrip (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO where IDType = 'P' and startgamingdate between @vstartdate and @venddate >> %TEMP%\%ship%_%finrptname%.SQL
ECHO group by meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO. >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO Create table #TotalTwin >> %TEMP%\%ship%_%finrptname%.SQL
ECHO ( playerid int, TotalTwin    money, TotalActual money, PntsEarned int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   PntsMgrAdjusted int, PntsPromo           int, PntsRedeemed int ) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO insert into #TotalTwin >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select  meta_id,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   Round(sum(twin), 0),  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   Round(sum(cashin+frontin+chipsin+creditin)-sum(cashout+jackpot), 0), >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   0,0,0,0 >> %TEMP%\%ship%_%finrptname%.SQL
ECHO from       cds_statday (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO where     gamingdate between @vstartdate and @venddate >> %TEMP%\%ship%_%finrptname%.SQL
ECHO group by meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO UPDATE                         #TotalTwin                     --Points Earned >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET                    PntsEarned = amount.total >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM                (SELECT meta_id, IsNull(SUM(ptsearned), '0') As total  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           FROM   cds_statdetail  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           WHERE            idtype = 'P' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           GROUP BY meta_id) As Amount >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHERE              #TotalTwin.playerid = amount.meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO UPDATE                         #TotalTwin                     --Points Mgr Adjusted >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET                    PntsMGRAdjusted = amount.total >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM                (SELECT meta_id, IsNull(SUM(ptsmgradjusted), '0') As total  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           FROM   cds_statbalance  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           WHERE            idtype = 'P' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           GROUP BY meta_id) As Amount >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHERE              #TotalTwin.playerid = amount.meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO UPDATE                         #TotalTwin                     --Points Promo Adjusted >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET                    PntsPromo = amount.total >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM                (SELECT meta_id, IsNull(SUM(promoptsearned), '0') As total  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           FROM   cds_statday  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           WHERE            idtype = 'P' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           GROUP BY meta_id) As Amount >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHERE              #TotalTwin.playerid = amount.meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO UPDATE                         #TotalTwin                     --Points Redeemed >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET                    PntsRedeemed = amount.total >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM                (SELECT meta_id, IsNull(SUM(ptsredeemed), '0') As total  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           FROM   cds_statbalance  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           WHERE            idtype = 'P' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO                           GROUP BY meta_id) As Amount >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHERE              #TotalTwin.playerid = amount.meta_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     @CASINO as Casino,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   b.player_id, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(char(10), max(b.entrydate), 101) as EnrollDate, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(char(10), max(h.starttime), 101) as LastPlayDate, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(char(10), b.birthday, 101) as Birthday, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   FLOOR(DATEDIFF(DAY, b.birthday, @VENDDATE) / 365.25) as Age, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(b.firstname, ',', '##'))) as VARCHAR(25)) + ' ' + >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   CAST(LTRIM(RTRIM(replace(b.lastname, ',', '##'))) as VARCHAR(25)), 'None') fullname, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(e.address1a, ',', '##'))) as VARCHAR(50)), 'None') address1a, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   CAST(LTRIM(RTRIM(replace(e.address1b, ',', '##'))) as VARCHAR(10)) address1b, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(e.city1, ',', '##'))) as VARCHAR(40)), 'None') city1, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(e.state1, ',', '##'))) as VARCHAR(5)), 'None') state1,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   CAST(LTRIM(RTRIM(replace(e.zip1, ',', '##'))) as VARCHAR(10)) zip1,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(b.email, ',', '##'))) as VARCHAR(50)), 'No Email') email, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   IsNull(CAST(LTRIM(RTRIM(replace(e.homephone1, ',', '##'))) as VARCHAR(16)), 'No Phone') homephone1, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   CAST(LTRIM(RTRIM(replace(b.sourcecode, ',', '##'))) as VARCHAR(10)) sourcecode, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   CAST(LTRIM(RTRIM(replace(b.referral, ',', '##'))) as VARCHAR(6)) referral, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   c.TotalTrips, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(int,f.TotalTWin) as TotalTWin,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(int,f.TotalTWin)/c.totaltrips AvgTwin, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(int,f.totalactual) as TotalActual,  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   convert(int,f.totalactual)/c.totaltrips AvgActual, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.PntsEarned, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.PntsMgrAdjusted, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.PntsPromo, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.PntsRedeemed, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   0 as PntsAvailable >> %TEMP%\%ship%_%finrptname%.SQL
ECHO INTO       #Report >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM    #playertrips c (nolock)  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO left join #TotalTwin f (nolock) on c.playerid = f.playerid  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO left join cds_player b (nolock) on f.playerid = b.player_id  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO left join cds_account e (nolock) on b.account_id = e.account_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO left join cds_statsummary g (nolock) on c.playerid = g.meta_id and g.stattype in ('PIT','SLOT') >> %TEMP%\%ship%_%finrptname%.SQL
ECHO left join cds_statdetail h (nolock) on c.playerid = h.meta_id and h.idtype = 'P' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO where e.mailflag ^<^> 'N' and e.badaddress1 ^<^> 'Y' and h.starttime ^> @vstartdate >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO group by b.birthday,b.lastname,b.firstname,b.player_id,e.address1a, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   e.address1b, e.city1,e.state1,e.zip1,e.homephone1, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   b.email, b.sourcecode,b.referral,f.TotalTWin, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.totalactual, c.totaltrips, f.PntsEarned, f.PntsMgrAdjusted, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   f.PntsPromo,f.PntsRedeemed >> %TEMP%\%ship%_%finrptname%.SQL
ECHO ORDER BY b.player_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * INTO #Rem_TotalTwin FROM #Report where TotalTwin ^< @tstart or totalactual ^< @astart >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where TotalTwin ^< @tstart or totalactual ^< @astart >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * INTO #Rem_MaxAge FROM #Report where Age ^> @MaxAge >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where Age ^> @MaxAge >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * INTO #REM_enrolldate FROM #Report where enrolldate ^< @vmonth >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where enrolldate ^< @vmonth >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO /* -- TO VIEW DELETED ITEMS FROM #REPORT -- Run one at a time. >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * FROM #REM_lastplaydate (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * FROM #Rem_totaltwin (nolock)  -- Most popular failure to qualify >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * FROM #Rem_Pnts (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * FROM #Rem_MaxAge (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO */ >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO UPDATE             #Report                        --Points Available >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        PntsAvailable = amount.total >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM    (SELECT playerid, IsNull(SUM((pntsearned-pntsredeemed)+(pntsmgradjusted+pntspromo)), '0') As total  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               FROM   #TotalTwin  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               GROUP BY playerid) As Amount >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHERE #Report.player_id = amount.playerid >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT * INTO #REM_Pnts FROM #Report where PntsAvailable ^< @tpoints >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where PntsAvailable ^< @tpoints >> %TEMP%\%ship%_%finrptname%.SQL
ECHO. >> %TEMP%\%ship%_%finrptname%.SQL
ECHO /* CLEAN UP REPORT */ >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where fullname like '%Booklet Coupon%' >> %TEMP%\%ship%_%finrptname%.SQL
:: WORK HERE
ECHO --DELETE FROM #Report where NOT state1 IN ('PA', 'NY', 'NJ', 'CT', 'DE', 'MD', 'DC', 'MA', 'VT', 'NH', 'ME', 'RI') >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where sourcecode IN ('86') >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where sourcecode LIKE 'COP%' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where sourcecode LIKE 'DO NOT%' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where sourcecode LIKE 'HET%' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DELETE FROM #Report where sourcecode LIKE 'watch%' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO DECLARE          @CNTEMAIL                 float, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @PcntEmails                float, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @TTLPLAYERS                        float, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @AVGAGE                               int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @AvgTrips                    int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @AvgTwin                    int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @AvgActual                  int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @AvgPntsEarned          int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @RemTwin                    int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @RemMaxAge              int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @RemPnts                   int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @RemAge                                int, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO   @RemSUMonths                       int >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @AVGAGE       =(SELECT SUM(AGE)/count(Age) FROM #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @AvgTrips        =(SELECT sum(TotalTrips)/count(totaltrips) from #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @AvgTwin        =(SELECT sum(TotalTWin)/count(totaltrips) from #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @AvgActual      =(SELECT sum(TotalActual)/count(totaltrips) from #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @AvgPntsEarned          =(SELECT sum(PntsEarned)/count(totaltrips) from #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @TTLPLAYERS            =(SELECT count(player_id) from #Report) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @CNTEMAIL     =(SELECT count(email) from #report where email ^<^>'') >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @PcntEmails    =(SELECT Round(@CNTEMAIL/@TTLPLAYERS*100, 2)) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @RemTwin        =(SELECT IsNull(COUNT(player_id), 0) from #Rem_TotalTwin) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @RemPnts       =(SELECT IsNull(COUNT(player_id), 0) from #REM_Pnts) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @RemAge                    =(SELECT IsNull(COUNT(player_id), 0) from #REM_MaxAge) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET        @RemSUMonths           =(SELECT IsNull(COUNT(player_id), 0) from #REM_enrolldate) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO /*            GENERATE THE ACTUAL REPORT HERE */ >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHILE   @Verbosity = 0 >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO select     * from #Report (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO order by player_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET @Verbosity = '99' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL
ECHO.  >> %TEMP%\%ship%_%finrptname%.SQL
ECHO WHILE   @Verbosity = 1 >> %TEMP%\%ship%_%finrptname%.SQL
ECHO BEGIN >> %TEMP%\%ship%_%finrptname%.SQL
ECHO --Display Avg Stats >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SELECT             *, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@TTLPLAYERS as INT) TotalPlayers, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@AvgAge as INT) AvgAge, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@CNTEMAIL as INT) NumberEmails, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CONVERT(VARCHAR(20),@PcntEmails)+'%' PercEmails, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@AvgTwin as INT) AvgTwin, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@AvgActual as INT) AvgActual, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@AvgActual-@AvgTwin as INT) Actual2TwinDiff, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               CAST(@AvgPntsEarned as INT) AvgPntsEarned, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               @RemTwin NoRemTWin, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               @RemPnts RemPoints, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               @RemAge RemAge, >> %TEMP%\%ship%_%finrptname%.SQL
ECHO               @RemSUMonths RemSUMonths >> %TEMP%\%ship%_%finrptname%.SQL
ECHO FROM    #Report (nolock) >> %TEMP%\%ship%_%finrptname%.SQL
ECHO order by player_id >> %TEMP%\%ship%_%finrptname%.SQL
ECHO SET @Verbosity = '99' >> %TEMP%\%ship%_%finrptname%.SQL
ECHO END >> %TEMP%\%ship%_%finrptname%.SQL

Open in new window

0
Comment
Question by:smyers051972
  • 4
  • 2
6 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 24186861
Your like expressions contain %. % is for env var substitution. You will have to escape each % in like with an ^ (^%), or double it (%%).
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24186963
Will try it out and post back momentarily. THANK YOU! Got a bandage for my forhead??? :)
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24186990
Ok that was dumb on my part!!! LOL I thought the % wouldnt be a special character because you can do ECHO % in dos, not a problem...

Thanks a lot for your help...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31572343
Yep!
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 24186997
The same will apply to brackets - you will have to escape them with a caret:   ^(  ...  ^)
as they are interpreted as "subshell" command by cmd.exe
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24190532
Thank you!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question