Boolean to Bit?

IT seems to me that I can't write something like this:
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:

>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.

>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!

>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.

>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.

>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.

>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.

>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.

>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.

>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl

Boolean Calculated Fields in SQL Server

Hi
This may seem amazing and a stupid question but:

Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?select foo
, bar
, fap
, case when exists
( select 1 from tableB
where Afk = tableA.pk )
then 1 else 0 end as calcfield
from tableA|||Originally posted by r937
select foo
, bar
, fap
, case when exists
( select 1 from tableB
where Afk = tableA.pk )
then 1 else 0 end as calcfield
from tableA

Booking database - query

Hi,

I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.

The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.

How do I write this kind of query ?

Thanks
---

dteDateTime="27-Jul-02 15:30"
iCarID=3

SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"can you give a brief example of what the result set is supposed to look like with the holes in it?

have a look at Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) which shows how to use an integer table to generate dates

rudy
http://rudy.ca/|||Hi,

The results would be as follows :

car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1

Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.

The problem I have is that I only store the 1's in the db not the 0's!

The article looks like exactly what Im trying to do - Thanks.|||okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...

rudy|||Thanks rudy,

At least I know what its called now! that was half the problem trying to research it.

I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.|||the performance was bad in ASP?

then you must have been doing something wrong, like calling the database in a loop :cool:

call the database like this:SELECT bookedTime
FROM tblBookedTimes
INNER JOIN tblBookings
ON tblBookedTimes.fk_bookingID
= tblBookings.pk_bookingID
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTimenotice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence

now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...

there's no way that's going to have bad performance

laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)

so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array

i guess i should mention that i don't do ASP :rolleyes:

rudy
http://rudy.ca/

Book recommendations?

I do software support for software that works with both Oracle and SQL
Server, so I mostly just write queries to look at the data related to
the software. When I first started, I bought a couple of books to learn
the basics, intentionally going with generic books that would help with
both types of databases. I've got the basics down, and now I'm looking
for a really detailed reference book that goes really indepth into
querying for SQL Server specifically. Currently, we only work with the
2000 version, but we'll be going to 2005 soon, so I think I'd rather go
with a book on 2005, although if you know one that covers the
differences between them, that would be great. Any recommendations?

Besides just writing queries, I'd also like to learn more about SQL
Server in general. DBA software support, so I'd like to head my
career in that direction. I was looking at Microsoft's new
certification path for SQL 2005. Given that my company won't pay to
send me for training, and I really don't want to have to put down
$2200+ to pay for a class, I was thinking that I might be able to learn
enough on my own to pass the first test and get the MCTS title. I
ordered the Training Kit from Microsoft Press for that, which comes
with a 180 day trial of SQL Server 2005 to play with, along with a huge
book. Does anyone have any other recommendations for resources to help
learn this stuff?

--Richardblueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:

Quote:

Originally Posted by

I do software support for software that works with both Oracle and SQL
Server, so I mostly just write queries to look at the data related to
the software. When I first started, I bought a couple of books to learn
the basics, intentionally going with generic books that would help with
both types of databases. I've got the basics down, and now I'm looking
for a really detailed reference book that goes really indepth into
querying for SQL Server specifically. Currently, we only work with the
2000 version, but we'll be going to 2005 soon, so I think I'd rather go
with a book on 2005, although if you know one that covers the
differences between them, that would be great. Any recommendations?

The ultimate book on writing queries on SQL 2005 simply must be "Inside
T-SQL Querying" by Itzik Ben-Gan on Microsoft Press. If there is a problem
he can't write a query for, then there is simple is no query to write - or
the query is not worth writing. I'm just about to read the book myself,
so I cannot say how much he compares with SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||According to Amazon, Ken Henderson is not even planning to release a
book about 2005 yet. What a pity.|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
The ultimate book on writing queries on SQL 2005 simply must be "Inside
T-SQL Querying" by Itzik Ben-Gan on Microsoft Press.

Here's a link to the book:
http://www.microsoft.com/MSPress/books/9615.asp
and the actual title is:
"Inside Microsoft SQL Server 2005: T-SQL Querying"|||metaperl wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:
>

Quote:

Originally Posted by

The ultimate book on writing queries on SQL 2005 simply must be "Inside
T-SQL Querying" by Itzik Ben-Gan on Microsoft Press.

>
Here's a link to the book:
http://www.microsoft.com/MSPress/books/9615.asp
>
and the actual title is:
"Inside Microsoft SQL Server 2005: T-SQL Querying"

So apparently, there's a consensus that this is THE book for T-SQL.
That's just odd. I've been using Usenet for 15 years, and I've never
seen an agreement before. Where's the usual flame war? :P

In looking into this book, I noticed that the author posted some
comments on amazon.com that the rest of you might be interested in.
Here they are:

--
FAQ - what's the difference between the two books ("T-SQL Querying" and
"T-SQL Programming")?

I often get this question, so I'll use this opportunity to clarify
things.

The original intention was to cover T-SQL Querying and Programming in
one book, but we realized that it would end up with 1,200 - 1,300
pages. So we decided to split the content into two books. You can think
of T-SQL Querying as Volume 1 and of T-SQL Programming as Volume 2.

T-SQL Querying focuses on querying and query tuning/optimization.
T-SQL Programming focuses on programmatic constructs (T-SQL and CLR
routines, XML, Service Broker, Dynamic SQL, Cursors, etc.).

Make sure you read T-SQL Querying before T-SQL Programming.

Here's the TOC for T-SQL Querying:
Chapter 01 - Logical Query Processing
Chapter 02 - Physical Query Processing
Chapter 03 - Query Tuning
Chapter 04 - Subqueries, Table Expressions and Ranking Functions
Chapter 05 - Joins and Set Operations
Chapter 06 - Aggregating and Pivoting Data
Chapter 07 - TOP and APPLY
Chapter 08 - Data Modification
Chapter 09 - Graphs, Trees, Hierarchies and Recursive Queries
Appendix A - Logic Puzzles

And here's the TOC for T-SQL Programming:
Chapter 01 - Datatype Related Problems, XML, and CLR UDTs
Chapter 02 - Temporary Tables and Table Variables
Chapter 03 - Cursors
Chapter 04 - Dynamic SQL
Chapter 05 - Views

T-SQL and CLR Routines:
Chapter 06 - User Defined Functions
Chapter 07 - Stored Procedures
Chapter 08 - Triggers

Chapter 09 - Transactions
Chapter 10 - Exception Handling
Chapter 11 - Service Broker
Appendix A - Companion to CLR Routines

Let me know if you have any questions.

Itzik
--
Hi all,

You can find detailed info about the book at www.insidetsql.com
including: TOC, Sample Chapters (just uploaded), Source Code, Links to
Related Resources, Corrections, Letter to the Reader, Author BIOs.
Please let me know if the organization of the website is convenient for
you or if you think that some things should be changed/added. Also, I'd
be glad to get any comments/questions regarding the book. You can
contact me through the website or through
itzik@.solidqualitylearning.com.

Cheers,
Itzik
--sql

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error
?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/defaul...kb;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this err
or?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/defaul...b;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
http://support.microsoft.com/default...b;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
|||Have a look here:
http://support.microsoft.com/default...;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/default.aspx?scid=kb;en-us;309392
"Joe K." wrote:
> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309392
--
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

blonde to write query or design fault

Dear All,

Im wondering if its the design that needs to be changed or I simply cant put this together.

I have 3 tables.

1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)

Codes represent different skills of people, example the sort of job functions theyve held in their employment. Like:

t-CEO,
t-CFO
t-Founder
etc.

people, clearly holds data about people.

CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO

What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.

However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.

I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."

Is that wrong? No person is t-CEO AND t-CFO in your example.

Please tell us what you want the result to be.|||Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:

Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!

Blocked Transactions

I was wanting to write a stored procedure which would identify queries which
are currently being blocked (spids 'blocked by' some other spid) when the
stored procedure is being run.
Is this possible? How do I go about figuring out how to do this?This query will get you the head of the blocking chain...
Declare @.SPID Varchar(500)
Select @.SPID = COALESCE(@.SPID + ',', '') + Cast(a.spid as varchar(20))
From master.dbo.sysprocesses a
where a.blocked = 0 and
a.spid IN (Select b.blocked From master.dbo.sysprocesses b Where b.blocked
!= 0)
Select @.SPID
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>|||Heavey
Just as a starting point, I found that sysprocesses may be a good
place to start. I don't know what this does or anything, but it
looked relevant to the task:
> Select distinct spid, blocked, dbid from master..sysprocesses where blocked
>!= 0 for read only
>open DBA_lockinfo
>Declare @.spid varchar(5)
>Declare @.blocked varchar(5)
>Declare @.dbid varchar(10)
>Declare @.msg varchar (50)
>Declare @.event varchar(500)
>Declare @.event2 varchar(500)
>Create table #aux (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>Create table #aux2 (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>fetch next from DBA_lockinfo into @.spid, @.blocked, @.dbid
>While @.@.fetch_status = 0
>Begin
>Insert into #aux exec ('dbcc inputbuffer (' + @.spid + ')')
>Insert into #aux2 exec ('dbcc inputbuffer (' + @.blocked + ')')
>Set @.event = (select EventInfo from #aux)
>Set @.event2 = (select EventInfo from #aux2)
Good luck! Shoot me an email if you get a handle on it.
-Toby
On Tue, 21 Oct 2003 20:35:23 -0500, "Jim Heavey"
<JimHeavey@.hotmail.com> wrote:
>I was wanting to write a stored procedure which would identify queries which
>are currently being blocked (spids 'blocked by' some other spid) when the
>stored procedure is being run.
>Is this possible? How do I go about figuring out how to do this?
>|||Here is something I use. Show blocked spid and the path down to the one
causing all the waiting.
ALTER PROCEDURE dbo.ListBlockedTransactions
( @.ShowResults int = 1
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE BlockedSPIDSCursor CURSOR
FAST_FORWARD
FOR
SELECT BASE.SPID,
BASE.BLOCKED,
BASE.WAITTIME,
BASE.LASTWAITTYPE BLOCKED_LASTWAITTYPE,
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BASE.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BASE.hostname)
END BLOCKED_HOST
FROM SYSPROCESSES BASE
LEFT OUTER JOIN PCDB.dbo.PC PCBASE ON (BASE.hostname = PCBASE.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBASE ON (PCBASE.PERSONID =PERSONBASE.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBASE.CDID =SCD.PERSONID)
WHERE BASE.BLOCKED <> 0
ORDER BY BASE.last_batch DESC
FOR READ ONLY
DECLARE @.SampleTime DATETIME
SET @.SampleTime = GETDATE()
DECLARE @.BLOCKED_SPID INT
DECLARE @.BLOCKED_BLOCKEDBY INT
DECLARE @.BLOCKED_WAITTIME INT
DECLARE @.BLOCKED_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKED_HOST VARCHAR(255)
OPEN BlockedSPIDSCursor
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
WHILE @.@.FETCH_STATUS = 0 -- while fetch returns a row
BEGIN
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKED_BLOCKEDBY, 1
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
END
CLOSE BlockedSPIDSCursor
DEALLOCATE BlockedSPIDSCursor
RETURN 0
go
ALTER PROCEDURE dbo.ShowBlockingSPID
( @.SampleTime DATETIME,
@.BlockingSPID int,
@.NestLevel int
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE @.BLOCKING_SPID INT
DECLARE @.BLOCKING_BLOCKEDBY INT
DECLARE @.BLOCKING_WAITTIME INT
DECLARE @.BLOCKING_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKING_HOST VARCHAR(255)
DECLARE @.NESTNEXTLEVEL INT
SELECT @.BLOCKING_SPID = BLOCKING.SPID,
@.BLOCKING_BLOCKEDBY = BLOCKING.BLOCKED,
@.BLOCKING_WAITTIME = BLOCKING.WAITTIME,
@.BLOCKING_LASTWAITTYPE = BLOCKING.LASTWAITTYPE,
@.BLOCKING_HOST =
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BLOCKING.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BLOCKING.hostname)
END
FROM SYSPROCESSES BLOCKING
LEFT OUTER JOIN PCDB.dbo.PC PCBLOCKING ON (BLOCKING.hostname =PCBLOCKING.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBLOCKING ON (PCBLOCKING.PERSONID =PERSONBLOCKING.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBLOCKING.CDID =SCD.PERSONID)
WHERE BLOCKING.SPID = @.BlockingSPID
IF @.BLOCKING_BLOCKEDBY <> 0
BEGIN
SET @.NESTNEXTLEVEL = @.NestLevel + 1
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKING_BLOCKEDBY, @.NESTNEXTLEVEL
END
return 0
go
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>

Blobs in SQL Server

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...Connie (csawyer@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

I have a very quick sketch for this on
http://www.sommarskog.se/blobload.txt.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 15, 4:26 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Connie (csaw...@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

>
I have a very quick sketch for this onhttp://www.sommarskog.se/blobload.txt.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Here is some good information that I found in my search to complete
the above task, I am posting as this may help others working with
blobs:
In this article I want to show, how you can copy a single text or
image value into or out of SQL Server with textcopy.exe utility. You
can find this utility in the directory containing the standard SQL
Server EXE files (C:\Mssql\Binn by default for SQL Server 6.5 and C:
\Mssql7\Binn by default for SQL Server 7.0).

The Textcopy utility is not described in SQL Server Books Online, but
you can get its description by typing textcopy /? from the command
prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The
value
is a specified text or image 'column' of a single row (specified by
the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is
not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not
specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the
text or
image data. If 'database' is not specified, the
default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE
keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from
'file'.
/O Copy text or image value out of SQL Server into
'file'.
/K chunksize Size of the data transfer buffer in bytes.
Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of
textcopy utility:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (30),
@.whereclause varchar (40),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'textcopy /S ' + @.srvname +
' /U ' + @.login +
' /P ' + @.password +
' /D ' + @.dbname +
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause +
'" /F ' + @.filename +
' /' + @.direction
EXEC master..xp_cmdshell @.exec_str

This is the example to copy image into SQL Server database pubs, table
pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @.srvname = 'ServerName',
@.login = 'Login',
@.password = 'Password',
@.dbname = 'pubs',
@.tbname = 'pub_info',
@.colname = 'logo',
@.filename = 'c:\picture.bmp',
@.whereclause = " WHERE pub_id='0736' ",
@.direction = 'I'

Blob Data

Can someone please show me an example on how to read & write blob data to a Database? For example if I have the query below (Northwind), how do I actually place the blob item in a picture box on a windows form?
SELECT Picture
FROM Categories
WHERE CategoryID = 5

Try the link below for two sample code to upload image in SQL Server. Hope this helps.
http://www.developerfusion.co.uk/show/3905/

http://www.dotnetbips.com/displayarticle.aspx?id=60

|||

All those tutorials simply store the file into a byte array and than write it to the databse.

I was always asking myself the following:

How big can a byte array become? Is the array's index an integer?

What do I have to do if I want to store a file which has more bytes than an integer?

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
--
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> > My fellow administrator tells me that his biztalk account needs read and
> > write permissions to the master database of out MSSQL 2000 Enterprice
> > edition, running on a windows 2000 advanced server.
> >
> > Our biztalk developer does not remember that there has been an issue, so
> > he
> > is not doing anything specific on the masters database, in the jobs he is
> > running.
> >
> > Is it for real that biztalk needs this access?
> >
> > --
> > Best regards
> >
> > Mikael
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>> Mikael
>> You have to ask him why does he want to access? What process/s does he
>> run ?
>>
>>
>> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
>> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>> > My fellow administrator tells me that his biztalk account needs read
>> > and
>> > write permissions to the master database of out MSSQL 2000 Enterprice
>> > edition, running on a windows 2000 advanced server.
>> >
>> > Our biztalk developer does not remember that there has been an issue,
>> > so
>> > he
>> > is not doing anything specific on the masters database, in the jobs he
>> > is
>> > running.
>> >
>> > Is it for real that biztalk needs this access?
>> >
>> > --
>> > Best regards
>> >
>> > Mikael
>>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I canâ't.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> > Yes, it has been a while since they did the installation and he just
> > remembers that it needs the permissions, but not why. My question is just
> > if
> > the biztalk application itself needs this access to run?
> >
> > --
> > Best regards
> >
> > Mikael
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mikael
> >>
> >> You have to ask him why does he want to access? What process/s does he
> >> run ?
> >>
> >>
> >>
> >>
> >> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> >> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> >> > My fellow administrator tells me that his biztalk account needs read
> >> > and
> >> > write permissions to the master database of out MSSQL 2000 Enterprice
> >> > edition, running on a windows 2000 advanced server.
> >> >
> >> > Our biztalk developer does not remember that there has been an issue,
> >> > so
> >> > he
> >> > is not doing anything specific on the masters database, in the jobs he
> >> > is
> >> > running.
> >> >
> >> > Is it for real that biztalk needs this access?
> >> >
> >> > --
> >> > Best regards
> >> >
> >> > Mikael
> >>
> >>
> >>
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a Windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a Windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just i
f
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run
?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
--
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
Mikael
Mikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael
|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>
|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>

Bit mask issues...

With this clause:
where
CAST(substring(Column,1,1) as int) & 128 > 0
--
I try to find result where first bit is on.
Is there a better way to write this to speed up ?
Any help much appreciated.
Regards.
Fabri
-Mandrake "A Poma', c'hai 'na faccia...!"
-Pomata "S, se ce n'avevo due gi stavo all'universit... sotto
spirito!"Best get rid of the bit mask column and store the data as separate
attributes or rows. Bit maps are a poor and very inefficient way to
store data.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Best get rid of the bit mask column and store the data as separate
> attributes or rows. Bit maps are a poor and very inefficient way to
> store data.
David, you are right.
But what about when you inherit a large data with these schema?
Do you think, also I'll try to migrate before or later, I can write that
where clause better?
I add that <column> is binary data type.
Regards.
Fabri
-Mandrake "A Poma', c'hai 'na faccia...!"
-Pomata "S, se ce n'avevo due gi stavo all'universit... sotto
spirito!"|||I'd suggest adding a table to store extracted relevant values from that
"overloaded" column. That way the old table remains intact and you gain a ne
w
relation to help you with your queries.
The only change to the old table would be a trigger to help propagate those
values to the new table (for inserted, updated, and deleted rows
respectively).
ML|||>> <column> is binary data type.<<
Quit writing assembly language in SQL.
Create a proper 1NF table. Put each attrivute in its own column, with
appropriate constraints. When you do this, you will probably find that
the data is very dirty. Drop the old table.
Fire the moron that wrote this code. This will improve future data
quality and make programming much easier.|||Firing morons just helps problems migrate somewhere else.
ML|||--CELKO-- wrote:
You are "that" CELKO ? 0__0

> Quit writing assembly language in SQL.
lol

> Create a proper 1NF table.
What does it mean 1NF?

> Fire the moron that wrote this code.
lol...even if I don't know what moron means I can try to figure it
out..:-)))
Regards.
Fabri
-Mandrake "A Poma', c'hai 'na faccia...!"
-Pomata "S, se ce n'avevo due gi stavo all'universit... sotto
spirito!"|||>> Firing morons just helps problems migrate somewhere else. <<
Operative word: "somewhere else" :) As I used to remind people at teh
last university I taught, I don't teach ethics or morals.|||>> You are "that" CELKO ? <<
Eat just one little baby and everyone remembers your name!
First normal form. It means that each column holds one and only one
scalara (atomic) value; not a list, not an array, not a sub-table. Any
book on RDBMS will have a formal definition.
person stupida|||--CELKO-- wrote:
>
> Eat just one little baby and everyone remembers your name!
CELKO, can I use this as signature?
Fabri
(Eat just one little baby and everyone remembers your name! |CELKO|)