im developing a report in SQL Reporting Services 2000 using VS 2003.
i tested my query in SQL Query Analyzer and i can get the result within 2-3 seconds. But when i run the query in VS 2003, the VS 2003 becomes Not Responding.

below is the query and i set my query into 3 variable, @MYSQL, @MYSQL2 and @MYSQL3. and i found out that only @MYSQL will cause the VS 2003 Not Responding. Please help me to check what is my problem, if my query has error, why i can get the result so fast in SQL Query Analyzer.

Query:

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)