MSDN - Read-Only Recordset with Microsoft SQL Server

Author

Read-Only Recordset with Microsoft SQL Server

City University Grp17

2004-10-26, 8:55 am

Hello, every body...

I had a problem in creating table in SQL Server.

I had create some table and insert a some of data into the table ( about 200).

Suddenly, it becomes read only ( I am not sure what I have done). Since then
every table I created would become read-only.

Any body can help?

Ronny Ong

2004-10-26, 3:56 pm

The subject of your post indicates that you are trying to update the table
thru a Recordset, which is a client-oriented object.

In order to update tables from a client, the tables must have a primary key
or unique index. You can execute INSERT INTO statements without a primary
key but you cannot create an updateable recordset without a unique way to
identify the records. This applies to all client-server database products,
from all vendors, on all platforms.

"City University Grp17" <City University Grp17@discussions.microsoft.com>
wrote in message news:F409BB43-308D-4F7E-8E93-73C0A48C9267@microsoft.com...
> I had a problem in creating table in SQL Server.
>
> I had create some table and insert a some of data into the table ( about
> 200).
>
> Suddenly, it becomes read only ( I am not sure what I have done). Since
> then
> every table I created would become read-only.

Lawrence Groves

2004-10-26, 3:56 pm

"Ronny Ong" <ronnyong@killspam-bigfoot.com> wrote in message
news:e%23Wsu62uEHA.940@TK2MSFTNGP14.phx.gbl...
> The subject of your post indicates that you are trying to update the table
> thru a Recordset, which is a client-oriented object.
>
> In order to update tables from a client, the tables must have a primary
> key or unique index. You can execute INSERT INTO statements without a
> primary key but you cannot create an updateable recordset without a unique
> way to identify the records. This applies to all client-server database
> products, from all vendors, on all platforms.

Hi Ronny,

I beg to differ. When using SQL Server, ADO uses a clever method of
selecting the original source record when applying an update. The update
statement that it issues gives a where clause that identifies the original
content by specifying every source column's value.... something like:

ColumnA: 100
ColumnB: 200
ColumnC: 300

SQL update issued:

update table_x
set ColumnA = 1100, ColumnB = 1200, ColumnC = 1300
where ColumnA = 100 and ColumnB = 200 and ColumnC = 300

This has two implications:

1. No primary key or unique index is required

2. Data concurrency is upheld since if someone else has changed the row
since it was read, the update will fail.

Sometimes this can be a right pain in the neck but you can change how it
works by setting the 'Update Criteria' property of the recordset object. See
here for more info:

http://support.microsoft.com/defaul...kb;en-us;190727

Loz.

Ronny Ong

2004-10-26, 3:56 pm

If you had actually tested this, you would have realized that you misread KB
190727. Setting the Update Criteria to adCriteriaAllCols does indeed use all
columns in the recordset BUT this still assumes that one or more of those
columns makes up a primary key (or at least a unique index which exists).
The Update Criteria property does NOT allow you to have an updateable
recordset in the complete absence of any unique index.

Here's a script you can test for yourself with ADO and SQL Server
(absolutely any version, any edition, any OS). It matches your example
below, as well as the sample from KB 190727. The long lines will break in
the newsreader so you will have to edit them after copying and pasting.

Set cn = CreateObject("ADODB.Connection")
cn.Open
" PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=pubs;TRUSTED_CONNECTION=yes"
cn.Execute "IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.table_x') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP
TABLE dbo.table_x"
cn.Execute "CREATE TABLE dbo.table_x (ColumnA int, ColumnB int, ColumnC
int)"
cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
cn.Execute "INSERT INTO dbo.table_x VALUES (150, 250, 350)"
cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
Set rst = CreateObject("ADODB.Recordset")

' Set CursorLocation and Update Criteria like in KB 190727 for demonstration
' but they don't really matter because you can comment these lines out and
' the defaults also fail due to non-updateable recordset.

rst.CursorLocation = 3 ' adUseClient but adUseServer fails, too
rst.Properties("Update Criteria") = 1 ' adCriteriaAllCols but
adCriteriaUpdCols fails, too

' In the line below, we use adOpenStatic, adLockOptimistic, adCmdText just
' to match KB 190727 but again these are irrelevant. The defaults fail, too.

rst.Open "SELECT * FROM dbo.table_x", cn ', 3, 3, 1
rst("ColumnA") = 200
rst("ColumnB") = 400
rst("ColumnC") = 600
rst.Update ' BOOM!
rst.Close
cn.Close

"Lawrence Groves" <lgroves@ducost.deleteme.com> wrote in message
news:eleVwH3uEHA.1452@TK2MSFTNGP11.phx.gbl...
> "Ronny Ong" <ronnyong@killspam-bigfoot.com> wrote in message
> news:e%23Wsu62uEHA.940@TK2MSFTNGP14.phx.gbl...
>
> Hi Ronny,
>
> I beg to differ. When using SQL Server, ADO uses a clever method of
> selecting the original source record when applying an update. The update
> statement that it issues gives a where clause that identifies the original
> content by specifying every source column's value.... something like:
>
> ColumnA: 100
> ColumnB: 200
> ColumnC: 300
>
> SQL update issued:
>
> update table_x
> set ColumnA = 1100, ColumnB = 1200, ColumnC = 1300
> where ColumnA = 100 and ColumnB = 200 and ColumnC = 300
>
> This has two implications:
>
> 1. No primary key or unique index is required
>
> 2. Data concurrency is upheld since if someone else has changed the row
> since it was read, the update will fail.
>
> Sometimes this can be a right pain in the neck but you can change how it
> works by setting the 'Update Criteria' property of the recordset object.
> See here for more info:
>
> http://support.microsoft.com/defaul...kb;en-us;190727

City University Grp17

2004-10-27, 3:55 am

The problem is nothing to do with referecrial integrity nor update
constriaint. We are actually dealing with SQL Server (Query Analyzer). The
problem simply appear on every table created had a (read only) indication but
it can be update/insert/delete with any record.

"Ronny Ong" wrote:

> If you had actually tested this, you would have realized that you misread KB
> 190727. Setting the Update Criteria to adCriteriaAllCols does indeed use all
> columns in the recordset BUT this still assumes that one or more of those
> columns makes up a primary key (or at least a unique index which exists).
> The Update Criteria property does NOT allow you to have an updateable
> recordset in the complete absence of any unique index.
>
> Here's a script you can test for yourself with ADO and SQL Server
> (absolutely any version, any edition, any OS). It matches your example
> below, as well as the sample from KB 190727. The long lines will break in
> the newsreader so you will have to edit them after copying and pasting.
>
> Set cn = CreateObject("ADODB.Connection")
> cn.Open
> " PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=pubs;TRUSTED_CONNECTION=yes"
> cn.Execute "IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id =
> object_id(N'dbo.table_x') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP
> TABLE dbo.table_x"
> cn.Execute "CREATE TABLE dbo.table_x (ColumnA int, ColumnB int, ColumnC
> int)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (150, 250, 350)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
> Set rst = CreateObject("ADODB.Recordset")
>
> ' Set CursorLocation and Update Criteria like in KB 190727 for demonstration
> ' but they don't really matter because you can comment these lines out and
> ' the defaults also fail due to non-updateable recordset.
>
> rst.CursorLocation = 3 ' adUseClient but adUseServer fails, too
> rst.Properties("Update Criteria") = 1 ' adCriteriaAllCols but
> adCriteriaUpdCols fails, too
>
> ' In the line below, we use adOpenStatic, adLockOptimistic, adCmdText just
> ' to match KB 190727 but again these are irrelevant. The defaults fail, too.
>
> rst.Open "SELECT * FROM dbo.table_x", cn ', 3, 3, 1
> rst("ColumnA") = 200
> rst("ColumnB") = 400
> rst("ColumnC") = 600
> rst.Update ' BOOM!
> rst.Close
> cn.Close
>
>
> "Lawrence Groves" <lgroves@ducost.deleteme.com> wrote in message
> news:eleVwH3uEHA.1452@TK2MSFTNGP11.phx.gbl...
>
>
>

Lawrence Groves

2004-10-27, 8:55 am

"Ronny Ong" <ronnyong@killspam-bigfoot.com> wrote in message
news:u7Czdt4uEHA.2116@TK2MSFTNGP14.phx.gbl...
> If you had actually tested this, you would have realized that you misread
> KB 190727. Setting the Update Criteria to adCriteriaAllCols does indeed
> use all columns in the recordset BUT this still assumes that one or more
> of those columns makes up a primary key (or at least a unique index which
> exists). The Update Criteria property does NOT allow you to have an
> updateable recordset in the complete absence of any unique index.

I didn't misread the KB aritcle.... it didn't even exist 5/6 years ago when
I cam across this. There was at the time a single refernce to 'Update
Criteria' that I found which solved the problem I had (when I mentioned the
default behavior being a pain in the neck).

I quoted the current KB aricle as a refernce to how ADO works internally,
not specifically to show how to break things when no index exsists, as your
example appears to show.....

> Here's a script you can test for yourself with ADO and SQL Server
> (absolutely any version, any edition, any OS). It matches your example
> below, as well as the sample from KB 190727. The long lines will break in
> the newsreader so you will have to edit them after copying and pasting.
>
> Set cn = CreateObject("ADODB.Connection")
> cn.Open
> " PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=pubs;TRUSTED_CONNECTION=yes"
> cn.Execute "IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id =
> object_id(N'dbo.table_x') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP
> TABLE dbo.table_x"
> cn.Execute "CREATE TABLE dbo.table_x (ColumnA int, ColumnB int, ColumnC
> int)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (150, 250, 350)"
> cn.Execute "INSERT INTO dbo.table_x VALUES (100, 200, 300)"
> Set rst = CreateObject("ADODB.Recordset")
>
> ' Set CursorLocation and Update Criteria like in KB 190727 for
> demonstration
> ' but they don't really matter because you can comment these lines out and
> ' the defaults also fail due to non-updateable recordset.
>
> rst.CursorLocation = 3 ' adUseClient but adUseServer fails, too
> rst.Properties("Update Criteria") = 1 ' adCriteriaAllCols but
> adCriteriaUpdCols fails, too
>
> ' In the line below, we use adOpenStatic, adLockOptimistic, adCmdText just
> ' to match KB 190727 but again these are irrelevant. The defaults fail,
> too.
>
> rst.Open "SELECT * FROM dbo.table_x", cn ', 3, 3, 1
> rst("ColumnA") = 200
> rst("ColumnB") = 400
> rst("ColumnC") = 600
> rst.Update ' BOOM!
> rst.Close
> cn.Close

Fine... But I would seriously like to know how production code has run
***without indices or keys*** for the number of years that it has so far???

(by the way, before the flame, the database uses primary keys generally but
there's a specific need not to in one or two certain cases)

Trust me... its possible to have an updateable recordset based on a table
with no primary key or index. Admitadely, it depends on the cursor type and
the setting of the 'Update Criteria' property, which are certainly not left
at their defaults. I'll check what I use when I get a chance and let you
know.

Loz.

Ronny Ong

2004-10-30, 3:55 am

"Lawrence Groves" <lgroves@ducost.deleteme.com> wrote in message
news:%23ZPAqx$uEHA.2860@TK2MSFTNGP11.phx.gbl...
> Trust me... its possible to have an updateable recordset based on a table
> with no primary key or index. Admitadely, it depends on the cursor type
> and

I think the original poster's response indicates the real misunderstanding
here: He/she did not really mean "Recordset" when he/she said "Recordset."
In Query Analyzer, there is no such thing as a Recordset because you're
executing T-SQL directly. It might have been accurate to say "rowset" or
"result set" but not Recordset.

In your case, I suspect you're not remembering the exact scenario. Your
situation might have involved some mechanism which substituted for a primary
key. I already mentioned that SQL Server will use a unique index or unique
constraint if one of those is available and a primary key is not, but there
are other similar scenarios with products like the Jet engine in Access. If
you create a Linked Table (a TableDef pointing to a remote ODBC data source)
with no primary key, Access will prompt you to select one or more fields
which uniquely identify each record. It then maintains its own client-side
primary key (because Linked Tables only require read access to the remote
databases, so you might not have rights to create a server-side index of any
type). This client-side primary key is transparently managed by Jet, so I've
seen many people who thought they were updating a remote table with no
primary key but when I get them to try re-linking without specifying key
fields, they learn that the table becomes read-only.

The only theoretical way that an updateable recordset "could" be implemented
by any RDBMS server product without a primary key would be to keep a
server-side cursor allocated and lock all tables involved for the entire
life of the recordset. Otherwise, it would be impossible to support
transactions, isolation levels, etc. Maybe something like MySQL would get
away with it, but nothing like an Oracle or SQL Server. Even if you wrote a
database like this, the server-side cursor would have to maintain its own
pointers of some sort, so even though it wouldn't be a physically visible
primary key, it would be another type of substitute.

Lawrence Groves

2004-11-01, 8:55 am

"Ronny Ong" <ronnyong@killspam-bigfoot.com> wrote in message
news:eqP7wHivEHA.4020@TK2MSFTNGP10.phx.gbl...
> "Lawrence Groves" <lgroves@ducost.deleteme.com> wrote in message
> news:%23ZPAqx$uEHA.2860@TK2MSFTNGP11.phx.gbl...
>
> I think the original poster's response indicates the real misunderstanding
> here: He/she did not really mean "Recordset" when he/she said "Recordset."
> In Query Analyzer, there is no such thing as a Recordset because you're
> executing T-SQL directly. It might have been accurate to say "rowset" or
> "result set" but not Recordset.
>
> In your case, I suspect you're not remembering the exact scenario. Your
> situation might have involved some mechanism which substituted for a
> primary key. I already mentioned that SQL Server will use a unique index
> or unique constraint if one of those is available and a primary key is
> not, but there are other similar scenarios with products like the Jet
> engine in Access. If you create a Linked Table (a TableDef pointing to a
> remote ODBC data source) with no primary key, Access will prompt you to
> select one or more fields which uniquely identify each record. It then
> maintains its own client-side primary key (because Linked Tables only
> require read access to the remote databases, so you might not have rights
> to create a server-side index of any type). This client-side primary key
> is transparently managed by Jet, so I've seen many people who thought they
> were updating a remote table with no primary key but when I get them to
> try re-linking without specifying key fields, they learn that the table
> becomes read-only.

Ronny,

This sentence from the KB article implies that what I have said is indeed
possible. And as I have said, it does actually work....

"ADO determines which field, or set of fields, is the primary key and uses
that information to make sure it can find the correct row in the database to
update. If you are going to perform updates with the client cursor engine,
make sure you have a primary key defined in your table. If you don't, you
may accidentally update more rows than you intended."

So what you say about Access appears to be done by ADO automatically, if it
can. That's what I have found. The point is that the update might affect
more than one row because of the columns chosen by ADO. But if you know that
all rows are unique then you don't have the problem.

In this situation, ADO correctly handles updateable rowsets from tables that
have niether a primary key or indices.

Loz.

Sponsored Links