|
Subject:
|
Visual Studio 2003 Not Responding
|
|
Posted By:
|
pcliu
|
Post Date:
|
8/30/2006 3:12:08 AM
|
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)
|
|
