Microsoft KB Archive/253673: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - """ to """) |
||
(One intermediate revision by the same user not shown) | |||
Line 113: | Line 113: | ||
' Open Connect with Admin rights | ' Open Connect with Admin rights | ||
Set cn2 = New ADODB.Connection | Set cn2 = New ADODB.Connection | ||
cn2.ConnectionString = | cn2.ConnectionString = "Provider=SQLOLEDB;database=pubs;User ID=<username>;Password=<strong password>;server=football" | ||
cn2.Open | cn2.Open | ||
'Create a new login | 'Create a new login | ||
cn2.Execute | cn2.Execute "sp_addlogin testuser2" | ||
'Create a new view | 'Create a new view | ||
cn2.Execute | cn2.Execute "CREATE VIEW view_auth2 AS select * from authors" | ||
'Revoke all rights on the table the view is based on for the guest user account | 'Revoke all rights on the table the view is based on for the guest user account | ||
cn2.Execute | cn2.Execute "revoke all on authors from guest" | ||
'Grant all rights on the view for the guest account | 'Grant all rights on the view for the guest account | ||
cn2.Execute | cn2.Execute "GRANT SELECT , INSERT , DELETE , UPDATE ON dbo.view_auth2 TO guest" | ||
'login with view the new login | 'login with view the new login | ||
strConn = | strConn = "Provider=SQLOLEDB;database=pubs;User ID=testuser2;Password=;server=football" | ||
Set cn = New ADODB.Connection | Set cn = New ADODB.Connection | ||
cn.Open strConn | cn.Open strConn | ||
'Open a recordset based on the view | 'Open a recordset based on the view | ||
strSQL = | strSQL = "select * from view_auth2" | ||
Set rs = New ADODB.Recordset | Set rs = New ADODB.Recordset | ||
rs.CursorLocation = adUseClient | rs.CursorLocation = adUseClient | ||
Line 136: | Line 136: | ||
'Change a value in the recordset and attempt to update the view | 'Change a value in the recordset and attempt to update the view | ||
rs!au_lname = rs!au_lname & | rs!au_lname = rs!au_lname & "z" | ||
rs.Update 'Errors here because the action query is created against the base table | rs.Update 'Errors here because the action query is created against the base table | ||
Latest revision as of 13:52, 21 July 2020
Article ID: 253673
Article Last Modified on 11/7/2003
APPLIES TO
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q253673
SYMPTOMS
Trying to issue an Update or Resync command on a recordset based on a SQL Server view might generate the following error:
CAUSE
When ADO requests metadata for each column in the recordset (column name, table name, and so forth), SQL Server returns the base table name or names rather than the name of the view itself. ADO then uses this information to generate queries to resynchronize and update data.
RESOLUTION
To resolve this problem, grant users the appropriate permissions to the underlying base tables that the view is based upon. Typically, users need at least SELECT permission on the tables.
The Requery method can be used in place of the Resync method if it is necessary. However, there is a performance penalty with this option.
If you are using SQL Server 2000, create the view with the View_MetaData clause of the Create View command. For this syntax and other information, see the SQL Server 2000 documentation.
STATUS
This behavior is by design beginning with SQL Server version 7.0.
MORE INFORMATION
If a provider needs metadata information on a SQL statement, it queries the datasource to see what methods are available to it to request the metadata. If the data source responds with no native method of returning the metadata, the provider asks the Client Cursor Engine (CCE) to parse the SQL SELECT statement to figure out the metadata information long hand rather than relying on the more efficient method that was not available through the datasource.
This code might work with other data sources. This is because many data sources do not have such built-in optimized methods of returning metadata, like SQL Server, regarding a SQL statement.
Steps to Reproduce Behavior
- Start a new Visual Basic Standard EXE project. Form1 is added by default.
- From the Project menu, click References, and select the Microsoft ActiveX Data Objects.
Paste the following code into the load event of Form1 and modify the ConnectionString property accordingly:
Note You must change User ID=<username> and Password= to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.Dim cn As ADODB.Connection Dim cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim strConn As String, strSQL As String ' Open Connect with Admin rights Set cn2 = New ADODB.Connection cn2.ConnectionString = "Provider=SQLOLEDB;database=pubs;User ID=<username>;Password=<strong password>;server=football" cn2.Open 'Create a new login cn2.Execute "sp_addlogin testuser2" 'Create a new view cn2.Execute "CREATE VIEW view_auth2 AS select * from authors" 'Revoke all rights on the table the view is based on for the guest user account cn2.Execute "revoke all on authors from guest" 'Grant all rights on the view for the guest account cn2.Execute "GRANT SELECT , INSERT , DELETE , UPDATE ON dbo.view_auth2 TO guest" 'login with view the new login strConn = "Provider=SQLOLEDB;database=pubs;User ID=testuser2;Password=;server=football" Set cn = New ADODB.Connection cn.Open strConn 'Open a recordset based on the view strSQL = "select * from view_auth2" Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText 'Change a value in the recordset and attempt to update the view rs!au_lname = rs!au_lname & "z" rs.Update 'Errors here because the action query is created against the base table 'or to see potential problems with Resync, comment the previous 2 lines and uncomment the following 'rs.Resync adAffectCurrent 'errors here
- Run the project.
Keywords: kbserver kbdatabase kbprb KB253673