Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Showing posts with label ms-sql. Show all posts
Showing posts with label ms-sql. Show all posts
Friday, March 30, 2012
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETsql
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETsql
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does is
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||As a basic transaction question, why did the second one block if the
first hadn't changed any rows and the query was done executing?|||pb648174 (google@.webpaul.net) writes:
> As a basic transaction question, why did the second one block if the
> first hadn't changed any rows and the query was done executing?
Because that transaction had isolation level serializable. This means
that if you resubmit a query with in the transaction, you should
essentially get the same results. Therefore writes must be blocked
until the reader transaction commits.
In the default isolation level "Read Committed", the same query in
the same transaction can give different results, and thus locks can
be released once data has been read.
There is also "Repeatable Read" which is between Serializable and Read
Committed. With Repeatable Read, reading the same row twice should yield
the same result. However, a for a range query that is resubmitted newly
entered rows may appear, so called "phantom inserts".
Finally there is "read uncommitted" in which no locks are taken for
reading at all, and thus you can get to read uncommitted data.
And, oh, in SQL 2005, there is a new level called snapshot. The result
is essentially the same as in serializable, but instead of locking
rows, old versions of changed rows are added to a snapshot. In this
way readers do not block writers. Also, the reader gets a wholly
consistent view of the database at a certain point in time, which you
in fact cannot achieve with Serializable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It seems that the serializable should just not "get" the updated rows
when it does queries, not lock the table for everyone else... It seems
like that is what the snapshot is I guess.|||pb648174 (google@.webpaul.net) writes:
> It seems that the serializable should just not "get" the updated rows
> when it does queries, not lock the table for everyone else... It seems
> like that is what the snapshot is I guess.
Without a snapshot like there is an Oracle and Rdb, it's difficult to
do serializable without blocking writers. A snapshot effectively means
that there is more than once instance of the data, and in SQL Server
there is only one single.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, March 9, 2012
Possible to Insert Records into ODBC Data Source from Stored Procedure?
I would like to insert records into a non MS-SQL database that I can connect
to with an ODBC data source.
This is possible? If so, can you give me an example of how to connect?
Thanks,
MikeYes, it is possible, and there are numerous ways of connecting to an ODBC
data source. If you are using C#, take a look at class OdbcConnection in the
System.Data.Odbc namespace --
http://msdn2.microsoft.com/en-us/library/at2sk77y.aspx.
Linchi
"Mike" wrote:
> I would like to insert records into a non MS-SQL database that I can connect
> to with an ODBC data source.
> This is possible? If so, can you give me an example of how to connect?
> Thanks,
> Mike
>
>
to with an ODBC data source.
This is possible? If so, can you give me an example of how to connect?
Thanks,
MikeYes, it is possible, and there are numerous ways of connecting to an ODBC
data source. If you are using C#, take a look at class OdbcConnection in the
System.Data.Odbc namespace --
http://msdn2.microsoft.com/en-us/library/at2sk77y.aspx.
Linchi
"Mike" wrote:
> I would like to insert records into a non MS-SQL database that I can connect
> to with an ODBC data source.
> This is possible? If so, can you give me an example of how to connect?
> Thanks,
> Mike
>
>
Wednesday, March 7, 2012
Possible SQL Bug with clustered indexes and DOP > 1
Hi there,
I think I have identified a possible bug in MS-SQL 2K....
The information below has been obfuscated for provacy reasons, however
the principal is the same...
I have a table which has several fields (nothing interesting). The
table has approx 1M rows. One of the fields [HR_POSITION] is of
datatype varchar(2).
I create a clustered index using several of the fields with
[HR_POSITION] at the top of the clustered index. Lets say the possible
values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
I issue a simple SELECT query as follows:-
SELECT [HR_POSITION],
[FIELD1],
[FIELD2],
[FIELD3],
[FIELD4],
[FIELD5],
[FIELD6],
[FIELD7],
[FIELD8],
[FIELD9],
[FIELD10]
from TABLE_BROKEN
WHERE [FIELD1] = 'Hello'
and [HR_POSITION] != 'L1'
ORDER BY [HR_POSITION]
Believe it or not I get L1 values in the field [HR_POSITION] in the
result set. If include the OPTION (MAXDOP 1) at the end I get a
different result set which appears to be accurate.
Has nay one seen this before, is it documented, am I the first, do I
get a free T-Shirt?
I have replicated the bug on three different machines and have
discussed it with our team here, and no one can seem to come with an
answer.
We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4<justin.parry-okeden@.cba.com.au> wrote in message
news:1137382255.509303.42040@.g14g2000cwa.googlegroups.com...
> Hi there,
> I think I have identified a possible bug in MS-SQL 2K....
> The information below has been obfuscated for provacy reasons, however
> the principal is the same...
> I have a table which has several fields (nothing interesting). The
> table has approx 1M rows. One of the fields [HR_POSITION] is of
> datatype varchar(2).
> I create a clustered index using several of the fields with
> [HR_POSITION] at the top of the clustered index. Lets say the possible
> values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
> I issue a simple SELECT query as follows:-
> SELECT [HR_POSITION],
> [FIELD1],
> [FIELD2],
> [FIELD3],
> [FIELD4],
> [FIELD5],
> [FIELD6],
> [FIELD7],
> [FIELD8],
> [FIELD9],
> [FIELD10]
> from TABLE_BROKEN
> WHERE [FIELD1] = 'Hello'
> and [HR_POSITION] != 'L1'
> ORDER BY [HR_POSITION]
> Believe it or not I get L1 values in the field [HR_POSITION] in the
> result set. If include the OPTION (MAXDOP 1) at the end I get a
> different result set which appears to be accurate.
> Has nay one seen this before, is it documented, am I the first, do I
> get a free T-Shirt?
> I have replicated the bug on three different machines and have
> discussed it with our team here, and no one can seem to come with an
> answer.
> We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4
>
Is it this issue?
http://support.microsoft.com/kb/814509/en-us
Can you replicate the issue on SQL 2000 SP4?
David|||Hi there David,
After extensive testing last night I came to the following
conclusions...
(*) If I change the datatype of the first member in the list of fields
for the index to a char from a varchar the bug goes away
(*) If I leave the datatype of the first member in the list of fields
for the index as varchar but set the last member of the list to not
nullable the bug goes away.
(*) If I don't change anything and apply sp4 the bug goes away.
I think there is at least two VERY NASTY bugs going on here which have
been addressed in some capacity in SP4.
JPO
I think I have identified a possible bug in MS-SQL 2K....
The information below has been obfuscated for provacy reasons, however
the principal is the same...
I have a table which has several fields (nothing interesting). The
table has approx 1M rows. One of the fields [HR_POSITION] is of
datatype varchar(2).
I create a clustered index using several of the fields with
[HR_POSITION] at the top of the clustered index. Lets say the possible
values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
I issue a simple SELECT query as follows:-
SELECT [HR_POSITION],
[FIELD1],
[FIELD2],
[FIELD3],
[FIELD4],
[FIELD5],
[FIELD6],
[FIELD7],
[FIELD8],
[FIELD9],
[FIELD10]
from TABLE_BROKEN
WHERE [FIELD1] = 'Hello'
and [HR_POSITION] != 'L1'
ORDER BY [HR_POSITION]
Believe it or not I get L1 values in the field [HR_POSITION] in the
result set. If include the OPTION (MAXDOP 1) at the end I get a
different result set which appears to be accurate.
Has nay one seen this before, is it documented, am I the first, do I
get a free T-Shirt?
I have replicated the bug on three different machines and have
discussed it with our team here, and no one can seem to come with an
answer.
We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4<justin.parry-okeden@.cba.com.au> wrote in message
news:1137382255.509303.42040@.g14g2000cwa.googlegroups.com...
> Hi there,
> I think I have identified a possible bug in MS-SQL 2K....
> The information below has been obfuscated for provacy reasons, however
> the principal is the same...
> I have a table which has several fields (nothing interesting). The
> table has approx 1M rows. One of the fields [HR_POSITION] is of
> datatype varchar(2).
> I create a clustered index using several of the fields with
> [HR_POSITION] at the top of the clustered index. Lets say the possible
> values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
> I issue a simple SELECT query as follows:-
> SELECT [HR_POSITION],
> [FIELD1],
> [FIELD2],
> [FIELD3],
> [FIELD4],
> [FIELD5],
> [FIELD6],
> [FIELD7],
> [FIELD8],
> [FIELD9],
> [FIELD10]
> from TABLE_BROKEN
> WHERE [FIELD1] = 'Hello'
> and [HR_POSITION] != 'L1'
> ORDER BY [HR_POSITION]
> Believe it or not I get L1 values in the field [HR_POSITION] in the
> result set. If include the OPTION (MAXDOP 1) at the end I get a
> different result set which appears to be accurate.
> Has nay one seen this before, is it documented, am I the first, do I
> get a free T-Shirt?
> I have replicated the bug on three different machines and have
> discussed it with our team here, and no one can seem to come with an
> answer.
> We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4
>
Is it this issue?
http://support.microsoft.com/kb/814509/en-us
Can you replicate the issue on SQL 2000 SP4?
David|||Hi there David,
After extensive testing last night I came to the following
conclusions...
(*) If I change the datatype of the first member in the list of fields
for the index to a char from a varchar the bug goes away
(*) If I leave the datatype of the first member in the list of fields
for the index as varchar but set the last member of the list to not
nullable the bug goes away.
(*) If I don't change anything and apply sp4 the bug goes away.
I think there is at least two VERY NASTY bugs going on here which have
been addressed in some capacity in SP4.
JPO
Subscribe to:
Posts (Atom)