Boolean logic on binary data

Hello,

I have a 21-byte binary field in a SQL Server table with which I want to do boolean AND logic in a report. (Basically, I want to test whether or not individual bits are turned on.) How can I do this? Is it possible to deal with the binary data without first converting it to a string?

Thanks,
MarkYou can analyze your bitmap values using RDL expressions which are regular VB.NET.|||How exactly do I refer to the data, however? What I mean is, I need to access the bits of this big blob of binary data, but SSRS is not binary-friendly--it wants a string or an int.

All I want to do is access 1 particular bit of this binary blob. If I have to convert this to a string somewhere I will, but I'd prefer not to if at all possible.

(FYI, the report needs to analyze the logonHours attribute for an Active Directory user. This is stored as a byte array, with each bit representing 1 hour in a given week--it's 1 if the user can logon, or 0 if he cannot. I'm converting this to be a 21-byte binary field in SQL.)sql

Blocking Problems

We have a stored proc that is causing alot of blocking
problems. Basically, it selects a field based on the PK
and returns the value (0 or 1) as an output parameter:
SELECT @.Anonymous_Bool = Anonymous_Bool
FROM Member_Properties
WHERE GUID = @.GUID
The estimated execution plan shows that it does a
clustered index seek. When we tested it out it was fine,
but when it's in production, it looks like it starts
causing massive blocking problems. I don't see how the
proc itself could be a problem since it is very
efficient.
Has anyone else encountered a similar problem? could it
possibly be something in the front end code? or maybe
connection pooling? any ideas on a direction to look
would be much appreciated...Is @.Guid a UniqueIdentifier datatype? If not and the GUID column is it may
have an issue actually using the index properly. But I suspect you are
seeing the results of the connection changing the isolation level to
serializable. Or you can be seeing the effects of page splitting due to the
Guid is clustered. Check to see what is going on with profile on the
connections doing the blocking. In my opinion Guid's are a horrible PK and
especially if you cluster on them. There are few if any alternatives that
can perform worse than a Guid in a clustered index on a table with new rows
being inserted.
--
Andrew J. Kelly
SQL Server MVP
"A. Sugrue" <sugruea@.hotmail.com> wrote in message
news:058701c356ec$ec57e640$a401280a@.phx.gbl...
> We have a stored proc that is causing alot of blocking
> problems. Basically, it selects a field based on the PK
> and returns the value (0 or 1) as an output parameter:
> SELECT @.Anonymous_Bool = Anonymous_Bool
> FROM Member_Properties
> WHERE GUID = @.GUID
> The estimated execution plan shows that it does a
> clustered index seek. When we tested it out it was fine,
> but when it's in production, it looks like it starts
> causing massive blocking problems. I don't see how the
> proc itself could be a problem since it is very
> efficient.
> Has anyone else encountered a similar problem? could it
> possibly be something in the front end code? or maybe
> connection pooling? any ideas on a direction to look
> would be much appreciated...|||Yes it is a uniqueidentifier and is the PK with a
clustered index and it is also the rowguidcol for merge
replication.
>--Original Message--
>Is @.Guid a UniqueIdentifier datatype? If not and the
GUID column is it may
>have an issue actually using the index properly. But I
suspect you are
>seeing the results of the connection changing the
isolation level to
>serializable. Or you can be seeing the effects of page
splitting due to the
>Guid is clustered. Check to see what is going on with
profile on the
>connections doing the blocking. In my opinion Guid's are
a horrible PK and
>especially if you cluster on them. There are few if any
alternatives that
>can perform worse than a Guid in a clustered index on a
table with new rows
>being inserted.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> We have a stored proc that is causing alot of blocking
>> problems. Basically, it selects a field based on the PK
>> and returns the value (0 or 1) as an output parameter:
>> SELECT @.Anonymous_Bool = Anonymous_Bool
>> FROM Member_Properties
>> WHERE GUID = @.GUID
>> The estimated execution plan shows that it does a
>> clustered index seek. When we tested it out it was
fine,
>> but when it's in production, it looks like it starts
>> causing massive blocking problems. I don't see how the
>> proc itself could be a problem since it is very
>> efficient.
>> Has anyone else encountered a similar problem? could it
>> possibly be something in the front end code? or maybe
>> connection pooling? any ideas on a direction to look
>> would be much appreciated...
>
>.
>|||No, but since we already had a uniqueidentifier there as
the PK, there was no need to add another column to be the
rowguidcol.
>--Original Message--
>Having a Guid for merge replication purposes is fine but
that does not mean
>it has to be the PK.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:053f01c357ae$41b2aaa0$a301280a@.phx.gbl...
>> Yes it is a uniqueidentifier and is the PK with a
>> clustered index and it is also the rowguidcol for merge
>> replication.
>> >--Original Message--
>> >Is @.Guid a UniqueIdentifier datatype? If not and the
>> GUID column is it may
>> >have an issue actually using the index properly. But I
>> suspect you are
>> >seeing the results of the connection changing the
>> isolation level to
>> >serializable. Or you can be seeing the effects of page
>> splitting due to the
>> >Guid is clustered. Check to see what is going on with
>> profile on the
>> >connections doing the blocking. In my opinion Guid's
are
>> a horrible PK and
>> >especially if you cluster on them. There are few if
any
>> alternatives that
>> >can perform worse than a Guid in a clustered index on a
>> table with new rows
>> >being inserted.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>> >news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> >> We have a stored proc that is causing alot of
blocking
>> >> problems. Basically, it selects a field based on
the PK
>> >> and returns the value (0 or 1) as an output
parameter:
>> >>
>> >> SELECT @.Anonymous_Bool = Anonymous_Bool
>> >> FROM Member_Properties
>> >> WHERE GUID = @.GUID
>> >>
>> >> The estimated execution plan shows that it does a
>> >> clustered index seek. When we tested it out it was
>> fine,
>> >> but when it's in production, it looks like it starts
>> >> causing massive blocking problems. I don't see how
the
>> >> proc itself could be a problem since it is very
>> >> efficient.
>> >>
>> >> Has anyone else encountered a similar problem?
could it
>> >> possibly be something in the front end code? or
maybe
>> >> connection pooling? any ideas on a direction to look
>> >> would be much appreciated...
>> >
>> >
>> >.
>> >
>
>.
>

Blocking issue

We are experencing some serious locking issue on sql server (sql2000). Basically a method in VB component opens a recordset which loops through a table A (about 100,000 times), inside the loop, it calls a stpred proc and does an update on table A. Each update in the loop is quite small, a few records at the most. Table A has an update trigger which insert the updated records to a history table then delete this record from table a. There are some f other queries running on table a. The big update loop on table A causes locking issues. With every update in the loop, there are hundred of locks on the delete job when it's blocking other jobs. I can't fingure out why continuous small updates opens so many locks(by looking at sp_lock) therefore blocks other process that query the same table. Any help is appreciated. thanks.Ouch. That's just asking for locking problems... Why do you this using a
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.

Blank page?

In my report I have 4 lists. In the each list I have textbox (Company Name)
And matrix. Each list shows in separate page. So basically if I view report
I have 4 pages.
The problem is.
For example, If in my report parameter, which is â'Group Nameâ'
(A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
still 4 pages and 2 pages is blank. How can I remove that blank pages?
I used matrix in the list to able to view each matrix in separate pages.
Thank you.On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> In my report I have 4 lists. In the each list I have textbox (Company Name)
> And matrix. Each list shows in separate page. So basically if I view report
> I have 4 pages.
> The problem is.
> For example, If in my report parameter, which is 'Group Name'
> (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> still 4 pages and 2 pages is blank. How can I remove that blank pages?
> I used matrix in the list to able to view each matrix in separate pages.
> Thank you.
I don't work with lists often but if I were trying to do here is where
I'd start.
Try conditioning the visibility properties of your lists.
Insert an expression into the Visibility>Hidden property of the
individual lists. For the Group Name A list, try something like:
=IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
You may need to fiddle with the syntax some but what you're trying to
get at is
"If A is found in the Group Name param
then show the List
otherwise hide the List"
If needed, the namespace is Microsoft.VisualBasic.
If lists work like tables and other report items, the non-hidden ones
should move up and fill the white space left by the hidden ones.
HTH
toolman|||I do not have problem to hide my lists. If i unselect 2 groups it will hide
the lists but leaves the 2 blank pages. I need to remove these blank pages.
"toolman" wrote:
> On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > And matrix. Each list shows in separate page. So basically if I view report
> > I have 4 pages.
> > The problem is.
> > For example, If in my report parameter, which is 'Group Name'
> > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > I used matrix in the list to able to view each matrix in separate pages.
> >
> > Thank you.
> I don't work with lists often but if I were trying to do here is where
> I'd start.
> Try conditioning the visibility properties of your lists.
> Insert an expression into the Visibility>Hidden property of the
> individual lists. For the Group Name A list, try something like:
> =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> You may need to fiddle with the syntax some but what you're trying to
> get at is
> "If A is found in the Group Name param
> then show the List
> otherwise hide the List"
> If needed, the namespace is Microsoft.VisualBasic.
> If lists work like tables and other report items, the non-hidden ones
> should move up and fill the white space left by the hidden ones.
> HTH
> toolman
>|||Try to adjust the canvas size in layout to match the report width and height
in Report Properties.
Thanks,
"JT" wrote:
> I do not have problem to hide my lists. If i unselect 2 groups it will hide
> the lists but leaves the 2 blank pages. I need to remove these blank pages.
>
> "toolman" wrote:
> > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > And matrix. Each list shows in separate page. So basically if I view report
> > > I have 4 pages.
> > > The problem is.
> > > For example, If in my report parameter, which is 'Group Name'
> > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > I used matrix in the list to able to view each matrix in separate pages.
> > >
> > > Thank you.
> >
> > I don't work with lists often but if I were trying to do here is where
> > I'd start.
> >
> > Try conditioning the visibility properties of your lists.
> >
> > Insert an expression into the Visibility>Hidden property of the
> > individual lists. For the Group Name A list, try something like:
> > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > You may need to fiddle with the syntax some but what you're trying to
> > get at is
> > "If A is found in the Group Name param
> > then show the List
> > otherwise hide the List"
> > If needed, the namespace is Microsoft.VisualBasic.
> >
> > If lists work like tables and other report items, the non-hidden ones
> > should move up and fill the white space left by the hidden ones.
> >
> > HTH
> > toolman
> >|||Each list shows in separate pages but if is one list unselected (For exmp:
group A - from report parameter) then it should not show the 1 page. Instead
of that I do have the blank page. How to remove the blank page?
"naren" wrote:
> Try to adjust the canvas size in layout to match the report width and height
> in Report Properties.
> Thanks,
> "JT" wrote:
> > I do not have problem to hide my lists. If i unselect 2 groups it will hide
> > the lists but leaves the 2 blank pages. I need to remove these blank pages.
> >
> >
> > "toolman" wrote:
> >
> > > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > > And matrix. Each list shows in separate page. So basically if I view report
> > > > I have 4 pages.
> > > > The problem is.
> > > > For example, If in my report parameter, which is 'Group Name'
> > > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > > I used matrix in the list to able to view each matrix in separate pages.
> > > >
> > > > Thank you.
> > >
> > > I don't work with lists often but if I were trying to do here is where
> > > I'd start.
> > >
> > > Try conditioning the visibility properties of your lists.
> > >
> > > Insert an expression into the Visibility>Hidden property of the
> > > individual lists. For the Group Name A list, try something like:
> > > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > > You may need to fiddle with the syntax some but what you're trying to
> > > get at is
> > > "If A is found in the Group Name param
> > > then show the List
> > > otherwise hide the List"
> > > If needed, the namespace is Microsoft.VisualBasic.
> > >
> > > If lists work like tables and other report items, the non-hidden ones
> > > should move up and fill the white space left by the hidden ones.
> > >
> > > HTH
> > > toolman
> > >

Bizarre Stored Procedure Behavior

I have dozens of stored procedures, but one of them is suddenly behaving badly.

Basically, if the sp is run as a query (full text), it runs fine and finishes successfully, but if 'executed' (Exec [sp_name]) it fails, saying that one of the referenced tables doesn't exist. The really weird thing is that the table is created earlier in the same procedure and is recognized enough to allow an index to be created on it. But when it comes to an Update statement, forget it. The sp dies saying the object (table) is unrecognized. I have dropped and recreated it, tried everything I can think of.

Has anyone else experienced this? Any ideas what may be causing it?

I'm stumped.

Thanks!you do have the correct names on your command object of the update statement?

Bizarre sql management studio problem...cant see server status for

This one has me stumped.
Basically when I open sql management studio 2005 and connect to one of
my servers, the little server status icon adjacent to the server name
contains a blank circle. Normally it contains a small green triangle
to indicate the server is running. If I right click on the server
name, all the start/stop/pause/resume etc options are greyed out. This
is very confusing for the following reasons:
- I am logged on as 'sa' and all my other permissions work.
- Other logins (both sql server and windows authentication) show the
same issue
- Connecting to other database servers works fine (and shows the
correct status icon)
I originally thought it was some bizarre permissions issue, however if
I am logged directly into the server start the sql management tool
there using the same database login, the server shows the 'started'
green triangle and the start/stop/pause/resume etc options are
available.
This issue appears to have occurred randomly, I can not think of
anything I have done recently that involved changing permissions or
anything like that.
Any ideas?The connection as sa is a regular SQL Server connection. The
indicator that shows that the server is running is not updated based
on making a SQL Server connection; if you open SSMS without connecting
to any SQL Server the status of the servers should show.
The indication that SQL Server is running, or not, is based on SSMS
finding out if the service is running. That query is made to the
server's operating system, and is based on the rights of the Windows
account you are logged in with. For the servers that are displayed
with the status you have rights, for the others you do not.
I admit to a bit of guesswork in the above, but I think it is the
answer.
Roy Harvey
Beacon Falls, CT
On Fri, 11 Jan 2008 17:16:50 -0800 (PST), Alex Danger
<alex.csp@.gmail.com> wrote:
>This one has me stumped.
>Basically when I open sql management studio 2005 and connect to one of
>my servers, the little server status icon adjacent to the server name
>contains a blank circle. Normally it contains a small green triangle
>to indicate the server is running. If I right click on the server
>name, all the start/stop/pause/resume etc options are greyed out. This
>is very confusing for the following reasons:
>
>- I am logged on as 'sa' and all my other permissions work.
>- Other logins (both sql server and windows authentication) show the
>same issue
>- Connecting to other database servers works fine (and shows the
>correct status icon)
>I originally thought it was some bizarre permissions issue, however if
>I am logged directly into the server start the sql management tool
>there using the same database login, the server shows the 'started'
>green triangle and the start/stop/pause/resume etc options are
>available.
>This issue appears to have occurred randomly, I can not think of
>anything I have done recently that involved changing permissions or
>anything like that.
>Any ideas?

Bit of help with a small project i have

hi, im basically looking some advice here for a small project we have.
basically im looking at automating an attendance register
in short want i want to do i build a matrix of students attendance on a
course. this table of data will then be used to populate a Datagrid. Course
Duration can vary but i will only ever report on the attendance for any one
course at a time. basically i will pass in the courseID as a parameter to
return the attendance for all the students on that course
what all would i need?
tbl_Students.(StudID, Name)
tbl_Course (CourseID, CourseDesc, CourseDuration)
tbl_Attendance(ID, StudID, CourseID, WNum, Attended)
what im thinking is that i will need a record for each student for each w
the course runs
im getting a bit stuck on how i would return the records in the way i want
Student Name W1, W2 etc......
Craig G 1 0 1 1 1 1 1
Jim B 1 0 0 1 1 1 1
Sam A 0 1 1 1 1 1 1
basically i would like my output to be as above where each W is a column
in a temp_table. and the 1 & 0 simply represent whether they attended or not
This may not be the best way to do it, its only the early stages of the
project so i am open to an ideas on how you would go about it
Cheers for the help,
CraigCross tabulations are best done using a report writer or a client tier
programming language. To get this done as a resultset, refer to:
http://support.microsoft.com/?scid=kb;EN-US;q175574
Anith|||On Tue, 22 Feb 2005 21:28:14 -0000, Craig G wrote:

>hi, im basically looking some advice here for a small project we have.
>basically im looking at automating an attendance register
(snip)
>This may not be the best way to do it, its only the early stages of the
>project so i am open to an ideas on how you would go about it
Hi Craig,
For the cross-tab query, check out the link Anith posted. But since
you're in the early stages of the project, allow me to post some
comments on your table design.

>what all would i need?
>tbl_Students.(StudID, Name)
Rename this table to "Students". No need to use the "tbl_" prefix; if
the name is in a FROM clause, you'll know it's a table anyway.
See if you can find a better key for students - something that can be
verified in the real world. The student's SSN would be ideal; you can
also check the school administration to find out if they have some
registration number.

>tbl_Course (CourseID, CourseDesc, CourseDuration)
The tbl_ prefix on this one should go as well. Most people prefer plural
names for tables, except for tables that hold only one row, so consider
renaming to "Courses".
Doesn't your school use mnemonic abbreviations for courses? These are
often an ideal candidate for primary keys: they are short enough, they
are easy verifiable and they are already in use and known.

>tbl_Attendance(ID, StudID, CourseID, WNum, Attended)
WNum? What will you do after december? If you start using w 1
again, you'll get duplicate data. I think you should add year as well,
or you should use a datetime column holding the first day of the w
(that way, you'll have date and year in the same column, the downside is
that you have to do some calculations to extract w number).
I don't think you need a seperate ID column in this table. Just use the
compound primary key on StudID, CourseID, WNum and Year.
Get rid of the Attended column. Only record the students that attend a
course. If there's no row for a student in a specific w, (s)he did
not attend the course.
I also think you're missing a table. It seems logical that students
enlist for some courses, probably picking a starting w. So you also
need a table
Enlistments (StudID, CourseID, StartW, StartYear)
With (StudID, CourseID) the primary key. StudID and CourseID are each a
foreign key to Students resp. Courses. And the combination (StudID,
CourseID) in Attendance will be a foreign key to Enlistments.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I can't believe you are telling someone to use a user editable field as a
key. You need to do a little research on database design before you start
telling people how to screw up their design. Don't you know that SSN's can
be mistyped? Proper PK's should have NO real world meaning.
JIM
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke9r115eavtte6r7hjgpt6i14vjb5a3vke@.
4ax.com...
> On Tue, 22 Feb 2005 21:28:14 -0000, Craig G wrote:
>
> (snip)
> Hi Craig,
> For the cross-tab query, check out the link Anith posted. But since
> you're in the early stages of the project, allow me to post some
> comments on your table design.
>
> Rename this table to "Students". No need to use the "tbl_" prefix; if
> the name is in a FROM clause, you'll know it's a table anyway.
> See if you can find a better key for students - something that can be
> verified in the real world. The student's SSN would be ideal; you can
> also check the school administration to find out if they have some
> registration number.
>
> The tbl_ prefix on this one should go as well. Most people prefer plural
> names for tables, except for tables that hold only one row, so consider
> renaming to "Courses".
> Doesn't your school use mnemonic abbreviations for courses? These are
> often an ideal candidate for primary keys: they are short enough, they
> are easy verifiable and they are already in use and known.
>
> WNum? What will you do after december? If you start using w 1
> again, you'll get duplicate data. I think you should add year as well,
> or you should use a datetime column holding the first day of the w
> (that way, you'll have date and year in the same column, the downside is
> that you have to do some calculations to extract w number).
> I don't think you need a seperate ID column in this table. Just use the
> compound primary key on StudID, CourseID, WNum and Year.
> Get rid of the Attended column. Only record the students that attend a
> course. If there's no row for a student in a specific w, (s)he did
> not attend the course.
>
> I also think you're missing a table. It seems logical that students
> enlist for some courses, probably picking a starting w. So you also
> need a table
> Enlistments (StudID, CourseID, StartW, StartYear)
> With (StudID, CourseID) the primary key. StudID and CourseID are each a
> foreign key to Students resp. Courses. And the combination (StudID,
> CourseID) in Attendance will be a foreign key to Enlistments.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)