Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
Do it the sloppy way without using recursion.
Do you have the code?
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
Yours sincerely
Thomas Kejser
M.Sc, MCDBADo it the sloppy way without using recursion.
Do you have the code?
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
--
Yours sincerely
Thomas Kejser
M.Sc, MCDBADo it the sloppy way without using recursion.
Do you have the code?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Matrix Reprt Drill Through

Hi:
I wonder is there anyway I can show all items in a grouped level in a
cross-tab report with one click of a button (or an action)? The report is
query based.
For example, I have 10 stores and I want the user to be able to click a
botton and see all the customers in the 10 stores instead of clicking one by
one.
--
AraleHelo Arale,
What is a botton? Is that Chinese for button? Plewse spell better.
"Arale" wrote:
> Hi:
> I wonder is there anyway I can show all items in a grouped level in a
> cross-tab report with one click of a button (or an action)? The report is
> query based.
> For example, I have 10 stores and I want the user to be able to click a
> botton and see all the customers in the 10 stores instead of clicking one by
> one.
> --
> Arale

Matrix report 4th row group subtotal row color

I'm dealing w/ SSRS 2005.

I have my main matrix report which has five row groups.

What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time...so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).

At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.

Thanks!

got it...click on the little green triangle in the upper right corner, and set the background property.

Matrices & Totals

I have a matrix with two levels of row grouping and one level of column
grouping. For each cell I need to show the percentage of the group subtotal.
Here's an example.
CG1 CG2 CG3
# % # % # %
RG1 RG11 50 50% 20 10% 400 20%
RG12 20 20% 80 40% 300 15%
RG13 30 30% 100 50% 1300 65%
Total 100 33% 200 40% 2000 12%
RG2 RG21 10 5% 50 17% 1000 7%
RG22 30 15% 60 20% 2000 13%
RG23 40 20% 70 23% 3000 20%
RG24 60 30% 80 27% 4000 27%
RG25 60 30% 40 13% 5000 33%
Total 200 67% 300 60% 15000 88%
Grand Total 300 100% 500 100% 17000 100%
I've stuck with formula to calculate %.
Please, help!
--
Thanks,
IDhi,
Its hard to get the total from the report .. I had the same problem. What I
did, I took the total of the each rowm from the Query itself and in the
report screen, in the % cell, I did
Fields!RG!.Value/Fields!Total.Value and put its format P2.
it was working fine with these expression.
try this...
Bismi
"exkievan" wrote:
> I have a matrix with two levels of row grouping and one level of column
> grouping. For each cell I need to show the percentage of the group subtotal.
> Here's an example.
>
> CG1 CG2 CG3
> # % # % # %
> RG1 RG11 50 50% 20 10% 400 20%
> RG12 20 20% 80 40% 300 15%
> RG13 30 30% 100 50% 1300 65%
> Total 100 33% 200 40% 2000 12%
> RG2 RG21 10 5% 50 17% 1000 7%
> RG22 30 15% 60 20% 2000 13%
> RG23 40 20% 70 23% 3000 20%
> RG24 60 30% 80 27% 4000 27%
> RG25 60 30% 40 13% 5000 33%
> Total 200 67% 300 60% 15000 88%
> Grand Total 300 100% 500 100% 17000 100%
> I've stuck with formula to calculate %.
> Please, help!
> --
> Thanks,
> ID|||Thanks, Bismi. I did it the same way. I thought there's a way to do it on the
report.
--
Thanks,
ID
"Bismi" wrote:
> hi,
> Its hard to get the total from the report .. I had the same problem. What I
> did, I took the total of the each rowm from the Query itself and in the
> report screen, in the % cell, I did
> Fields!RG!.Value/Fields!Total.Value and put its format P2.
> it was working fine with these expression.
> try this...
> Bismi
> "exkievan" wrote:
> > I have a matrix with two levels of row grouping and one level of column
> > grouping. For each cell I need to show the percentage of the group subtotal.
> >
> > Here's an example.
> >
> >
> > CG1 CG2 CG3
> > # % # % # %
> > RG1 RG11 50 50% 20 10% 400 20%
> > RG12 20 20% 80 40% 300 15%
> > RG13 30 30% 100 50% 1300 65%
> > Total 100 33% 200 40% 2000 12%
> > RG2 RG21 10 5% 50 17% 1000 7%
> > RG22 30 15% 60 20% 2000 13%
> > RG23 40 20% 70 23% 3000 20%
> > RG24 60 30% 80 27% 4000 27%
> > RG25 60 30% 40 13% 5000 33%
> > Total 200 67% 300 60% 15000 88%
> > Grand Total 300 100% 500 100% 17000 100%
> >
> > I've stuck with formula to calculate %.
> >
> > Please, help!
> > --
> > Thanks,
> >
> > ID

math error

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

Try

select convert(decimal(20,2),1.2334e+006)

Madhivanan|||Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.
Thanks.

Madhivanan wrote:

Quote:

Originally Posted by

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

>
Try
>
select convert(decimal(20,2),1.2334e+006)
>
Madhivanan

|||(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.

Unfortunately, there are not really any good options. The best is probably
to run a SELECT query, and takes some hint from where it terminates. This
example illustrates:

CREATE TABLE #tmp1 (a varchar(23) NOT NULL, b int IDENTITY)
go
INSERT #tmp1(a) VALUES ('1234')
INSERT #tmp1(a) VALUES ('1232')
INSERT #tmp1(a) VALUES ('2344')
INSERT #tmp1(a) VALUES ('34.34')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('1234e+006')
INSERT #tmp1(a) VALUES ('777')
go
SELECT convert(decimal(20, 2), a) FROM #tmp1 ORDER BY b
go
DROP TABLE #tmp1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Materialized View Error 8908

Microsoft SQL Server 2005 - 9.00.1187.07

dbcc checkdb is failing with an interesting message:

Msg 8908, Level 16, State 1, Line 1

Indexed view 'BritishEnglishMV' (object ID 226099846) does not contain all rows that the view definition produces. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.

The data materialized in the indexed view is exactly the same as the data in the underlying tables...
Books online has no info on this error.
Rebuilding the index fixes the problem.

This warning is produced if the indexed view does not 100% match the "newly generated" indexed view. This may happen in cases when there are updates performed on the underlying tables.
I will use an example to explain. If a view contains for examle an aggregation SUM, then inserting of new value to underlying table will add a new value to this sum. If the SUM was produced originally from a sequence of numbers, say a1, a2, ..., an, and the new inserted value is bb, then updating the indexed view means
(a1+a2+a3+...+an) + bb while recalculating the indexed view may prform the sum in different order.
We are still working on providing more information about warnings and errors we generate. This should improve substantially by the time we ship the final release of SQL Server 2005.

Lubor Kollar