The following is the log from an ODBC Test session against a SQL Server 2000 database.
MDAC Ver. 2.7 [SP1 I think].
The point of interest is that the SQL_ATTR_CONCURRENCY setting is changing from SQL_CONCUR_LOCK (READ/WRITE), to SQL_CONCUR_READ_ONLY after the SQLExecDirect() call [I've also tried SQLPrepare/SQLExecute with the same result].
Can anyone please throw some light onto why this happens, and how to avoid it? [It kills my SQLBulkOperations() call to insert values into the table]
Many thanks.
PeterC.
dbc: szSqlState = "01000", *pfNativeError = 5701, *pcbErrorMsg = 84, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'pctest'."
dbc: szSqlState = "01000", *pfNativeError = 5703, *pcbErrorMsg = 86, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english."
Full Connect:
Full Connect(Use Driver)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'Testpc'.
SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = SQL_CONCUR_LOCK=2,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CURSOR_TYPE=6,
ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_ROW_ARRAY_SIZE=27,
ValuePtr = 1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
SQLExecDirect:
In: Statementhandle = 0x00841978,
StatementText = "select * from pc02",
Statementlength = 20
Return: SQL_SUCCESS_WITH_INFO=1
SQLGetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = 0x0014C3F8,
BufferLength = 300,
StringLengthPtr = 0x0014E968,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
Out:
*ValuePtr = SQL_CONCUR_READ_ONLY = 1,
*StringLengthPtr = 4I should also add that the cursor is changing from SQL_CURSOR_KEYSET_DRIVEN to SQL_CURSOR_STATIC.
PeterC.
Showing posts with label mdac. Show all posts
Showing posts with label mdac. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
ODBC Version with MDAC 2.8?
I am unable to determine what version of Microsoft's SQL Server ODBC
driver comes with MDAC 2.8.
Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
Any ideas?
MDAC 2.8 installs version 2000.85.1022.0
-Sue
On Fri, 16 Apr 2004 18:40:42 GMT, John Smith
<JohnSmith@.hotmail.com> wrote:
>I am unable to determine what version of Microsoft's SQL Server ODBC
>driver comes with MDAC 2.8.
>Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
>version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
>and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
>Any ideas?
driver comes with MDAC 2.8.
Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
Any ideas?
MDAC 2.8 installs version 2000.85.1022.0
-Sue
On Fri, 16 Apr 2004 18:40:42 GMT, John Smith
<JohnSmith@.hotmail.com> wrote:
>I am unable to determine what version of Microsoft's SQL Server ODBC
>driver comes with MDAC 2.8.
>Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
>version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
>and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
>Any ideas?
ODBC Version with MDAC 2.8?
I am unable to determine what version of Microsoft's SQL Server ODBC
driver comes with MDAC 2.8.
Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
Any ideas?MDAC 2.8 installs version 2000.85.1022.0
-Sue
On Fri, 16 Apr 2004 18:40:42 GMT, John Smith
<JohnSmith@.hotmail.com> wrote:
>I am unable to determine what version of Microsoft's SQL Server ODBC
>driver comes with MDAC 2.8.
>Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
>version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
>and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
>Any ideas?
driver comes with MDAC 2.8.
Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
Any ideas?MDAC 2.8 installs version 2000.85.1022.0
-Sue
On Fri, 16 Apr 2004 18:40:42 GMT, John Smith
<JohnSmith@.hotmail.com> wrote:
>I am unable to determine what version of Microsoft's SQL Server ODBC
>driver comes with MDAC 2.8.
>Currently I'm using driver "SQL SERVER" SQLSRV32.DLL, 10/27/2003,
>version 2000.81.9042.00 (with Windows 2000, SQL Server 2000, Access XP)
>and I only want to upgrade to MDAC 2.8 if it updates this ODBC driver.
>Any ideas?
Monday, February 20, 2012
ODBC in MS Access ignores DSN authorization method
I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.
Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:
>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.
|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/downl...s/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:
>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.
Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:
>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.
|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/downl...s/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:
>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
ODBC in MS Access ignores DSN authorization method
I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:
>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:
>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:
>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:
>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)