Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  Reporting Services Development  VS 2003 Not Responding when executing the query

Author  Topic  pcliu Starting Member 1 Post Posted - 2006-08-30 : 04:27:55 im developing a report for reporting services 2000. My query is working if i test it in SQL Query Analyzer and im able to get the reulst within 2-3 seconds. but when i execute the same query in VS 2003, it becomes Not Responding. for my query, i assign them into 3 variables: @MYSQL @MYSQL2 @MYSQL3 and i tested the query 1 by 1, i found out that the @MYSQL causes the VS 2003 Not Responding. i really do not know what is going wrong that i can get the result so fast in SQL Query Analyzer but i get Not Responding when run the same query in VS 2003. below is my code for your reference, please help me to find out the problem, thanks alot: DECLARE @SQL char(1000) DECLARE @FCWeekNo int DECLARE @LCWeekNo int DECLARE @i int DECLARE @STARTDATE char(10) DECLARE @ENDDATE char(10) DECLARE @WEEKNO INT DECLARE @WEEKMONTH INT DECLARE @STARTDATEMONTH char(10) DECLARE @DAYNO INT DECLARE @COUNTER INT DECLARE @LSELECTEDDAY CHAR(10) DECLARE @LSTARTDATE CHAR(10) DECLARE @LENDDATE CHAR(10) DECLARE @LWEEKNO INT DECLARE @LWEEKMONTH INT DECLARE @LSTARTDATEMONTH char(10) DECLARE @Year char(4) DECLARE @LYear char(4) DECLARE @SELECTEDDAY CHAR(10) DECLARE @STARTWEEK int DECLARE @LSTARTWEEK int set @SELECTEDDAY = '07.01.2006' SET DATEFORMAT DMY; SELECT @STARTDATE = CONVERT(CHAR(10), DATE_BEG, 104), @ENDDATE = CONVERT(CHAR(10), DATE_END, 104), @WEEKNO = WEEK, @WEEKMONTH = WEEK_MONTH, @Year= WEEK_YEAR FROM WEEK_CY WHERE DATE_BEG <= @SELECTEDDAY AND DATE_END >= @SELECTEDDAY SELECT TOP 1 @STARTDATEMONTH = CONVERT(CHAR(10), DATE_BEG, 104), @STARTWEEK =WEEK FROM Week_CY WHERE (WEEK_MONTH = @WEEKMONTH) AND (WEEK_YEAR = YEAR(@SELECTEDDAY)) ORDER BY WEEK SET DATEFORMAT DMY; SELECT @DAYNO = DATEDIFF(d, CONVERT(CHAR(10), DATE_BEG, 104), @SELECTEDDAY) FROM WEEK_CY WHERE DATE_BEG <= @SELECTEDDAY AND DATE_END >= @SELECTEDDAY SET DATEFORMAT DMY; SELECT @COUNTER = SAL_COUNTER FROM WEEK_REF WHERE SAL_YEAR = YEAR(@SELECTEDDAY) SET @LWEEKNO = @WEEKNO + @COUNTER SET DATEFORMAT DMY; SELECT @LSTARTDATE = CONVERT(CHAR(10), DATE_BEG, 104), @LENDDATE = CONVERT(CHAR(10), DATE_END, 104), @LWEEKMONTH = WEEK_MONTH, @LYear = WEEK_YEAR FROM WEEK_CY WHERE WEEK = @LWEEKNO AND WEEK_YEAR = YEAR(@SELECTEDDAY) - 1 SELECT TOP 1 @LSTARTDATEMONTH = CONVERT(CHAR(10), DATE_BEG, 104), @LSTARTWEEK =WEEK FROM Week_CY WHERE (WEEK_MONTH = @LWEEKMONTH) AND (WEEK_YEAR = YEAR(@SELECTEDDAY) - 1) ORDER BY WEEK SET DATEFORMAT DMY; SELECT @LSELECTEDDAY = CONVERT(char(10), DATEADD(d, @DAYNO, CONVERT(CHAR(10), DATE_BEG, 104)), 104) FROM WEEK_CY WHERE DATE_BEG = @LSTARTDATE AND DATE_END = @LENDDATE set @FCWeekNo = @STARTWEEK set @LCWeekNo = @WEEKNO DECLARE @mycounter int DECLARE @tempName char(8) DECLARE @tblName varchar(1000) DECLARE @tblName2 varchar(1000) set @tblName = '' set @mycounter= @FCWeekNo while @mycounter>= @FCWeekNo and @mycounter <=@LCWeekNo begin IF @mycounter < 10 SET @tempName = '0' + cast(@mycounter as char(1)) + @Year ELSE SET @tempName = cast(@mycounter as char(2)) + @Year SET @tempName = 'SA' + @tempName set @tblName ='select div, prdt_dept, sum(net_sale) AS CNetSales, 0 AS LNetSales, sum(newprofit) AS CNewProfit, 0 AS LNewProfit, sale_dte from ' + @tempName + ' WHERE DIV= ''A'' AND SALE_DTE <=''' + @SELECTEDDAY + ''' group by div, prdt_dept, sale_dte UNION ALL ' + @tblName set @mycounter = @mycounter + 1 end set @tblName = rtrim(@tblName) set @tblName = left(@tblName,len(@tblName)-10) set @FCWeekNo = @LSTARTWEEK set @LCWeekNo = @LWEEKNO set @mycounter= @FCWeekNo set @tempName = '' set @tblName2 = '' while @mycounter>= @FCWeekNo and @mycounter <=@LCWeekNo begin IF @mycounter < 10 SET @tempName = '0' + cast(@mycounter as char(1)) + @LYear ELSE SET @tempName = cast(@mycounter as char(2)) + @LYear SET @tempName = 'SA' + @tempName set @tblName2 ='select div, prdt_dept, 0 AS CNetSales, sum(net_sale) AS LNetSales, 0 AS CNewProfit, sum(newprofit) AS LNewProfit, sale_dte from ' + @tempName + ' WHERE DIV= ''A'' AND SALE_DTE <=''' + @LSELECTEDDAY + ''' group by div, prdt_dept, sale_dte UNION ALL ' + @tblName2 set @mycounter = @mycounter + 1 end set @tblName2 = rtrim(@tblName2) set @tblName2 = left(@tblName2,len(@tblName2)-10) DECLARE @tempUnionTableSQL varchar(4000) set @tempUnionTableSQL =@tblName + ' UNION ALL ' + @tblName2 + ' order by div, prdt_dept' DECLARE @MYSQL3 varchar(700) set @MYSQL3 = 'DECLARE @tempUnionTable table(div char(1), prdt_dept char(2), CNetSales money, LNetSales money, CNewProfit money, LNewProfit money, saledate datetime) set dateformat dmy; insert into @tempUnionTable ' + @tempUnionTableSQL DECLARE @MYSQL varchar(3000) set @MYSQL ='DECLARE @temp TABLE(DivCode char(1), DivName char(40), DeptNo int, DeptName char(40), CDNetSales money, LDNetSales money, CWNetSales money, LWNetSale money, CMNetSales money, LMNetSales money, CDNewProfit money, LDNewProfit money, CWNewProfit money, LWNewProfit money, CMNewProfit money, LMNewProfit money) set dateformat dmy; INSERT INTO @temp SELECT DivCode, DivName, DeptNo, DeptName, SUM(CDNetSales) AS CDNetSales, SUM(LDNetSales)AS LDNetSales, SUM(CWNetSales) AS CWNetSales, SUM(LWNetSales) AS LWNetSales, SUM(CMNetSales) AS CMNetSales, SUM(LMNetSales) AS LMNetSales, SUM(CDNewProfit) AS CDNewProfit, SUM(LDNewProfit) AS LDNewProfit, SUM(CWNewProfit) AS CWNewProfit, SUM(LWNewProfit) AS LWNewProfit, SUM(CMNewProfit) AS CMNewProfit, SUM(LMNewProfit) AS LMNewProfit FROM (SELECT DIVMASTR.DIVISION AS DivCode, DIVMASTR.DIV_DESC AS DivName, DPTMASTR.DEPT AS DeptNo, DPTMASTR.DPT_DESC AS DeptName, ''CDNetSales'' = CASE when A.SALE_DTE =''' + @SELECTEDDAY + ''' then A.NET_SALE ELSE 0 END, 0 AS LDNetSales, ''CWNetSales'' = CASE when A.SALE_DTE <=''' + @SELECTEDDAY + ''' then A.NET_SALE ELSE 0 END, 0 AS LWNetSales, 0 as CMNetSales, 0 as LMNetSales, ''CDNewProfit'' = CASE when A.SALE_DTE =''' + @SELECTEDDAY + ''' then A.NEWPROFIT ELSE 0 END, 0 AS LDNewProfit, ''CWNewProfit'' = CASE when A.SALE_DTE <=''' + @SELECTEDDAY + ''' then A.NEWPROFIT ELSE 0 END, 0 AS LWNewProfit, 0 as CMNewPRofit, 0 as LMNewPRofit FROM STRMASTR INNER JOIN SA012006 AS A ON STRMASTR.STR_NBR = A.STR_NBR INNER JOIN DIVMASTR ON DIVMASTR.DIVISION = A.DIV INNER JOIN DPTMASTR ON DIVMASTR.DIVISION = DPTMASTR.DIVISION and A.DIV = DIVMASTR.DIVISION AND A.PRDT_DEPT = DPTMASTR.DEPT WHERE (A.DIV = ''A'') AND (A.NET_SALE > 0) UNION ALL SELECT DIVMASTR.DIVISION AS DivCode, DIVMASTR.DIV_DESC AS DivName, DPTMASTR.DEPT AS DeptNo, DPTMASTR.DPT_DESC AS DeptName , 0 AS CDNetSales, ''LDNetSales'' = CASE when B.SALE_DTE =''' + @LSELECTEDDAY + ''' then B.NET_SALE ELSE 0 END, 0 As CWNetSales, ''LWNetSales'' = CASE when B.SALE_DTE <=''' + @LSELECTEDDAY + ''' then B.NET_SALE ELSE 0 END, 0 as CMNetSales, 0 as LMNetSales, 0 AS CDNewProfit, ''LDNewProfit'' = CASE when B.SALE_DTE =''' + @LSELECTEDDAY + ''' then B.NEWPROFIT ELSE 0 END, 0 AS CWNewProfit, ''LWNewProfit'' = CASE when B.SALE_DTE <=''' + @LSELECTEDDAY + ''' then B.NEWPROFIT ELSE 0 END, 0 as CMNewPRofit, 0 as LMNewPRofit FROM STRMASTR INNER JOIN SA022005 AS B ON STRMASTR.STR_NBR = B.STR_NBR INNER JOIN DIVMASTR ON DIVMASTR.DIVISION = B.DIV INNER JOIN DPTMASTR ON DIVMASTR.DIVISION = DPTMASTR.DIVISION and B.DIV = DIVMASTR.DIVISION AND B.PRDT_DEPT = DPTMASTR.DEPT WHERE (B.DIV = ''A'') AND (B.NET_SALE > 0) ) AS XXX GROUP BY DivCode, DivName, DeptNo, DeptName order by DeptNo' DECLARE @MYSQL2 varchar (1400) set @MYSQL2 = ' DECLARE @tempCNetSales money DECLARE @tempLNetSales money DECLARE @tempCNewPorfit money DECLARE @tempLNewProfit money DECLARE @s varchar(50) DECLARE fil SCROLL CURSOR FOR SELECT DeptNo FROM @temp OPEN fil FETCH NEXT FROM fil INTO @s -- Need to do FETCH before the loop - because value of @@fetch_status can be still set from previous time WHILE @@fetch_status = 0 BEGIN set @tempCNetSales = 0 set @tempLNetSales = 0 set @tempCNewPorfit = 0 set @tempLNewProfit = 0 set @tempCNetSales = (select sum(CNetSales) from @tempUnionTable where saledate <= ''' + @SELECTEDDAY + ''' AND prdt_dept = @s) set @tempLNetSales = (select sum(LNetSales) from @tempUnionTable where saledate <= ''' + @LSELECTEDDAY + ''' AND prdt_dept = @s) set @tempCNewPorfit = (select sum(CNewProfit) from @tempUnionTable where saledate <= ''' + @SELECTEDDAY + ''' AND prdt_dept = @s) set @tempLNewProfit = (select sum(LNewProfit) from @tempUnionTable where saledate <= ''' + @LSELECTEDDAY + ''' AND prdt_dept = @s) set dateformat dmy; update @temp set CMNetSales = @tempCNetSales , CMNewProfit = @tempCNewPorfit, LMNetSales = @tempLNetSales, LMNewProfit = @tempLNewProfit where DeptNo = @s FETCH NEXT FROM fil INTO @s END CLOSE fil DEALLOCATE fil select * from @temp ' exec (@MYSQL3 + ' ' + @MYSQL + ' ' + @MYSQL2)