Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > Reporting Services
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Reporting Services SQL Server Reporting Services. Please specify which version.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Reporting Services section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 30th, 2006, 03:12 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Visual Studio 2003 Not Responding

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)


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Installing IIS for Visual Studio 2003 createrk General .NET 3 April 25th, 2008 12:03 PM
asking information about Visual Studio 2003 books hari_purwanto_ui ASP.NET 2.0 Basics 3 October 1st, 2007 08:54 AM
Visual Studio 2003 vs. Visual Studio 2005 eitanbarazani C# 2005 4 May 9th, 2006 01:34 AM
Visual Studio 2005 AND vs 2003 MAB VS.NET 2002/2003 1 December 27th, 2005 05:40 PM
Visual Studio 2002 Vs. 2003 psmothers BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 September 13th, 2004 09:52 PM



All times are GMT -4. The time now is 09:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.