Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.ukhttp://www.actongate.co.uk/
______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
https://msdn.microsoft.com/en-us/library/aa975707%28v=vs.71%29.aspx
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.ukhttp://www.actongate.co.uk/
______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.ukhttp://www.actongate.co.uk/
______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.ukhttp://www.actongate.co.uk/
______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.ukhttp://www.actongate.co.uk/
______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
It is always the wise policy to test out these situations, as ultimately, it doesn't matter what SHOULD happen, in theory, as much as what DOES happen, in practice.
On Tue, Mar 1, 2016 at 11:55 AM, Chris Davis chrisd@actongate.co.uk wrote:
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Just to cloud the issue further, I remember a presentation in Prague where (to my surprise) *either* row or table buffering was completely debunked - can't remember which or why - anyone?
Sorry you have to tell the server to keep them or chuck them. Then disconnect because SQL is a disconnect API and nothing like a VFP cursor or table. EVER. :)
Create your connection.
Pass the CRUD statement(s) Commit or Rollback
disconnect.
On Tue, Mar 1, 2016 at 10:55 AM, Chris Davis chrisd@actongate.co.uk wrote:
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting
rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Ok how about if I do this ..
BEGIN TRANSACTION
Stuff
Then another BEGIN TRANSACTION
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 17:15 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Sorry you have to tell the server to keep them or chuck them. Then disconnect because SQL is a disconnect API and nothing like a VFP cursor or table. EVER. :)
Create your connection.
Pass the CRUD statement(s) Commit or Rollback
disconnect.
On Tue, Mar 1, 2016 at 10:55 AM, Chris Davis chrisd@actongate.co.uk wrote:
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting
rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
__ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ____________________________________________________________________ __
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
You already are inside a transaction, why ask for another one?
Let's go back to why you need transactions in the first place.
Doing an update that changes rows across the entire table and I mean a table with a half million rows to 2 million. This update is using a where clause that doesn't have an index available for use and you are doing a table scan on a general column.
In SQL Server we just don't make indexes unless we really need them because the work of inserting into a lot of them acts like a bottleneck in insert statements.
The use of the transaction sets up a shield around your table to protect your process from others as well as others from gaining information about something in process instead of completed and now factual.
Are you using this to capture new PKeys generated and not continuing until it is correct?
If so you might consider creating a stored procedure that receives the insert data as params and will return the proper pkey for the table via identity.
So what are you doing within your transaction?
On Tue, Mar 1, 2016 at 11:17 AM, Chris Davis chrisd@actongate.co.uk wrote:
Ok how about if I do this ..
BEGIN TRANSACTION
Stuff
Then another BEGIN TRANSACTION
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 17:15 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Sorry you have to tell the server to keep them or chuck them. Then disconnect because SQL is a disconnect API and nothing like a VFP cursor or table. EVER. :)
Create your connection.
Pass the CRUD statement(s) Commit or Rollback
disconnect.
On Tue, Mar 1, 2016 at 10:55 AM, Chris Davis chrisd@actongate.co.uk wrote:
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting
rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
__ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ____________________________________________________________________ __
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Never use SQLDISCONNECT when you have an open transaction. That's a recipe for orphaning your SQL process.
I would suggest a review of the SQL functions in the help. There are connection properties related to transactions and functions for SQLCOMMIT, SQLCANCEL, SQLROLLBACK, etc.
--
rk
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Chris Davis Sent: Tuesday, March 01, 2016 11:55 AM To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
I think I may have found the issue, what happens if I
BEGIN TRANSACTION
Append records via VFP Remote View with Buffering at 5.
And then if TABLEUPDATE is .T.
So a SQL DISCONNECT and don't do a COMMIT?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: 01 March 2016 16:52 To: profoxtech@leafe.com Subject: Re: SQL Server Transactions
Do these rows generate a PKey? If so do you need that for secondary table inserts as well?
On Tue, Mar 1, 2016 at 10:50 AM, Chris Davis chrisd@actongate.co.uk wrote:
Thanks for this, but with READ COMMITTED being the SQL Server default, in theory my appended records cannot be seen by other clients until the COMMIT fires?
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:47 To: profoxtech@leafe.com Subject: RE: SQL Server Transactions
Chris, Sorry I may have clouded the issue, I just thought VFP tables and not SQL Server DOH!!!.... it has been a long day!
+1 for what Ted has said re Transaction Isolation Level.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 01 March 2016 16:40 To: ProFox Email List profox@leafe.com Subject: RE: SQL Server Transactions
Buffered records actually contain a -ve record number property which exists until the tableupdate/tablerevert.
See towards the bottom of that link.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Chris Davis Sent: 01 March 2016 16:25 To: profox@leafe.com Subject: SQL Server Transactions
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On Tue, Mar 1, 2016 at 11:24 AM, Chris Davis chrisd@actongate.co.uk wrote:
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine?
They could be, depending on how you set up the Transaction Isolation Level:
https://technet.microsoft.com/en-us/library/ms173763(v=sql.110).aspx
If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
This is a massive grey area because you can preset, like Ted showed, or you can hide intentional.
I worry that you are not setting the rollback or commit when needed and in essence you could be defining a wall to shelter your data from others.
Are you setting only one connection and pushing all statements through it before you close it? Do you ever close a connection to a db in your code?
Found one of our SMEs kept on submitting statements against the db and only committing in the end. He put the table in lock mode until we rolled back a lot of his tests.
On Tue, Mar 1, 2016 at 10:24 AM, Chris Davis chrisd@actongate.co.uk wrote:
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
Chris Davis Acton Gate Systems Limited
We are moving! Please note our new address from 1st Feb Hilton Hall Business Centre Hilton Lane Essington West Midlands WV11 2BQ M: 07966 020473 *: chrisd@actongate.co.uk Webmailto:chrisd@actongate.co.uk%0bWeb: www.actongate.co.uk< http://www.actongate.co.uk/%3E
This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Are you asking whether the VFP View is available on another machine, or whether the SQL Server table is visible? If the View is part of a dbc database then the answer *may* be yes, on the SQL table I'm 99% sure its no. If, for whatever reason you don't want to complete the transaction then surely you issue a rollback?
(... if this is for getting FK for child records then +1 for using a stored proc)
On 01/03/2016 21:54, Chris Davis wrote:
Hi All
If I start a SQL transaction and append records via a VFP View, issue a table update but don't COMMIT the transaction are these records available from another machine? If they are how could I know that they have been appended in a transaction that may well end up getting rolled back?
Thanks
Chris.
<snip>
Lots of variables here...
First of all, in VFP you can set SQL Server transactions to manual or automatic. The default is automatic, so each SQL batch sent from VFP is automatically committed. If you change to manual, you need to either commit or rollback on your own.
If you don't, there's another setting in VFP that defines whether closing a connection with a transaction performs an implicit commit or rollback. Both settings are changed with SQLSETPROP. the name of the first setting is "Transactions", the name of the second one is "DisconnectRollback".
Furthermore, you can start transactions in VFP using BEGIN TRANSACTION, END TRANSACTION and ROLLBACK, and in SQL Server. Both transactions exist simultaneously and are independent of each other. You can commit on SQL Server and rollback in VFP without a problem.
To complicate things, there are two ways to control transaction on SQL Server. You have SQLCOMMIT() and SQLROLLBACK() for the connection handle, or you can send the corresponding SQL Statements with SQLEXEC. Those are BEGIN TRANSACTION, COMMIT, and ROLLBACK.
Nested transactions are handled differently. In VFP you can start a nested transaction. ROLLBACK will only undo changes of that nested transaction. You can rollback an inner transaction and commit the outer one. COMMIT in the inner transaction will only commit to the outer transaction. If you COMMIT on the inner and then ROLLBACK on the outer, nothing gets saved in VFP.
Not so in SQL Server. Here there's only one level of transactions. You can start multiple transactions which increments a transaction count. However, ROLLBACK will always roll back all transactions at once and COMMIT is ignored unless you are on the outer transaction.
What data you read is dependent on the isolation level of the OTHER transaction, not your own one. If another client either changes the isolation level or uses on of the query hints that change the isolation level for a single query then, yes, someone could read all those changes. Everyone else has to wait for you to finish the transaction, even for read operations.
It's different if you were not talking about MS SQL Server (which I kind of assumed here).
Christof, I'm going to have to have a strong coffee now and a rest after reading that last post!!! The old brain cells have refused to take in all the info in one swoop!
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Wollenhaupt, Christof Sent: 02 March 2016 08:30 To: profox@leafe.com Subject: Re: SQL Server Transactions
Lots of variables here...
First of all, in VFP you can set SQL Server transactions to manual or automatic. The default is automatic, so each SQL batch sent from VFP is automatically committed. If you change to manual, you need to either commit or rollback on your own.
If you don't, there's another setting in VFP that defines whether closing a connection with a transaction performs an implicit commit or rollback. Both settings are changed with SQLSETPROP. the name of the first setting is "Transactions", the name of the second one is "DisconnectRollback".
Furthermore, you can start transactions in VFP using BEGIN TRANSACTION, END TRANSACTION and ROLLBACK, and in SQL Server. Both transactions exist simultaneously and are independent of each other. You can commit on SQL Server and rollback in VFP without a problem.
To complicate things, there are two ways to control transaction on SQL Server. You have SQLCOMMIT() and SQLROLLBACK() for the connection handle, or you can send the corresponding SQL Statements with SQLEXEC. Those are BEGIN TRANSACTION, COMMIT, and ROLLBACK.
Nested transactions are handled differently. In VFP you can start a nested transaction. ROLLBACK will only undo changes of that nested transaction. You can rollback an inner transaction and commit the outer one. COMMIT in the inner transaction will only commit to the outer transaction. If you COMMIT on the inner and then ROLLBACK on the outer, nothing gets saved in VFP.
Not so in SQL Server. Here there's only one level of transactions. You can start multiple transactions which increments a transaction count. However, ROLLBACK will always roll back all transactions at once and COMMIT is ignored unless you are on the outer transaction.
What data you read is dependent on the isolation level of the OTHER transaction, not your own one. If another client either changes the isolation level or uses on of the query hints that change the isolation level for a single query then, yes, someone could read all those changes. Everyone else has to wait for you to finish the transaction, even for read operations.
It's different if you were not talking about MS SQL Server (which I kind of assumed here).
-- Christof
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
-
Alan Bourke -
AndyHC -
Chris Davis -
Dave Crozier -
Richard Kaye -
Stephen Russell -
Ted Roche -
Wollenhaupt, Christof