Recovery mode 'magically' changing

I have a database that is the source in a logshipping pair (SQL2k,
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with appropriate events and
filter to catch it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:
> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profiler trace to see who it
is.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
>> filter to catch it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "mryan" <coffegrl@.gmail.com> wrote in message
>> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>> >I have a database that is the source in a logshipping pair (SQL2k,
>> > SP3a) that is 'magically' changing from Full Recovery to Simple
>> > Recovery. Is there a trace flag I can turn on to catch this change and
>> > where it is being triggered from?
>> > Obviously this is wrecking havoc on my log shipping.
>> >
>> > thanks
>> > Meredith Ryan
>> >
>

Recovery mode 'magically' changing

I have a database that is the source in a logshipping pair (SQL2k,
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with app
ropriate events and
filter to catch it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:[vbcol=seagreen]
> You have it in the errorlog file. You can also run a profiler trace with a
ppropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server
. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profi
ler trace to see who it
is.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>

Recordset opens as read-only -- WHY?

Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here?
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL
"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here?
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/default...b;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon
|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here?
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...[vbcol=seagreen]
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/default...b;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>

Recordset opens as read-only -- WHY?

Hello,

I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here??

I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).

I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.

In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.

I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.

Thanks,

SL"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here??
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL

This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:

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

However, I don't do much client-side coding, so I might be on completely the
wrong track here.

Simon|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here??

It would have helped if you had posted the query.

I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.

When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.

The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)

--
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 matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.

Problem fixed. Thanks!

Silvio

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
> > Hello,
> > I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
a
> > ODBC data source for a SQL Server 7 database. The application worked
> > properly with a MySQL database, but after swapping it for SQL Server
> > problems emerged. In particular, any calls to CRecordset::AddNew() and
> > CRecordset::Edit() cause an exception to be thrown with the error
message
> > "Recordset is read-only". Stepping through the code for
> CRecordset::Open(),
> > I can see that it indeeds fail to open the recordset with
> SQL_CONCUR_ROWVER
> > and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I
need
> to
> > be able to write to the db!! What is going on here??
> > I call CRecordset::Open() with no SQL string and no options (which
> defaults
> > to full access and not read-only).
> > I am not a SQL Server expert, but I tried playing with the config a
little
> > bit:
> > - The database is NOT set for read-only.
> > - The database option "Restrict Access" is NOT selected.
> > - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE,
DRI)
> > for my app's user on all tables in the db.
> > - My connection string uses the user 'sa', which is the login name
for
> > the user 'dbo'.
> > In the ODBC data source config, I set authentication to SQL Server
> > authentication, and accepted the default for all the other options.
> > I haven't tried using a different class (say, CDaoRecordset) nor do I
plan
> > on doing that. I really need to get this to work with CRecordset. Any
help
> > is appreciated.
> > Thanks,
> > SL
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/defaul...kb;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon

Recordset opens as read-only -- WHY?

Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a mysql database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here'
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here'
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/defaul...kb;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here'
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
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 matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my mysql database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help[vbcol=seagreen]
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/defaul...kb;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

recordset

hi
I have 3 tables:Article,Source and File.
Each article can have multiple filenames.
The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory
The fields of table Source are:SourceID,SourceName
the fields of Table File:ID,ArticleID,Filename
Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate
I obtain a number of recordsets from the above query.
Then for each recordset(Rs1),Let's say for the first recordset
Rs1.MoveFirst
I want to apply this query:
Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID
I want from the above query to have the Filenames corresponding to each Article because in my VB
form I have 2 command buttons:one gives me the article's definition(Date,Source)
and the other gives me the Filename of the current recordset(Article)
The above SQL syntax is it correct?why not just join all 3 tables in your 1st query
select a.ArticleID, a.SourceID, s.SourceName, a.ArticleDate, a.ArticleCategory, f.ID, f.Filename
from Article a inner join Source s
on a.SourceID = s.SourceID
inner join File f
on a.ArticleID = f.ArticleID
order by a.ArticleDate|||KH is correct with the best solution.

Just as an FYI - you cannot use a recordset in a SQL statement. There are, I believe, 3rd party utilities that allow you to join recordsets and query them a little like you are trying to do but I don't know how well these perform (quite badly I would imagine) and I have never come accross a situation where they are necessary.

HTH