asked
Looping to Send Data Driven Subscription
I am trying to loop through customers to send their reports. The temp table loads in about 14 seconds but the process to convert each customer's information to an email string takes forever. is there a better way to do this? DECLARE @TO VARCHAR(2000) DECLARE @SPLR_ID DECIMAL(5,0) CREATE TABLE #TMP (SPLR_ID DECIMAL(5,0), CNTCT_EMAIL_ADDR VARCHAR(100)) BEGIN --ONLY LOADS DATA WITH REPORTS AND RECIPIENTS TO BE SENT INSERT INTO #TMP SELECT splr.SPLR, c.CNTCT_EMAIL_ADDR FROM dbo.T_SPLR_ACCT splr JOIN GX_RPT.dbo.T_SPLR_DSTRB_LIST dl ON splr.SPLR = dl.SPLR JOIN dbo.T_RPT_DSTRB rd ON dl.DISTRIB_LIST_ID = rd.RPT_ID JOIN GX_RPT.dbo.TEMP_CONTACT c ON rd.CNTCT_ID = c.CNTCT_ID WHERE splr.ACCT_NUM IN ( --Suppliers that need report sent SELECT SPLR_ACCT FROM REFERENCE.dbo.T_ITEM WHERE ITEM_NUM IN ( --Items with price change of interest SELECT ITEM_NUM FROM dbo.TBL_DAILY_CHG WHERE PROC_DT = dbo.UDF_LAST_BUS_DAY(GETDATE()) AND ( RMV_IND = 1 OR ADD_IND = 1 OR RMV_IND = 1 OR ADD_IND = 1) AND PROC_CD = 'C')) WHILE EXISTS (SELECT * FROM #TMP) SET @SPLR = (SELECT MIN(SPLR) FROM #TMP) BEGIN SET @TO ='' SELECT @TO = CNTCT_EMAIL_ADDR + ';' FROM #TMP WHERE SPLR = @SPLR EXEC ReportServer.dbo.USP_DATA_DRIV_SUB @ID = 199 ,@emailTO = @TO ,@param1 = @SPLR END DELETE FROM #TMP WHERE SPLR = @SPLR END DROP TABLE #TMP
Let me try to articulate better - I have some values to use for sampling. in the commented select statement you can see that the string is supposed to build grouped by cust is one long email address string (separated by ';') CREATE TABLE #EM_TEMP (CUST_ID VARCHAR(10), EMAIL_ADDR VARCHAR(100)) insert into #EM_TEMP VALUES('12345',
'Jane@Company12345.com') insert into #EM_TEMP VALUES('12345',
'Joe@Company12345.com') insert into #EM_TEMP VALUES('12345',
'Jim@Company12345.com') insert into #EM_TEMP VALUES('9876',
'Sally@Company9876.com') insert into #EM_TEMP VALUES('9876',
'Sue@Company9876.com') insert into #EM_TEMP VALUES('9876',
'Steve@Company9876.com') insert into #EM_TEMP VALUES('80210',
'Barbara@Company80210.com') insert into #EM_TEMP VALUES('80210',
'Barry@Company80210.com') insert into #EM_TEMP VALUES('80210',
'Bo@Company80210.com') --drop table #EM_TEMP DECLARE @TO VARCHAR(2000) DECLARE @CUST_ID DECIMAL(5,0) WHILE EXISTS (SELECT * FROM #EM_TEMP) SET @CUST_ID = (SELECT MIN(CUST_ID) FROM #EM_TEMP) BEGIN SET @TO ='' SELECT @TO + LTRIM(RTRIM(C.EMAIL_ADDR)) + '; ' FROM (SELECT C.EMAIL_ADDR, c.CUST_ID FROM #EM_TEMP RD INNER JOIN #EM_TEMP C ON RD.CUST_ID = C.CUST_ID) AS C INNER JOIN #EM_TEMP AS dl ON dl.CUST_ID = C.CUST_ID WHERE C.CUST_ID = @CUST_ID END --Select @TO,@CUST_ID from #EM_TEMP WHERE CUST_ID = @CUST_ID -- USE FOR TESTING EXEC ReportServer.dbo.USP_DATA_DRIVEN_SUBSCR @ID = 187 ,@emailTO = @TO ,@param1 = @SPLR_ID DELETE FROM @TMP WHERE SPLR_ID = @SPLR_ID DROP TABLE #EM_TEMP if you comment out the while - the result set builds very quickly.. just not a value that can be used for an email recipient string to be passed in as the paramater.
Ah.. now I noticed your problem.. You have a never ending loop in your statements. ... WHILE EXISTS (SELECT * FROM #TMP) SET @SPLR = (SELECT MIN(SPLR) FROM #TMP) BEGIN ... As you can take a look.. the `SET` command will be executed in the loop only. You have to put the SET command after the `BEGIN` keyword. Also you have to put the DELETE FROM #TMP WHERE SPLR = @SPLR42 inside the `BEGIN... END` of the `WHILE` cycle.
Yes, move the processing out to SSIS or a programming language. This is not an area where TSQL shines.
If you say, that the list loads in 14 secs, then depends what you are doing in the `ReportServer.dbo.USP_DATA_DRIV_SUB` procedure you are executing. You didn't provide any info about its content. If you provide details about it, maybe we will be able to help you.
First, I want to very strongly agree with Grant. This is not an area where SQL shines at all and I would do this in another language like SSIS or Python or C#. But, if you really want to do this in T-SQL, then instead of looping, you can create one enormous dynamic sql String that contains numerous calls to your ReportServer.dbo.USP_DATA_DRIVEN_SUBSCR with all the values for the paramaters filled in and then just execute that string. This would get somewhat complicated in your particular case since you would need to concatenate the TO section each time in a subquery, but Jeff Moden has some good techniques for that at [
http://www.sqlservercentral.com/articles/Test+Data/61572/][1] [1]:
http://www.sqlservercentral.com/articles/Test+Data/61572/
