Re: ODBC+MFC; CRecordset::Update() syntax error or access violation

I found the source of the problem.
It seems that the ODBC tries to read the SYSDATABASES to get the info about
MYDB (probably if the database is read-only). When it fails the app carries
on.
When the CRecordset tries to update the record the error is generated
because of the lack of the info.
The problem is that the MYDB_USER is not the master database user so the
ODBC tries to use the
public to get the info.

I added the MYDB_USER to the master database and granted him the SELECT
rights on the sysdatabases.
Now the CRecordset finishes the Update() operation successfully.

Actually the profiler wasn't to useful. I turned on the ODBC tracing and was
able to get some info about
the background ODBC processing.

Dorota

"Dorota Laudon" <DorotaLaudon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BB599441-EEAC-4004-A867-F1CE2AC79F1F@xxxxxxxxxxxxxxxx
> DBMS: SQL Server 2000 sp3 (and sp4)
> Tool: VC++ 6.0 with the MFC
> Connection: ODBC MDAC 2.8
>
> Problem:
> "Syntax error or access violation" error when the public role does not
> select rights on the sysdatabases and the CRecorset::Update() is invoked.
>
> The problem described below uses the Patient table as an example. This
> problem
> persist through the whole application whenever we try to update the data
> using the
> CRecorset::Update();
>
> /////////////////////////////////////////////
> class Patient : public CRecordset
> {
> public:
> Patient(CDatabase* pDatabase = NULL);
> DECLARE_DYNAMIC(Patient)
>
> long m_PATIENT_ID;
> .....
> CString m_PAT_FNAME;
> CString m_PAT_LNAME;
> public:
> virtual CString GetDefaultConnect();
> virtual CString GetDefaultSQL();
> virtual void DoFieldExchange(CFieldExchange* pFX);
>
> CString Patient::GetDefaultSQL()
> {
> return _T("[dbo].[PATIENT]");
> }
>
///////////////////////////////////////////////////////////////////////////
> //We are connecting to the database using ODBC (DSNless connection) TCP/IP
>
> CString CONNECTSTRING_EX;
> CDatabase *currDatabase;
>
>
CONNECTSTRING_EX.Format("DRIVER={SQLServer};DSN='';UID=MYDB_USER;PWD=Pwd;DAT
ABASE=MYDB;" "SERVER=server1;Network=DBMSSOCN;Address=127.0.0.1,1456");
>
> currDatabase->OpenEx( _T(CONNECTSTRING_EX), CDatabase::noOdbcDialog);
>
>
> Patient *us = new Patient(currDatabase);
> us->m_strFilter = "PATIENT_ID = 2" ;
> us->Open(CRecordset::snapshot,NULL,CRecordset::none);
>
> //The recordset opens fine and retrieves the right record.
> //We want to update this record:
>
> us->Edit();
> us->m_PAT_FNAME = "Some_NAME";
> us->Update();
>
> /////////////// PROBLEM /////////////////////////////
> Error Message: syntax error or access violation
> ///////////////////////////////////////////////////////
>
> The user MYDB_USER is a member of the MYDB_USERS role that has select,
> update, delete and insert rights on all users tables in
> MYDB database.
>
> This user is able to update the Patient table from SQLQuery Analyzer.
>
> Everything was working fine until the select permission on the public role
> were removed in all system tables in the master database
> When we investigated this problem and we discovered that the lack of the
> select permission for the public role for sysdatabases table in master
> database is the cause of the error.
>
> We were looking for the symilar problem report but we have not been able
to
> find anything.
>
> It seems that the MFC recordset is trying to acceess the master database
> sysdatabase table.


.

Relevant Pages

  • Re: How to delete a database without name
    ... If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this ... you might just have an inconsistent master database. ... Dim ss As New SQLDMO.SQLServer ...
    (microsoft.public.sqlserver.server)
  • Re: How to delete a database without name
    ... If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this ... you might just have an inconsistent master database. ... Dim ss As New SQLDMO.SQLServer ...
    (microsoft.public.sqlserver)
  • Re: Rights for Access2000 Upsizing-Wizard
    ... It looks like I'm not allowed to change the default database in ODBC. ... in the master database was successfull. ... master and then change the owner and database I'm happy. ...
    (microsoft.public.sqlserver.security)
  • Re: Detach SQL Server Exress 2005 Database on Form Startup
    ... If you want to know if the database is attached, run a query against the sysdatabases table in the master database. ... You can detach a database by running the proc sp_detach_db 'dbname' and attach a database by running ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: Nameless CREATE
    ... The starting point is either an existing database or one that is ... "Describe" involves issuing an SQLDescribeCol function to ODBC ... essentially much the same as character data type apart from all 8 bits ... ALTER TABLE tablename ADD COLUMN BinaryColumn BINARY ...
    (comp.lang.forth)
Loading