Boolean data type

Is there a boolean data type in SQL? I'm trying to bind a number of check
boxes and radio buttons in a .net app to some columns in a SQL table and I
don't see any boolean data type. What should I do if I want to assign a data
type "boolean" to a table's column in SQL?
--
TSThere is not... Many developers use CHAR(1), with a check constraint
restricting the values to 'Y' or 'N'. Some people like to use BIT instead,
but I personally prefer the former approach...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> Is there a boolean data type in SQL?
No, you probably want BIT, though it can take 0, 1, or false. Many people
use CHAR(1) and set a constraint to be either T/F or Y/N.
A|||Well, there is the BIT type. You could also just use a numeric field (of
some sort) and treat it as 0=FALSE, !0=TRUE.
I _think_ bit is preferable, because being a bit, it is either 0 or 1
(or -1, im not sure) and is therefore a logical boolean, but a plain numeric
is more portable if that is a concern.
HTH.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> No, you probably want BIT, though it can take 0, 1, or false.
Of course, I meant 0, 1, or NULL.|||Uh, oh. Now you're starting to think like SSMS :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u$vi6Yt7FHA.2608@.TK2MSFTNGP10.phx.gbl...
> Of course, I meant 0, 1, or NULL.
>|||> Uh, oh. Now you're starting to think like SSMS :)
Nah, I would have put NULL in italics and spelled out 0 as "false" and 1 as
"true"... :-)|||There isn't a boolean type in SQL but you can use bit type.
If you want to assign a data type "boolean" to table column ( I assume that
column's name is Male), you can do
if chkMale.Checked =true then
insert into ... (PersonID, Name, Male) values ('P001','Richard',1)
else
insert into ... (PersonID, Name, Male) values ('P001','Richard',0)
end if
That's it.
"TS" wrote:

> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a da
ta
> type "boolean" to a table's column in SQL?
> --
> TS

booking db design help

Hey Guys,
I am trying to create a web app that uses an SQL db to handle employee
scheduling. I can design a table to hold the appointments with no problems
but I need to be able to search for free time for the employees.
all bookings are based on 30 minutes blocks.
So far I managed to create all tables and this is what I have concerning the
booking tables "after seeing this post on
http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
of DB design I am having a hard time understanding the solution.
eventually I need to be able to achieve the search using similar statement
to this
select min(starttime)
from schedule
where usedflag='free'
and duration >= :desired_duration
but I am having a problem with tables structure.
would someone explain the structure posted please?
thanks in advancesammy
It ishard to suggest soemthing without seeing the actual data and some DDL
Take a look at Itzik Ben-Gan's example showing the intreval connections to
the internet and resturns the connections was made less than 5 minutes
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
select * from tblConnection
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
"sammy" <sammy1971@.hotmail.com> wrote in message
news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
> Hey Guys,
> I am trying to create a web app that uses an SQL db to handle employee
> scheduling. I can design a table to hold the appointments with no problems
> but I need to be able to search for free time for the employees.
> all bookings are based on 30 minutes blocks.
> So far I managed to create all tables and this is what I have concerning
> the booking tables "after seeing this post on
> http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
> of DB design I am having a hard time understanding the solution.
> eventually I need to be able to achieve the search using similar statement
> to this
> select min(starttime)
> from schedule
> where usedflag='free'
> and duration >= :desired_duration
> but I am having a problem with tables structure.
> would someone explain the structure posted please?
> thanks in advance
>
>|||Thanks for the response Uri,
what I am thinking of best way to to describe the data is like this. I will
create a table to hold sku's refering to the services to be booked the
skutable should be something like this
SKuID --PK
Description -- char
Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so on
The employee table should be like this
EmpID -->PK
Fname
Lname
location
ScheduleID -->FK
the scheduletable should be like this
SID -->PK
sundayStart --> ie 12/06/2006 9:30 AM
SundayEnd --> 12/06/2006 6:00 PM
MondayStart
MondayEnd
TuesdayStart
TuesdayEnd
WedStart
WedEnd
ThurStart
ThursEnd
FridayStart
FridayEnd
SaturedayStart
SatureDayEnd
what I am trying to achieve can be described best as calenday with all
employees, the workdays, starttime, endtime and the times they are available
to take calls "calls are based on 30 minutes blocks incremening by 30
minutes.
I hope that explains the issue better
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
> sammy
> It ishard to suggest soemthing without seeing the actual data and some DDL
> Take a look at Itzik Ben-Gan's example showing the intreval connections to
> the internet and resturns the connections was made less than 5 minutes
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> select * from tblConnection
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> "sammy" <sammy1971@.hotmail.com> wrote in message
> news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
>|||sammy
Take a look at http://www.aspfaq.com/show.asp?id=2519
"sammy" <sammy1971@.hotmail.com> wrote in message
news:OdtVFi0bGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response Uri,
> what I am thinking of best way to to describe the data is like this. I
> will create a table to hold sku's refering to the services to be booked
> the skutable should be something like this
> SKuID --PK
> Description -- char
> Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so
> on
> The employee table should be like this
> EmpID -->PK
> Fname
> Lname
> location
> ScheduleID -->FK
> the scheduletable should be like this
> SID -->PK
> sundayStart --> ie 12/06/2006 9:30 AM
> SundayEnd --> 12/06/2006 6:00 PM
> MondayStart
> MondayEnd
> TuesdayStart
> TuesdayEnd
> WedStart
> WedEnd
> ThurStart
> ThursEnd
> FridayStart
> FridayEnd
> SaturedayStart
> SatureDayEnd
> what I am trying to achieve can be described best as calenday with all
> employees, the workdays, starttime, endtime and the times they are
> available to take calls "calls are based on 30 minutes blocks incremening
> by 30 minutes.
> I hope that explains the issue better
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
>

blocking and deadlock detection

I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
may be causing either some sort of block or deadlock when certain operations
are performed. Can anyone suggest a good way of configuring the profiler (or
any other method) to help in identifying problem spids?
tia
MG
Have a look here: http://support.microsoft.com/default.aspx/kb/271509
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG
|||You can also enable a trace flag to get detailed information about
deadlocks. See here: http://msdn2.microsoft.com/en-us/library/ms178104.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eCd19JDaIHA.220@.TK2MSFTNGP04.phx.gbl...
> Have a look here: http://support.microsoft.com/default.aspx/kb/271509
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
>
|||Hi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG

blocking and deadlock detection

I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
may be causing either some sort of block or deadlock when certain operations
are performed. Can anyone suggest a good way of configuring the profiler (or
any other method) to help in identifying problem spids?
tia
--
MGHave a look here: http://support.microsoft.com/default.aspx/kb/271509
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG|||You can also enable a trace flag to get detailed information about
deadlocks. See here: http://msdn2.microsoft.com/en-us/library/ms178104.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eCd19JDaIHA.220@.TK2MSFTNGP04.phx.gbl...
> Have a look here: http://support.microsoft.com/default.aspx/kb/271509
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
>> I'm using sql 2005 standard. There's a that a 3rd party app that I
>> suspect
>> may be causing either some sort of block or deadlock when certain
>> operations
>> are performed. Can anyone suggest a good way of configuring the profiler
>> (or
>> any other method) to help in identifying problem spids?
>> tia
>> --
>> MG
>|||Hi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.
|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)

>
> TIA, ChrisR
>
|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com... [vbcol=seagreen]
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
come[vbcol=seagreen]
boss
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>
|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas

"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisROn Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com...
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
> >Is there a way to make it so that nobody can connect to sql unless they
come
> >in through a certain app... without the use of Application Roles. ( my
boss
> >doesnt want to have to pass in a password each time.)
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

Bitmask query?

I'm working on a new app with Sql CE and before I got too deep in DB design, I wanted to make sure that it can support a bitmask query.

What I want is a bit-packed field to handle support for region. So I'd like to be able to take a field (e.,g. [Support]) and do a bitwise and with the current region code and see if it is supported.

I know I could get all the data and write a method to make it work, but was hoping there would be some way to construct a query to only pull down the relevant information.

Thanks

Something like this ?

Code Snippet

CREATE TABLE test (

[id] [int] NULL,

[bitwise] [int] NULL

)

INSERT INTO Test (id, bitwise) VALUES (1, 7)

INSERT INTO Test (id, bitwise) VALUES (2, 1)

INSERT INTO Test (id, bitwise) VALUES (3, 0)

select * FROM test WHERE (bitwise & 1) = 1

(returns 2 rows)

|||That should do it. I thought it would be something similar to that. I just couldn't verify that would work with CE... maybe I need to improve my googling skills Thanks

Bit column in FoxPro GUI

I have to pull data from a SQL app for a WIN-Form app in .NET, where data is
stored in FoxPro. Seems that the bit data type is not casting properly
through the ODBC driver back to FoxPro'
Any way to cast or convert a bit column? When I did case when IsPrint = 0
then ' 0' else '1' end IsPrint but that barfed?
Any ideas?How about CAST(bitcol AS int)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:%23bEzNe6DFHA.328@.tk2msftngp13.phx.gbl...
>I have to pull data from a SQL app for a WIN-Form app in .NET, where data i
s
> stored in FoxPro. Seems that the bit data type is not casting properly
> through the ODBC driver back to FoxPro'
> Any way to cast or convert a bit column? When I did case when IsPrint = 0
> then ' 0' else '1' end IsPrint but that barfed?
> Any ideas?
>
>|||"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:%23bEzNe6DFHA.328@.tk2msftngp13.phx.gbl...
> I have to pull data from a SQL app for a WIN-Form app in .NET, where data
is
> stored in FoxPro. Seems that the bit data type is not casting properly
> through the ODBC driver back to FoxPro'
cast (ap_noprint as int) ap_noprint
Worked fine.