Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas
|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
--
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
TIA,
ChrisR
Chris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR
|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling a
s
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechn...art3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,
Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evaluat...s/fulltext.asp
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evaluation/features/fulltext.asp
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evalua...es/fulltext.asp
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

blob data type in SQL Express

Hi all,

I am trying to store binary datafiles into a SQl Express table. However I can not find the BLOB data type when I am designing the table. Are we limited to using that kind of data type in the Express edition? Thanks.

Ke

hi Ke,

if you are searching for a BLOB datatype, you'll miss it..

SQLExpress does support blobs in different formats, binary, varbinary, image and text/ntext as long as the new datatypes as varchar(MAX), nvarchar(MAX), varbinary(MAX), XML

use tempdb;

GO

CREATE TABLE dbo.testBlob (

Id int NOT NULL PRIMARY KEY,

b varchar(max),

i image,

b2 varbinary (50)

);

GO

DROP TABLE dbo.testBlob ;

I've just run SQL Server Management Studio Express, the official graphical client tool for SQLExpress, available for free download at http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en, and the relative datatypes are listed in the datatypes combobox when editing tables...

please have a look at http://msdn2.microsoft.com/en-us/library/ms187752.aspx for all supported data types..

regards

Blob data

Can someone please give me an example in C# on how to retrieve an Image from a Table and store i into a Picture box on a windows form? In addition, how to insert a blob record into a table as well.Reading BLOBs from SQL Server and display it inaWindows FormPictureBox
http://www.akadia.com/services/dotnet_load_blob.html|||Does any one have an example that they have used and written themselves? This example is very confusing.|||Check the code in the following article. It is from the Microsoft Knowledge base article.
http://www.kbalertz.com/Feedback_326502.aspx|||Just in case the above article doesn't suffice your requirement, let me know and I shall post the code written by me.

Blank Report Displayed..

Hello All,
I am developing a software.in that sfotware i have to store a logo file(*.jpg)file in oracle 8i. to store logo file in a blob file when i link that table to crystal report 8.5 report become blank
please help me.

i follow these stape

1) I create a table. table name is test field as nm varchar2(20), logo blob
2)by using toad software load a jpeg file in a record & commited
3) in cryastal report, new report is created. system dsn is selected and connected to database
4) test table is added and nm & logo field are placed in detail section when i refresh that report it is a blank
please help me.

prasadMake sure you have records to the table you are referencing
Also do verify database; Uncheck the option Save data with Report

BitArray to binary

I want to store a binary string in SqlServer from VB .NET through a
stored procedure. I have been storing the binary data in a BitArray
which I am not set on by any means. When I pass the BitArray into a
stored procedure, which is looking for a variable of type binary, it
throws the following: "Object must implement IConvertible." BitArray
obviously does not implement IConvertable.

If anyone has a good way of passing binary data into a stored
procedure through a parameter array I'd appreciate it."Ramzi Abboud" <ramziabb@.gmail.com> wrote in message
news:a5617e65.0410141217.21e48d0@.posting.google.co m...
>I want to store a binary string in SqlServer from VB .NET through a
> stored procedure. I have been storing the binary data in a BitArray
> which I am not set on by any means. When I pass the BitArray into a
> stored procedure, which is looking for a variable of type binary, it
> throws the following: "Object must implement IConvertible." BitArray
> obviously does not implement IConvertable.
> If anyone has a good way of passing binary data into a stored
> procedure through a parameter array I'd appreciate it.

You might get a better answer by posting in a .NET or VB group. I don't know
what a BitArray is, but you can use the ADO Command object to execute stored
procedures with correctly typed parameters, which might be another
possiblity for you.

Simon

bit value as parameter

Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>

bit value as parameter

Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>