Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts

Thursday, March 22, 2012

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

Sunday, March 11, 2012

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/de...on_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/de...on_07_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(7a_8um1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
7_7d6b.asp" target="_blank">http://msdn.microsoft.com/library/d... />
7_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)). The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess). If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release.<br>
<br>
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>
<br>
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement).<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL:<br>
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.<br>
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Wednesday, March 7, 2012

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?
>

Friday, February 24, 2012

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
Larry
Larry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

Thursday, February 16, 2012

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

Friday, February 10, 2012

Bit fields in T-SQL Queries?

I'm creating a query that filters on a bit field that I'm using to represent a boolean value. My query looks something like this:

SELECT * FROM MYTABLE WHERE ACTIVEORDER = 1

And that query works okay--I get all records where 'active order' is true.

Here's my problem--I'd like to make this query more readable by using TRUE or FALSE, instead of 1 or 0. I thought that I had read in BOL that T-SQL can convert the string values TRUE and FALSE to 1 and 0. But I can't get that to work in my query.

Is there a way to use TRUE and FALSE as the filter value for a bit field in a T-SQL query, rather than 1 and o? If so, what does the query look like? Thanks.

AFAIR no, it's not possible. Moreover, I highly recommend you not to mix different languages - it is only confusing. In other words, your query may look more readable for you, but it may look completely enigmatic for another one at the same time.|||Thanks--that makes sense. Just for my own curiousity, any idea why T-SQL doesn't have a boolean type?|||Why, it has - a bit type is perfectly suitable for such a role
For example, there may be some strictly defined things in ANSI SQL which MSFT has to follow. Also, the SEQUEL language (ancestor for current SQL) was developed in the past when every byte in the listing was thrice-counted, so no wonder they decided to use short 1/0 instead of literal constants.|||

--Sure there's a way to make it more readable.

--Just put this at the top of the query:

declare @.True bit,@.False bit

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

--Sorry, I fat-fingered this query. The below is corrected:

declare @.True bit,@.False bit

select @.True = 1, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

So, this works when BOTH True and False are equal to zero?

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

Ennor wrote:

there may be some strictly defined things in ANSI SQL which MSFT has to follow.

None of the SQL Standard says BOOLEAN is one of the datatype in your database.

It says,

1. The value True/False are reserved words as per ANSI Standard

2. Boolean is not a valid datatype for your Table/SQL quries

3. Boolean/Truth values should be TRUE | FALSE | UNKNOWN

4. You can't use the TRUE/FALSE on your query.

|||

You can try

select case when activeorder=1 then 'TRUE'

case when activeorder=0 then FALSE'

end

from mytable

|||

Sorry, I fat fingered that one:

select @.True = 1, @.False = 0

Bit Data Type Question

I have posted in Access Queries with no success. Thought I would try here.
I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
upsize wizard. I have a Access 2003 front end with linked tables to the SQL
backend.
I have a table with a bit data type field in the backend. When I append new
records to that table with the default value for the bit data type set to 0,
my Access front end queries do not return that data. If I manually edit the
data and replace the zero that I see there with another zero, then my queries
will work. Does anyone know why this is true? (I don't want to have to do
that of course!)
Can you use smallint instead bit datatype?
AMB
"tkosel" wrote:

> I have posted in Access Queries with no success. Thought I would try here.
> I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> backend.
> I have a table with a bit data type field in the backend. When I append new
> records to that table with the default value for the bit data type set to 0,
> my Access front end queries do not return that data. If I manually edit the
> data and replace the zero that I see there with another zero, then my queries
> will work. Does anyone know why this is true? (I don't want to have to do
> that of course!)
|||I suppose I could, but would rather know why this is happening. It doesn't
make any sense to me and is driving me crazy!!! (The upsize wizard converted
my Access Yes/No data type to a SQL Bit type.)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you use smallint instead bit datatype?
>
> AMB
> "tkosel" wrote:
|||Additionally,
When I run the following query i get no data returned:
SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
Absences.Hours_Missed, Absences.Absence, Absences.Comments,
Absences.Reason_Code, Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code)="F") AND
((Absences.FMLA_Transaction_Complete)=False));
If I run this query first, then the query above returns the record(s):
UPDATE Absences SET Absences.FMLA_Transaction_Complete =
[FMLA_Transaction_Complete]+0;
Weird eh?
"tkosel" wrote:
[vbcol=seagreen]
> I suppose I could, but would rather know why this is happening. It doesn't
> make any sense to me and is driving me crazy!!! (The upsize wizard converted
> my Access Yes/No data type to a SQL Bit type.)
> "Alejandro Mesa" wrote:
|||This definitely seems to be an access issue, not a SQL Server issue. Some comments:

> If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work.
You can use Query Analyzer and do a SELECT from the table to see if there is any difference.

> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
Above wouldn't even compile in SQL Server, it would return an error message. Unless there is a
column in the table named false. In addition, you enclose a string in double quotes which by default
will also generate an error (unless in above example there exist a column named F). So, probably
Access does something with the query before submitting it to SQL Server. You can use Profiler and
see what is submitted.
You will probably get better response to this in an Access group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tkosel" <tkosel@.discussions.microsoft.com> wrote in message
news:97025794-84BF-44F3-B201-A4AA54515A71@.microsoft.com...[vbcol=seagreen]
> Additionally,
> When I run the following query i get no data returned:
> SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
> Absences.Hours_Missed, Absences.Absence, Absences.Comments,
> Absences.Reason_Code, Absences.FMLA_Transaction_Complete
> FROM Absences
> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
>
> If I run this query first, then the query above returns the record(s):
> UPDATE Absences SET Absences.FMLA_Transaction_Complete =
> [FMLA_Transaction_Complete]+0;
> Weird eh?
> "tkosel" wrote:
|||There are a few problems regarding the bit datatype in SQL Server
tables that are accessed through linked tables from Microsoft Access,
so using a smallint instead of a bit is a good workaround (suggested by
Microsoft, too):
http://support.microsoft.com/kb/280730
http://support.microsoft.com/kb/318882
Razvan
|||On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:

>I have posted in Access Queries with no success. Thought I would try here.
>I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
>upsize wizard. I have a Access 2003 front end with linked tables to the SQL
>backend.
>I have a table with a bit data type field in the backend. When I append new
>records to that table with the default value for the bit data type set to 0,
>my Access front end queries do not return that data. If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work. Does anyone know why this is true? (I don't want to have to do
>that of course!)
Hi tkosel,
From the "tossing around some ideas department":
Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
column in the offending rows is not 0 but NULL? Access will (IIRC)
represent a bit column as a yes/no column and has no way to repressent
the NULL that is also allowed in a SQL Server bit column - maybe it
chooses to represent it as No, though it won't match on a search for
rwos with the bit value equal to No?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
You were correct. In the table view of enterprise manager as well as table
and query views of Access, the value is shown as a zero. However with Query
Analyzer it is shown as a null. I learned a valuable lesson, don't trust
anything other that results garnered by Query Analyzer for sure. Anyhow,
thanks.
"Hugo Kornelis" wrote:

> On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:
>
> Hi tkosel,
> From the "tossing around some ideas department":
> Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
> column in the offending rows is not 0 but NULL? Access will (IIRC)
> represent a bit column as a yes/no column and has no way to repressent
> the NULL that is also allowed in a SQL Server bit column - maybe it
> chooses to represent it as No, though it won't match on a search for
> rwos with the bit value equal to No?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Bit Data Type Question

I have posted in Access Queries with no success. Thought I would try here.
I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
upsize wizard. I have a Access 2003 front end with linked tables to the SQL
backend.
I have a table with a bit data type field in the backend. When I append new
records to that table with the default value for the bit data type set to 0,
my Access front end queries do not return that data. If I manually edit the
data and replace the zero that I see there with another zero, then my querie
s
will work. Does anyone know why this is true? (I don't want to have to do
that of course!)Can you use smallint instead bit datatype?
AMB
"tkosel" wrote:

> I have posted in Access Queries with no success. Thought I would try here
.
> I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> upsize wizard. I have a Access 2003 front end with linked tables to the S
QL
> backend.
> I have a table with a bit data type field in the backend. When I append n
ew
> records to that table with the default value for the bit data type set to
0,
> my Access front end queries do not return that data. If I manually edit t
he
> data and replace the zero that I see there with another zero, then my quer
ies
> will work. Does anyone know why this is true? (I don't want to have to d
o
> that of course!)|||I suppose I could, but would rather know why this is happening. It doesn't
make any sense to me and is driving me crazy!!! (The upsize wizard converte
d
my Access Yes/No data type to a SQL Bit type.)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you use smallint instead bit datatype?
>
> AMB
> "tkosel" wrote:
>|||Additionally,
When I run the following query i get no data returned:
SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
Absences.Hours_Missed, Absences.Absence, Absences.Comments,
Absences.Reason_Code, Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code)="F") AND
((Absences.FMLA_Transaction_Complete)=False));
If I run this query first, then the query above returns the record(s):
UPDATE Absences SET Absences.FMLA_Transaction_Complete =
[FMLA_Transaction_Complete]+0;
Weird eh?
"tkosel" wrote:
[vbcol=seagreen]
> I suppose I could, but would rather know why this is happening. It doesn'
t
> make any sense to me and is driving me crazy!!! (The upsize wizard conver
ted
> my Access Yes/No data type to a SQL Bit type.)
> "Alejandro Mesa" wrote:
>|||This definitely seems to be an access issue, not a SQL Server issue. Some co
mments:

> If I manually edit the
>data and replace the zero that I see there with another zero, then my queri
es
>will work.
You can use Query Analyzer and do a SELECT from the table to see if there is
any difference.

> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
Above wouldn't even compile in SQL Server, it would return an error message.
Unless there is a
column in the table named false. In addition, you enclose a string in double
quotes which by default
will also generate an error (unless in above example there exist a column na
med F). So, probably
Access does something with the query before submitting it to SQL Server. You
can use Profiler and
see what is submitted.
You will probably get better response to this in an Access group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tkosel" <tkosel@.discussions.microsoft.com> wrote in message
news:97025794-84BF-44F3-B201-A4AA54515A71@.microsoft.com...[vbcol=seagreen]
> Additionally,
> When I run the following query i get no data returned:
> SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
> Absences.Hours_Missed, Absences.Absence, Absences.Comments,
> Absences.Reason_Code, Absences.FMLA_Transaction_Complete
> FROM Absences
> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
>
> If I run this query first, then the query above returns the record(s):
> UPDATE Absences SET Absences.FMLA_Transaction_Complete =
> [FMLA_Transaction_Complete]+0;
> Weird eh?
> "tkosel" wrote:
>|||There are a few problems regarding the bit datatype in SQL Server
tables that are accessed through linked tables from Microsoft Access,
so using a smallint instead of a bit is a good workaround (suggested by
Microsoft, too):
http://support.microsoft.com/kb/280730
http://support.microsoft.com/kb/318882
Razvan|||On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:

>I have posted in Access Queries with no success. Thought I would try here.
>I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
>upsize wizard. I have a Access 2003 front end with linked tables to the SQ
L
>backend.
>I have a table with a bit data type field in the backend. When I append ne
w
>records to that table with the default value for the bit data type set to 0
,
>my Access front end queries do not return that data. If I manually edit th
e
>data and replace the zero that I see there with another zero, then my queri
es
>will work. Does anyone know why this is true? (I don't want to have to do
>that of course!)
Hi tkosel,
From the "tossing around some ideas department":
Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
column in the offending rows is not 0 but NULL? Access will (IIRC)
represent a bit column as a yes/no column and has no way to repressent
the NULL that is also allowed in a SQL Server bit column - maybe it
chooses to represent it as No, though it won't match on a search for
rwos with the bit value equal to No?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
You were correct. In the table view of enterprise manager as well as table
and query views of Access, the value is shown as a zero. However with Query
Analyzer it is shown as a null. I learned a valuable lesson, don't trust
anything other that results garnered by Query Analyzer for sure. Anyhow,
thanks.
"Hugo Kornelis" wrote:

> On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:
>
> Hi tkosel,
> From the "tossing around some ideas department":
> Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
> column in the offending rows is not 0 but NULL? Access will (IIRC)
> represent a bit column as a yes/no column and has no way to repressent
> the NULL that is also allowed in a SQL Server bit column - maybe it
> chooses to represent it as No, though it won't match on a search for
> rwos with the bit value equal to No?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Bit Data Type Question

I have posted in Access Queries with no success. Thought I would try here.
I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
upsize wizard. I have a Access 2003 front end with linked tables to the SQL
backend.
I have a table with a bit data type field in the backend. When I append new
records to that table with the default value for the bit data type set to 0,
my Access front end queries do not return that data. If I manually edit the
data and replace the zero that I see there with another zero, then my queries
will work. Does anyone know why this is true? (I don't want to have to do
that of course!)Can you use smallint instead bit datatype?
AMB
"tkosel" wrote:
> I have posted in Access Queries with no success. Thought I would try here.
> I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> backend.
> I have a table with a bit data type field in the backend. When I append new
> records to that table with the default value for the bit data type set to 0,
> my Access front end queries do not return that data. If I manually edit the
> data and replace the zero that I see there with another zero, then my queries
> will work. Does anyone know why this is true? (I don't want to have to do
> that of course!)|||I suppose I could, but would rather know why this is happening. It doesn't
make any sense to me and is driving me crazy!!! (The upsize wizard converted
my Access Yes/No data type to a SQL Bit type.)
"Alejandro Mesa" wrote:
> Can you use smallint instead bit datatype?
>
> AMB
> "tkosel" wrote:
> > I have posted in Access Queries with no success. Thought I would try here.
> >
> > I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> > upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> > backend.
> >
> > I have a table with a bit data type field in the backend. When I append new
> > records to that table with the default value for the bit data type set to 0,
> > my Access front end queries do not return that data. If I manually edit the
> > data and replace the zero that I see there with another zero, then my queries
> > will work. Does anyone know why this is true? (I don't want to have to do
> > that of course!)|||Additionally,
When I run the following query i get no data returned:
SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
Absences.Hours_Missed, Absences.Absence, Absences.Comments,
Absences.Reason_Code, Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code)="F") AND
((Absences.FMLA_Transaction_Complete)=False));
If I run this query first, then the query above returns the record(s):
UPDATE Absences SET Absences.FMLA_Transaction_Complete =[FMLA_Transaction_Complete]+0;
Weird eh?
"tkosel" wrote:
> I suppose I could, but would rather know why this is happening. It doesn't
> make any sense to me and is driving me crazy!!! (The upsize wizard converted
> my Access Yes/No data type to a SQL Bit type.)
> "Alejandro Mesa" wrote:
> > Can you use smallint instead bit datatype?
> >
> >
> > AMB
> >
> > "tkosel" wrote:
> >
> > > I have posted in Access Queries with no success. Thought I would try here.
> > >
> > > I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> > > upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> > > backend.
> > >
> > > I have a table with a bit data type field in the backend. When I append new
> > > records to that table with the default value for the bit data type set to 0,
> > > my Access front end queries do not return that data. If I manually edit the
> > > data and replace the zero that I see there with another zero, then my queries
> > > will work. Does anyone know why this is true? (I don't want to have to do
> > > that of course!)|||This definitely seems to be an access issue, not a SQL Server issue. Some comments:
> If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work.
You can use Query Analyzer and do a SELECT from the table to see if there is any difference.
> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
Above wouldn't even compile in SQL Server, it would return an error message. Unless there is a
column in the table named false. In addition, you enclose a string in double quotes which by default
will also generate an error (unless in above example there exist a column named F). So, probably
Access does something with the query before submitting it to SQL Server. You can use Profiler and
see what is submitted.
You will probably get better response to this in an Access group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tkosel" <tkosel@.discussions.microsoft.com> wrote in message
news:97025794-84BF-44F3-B201-A4AA54515A71@.microsoft.com...
> Additionally,
> When I run the following query i get no data returned:
> SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
> Absences.Hours_Missed, Absences.Absence, Absences.Comments,
> Absences.Reason_Code, Absences.FMLA_Transaction_Complete
> FROM Absences
> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
>
> If I run this query first, then the query above returns the record(s):
> UPDATE Absences SET Absences.FMLA_Transaction_Complete => [FMLA_Transaction_Complete]+0;
> Weird eh?
> "tkosel" wrote:
>> I suppose I could, but would rather know why this is happening. It doesn't
>> make any sense to me and is driving me crazy!!! (The upsize wizard converted
>> my Access Yes/No data type to a SQL Bit type.)
>> "Alejandro Mesa" wrote:
>> > Can you use smallint instead bit datatype?
>> >
>> >
>> > AMB
>> >
>> > "tkosel" wrote:
>> >
>> > > I have posted in Access Queries with no success. Thought I would try here.
>> > >
>> > > I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
>> > > upsize wizard. I have a Access 2003 front end with linked tables to the SQL
>> > > backend.
>> > >
>> > > I have a table with a bit data type field in the backend. When I append new
>> > > records to that table with the default value for the bit data type set to 0,
>> > > my Access front end queries do not return that data. If I manually edit the
>> > > data and replace the zero that I see there with another zero, then my queries
>> > > will work. Does anyone know why this is true? (I don't want to have to do
>> > > that of course!)|||There are a few problems regarding the bit datatype in SQL Server
tables that are accessed through linked tables from Microsoft Access,
so using a smallint instead of a bit is a good workaround (suggested by
Microsoft, too):
http://support.microsoft.com/kb/280730
http://support.microsoft.com/kb/318882
Razvan|||On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:
>I have posted in Access Queries with no success. Thought I would try here.
>I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
>upsize wizard. I have a Access 2003 front end with linked tables to the SQL
>backend.
>I have a table with a bit data type field in the backend. When I append new
>records to that table with the default value for the bit data type set to 0,
>my Access front end queries do not return that data. If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work. Does anyone know why this is true? (I don't want to have to do
>that of course!)
Hi tkosel,
From the "tossing around some ideas department":
Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
column in the offending rows is not 0 but NULL? Access will (IIRC)
represent a bit column as a yes/no column and has no way to repressent
the NULL that is also allowed in a SQL Server bit column - maybe it
chooses to represent it as No, though it won't match on a search for
rwos with the bit value equal to No?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
You were correct. In the table view of enterprise manager as well as table
and query views of Access, the value is shown as a zero. However with Query
Analyzer it is shown as a null. I learned a valuable lesson, don't trust
anything other that results garnered by Query Analyzer for sure. Anyhow,
thanks.
"Hugo Kornelis" wrote:
> On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:
> >I have posted in Access Queries with no success. Thought I would try here.
> >
> >I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> >upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> >backend.
> >
> >I have a table with a bit data type field in the backend. When I append new
> >records to that table with the default value for the bit data type set to 0,
> >my Access front end queries do not return that data. If I manually edit the
> >data and replace the zero that I see there with another zero, then my queries
> >will work. Does anyone know why this is true? (I don't want to have to do
> >that of course!)
> Hi tkosel,
> From the "tossing around some ideas department":
> Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
> column in the offending rows is not 0 but NULL? Access will (IIRC)
> represent a bit column as a yes/no column and has no way to repressent
> the NULL that is also allowed in a SQL Server bit column - maybe it
> chooses to represent it as No, though it won't match on a search for
> rwos with the bit value equal to No?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>