Bookmark in Code not working.

I have a report in reporting services that has a bookmark value asigned to each row. In the following Page_Load event for the page, I'm getting the following error on the ReportViewer1.JumpToBookmark("6") statement:

"An exception of type 'System.InvalidOperationException' occurred in Microsoft.ReportViewer.WebForms.dll but was not handled in user code

Additional information: Some parameters or credentials have not been specified"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim strReport1 As String = "/ReportDirectory/CurrentWeekSales"

ReportViewer1.ServerReport.ReportServerUrl = New System.Uri(CStr(Session.Item("ReportServer")))
ReportViewer1.ServerReport.ReportPath = strReport1
ReportViewer1.ServerReport.Timeout = 500000

Do While ReportViewer1.ServerReport.IsDrillthroughReport
ReportViewer1.PerformBack()
Loop

Dim RptParameters1 As ReportParameter() = New ReportParameter(0) {}
RptParameters1(0) = New ReportParameter("EBO_EmpKey", CStr(Session.Item("UserKey")))
ReportViewer1.ServerReport.SetParameters(RptParameters1)

ReportViewer1.ServerReport.Refresh()

ReportViewer1.JumpToBookmark("6")

End If
End Sub

What am I missing?

ThanksI too am having a similar problem. I find that JumpToBookmark() simply does not work no matter what I try. Interestingly, setting the 'action' property of a TextBox within the report to jump to the target bookmark seems to work. Unfortunately, I need to be able to do this in code without having to force the user to click on the report.

Is this a known problem, or is there something else on the viewer and/or report that needs to be set?

Thanks,

Ian.

Book suggestions for studying for MCDBA

Hi All,

I'm about to begin studying for the MCDBA certification and I'm
wondering what books people would recommend?

The following gets a good review, so I'm planning on purchasing it:

MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
System Administration, 70-228, Second Edition

However, I'd like to also go through a second book as well, just to
make sure my coverage is as complete as possible, and I'm wondering if
anyone has any suggestions?

Many thanks in advance,

MurrayHi

There are two things that you need to look at when doing these exams,
knowledge and exam technique. In general books that are for specifically for
an examination tend to be more focussed on exam technique and I would
personally would not get a second book for that! There are also websites
that can take you through sample exams as well as those found in the book.

For general knowledge, apart from hands on day to day use, you should study
books online and all the places etc listed at
http://www.aspfaq.com/show.asp?id=2423

John

"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:5p16705rc1it61v3p9r26rn4aoq0990e9c@.4ax.com...
> Hi All,
> I'm about to begin studying for the MCDBA certification and I'm
> wondering what books people would recommend?
> The following gets a good review, so I'm planning on purchasing it:
> MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
> System Administration, 70-228, Second Edition
> However, I'd like to also go through a second book as well, just to
> make sure my coverage is as complete as possible, and I'm wondering if
> anyone has any suggestions?
> Many thanks in advance,
> Murray|||Hi

There are two things that you need to look at when doing these exams,
knowledge and exam technique. In general books that are for specifically for
an examination tend to be more focussed on exam technique and I would
personally would not get a second book for that! There are also websites
that can take you through sample exams as well as those found in the book.

For general knowledge, apart from hands on day to day use, you should study
books online and all the places etc listed at
http://www.aspfaq.com/show.asp?id=2423

John

"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:5p16705rc1it61v3p9r26rn4aoq0990e9c@.4ax.com...
> Hi All,
> I'm about to begin studying for the MCDBA certification and I'm
> wondering what books people would recommend?
> The following gets a good review, so I'm planning on purchasing it:
> MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
> System Administration, 70-228, Second Edition
> However, I'd like to also go through a second book as well, just to
> make sure my coverage is as complete as possible, and I'm wondering if
> anyone has any suggestions?
> Many thanks in advance,
> Murraysql

BOL BCP example

When I run the following BOL example:
bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
I get the following error message:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
I have tried multiple variations ... still get the same error ...
This cannot be this hard.
Thanks in advance
GI hope you didn't give it in the SSMS. you are supposed to give it in comman
d
prompt|||Please find attached personal examples for that stuff...
C:\>bcp abs.abs_diario in c:\20050929.dat -n -Ssql4 -Uusrdts1 -Pdts1
C:\>bcp ABS_Diario_Dat in Diariomes10.dat -c -U ABS -P -S SQL1
C:\OFI0501>BCP abs.dbo.ABS_OF501 IN 20050726.DAT -e enric.txt -C -c -Ssql4
-Uusrdts1 -Pdts1
hope that helps
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"GH" wrote:

> When I run the following BOL example:
> bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
> I get the following error message:
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near '.'.
> I have tried multiple variations ... still get the same error ...
> This cannot be this hard.
> --
> Thanks in advance
> G|||Geeez ...
Thanks for the wake up call ... Doing SQL past 10PM should be a misdemeanor!
!
--
Thanks in advance
G
"Omnibuzz" wrote:

> I hope you didn't give it in the SSMS. you are supposed to give it in comm
and
> prompt|||Thanks Eric
--
Thanks in advance
GH
"Enric" wrote:
> Please find attached personal examples for that stuff...
> C:\>bcp abs.abs_diario in c:\20050929.dat -n -Ssql4 -Uusrdts1 -Pdts1
> C:\>bcp ABS_Diario_Dat in Diariomes10.dat -c -U ABS -P -S SQL1
> C:\OFI0501>BCP abs.dbo.ABS_OF501 IN 20050726.DAT -e enric.txt -C -c -Ssql
4
> -Uusrdts1 -Pdts1
> hope that helps
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "GH" wrote:
>

blog select with comment count

The following sql works great when the field for my main blog message is type nvarchar but doesn't work for text which I need to convert to.

select
a.id, b.textField
count(b.a_id) as myCount
from a left join b on a.id = b.a_id
group by a.id, b.textField

What other methods could i use to get "myCount" within one sql statement?

Thanks in advance, JeffHere is more information:

using sql server 2000 (from asp.net) the following sql works great when the "textForm" field is of type nvarchar but doesn't work for the text type.

I don't believe nvarchar will work for my needs since it is limited to 8000 (4000 due to double storage). I believe I will need to use the text type.

select bm.message_id,
bm.title,
bm.display_date,
bm.message,
count(bc.comment_id) as commentCount
from blog_messages as bm LEFT JOIN blog_comments as bc
on bm.message_id = bc.message_id
where bm.active_flag = 1
group by bm.message_id, bm.title, bm.display_date, bm.message
order by display_date DESC

The purpose of the select statement is to display a list of blog entries that includes the number of comments on each entry. An example of this in cold fusion is found at
http://www.camdenfamily.com/morpheus/blog/

With my limited knowledge of the asp.net repeater control, I'm not sure how to integrate 2 select statements (1st for blog content, 2nd for comment count). It seems the fastest way to get all of the information I want is to use a stored procedure using either a temp table or a cursor to compile all of the data together. However this method may forcee me to select the count from table b for each record of table a which could be time consuming.

Is there any way to achieve this goal with one select using the union statement or another type of join?

Although this might be a better sql question, I am also wondering about alternative asp.net solutions including repeaters with 2 or more select statements or using an array or list.|||How about something like:

select a.id,
b.textField,
myCount = (select count(*) from b where a_id = a.id)
from a

You might need to include a COALESCE function around the correlated subquery to check for NULL and send back a zero.|||it worked! thanks!

select bm.message_id,
bm.title,
bm.display_date,
bm.message,
commentCount = (select count('x')
from blog_comments as bc
where bc.message_id = bm.message_id)
from blog_messages as bm
where bm.active_flag = 1
order by bm.display_date DESC

Blank value when displaying parameter label

Hi,
I've got the following problem in a report:
I've got a parameter that i'm manually giving available values and labels.
When i'm trying to display this parameter's label into a tabcell
(Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
ReportManager).
Is there a way to fix this issue ?
Best Regards,
Grégory.Greg,
Are you trying to do this in report designer? Have you set a prompt string
for the report parameter on the report parameters dialog?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
news:3B6D9848-7DBE-4B4D-B9EE-3B23B969ABAD@.microsoft.com...
> Hi,
> I've got the following problem in a report:
> I've got a parameter that i'm manually giving available values and labels.
> When i'm trying to display this parameter's label into a tabcell
> (Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
> ReportManager).
> Is there a way to fix this issue ?
> Best Regards,
> Grégory.|||Hi Lukasz,
Yes i'm trying to do this in the report designer. The result is identitical
once the report is deployed. And there is a prompt string for this particular
parameter.
Any idea ?
Thanks in advance.
Grégory.
"Lukasz Pawlowski [MSFT]" wrote:
> Greg,
> Are you trying to do this in report designer? Have you set a prompt string
> for the report parameter on the report parameters dialog?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
> news:3B6D9848-7DBE-4B4D-B9EE-3B23B969ABAD@.microsoft.com...
> > Hi,
> >
> > I've got the following problem in a report:
> >
> > I've got a parameter that i'm manually giving available values and labels.
> > When i'm trying to display this parameter's label into a tabcell
> > (Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
> > ReportManager).
> >
> > Is there a way to fix this issue ?
> >
> > Best Regards,
> > Grégory.
>
>

Blank Pages following report

I have a report that uses 7 subreports. Some reports show, some don't
depending on the data received from the Shared Datasource. My initial report
appears fine in the viewer, however I have a single page following the last
page with data containing only the header. When I create a pdf, I have 4
pages at the end with only headers. The main report and the first subreport
should be the only ones outputting data.
Ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1I have found my problem, but no solution. When the subreports run, they are
given "space" on the report even if their visibility is set to False. This
is what is generating my "blank pages". This poses a new and even uglier
issue for me, What Now? My requirements are to have a single report that
gives alternate data depending on the needs of the user. I have two ideas:
1) Can I manually launch a subreport in code WITHOUT navigating away from
the parent report?
2) Is it possible to dynamically add a Subreport to the main report via VB
Code so only the subreports needed are run?
I am researching both avenues, but any advice you could offer would be WELL
received!
Rick wrote:
>I have a report that uses 7 subreports. Some reports show, some don't
>depending on the data received from the Shared Datasource. My initial report
>appears fine in the viewer, however I have a single page following the last
>page with data containing only the header. When I create a pdf, I have 4
>pages at the end with only headers. The main report and the first subreport
>should be the only ones outputting data.
>Ideas?
Message posted via http://www.sqlmonster.com|||I am also running into the same issue and finding no solution. Any help is
appreciated.
"Rick via SQLMonster.com" wrote:
> I have found my problem, but no solution. When the subreports run, they are
> given "space" on the report even if their visibility is set to False. This
> is what is generating my "blank pages". This poses a new and even uglier
> issue for me, What Now? My requirements are to have a single report that
> gives alternate data depending on the needs of the user. I have two ideas:
> 1) Can I manually launch a subreport in code WITHOUT navigating away from
> the parent report?
> 2) Is it possible to dynamically add a Subreport to the main report via VB
> Code so only the subreports needed are run?
> I am researching both avenues, but any advice you could offer would be WELL
> received!
> Rick wrote:
> >I have a report that uses 7 subreports. Some reports show, some don't
> >depending on the data received from the Shared Datasource. My initial report
> >appears fine in the viewer, however I have a single page following the last
> >page with data containing only the header. When I create a pdf, I have 4
> >pages at the end with only headers. The main report and the first subreport
> >should be the only ones outputting data.
> >
> >Ideas?
>
> --
> Message posted via http://www.sqlmonster.com
>|||Has Microsoft any answer to this problem as this is something has been faced
by many people and doesnt seem to find any solution.
Setting page size and margine is one part of avoiding blank pages but with
sub reports, it not only introduce blank pages and sometimes change the whole
look of the report.
"manish" wrote:
> I am also running into the same issue and finding no solution. Any help is
> appreciated.
> "Rick via SQLMonster.com" wrote:
> > I have found my problem, but no solution. When the subreports run, they are
> > given "space" on the report even if their visibility is set to False. This
> > is what is generating my "blank pages". This poses a new and even uglier
> > issue for me, What Now? My requirements are to have a single report that
> > gives alternate data depending on the needs of the user. I have two ideas:
> >
> > 1) Can I manually launch a subreport in code WITHOUT navigating away from
> > the parent report?
> >
> > 2) Is it possible to dynamically add a Subreport to the main report via VB
> > Code so only the subreports needed are run?
> >
> > I am researching both avenues, but any advice you could offer would be WELL
> > received!
> >
> > Rick wrote:
> > >I have a report that uses 7 subreports. Some reports show, some don't
> > >depending on the data received from the Shared Datasource. My initial report
> > >appears fine in the viewer, however I have a single page following the last
> > >page with data containing only the header. When I create a pdf, I have 4
> > >pages at the end with only headers. The main report and the first subreport
> > >should be the only ones outputting data.
> > >
> > >Ideas?
> >
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||This is a good question, I see this issue often on many boards I post to. In
this case, it was my error. I was setting sub reports to hidden, however the
Rectangle they were in were not being set to hidden. Hence the blank pages.
When I set the Rectangle to invisible, it works like a charm. If you run
into this issue, I suggest you look at the container you placed your Sub
Report in, that just might be your error.
manish wrote:
>Has Microsoft any answer to this problem as this is something has been faced
>by many people and doesnt seem to find any solution.
>Setting page size and margine is one part of avoiding blank pages but with
>sub reports, it not only introduce blank pages and sometimes change the whole
>look of the report.
>> I am also running into the same issue and finding no solution. Any help is
>> appreciated.
>[quoted text clipped - 22 lines]
>> > >
>> > >Ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1|||Also, if you set the subreport (or its container) to be invisible
conditionally, any pagebreaks selected for that subreport or container
will be ignored. For me, it is a really anoying "feature".

Blank field in column

I am using the following query:
DELETE FROM ORDER_HEADER
WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
This works fine but doesn't remove the records where the
INVOICE_NUMBER field is blank.
Is there a way to identify a blank or empty field?
Hi,
Use the below query to delete invoice number with NULLS, 0's and Blanks:-
DELETE FROM ORDER_HEADER
WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
OR (DATALENGTH(LTRIM(RTRIM(INVOICE_NUMBER)))=0)
Thanks
Hari
MCDBA
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:238c201c45ef6$c0af5ba0$a601280a@.phx.gbl...
> I am using the following query:
> DELETE FROM ORDER_HEADER
> WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
> This works fine but doesn't remove the records where the
> INVOICE_NUMBER field is blank.
> Is there a way to identify a blank or empty field?

Biztalk Server 2004 Configuration

Hi

I am implementing Biztalk with SQL server 2005

When the configuration gets to "Now Configuring: Biztalk WMI" it thows the following error: "ActiveX component can't create object."

Please help

Wonder Andile Hlongwane

wonder.hlongwane@.yahoo.com

Wrong forum. Try posting on the news://microsoft.public.biztalk.orchestration.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Biztalk Server 2004 Configuration

Hi

I am implementing Biztalk with SQL server 2005

When the configuration gets to "Now Configuring: Biztalk WMI" it thows the following error: "ActiveX component can't create object."

Please help

Wonder Andile Hlongwane

wonder.hlongwane@.yahoo.com

Wrong forum. Try posting on the news://microsoft.public.biztalk.orchestration.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Bizarre Query

Good afternoon,

I have a bizarre question. When running the following query:

select SomeColumnName from TableA where PK_TableA in
(select PK_TableA from TableB)

I get results. This should not be feasible, because the query within
the in clause:

select PK_TableA from TableB

is not possible - there is no PK_TableA column within the TableB table.
Running the sub-query alone gives an error, but when using it as
sub-query in the first statement, I get every row within TableA.

If it helps any, the exact query I'm running is:

select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
(select directsalesagentid from WebsiteSubscriptionPayment)

Shouldn't a query return an error if the sub-query has an invalid
column name?On 5 Jul 2006 15:30:42 -0700, Xeth Waxman wrote:

Quote:

Originally Posted by

>Good afternoon,
>
>I have a bizarre question. When running the following query:
>
>select SomeColumnName from TableA where PK_TableA in
>(select PK_TableA from TableB)
>
>I get results. This should not be feasible, because the query within
>the in clause:
>
>select PK_TableA from TableB
>
>is not possible - there is no PK_TableA column within the TableB table.
Running the sub-query alone gives an error, but when using it as
>sub-query in the first statement, I get every row within TableA.
>
>If it helps any, the exact query I'm running is:
>
>select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
>(select directsalesagentid from WebsiteSubscriptionPayment)
>
>Shouldn't a query return an error if the sub-query has an invalid
>column name?

Hi Xeth,

There are two types of subqueries: correlated and uncorrelated. The
correlated ones refer to columns in the outer query.

If a column name in a subquery is not qualified with table name (or
table alias), SQL Server will first test if it matches a column from a
table used in the subquery. If it doesn't, it will then go on and check
if it matches a column in one of the tables in the outer table, assuming
you want a correlated subquery if it does.

The query you wriite is equivalent to this one (adding table qualifiers
for clarity):

SELECT TableA.SomeColumnName
FROM TableA
WHERE TableA.PK_TableA IN (SELECT TableA.PK_TableA
FROM TableB)

This will return all rows from TableA if at least one row exists in
TableB, or no rows at all if TableB is empty. (If TableB is not empty,
the subqeury will have one row for each row in TableB, but the only
column will have the value of TableA.PK_TableA in each of those rows).

--
Hugo Kornelis, SQL Server MVP

Bitwise Operators!

The outcome of the following 2 simple queries
---
PRINT 15 & 75
PRINT 15 | 75
---
are 11 & 79 respectively. Can someone explain how does SQL Server
compute these values? I went through the topic 'Bitwise Operators' in
BOL but couldn't exactly comprehend the explanation & that's why I am
posting my query here.
Thanks,
Arpan"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124150426.712216.65110@.g14g2000cwa.googlegroups.com...
> The outcome of the following 2 simple queries
> ---
> PRINT 15 & 75
> PRINT 15 | 75
> ---
> are 11 & 79 respectively. Can someone explain how does SQL Server
> compute these values? I went through the topic 'Bitwise Operators' in
> BOL but couldn't exactly comprehend the explanation & that's why I am
> posting my query here.
> Thanks,
> Arpan
>
Bitwise OR : If either bit value is 1, then the resulting bit is 1, else 0
Bitwise AND : If either bit value is 0, then the resulting bit is 0, else 1
OR:
1001011 = 75
0001111 = 15
--
1001111 = 79
AND:
1001011 = 75
0001111 = 15
--
0001011 = 11|||15 & 75 =
(in binary)
00001111 &
01001011
--
00001011 = 11 (in decimal)
15 | 75 =
(in binary)
00001111 |
01001011
--
01001111 = 79 (in decimal)
SQL Server, from memory, just calculates these kind of binary ANDs and
ORs using twos complement arithmetic.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:

>The outcome of the following 2 simple queries
>---
>PRINT 15 & 75
>PRINT 15 | 75
>---
>are 11 & 79 respectively. Can someone explain how does SQL Server
>compute these values? I went through the topic 'Bitwise Operators' in
>BOL but couldn't exactly comprehend the explanation & that's why I am
>posting my query here.
>Thanks,
>Arpan
>
>|||Thanks, Chris, for your input but to be honest, I couldn't follow how
did you arrive at the values 1001111 & 0001011? What did you do with
the binary values of 75 & 15 to get to 79 and 11 & their respective
binary values? Please explain me this.
Also how does one find the binary value of a decimal number (of course,
other than using the Windows Scientific Calculator)? Is there any
built-in SQL Server function to do so?
Thanks once again & thanks to Mike as well,
Regards,
Arpan|||The binary number system: each bit represents a power of 2.
0 1
1 2
2 4
3 8
4 16
5 32
6 64
7 128
8 256
9 512
...
For fractions, each bit past the binary point:
-1 .5
-2 .25
-3 .125
-4 .0625
-5 .03125
-6 .015625
-7 .0078125
-8 .00390625
-9 .001953125
...
Each binary number is a combination of bits.
75decimal = 1001011
15decimal = 0001111
A bitwise AND performs a binary AND operation using the following table:
A B | A & B
--
0 0 | 0
0 1 | 0
1 0 | 0
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 & 15 = 0001011 = 11 decimal
Notice that for each bit position which is 1 in the result, both bits in the
same position of each operand is one.
A bitwise OR performs a binary OR operation using the following table:
A B | A | B
--
0 0 | 0
0 1 | 1
1 0 | 1
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 | 15 = 1001111 = 79 decimal
Notice that for each bit position which is 1 in the result, at least one bit
from the same position of either operand is one.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>|||"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>
[url]http://www.math.grin.edu/~rebelsky/Courses/152/97F/Readings/student-binary.html[/u
rl]|||1) These are not queries; they are print statements..
2) Good programmers do not do proprietary, non-realtional, low-level
bits and bytes stuff in SQL.
You keep posting requests for kludges. Perhaps you ought to take the
time to learn RDBMS, data modeling, etc. Remember that it takes six
years to become a Union Journey Carpenter in New York State. Do not
expect to learn all of this by posting to Newsgroups -- all you will do
is collect kludges and become a danger to your employers. .|||Binary arithmetic - All right! Now, if someone will start a thread
about zone and digit punches, I will have come full circle.
Actually, this reminds me of my favorite puzzle only a computer g
(like me) could love. I stole it from Issac Asimov. It was in one of
his Black Widowers mysteries.
Given: Halloween = Christmas
Construct a proof.
----
I will shorten it.
Halloween = Christmas
December 25 = October 31
25dec = 31oct
Payson|||>> Given: Halloween = Christmas Construct a proof.
You already gave the hint there with the author. From The Family Man? For
those who are unfamiliar:
It is basically the distinction between base 10 Vs. base 8
December => DEC
October => OCT
Mathematically, DEC(imal) 25 = OCT(al) 31 or more to put it colloquially:
Christmas = Halloween
Anith

BIT-Wise Aggregation

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

Here is code that shows a trick. The idea is to break each "rights mask" into a series of rows representing the separate bit values. These can be recombined with a Sum(distinct) across the roles owned by each account.

To make this work, you need a table that has one row for each bit position. My example shows a subset of bits built using a union. There is a system table called spt_values that holds a bunch of useful values used by system stored procedures. It contains rows for each bit position and would work with this application.

Drop Table #AccountRoles

Drop Table #Account

Drop Table #Role

go

Create Table #Account(

account_id int Not Null Identity( 1000, 100 ),

account_name varchar(100) Not Null

)

Create Table #Role(

role_id int Not Null Identity( 100, 1 ),

role_name varchar(100) Not Null,

rights int Not Null

)

Create Table #AccountRoles(

account_id int Not Null,

role_id int Not Null

)

go

Insert #Account values( 'Fred' )

Insert #Account values( 'Barney' )

Insert #Account values( 'Wilma' )

Insert #Account values( 'Betty' )

go

Select * from #Account

go

Insert #Role values ( 'Only 1', 1 )

Insert #Role values ( 'OneThree', 5 )

Insert #Role values ( 'JustTwo', 2 )

Insert #Role values ( 'All', 7 )

go

Select * from #Role

Insert #AccountRoles values ( 1100, 100 )

Insert #AccountRoles values ( 1100, 102 )

Insert #AccountRoles values ( 1200, 100 )

Insert #AccountRoles values ( 1200, 101 )

Insert #AccountRoles values ( 1300, 101 )

Insert #AccountRoles values ( 1300, 103 )

go

Select acc.account_id,

acc.account_name,

Sum(Distinct right_explode.single_right )

From #Account acc

Left Join #AccountRoles acr

On acr.account_id = acc.account_id

Join (

Select role_id,

bitposition,

rights & bitposition single_right

From #Roles

Cross Join

(

Select 1 as bitposition

Union

Select 2 as bitposition

Union

Select 4 as bitposition

Union

Select 8 as bitposition

Union

Select 16 as bitposition

) as bits

) right_explode

On right_explode.role_id = acr.role_id

Group

By acc.Account_Id,

acc.Account_Name

Order

By acc.Account_Name

Another approach would be to make a function that computed the bit-wise Or for a single account. This could be done using a local variable and a Select statement. This is not as good a solution.

Declare @.bitsum int

Select @.bitsum = bitsum | rights

From #AccountRoles ar

Join #Role r

On r.role_id = ar.role_id

Where ar.account_id = @.account

return @.bitsum|||

It works:

create table Account (Id int, AccountName nvarchar(25));

create table Role (id int, Rights int);

create table AccountRole (AccountID int, RoleID int);

insert into account values (1, 'DEMO');

insert into account values (2, 'TEST');

insert into role values (1, 125);

insert into role values (2, 225);

insert into AccountRole values (1, 1);

insert into AccountRole values (2, 1);

insert into AccountRole values (2, 2);

alter function f_or(@.acc_id int) returns int

begin

declare @.right int,

@.result int;

set @.result = 0

declare lcursor cursor for select rights from Role R, AccountRole AR where R.id = AR.roleid and AccountId = @.acc_id;

open lcursor;

fetch lcursor into @.right;

while @.@.FETCH_STATUS = 0 begin

set @.result = @.result | @.right

fetch lcursor into @.right

end

close lcursor;

return @.result;

end

go

select AccountName, dbo.f_or(id) 'Rights' from account

|||

Why do you think the second approach is not a good solution?

Thanks,
Loonysan

|||

If you only need to get the rights for a single Account, then the approach would be fine. However, in a set-wise report, my guess is that it would perform much worse as you are producing a new query for each Account instead of joining the tables in.

I only sketched what the function would look like. Can you complete the function or do you need more code? If you didn't simplify your tables or the problem, in a major way, for the sake of the post it should be easy enough to try both methods and weigh the advantages.