Many thanks for you quick reply, I will investigate the points you have raised.
I know little about connections to SQL in all honesty. I just set up a connection originally in the easiest way, it worked, so I left it. So the thought of changing those connections settings for pooling and disconnected is a little daunting as they are concepts I do not really understand. But I will read up on them and try to understand.
Many thanks,
Trevor
Search found 12 matches
- Wed 05 Nov 2008 08:57
- Forum: SQL Server Data Access Components
- Topic: "Connection failure"
- Replies: 2
- Views: 1533
- Mon 03 Nov 2008 09:00
- Forum: SQL Server Data Access Components
- Topic: "Connection failure"
- Replies: 2
- Views: 1533
"Connection failure"
Hi Devart Support,
I need to just check something with you if you don't mind.......
The software I have developed happily works for a week or so without problem. Then, suddenly, with no warning, each user starts to get "connection failure" messages. I have tried to find where the exact error occurs but it doesn't seem to be specific. And once it occurs, we continue to get the same problem until the SQL server is rebooted and then it goes away.
The fact that I cannot pinpoint any specific area in the code where this is occuring and it goes away after rebooting the server points to me that this is a server problem esp as our server seems to be very low on resources at the time the connection failure occurs.
I was just hoping for your thoughts on this. Is it something I could have done in code or in the general connection that would cause such a problem or would you agree that it is more likely to be something wrong with the server?
Thanks,
Trevor
I need to just check something with you if you don't mind.......
The software I have developed happily works for a week or so without problem. Then, suddenly, with no warning, each user starts to get "connection failure" messages. I have tried to find where the exact error occurs but it doesn't seem to be specific. And once it occurs, we continue to get the same problem until the SQL server is rebooted and then it goes away.
The fact that I cannot pinpoint any specific area in the code where this is occuring and it goes away after rebooting the server points to me that this is a server problem esp as our server seems to be very low on resources at the time the connection failure occurs.
I was just hoping for your thoughts on this. Is it something I could have done in code or in the general connection that would cause such a problem or would you agree that it is more likely to be something wrong with the server?
Thanks,
Trevor
- Mon 03 Nov 2008 08:51
- Forum: SQL Server Data Access Components
- Topic: 'dbo' disappeared from table and stored procedure components
- Replies: 5
- Views: 2567
Many thanks for your reply, and the reassurance that the code will work if not changed.
Can I just check...... Am I right in saying then that it isn't really a correct thing to do to call a SQL function directly but rather they are really only meant to be used in other SQL objects? Is this why you no longer allow direct access to these functions? In fact it isn't really to much of a problem as I simply encapsulate the functions within stored procedures and access them from there. I was just wondering if it wasn't a correct thing to do to access them directly from code.
Many thanks,
Trevor
Can I just check...... Am I right in saying then that it isn't really a correct thing to do to call a SQL function directly but rather they are really only meant to be used in other SQL objects? Is this why you no longer allow direct access to these functions? In fact it isn't really to much of a problem as I simply encapsulate the functions within stored procedures and access them from there. I was just wondering if it wasn't a correct thing to do to access them directly from code.
Many thanks,
Trevor
- Fri 24 Oct 2008 13:41
- Forum: SQL Server Data Access Components
- Topic: 'dbo' disappeared from table and stored procedure components
- Replies: 5
- Views: 2567
Hi Challenger,
Thanks for the explanation re tables and it's good to know I don't have to go through and change them all manually!
On the stored procedures, the problem was slightly different. They were suffixed with a ;1 or a ;0 (no .dbo prefix). It seemed that ;1 was for stored procedures and ;0 was appended to scalarvalued functions. Examples: we had a stored procedure GetCustomerHeader, this was stored as GetCustomerHeader;1 in the storedprocedure name property and now is simply GetCustomerHeader. And we had a function called GetCategory which previously was stored as GetCategory;0 and now we cannot pick this up at design time and store it in the storedprocedure property at all.
The ;1 and ;0 seemed to be dropped now.
Is this another change of behaviour? If it is, how can I now pick up and execute the functions? Or will I have to recreate them as stored procedures, which I guess is easy enough to do.
Thanks again for you prompt support, which is always appreciated.
Trevor
Thanks for the explanation re tables and it's good to know I don't have to go through and change them all manually!
On the stored procedures, the problem was slightly different. They were suffixed with a ;1 or a ;0 (no .dbo prefix). It seemed that ;1 was for stored procedures and ;0 was appended to scalarvalued functions. Examples: we had a stored procedure GetCustomerHeader, this was stored as GetCustomerHeader;1 in the storedprocedure name property and now is simply GetCustomerHeader. And we had a function called GetCategory which previously was stored as GetCategory;0 and now we cannot pick this up at design time and store it in the storedprocedure property at all.
The ;1 and ;0 seemed to be dropped now.
Is this another change of behaviour? If it is, how can I now pick up and execute the functions? Or will I have to recreate them as stored procedures, which I guess is easy enough to do.
Thanks again for you prompt support, which is always appreciated.
Trevor
- Fri 24 Oct 2008 11:18
- Forum: SQL Server Data Access Components
- Topic: 'dbo' disappeared from table and stored procedure components
- Replies: 5
- Views: 2567
'dbo' disappeared from table and stored procedure components
Hi,
This is kind of a strange one that I cannot work out why it has happned.
I have changed nothing (as far as I am aware) in the connection parameters. And I've checked with the person who looks after the server and he says nothing in the configuration has changed there either.
But.....
I have not had to go and edit a table or stored procedure definition for a while. But today I have to and when I have gone into (for example) a table component definition, the table name is set to dbo.customers but when I drop down the list of tables within the database to choose from, it doesn't default to any table because all the table names listed do not have the 'dbo.' prefix. I can reselect the name without the prefix and save it and go back in and then everything is fine.
On stored procedures, the problem is slightly different but presumably for the same reason. On SPs the original name has a ; and a number at the end e.g. getcustomer;1 - in the list now to select from there is no ';1' suffix.
But under what conditions would the 'dbo.' prefix and the ';1' suffix disappear?
If all these need changing, I have a lot of them to do. So I thought maybe I could do it from the text view. But this is unusual to. For example, before I changed it (and when it could not reconcile the name to the list of possibles in th drop down), the text for the a particular stored procedure looked like this....
object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch;1'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch;1}')
Left = 320
Top = 32
ParamData =
end
If I go into the StoredProcName property and reselect the SQL procedure, the text view is then changed to:
object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch }')
Left = 320
Top = 32
ParamData =
CommandStoredProcName = 'GetReadyForDespatch'
end
Does this point to any kind of problem or answer as to what is happening?
The only remote possibility that I can think of is that I have upgraded from 4.35 to 4.5, but I assume this wouldn't make a difference or it would be in the install and upgrade notes?
Do I have to go through and change all these to point to something that exists in the lists that drop down or do you think this won't cause a problem for now?
Many thanks,
Trevor
This is kind of a strange one that I cannot work out why it has happned.
I have changed nothing (as far as I am aware) in the connection parameters. And I've checked with the person who looks after the server and he says nothing in the configuration has changed there either.
But.....
I have not had to go and edit a table or stored procedure definition for a while. But today I have to and when I have gone into (for example) a table component definition, the table name is set to dbo.customers but when I drop down the list of tables within the database to choose from, it doesn't default to any table because all the table names listed do not have the 'dbo.' prefix. I can reselect the name without the prefix and save it and go back in and then everything is fine.
On stored procedures, the problem is slightly different but presumably for the same reason. On SPs the original name has a ; and a number at the end e.g. getcustomer;1 - in the list now to select from there is no ';1' suffix.
But under what conditions would the 'dbo.' prefix and the ';1' suffix disappear?
If all these need changing, I have a lot of them to do. So I thought maybe I could do it from the text view. But this is unusual to. For example, before I changed it (and when it could not reconcile the name to the list of possibles in th drop down), the text for the a particular stored procedure looked like this....
object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch;1'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch;1}')
Left = 320
Top = 32
ParamData =
end
If I go into the StoredProcName property and reselect the SQL procedure, the text view is then changed to:
object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch }')
Left = 320
Top = 32
ParamData =
CommandStoredProcName = 'GetReadyForDespatch'
end
Does this point to any kind of problem or answer as to what is happening?
The only remote possibility that I can think of is that I have upgraded from 4.35 to 4.5, but I assume this wouldn't make a difference or it would be in the install and upgrade notes?
Do I have to go through and change all these to point to something that exists in the lists that drop down or do you think this won't cause a problem for now?
Many thanks,
Trevor
- Fri 24 Oct 2008 10:45
- Forum: SQL Server Data Access Components
- Topic: Advice on lock and lock timeout handling
- Replies: 2
- Views: 2194
- Thu 16 Oct 2008 09:14
- Forum: SQL Server Data Access Components
- Topic: Advice on lock and lock timeout handling
- Replies: 2
- Views: 2194
Advice on lock and lock timeout handling
Hi Devart Team,
I thought I would ask here if you could provide some useful pointers on how to handle locks safely.
We have developed a multiuser system where we can have up to 5 people putting orders on at the same time. Due to the complexity of the orders, and the need to collate data from various places, it can take 5-10 seconds to complete the save. Hence the need for the transactions which you kindly helped me to get working in a previous thread.
I have found when we are very busy and sales people are saving orders regularly, we occasionallly get lock timeouts which is a bit of a worry as I gather this means the transaction might not rollback on the error.
I am pretty new to SQL and previously used Paradox which was a complete bodge up when it came to locking.
What I am looking for is some guidance on how to handle locking correctly so that it doesn't cause problems with the data. And I thought maybe with your expertise in SQL and your component you'd be the best to ask for some pointers
One thing I have done to try to cure this is to increase the locktimeout value in the connection options. But this kind of seems like a workaround rather than a proper fix. Do you think this is sensible to do this and will it help? By default, I think the timeout is 2 seconds. So with a possible 10 second save I guess is was exceeding the timeout. So upped the timeout to 20 seconds to give time for one save to complete. We are looking at reducing the time it takes also to do the save.
Is there are better way (I'm sure there is!) or this a valid method?
Is there anyway to check for a lock condition? Or how can I properly handle the exception when a lock timeout occurs?
Any advice or examples would be appreciated,
Many thanks,
Trevor
I thought I would ask here if you could provide some useful pointers on how to handle locks safely.
We have developed a multiuser system where we can have up to 5 people putting orders on at the same time. Due to the complexity of the orders, and the need to collate data from various places, it can take 5-10 seconds to complete the save. Hence the need for the transactions which you kindly helped me to get working in a previous thread.
I have found when we are very busy and sales people are saving orders regularly, we occasionallly get lock timeouts which is a bit of a worry as I gather this means the transaction might not rollback on the error.
I am pretty new to SQL and previously used Paradox which was a complete bodge up when it came to locking.
What I am looking for is some guidance on how to handle locking correctly so that it doesn't cause problems with the data. And I thought maybe with your expertise in SQL and your component you'd be the best to ask for some pointers
One thing I have done to try to cure this is to increase the locktimeout value in the connection options. But this kind of seems like a workaround rather than a proper fix. Do you think this is sensible to do this and will it help? By default, I think the timeout is 2 seconds. So with a possible 10 second save I guess is was exceeding the timeout. So upped the timeout to 20 seconds to give time for one save to complete. We are looking at reducing the time it takes also to do the save.
Is there are better way (I'm sure there is!) or this a valid method?
Is there anyway to check for a lock condition? Or how can I properly handle the exception when a lock timeout occurs?
Any advice or examples would be appreciated,
Many thanks,
Trevor
- Tue 14 Oct 2008 13:46
- Forum: SQL Server Data Access Components
- Topic: SDAC connection error - "manual or distributed mode"?
- Replies: 9
- Views: 7034
Hi Challenger,
I think I owe you an apology and an awful lot of thanks......
Following your persuading me to persist with the investigation into unfectched rows, I was still convinced this wasn't the problem as I couldn't find any data type in the data module that had 'fetch all' properties set to false.
But following your convincing me to persist with this, I tracked each active data type connecting to MSConnection and found that one (hardly used of course) table had a FetchAll and FetchRows set in code (but not at design). I think this was done early on in the development to allow it to be used from home (i.e. fetching 30 rows rather than many 1000's). At the time this was switched so it could be turned on and off with a command parameter, but that code switch was removed but the properties were still set in code. Ooops.
So my apologies (huge ones!) and much, much thanks for persisting with me along those lines to get to the bottom of the problem that was ultimately mine.
Many thanks once again for your time and patience in helping me with this and I am relieved we can revert to transaction processing once again.
Thank you,
Trevor
I think I owe you an apology and an awful lot of thanks......
Following your persuading me to persist with the investigation into unfectched rows, I was still convinced this wasn't the problem as I couldn't find any data type in the data module that had 'fetch all' properties set to false.
But following your convincing me to persist with this, I tracked each active data type connecting to MSConnection and found that one (hardly used of course) table had a FetchAll and FetchRows set in code (but not at design). I think this was done early on in the development to allow it to be used from home (i.e. fetching 30 rows rather than many 1000's). At the time this was switched so it could be turned on and off with a command parameter, but that code switch was removed but the properties were still set in code. Ooops.
So my apologies (huge ones!) and much, much thanks for persisting with me along those lines to get to the bottom of the problem that was ultimately mine.
Many thanks once again for your time and patience in helping me with this and I am relieved we can revert to transaction processing once again.
Thank you,
Trevor
- Tue 14 Oct 2008 12:08
- Forum: SQL Server Data Access Components
- Topic: SDAC connection error - "manual or distributed mode"?
- Replies: 9
- Views: 7034
Okay.... if I put in a check beforehand that checks for open datasets, there are 10 or so. If I close these active connections in the 'check' loop before starting the transaction then the transaction is okay and no error is produced on the first 'active:=true'.
But, the problem with this is I need those other tables open to accrue the data I need as part of the main 'save'. Obviously, if I close these tables (and they are tables rather than stored procedures/queries) I get an error because the table is closed. I can't open these other tables after the 'starttransaction' because these tables are preloaded when the program is started - they are needed throughout the process and would be a significant overhead should we keep closing and re-opening the tables.
What has changed in this respect since 4.35 because in theat version of SDAC we didn't have this problem? It was only when we were just about to go 'live' with the system of which SDAC is part that we thoght it would be wise to update SDAC to the latest version. From that point the transaction encompassing code no longer worked but produced the error we are discussing.
So we have had to comment out the transaction parts of the code which isn't ideal as we have areas where a save can mean updating 4-5 tables and transactions are ideal for rolling back all the changes should any single one fail. For example, when we save an order, we have to update the order header, order lines, payments, order notes and customer notes table. With transactions, should any fail, we can rollback the whole lot, which is great. Without transactions (as we have it now), although it works, it is not ideal as the last update could fail for some reason and then everything is out of sync.
Thanks for your continued help, appreciated.
Trevor.
But, the problem with this is I need those other tables open to accrue the data I need as part of the main 'save'. Obviously, if I close these tables (and they are tables rather than stored procedures/queries) I get an error because the table is closed. I can't open these other tables after the 'starttransaction' because these tables are preloaded when the program is started - they are needed throughout the process and would be a significant overhead should we keep closing and re-opening the tables.
What has changed in this respect since 4.35 because in theat version of SDAC we didn't have this problem? It was only when we were just about to go 'live' with the system of which SDAC is part that we thoght it would be wise to update SDAC to the latest version. From that point the transaction encompassing code no longer worked but produced the error we are discussing.
So we have had to comment out the transaction parts of the code which isn't ideal as we have areas where a save can mean updating 4-5 tables and transactions are ideal for rolling back all the changes should any single one fail. For example, when we save an order, we have to update the order header, order lines, payments, order notes and customer notes table. With transactions, should any fail, we can rollback the whole lot, which is great. Without transactions (as we have it now), although it works, it is not ideal as the last update could fail for some reason and then everything is out of sync.
Thanks for your continued help, appreciated.
Trevor.
- Fri 10 Oct 2008 08:46
- Forum: SQL Server Data Access Components
- Topic: SDAC connection error - "manual or distributed mode"?
- Replies: 9
- Views: 7034
Hi Dimon,
I have checked and rechecked your first suggestion to see if a new connection is established with a database and I don't think this is the case. For example, this can occur simply loading the program and going straight to an area that is editable without any other database activity.
The connection is correct in all instances.
And both of those properties are disabled.
Can you suggest anything else?
Thanks!
Trevor.
I have checked and rechecked your first suggestion to see if a new connection is established with a database and I don't think this is the case. For example, this can occur simply loading the program and going straight to an area that is editable without any other database activity.
The connection is correct in all instances.
And both of those properties are disabled.
Can you suggest anything else?
Thanks!
Trevor.
- Thu 02 Oct 2008 12:01
- Forum: SQL Server Data Access Components
- Topic: SDAC connection error - "manual or distributed mode"?
- Replies: 9
- Views: 7034
Hi Dimon,
Thanks for the prompt reply.
This, I think, is where we will have problems
When I create a small sample file, I cannot get it to reproduce this error. The error is happening in a project of 70,000+ lines and I obviously cannot send this project to you as a whole.
Can you think of any other way I can simulate what I am doing in the main program or suggest any settings I can check that might be causing this within the main program but not in a small sample.
I guess this is as frustrating for you as me that I cannot reproduce this on a small program and it only occurs on our main code.
I can't think of a way forward other than maybe to grant you some kind of Remote Desktop or PCAnywhere or VNC access to my PC to view the code and see the problem and allow you to check what is happening on the actual software and settings that I am using.
Trevor
Thanks for the prompt reply.
This, I think, is where we will have problems
When I create a small sample file, I cannot get it to reproduce this error. The error is happening in a project of 70,000+ lines and I obviously cannot send this project to you as a whole.
Can you think of any other way I can simulate what I am doing in the main program or suggest any settings I can check that might be causing this within the main program but not in a small sample.
I guess this is as frustrating for you as me that I cannot reproduce this on a small program and it only occurs on our main code.
I can't think of a way forward other than maybe to grant you some kind of Remote Desktop or PCAnywhere or VNC access to my PC to view the code and see the problem and allow you to check what is happening on the actual software and settings that I am using.
Trevor
- Wed 01 Oct 2008 08:47
- Forum: SQL Server Data Access Components
- Topic: SDAC connection error - "manual or distributed mode"?
- Replies: 9
- Views: 7034
SDAC connection error - "manual or distributed mode"?
Hi,
I've just updated from an older version of SDAC to 4.5.
We are now continually getting 'Cannot create new connection because in manual or distributed transaction mode' errors in code like that below:
The error occurs on the first 'execute'. I have read in other posts that the problem can occur if the the tables/queries fetchall is false. But none of our tables/queries use false, we have fetchall=true on all occasions.
The odd thing is this never happened in the previous version 4.35 and has only started since I updated to 4.5. Is it easy to return to version 4.35 if there is no solution to this problem because we really need the transactions to work?
Many thanks,
Trevor
I've just updated from an older version of SDAC to 4.5.
We are now continually getting 'Cannot create new connection because in manual or distributed transaction mode' errors in code like that below:
Code: Select all
begin
if CustomerNotesGrid.RowCount>1 then begin
with DataResource do begin
MSConnection.StartTransaction;
try
with MSDeleteCustomerNotes do begin
ParamByName('URNToDelete').AsString:=SendingFrom.URNAssignedToOrder.Caption;
Execute;
end;
with MSCustomerNotes do begin
Active:=true;
for RowLoop:=3 to CustomerNotesGrid.RowCount-1 do begin
Append;
FieldByName('CustURN').AsString:=SendingFrom.URNAssignedToOrder.Caption;
......
end{for}
MSConnection.Commit;
ShowMessage('Customer notes saved successfully');
except
on E:Exception do begin
ShowMessage('Customer notes could not be saved ('+E.Message+'), please retry.');
MSConnection.Rollback;
raise;
end
end;
MSConnection.ApplyUpdates;
end;
end{if};
Close
end;
The odd thing is this never happened in the previous version 4.35 and has only started since I updated to 4.5. Is it easy to return to version 4.35 if there is no solution to this problem because we really need the transactions to work?
Many thanks,
Trevor