When creating a system DSN via the ODBC Admistrator dialog...
What is the differecne between "SQL Native Client" and "SQL Server" drivers
listed in the "Create New Data Source" drivers list?
How do they differ?
"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> When creating a system DSN via the ODBC Admistrator dialog...
> What is the differecne between "SQL Native Client" and "SQL
> Server" drivers
> listed in the "Create New Data Source" drivers list?
> How do they differ?
From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."
- Arnie
|||Thanks, that helps... but what about the other one listed? ("SQL Server"
driver)
Which is the appropriate one to use in an IIS/Website scenario in terms of
data access?
"Arnie" wrote:
> "Coder23" <Coder23@.discussions.microsoft.com> wrote in message
> news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> From MSDN:
> "SQL Native Client is a data access technology that is new to
> Microsoft SQL Server 2005, and it is a stand-alone data access
> application programming interface (API) that is used for both OLE
> DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
> driver into one native dynamic-link library (DLL) while also
> providing new functionality above and beyond that supplied by the
> Microsoft Data Access Components (MDAC). SQL Native Client can be
> used to create new applications or enhance existing applications
> that need to take advantage of new SQL Server 2005 features such
> as multiple active result sets (MARS), user-defined data types
> (UDT), query notifications, snapshot isolation, and XML data type
> support."
> - Arnie
>
>
|||"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:A3AAA0F0-1898-4453-8D19-9613BA7FAA08@.microsoft.com...
> Thanks, that helps... but what about the other one listed?
> ("SQL Server"
> driver)
> Which is the appropriate one to use in an IIS/Website scenario
> in terms of
> data access?
>
The 'normal' SQL Server Driver should be fine unless you plan to
use some of the new features that were mentioned for SQL Server
2005. We use SQL Server 2000. I haven't tried to run the new
driver against it yet.
- Arnie
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Sunday, March 11, 2012
"SQL Native Client" / "SQL Server" drivers
When creating a system DSN via the ODBC Admistrator dialog...
What is the differecne between "SQL Native Client" and "SQL Server" drivers
listed in the "Create New Data Source" drivers list?
How do they differ?"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> When creating a system DSN via the ODBC Admistrator dialog...
> What is the differecne between "SQL Native Client" and "SQL
> Server" drivers
> listed in the "Create New Data Source" drivers list?
> How do they differ?
From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."
- Arnie|||Thanks, that helps... but what about the other one listed? ("SQL Server"
driver)
Which is the appropriate one to use in an IIS/Website scenario in terms of
data access?
"Arnie" wrote:
> "Coder23" <Coder23@.discussions.microsoft.com> wrote in message
> news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> From MSDN:
> "SQL Native Client is a data access technology that is new to
> Microsoft SQL Server 2005, and it is a stand-alone data access
> application programming interface (API) that is used for both OLE
> DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
> driver into one native dynamic-link library (DLL) while also
> providing new functionality above and beyond that supplied by the
> Microsoft Data Access Components (MDAC). SQL Native Client can be
> used to create new applications or enhance existing applications
> that need to take advantage of new SQL Server 2005 features such
> as multiple active result sets (MARS), user-defined data types
> (UDT), query notifications, snapshot isolation, and XML data type
> support."
> - Arnie
>
>|||"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:A3AAA0F0-1898-4453-8D19-9613BA7FAA08@.microsoft.com...
> Thanks, that helps... but what about the other one listed?
> ("SQL Server"
> driver)
> Which is the appropriate one to use in an IIS/Website scenario
> in terms of
> data access?
>
The 'normal' SQL Server Driver should be fine unless you plan to
use some of the new features that were mentioned for SQL Server
2005. We use SQL Server 2000. I haven't tried to run the new
driver against it yet.
- Arnie
What is the differecne between "SQL Native Client" and "SQL Server" drivers
listed in the "Create New Data Source" drivers list?
How do they differ?"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> When creating a system DSN via the ODBC Admistrator dialog...
> What is the differecne between "SQL Native Client" and "SQL
> Server" drivers
> listed in the "Create New Data Source" drivers list?
> How do they differ?
From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."
- Arnie|||Thanks, that helps... but what about the other one listed? ("SQL Server"
driver)
Which is the appropriate one to use in an IIS/Website scenario in terms of
data access?
"Arnie" wrote:
> "Coder23" <Coder23@.discussions.microsoft.com> wrote in message
> news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> From MSDN:
> "SQL Native Client is a data access technology that is new to
> Microsoft SQL Server 2005, and it is a stand-alone data access
> application programming interface (API) that is used for both OLE
> DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
> driver into one native dynamic-link library (DLL) while also
> providing new functionality above and beyond that supplied by the
> Microsoft Data Access Components (MDAC). SQL Native Client can be
> used to create new applications or enhance existing applications
> that need to take advantage of new SQL Server 2005 features such
> as multiple active result sets (MARS), user-defined data types
> (UDT), query notifications, snapshot isolation, and XML data type
> support."
> - Arnie
>
>|||"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:A3AAA0F0-1898-4453-8D19-9613BA7FAA08@.microsoft.com...
> Thanks, that helps... but what about the other one listed?
> ("SQL Server"
> driver)
> Which is the appropriate one to use in an IIS/Website scenario
> in terms of
> data access?
>
The 'normal' SQL Server Driver should be fine unless you plan to
use some of the new features that were mentioned for SQL Server
2005. We use SQL Server 2000. I haven't tried to run the new
driver against it yet.
- Arnie
Thursday, March 8, 2012
"Read Only" problem from Access 2003 to SQL Server
I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill
"Read Only" problem from Access 2003 to SQL Server
I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
Ken Wells
President
Smart Computing Solutions, LLC
All it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC
|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
Ken Wells
President
Smart Computing Solutions, LLC
All it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC
|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill
"Read Only" problem from Access 2003 to SQL Server
I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (o
r
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedur
e
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Acce
ss
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data
(or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't ge
t
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 o
n
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 databa
se
> application on PC-2 that needs to modify the SQL data on PC-1. I have tri
ed
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways
of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Acce
ss
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update
to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding update
s
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I trie
d
> to ask Microsoft (using my one free question) but my (valid) Product ID wa
s
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on o
ne
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (o
r
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedur
e
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Acce
ss
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data
(or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't ge
t
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 o
n
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 databa
se
> application on PC-2 that needs to modify the SQL data on PC-1. I have tri
ed
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways
of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Acce
ss
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update
to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding update
s
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I trie
d
> to ask Microsoft (using my one free question) but my (valid) Product ID wa
s
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on o
ne
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill
Thursday, February 9, 2012
"Cannot start more transactions on this session" - SQL 2005 via ADO
Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
"Cannot start more transactions on this session" - SQL 2005 via ADO
Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.
4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.
4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
"Cannot start more transactions on this session" - SQL 2005 via ADO
Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim
"Bulk copy insert failed"
I'm copying data from tables in one server to tables in another via DTS.
This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.
When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.
Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.
But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?
This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.
When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.
Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.
But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?
Subscribe to:
Posts (Atom)
