|
paresh_joe wrote: whether it is possible to coordinate two database transaction using Distributed Transaction Coordinator
Could you clarify this with a scenario.
modified on Tuesday, December 2, 2008 5:48 AM
|
|
|
|
|
Hello,
Some quick questions to help me in my investigation...
Is it possible for triggers not to execute when the performance is poor?
The scenario is this, we have a trigger on table A that copies data to table B whenever an insert/update is done on table A.
However, when we were having performance problems with the server, it seems like the trigger did not execute. This looks like the case because the data was successfully inserted in table A but it is not found in table B.
What could have been the problem?
Also, is it possible for the trigger to roll back the insert on table A if it fails? How can I do this?
Thank you in advance.
Rafferty
|
|
|
|
|
Rafferty Uy wrote: Is it possible for triggers not to execute when the performance is poor?
No, unless there's a bug in SQL Server.
Rafferty Uy wrote: is it possible for the trigger to roll back the insert on table A if it fails
Yes it is. By executing rollback statement which rolls back the entire transaction.
|
|
|
|
|
I see.
What I meant with roll back is... for example I run a stored procedure AddToTableA, which executes a trigger TableAInsertTrigger.
How can I write TableAInsertTrigger in such a way that, if it fails, it will also roll back the AddToTableA SP?
Rafferty
|
|
|
|
|
|
So, if I get this correctly... for example given the following SP and Trigger
CREATE PROCEDURE AddToTableA() AS
BEGIN
BEGIN TRANSACTION
INSERT INTO TableA VALUES(1, 'SomeValue')
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
ERR_HANDLER:
ROLLBACK TRANSACTION
END
CREATE TRIGGER TableAInsertTrigger ON TableA FOR INSERT
BEGIN
INSERT INTO TableB (SELECT * FROM INSERTED)
-- do something else here, one that causes error
END
In the above case, if I call AddToTableA() and since TableAInsertTrigger fails, it will roll back the inserts on both TableB (in the Trigger) and TableA (in the SP)?
btw, thank you for helping 
Rafferty
|
|
|
|
|
Rafferty Uy wrote: In the above case, if I call AddToTableA() and since TableAInsertTrigger fails, it will roll back the inserts on both TableB (in the Trigger) and TableA (in the SP)?
Yes, exactly. The insert statement in the trigger is inside the same transaction as the insert that fired the trigger. This is why if you roll back the transaction, also trigger modifications are rolled back.
It's easy to test if you create an error in the end of the trigger (for example set @variable = 1/0), you can see what happens.
Rafferty Uy wrote: btw, thank you for helping
No problem
|
|
|
|
|
Thanks again!
btw, just another quick question. If I do the condition above... does it mean that the SP will no longer raise an error?
For example, if I have another SP calling AddToTableA(), and let's say that this other SP has an if...else statement depending if AddToTableA() causes an error, will AddToTableA() still return an error when it already handled the @@ERROR and executed a ROLLBACK TRANSACTION?
Rafferty
|
|
|
|
|
When you execute RAISERROR in a stored procedure, the caller will see an error as a result, regardless who is calling the block. So even if you catch the error, the RAISERROR generates a 'new' one.
If the SP has executed rollback then there's no transaction left so be sure that you don't make any modifications since they would not be part of the original transaction.
Also, if I remember correctly, you cannot execute ROLLBACK again since it would generate a different error (no transaction or such).
I think it would be good if you create a test situation and try different variations in order to find the one that suits you best.
Mika
|
|
|
|
|
okay, thanks a lot!
Rafferty
|
|
|
|
|
No problem
|
|
|
|
|
I have this error "ExecuteReader: Connection property has not been initialized."
Here is my code . Could give me any advice to fix it ?Thanks
private void button1_Click(object sender, EventArgs e)
{
string cmd;
string field;
string parameter;
field = "Site,Model";
parameter = "@Site,@Model";
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db1.mdb");
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")";
OleDbCommand sCommand = new OleDbCommand(cmd);
aConnection.Open();
OleDbDataReader aReader = sCommand.ExecuteReader();
sCommand.Parameters.Add("@Site", OleDbType.VarChar, 20);
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20);
sCommand.Parameters["@Site"].Value = "site1";
sCommand.Parameters["@Model"].Value = "Model1";
aReader.Close();
aConnection.Close();
}
|
|
|
|
|
Below is the change in bold to do in your code:
private void button1_Click(object sender, EventArgs e)
{
string cmd;
string field;
string parameter;
field = "Site,Model";
parameter = "@Site,@Model";
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db1.mdb");
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")";
OleDbCommand sCommand = new OleDbCommand(cmd);
aConnection.Open();
sCommand.Connection = aConnection
OleDbDataReader aReader = sCommand.ExecuteReader();
sCommand.Parameters.Add("@Site", OleDbType.VarChar, 20);
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20);
sCommand.Parameters["@Site"].Value = "site1";
sCommand.Parameters["@Model"].Value = "Model1";
aReader.Close();
aConnection.Close();
}
Hope this solves your problem
|
|
|
|
|
thanks. but another error occur:
No value given for one or more required parameters.???
|
|
|
|
|
Why are you creating an OleDbDataReader for an INSERT statement? It makes no sense since reader is used when you fetch data from the database.
Also you execute the command before setting any parameters or their values.
|
|
|
|
|
I'm having a little bit of brain fry going on...
How can I go about to see if any date within a date range is in another date range?
Example: Is any date between 01/01/2008 - 12/31/2015 in 11/21/2008 - 12/21/2009?
MOD:
Ok, so I think I found my answer. Does anyone see an issue with this comparison?
01/01/2008 < 12/21/2009 AND 12/31/2015 > 11/21/2008 [obviously these would be parameters and fields in a database.... paramStart < dbEnd AND paramEnd > dbStart]
modified on Monday, December 1, 2008 3:09 PM
|
|
|
|
|
mobius111001 wrote: Is any date between 01/01/2008 - 12/31/2015 in 11/21/2008 - 12/21/2009?
If
date1 = 01/01/2008
date2 = 12/31/2015
date3 = 11/21/2008
date4 = 12/21/2009
then it would be something like:
... date1 between date3 and date4
or date2 between date3 and date4
or (date1 < date3 and date2 > date4) ...
|
|
|
|
|
We have developed software in .net application and backhand as a SQL Server Desktop Engine. The query is how to update database at client end without disturing his live data. There is continious update going on in database structure. This is the issue we have been facing from a long time.
Thanks.
|
|
|
|
|
Dear singhdheeraj21,
The best practice is to apply patches after the deployment of the application. While creating a patch keep the versioning of the patches and optionally keep the versioning of the database too. Always create a roll back patch as your last resort. It will help you to recover your last best working state.
Hope this answer your question.
Thanks and Regards,
Umair Feroze
|
|
|
|
|
The usual way is to make all your database changes repeatable, for example always try and do alter table scripts after checking the column defintion needs changing. A tip, if you make the change through enterpirse manager there is an option to create a change script. These sometimes can be tweaked, but they are the script that would be applied if you said save.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What Are Extended Properties Sql Server 2005 And What Should I Do To See Extended Properties In Sql Server 2005.I Searched In MSDN I Tried The Method Given Over There To See Extended Properties But Its Not Working
(MSDN:-Select Column Check Properties For Extended Properties)
|
|
|
|
|
Extended properties let you define your own properties on an object and add values to them.
For example you can add extended property Description to a table and then for the value you can define some descriptive text.
Refer to: sp_addextendedproperty (Transact-SQL)[^].
|
|
|
|
|
I am working in a mySQL db and we made a mistake when implementing the tables by having one of the fields in every table being to short. We use this field to catch which form in the applications update each table. The syntax I am finding is to do this...ALTER TABLE `cl_client_common` change `UpdateProgram` char(50). It is currently a char(20) I know that this will work and we should not lose any data however what I am wanting to accomplish is to do this to every table in the db without having to write the code for all 200+ tables. So what I am wondering is could I write something like ALTER TABLE `databaseName`.`*` change `UpdateProgram` char(50).
Any advice would be much appreciated, Please and Thank you.
Humble Programmer
|
|
|
|
|
You can use system tables to generate proper statements. It would be something like following:
SELECT CONCAT( 'ALTER TABLE ', Table_Name, ' CHANGE ...')
FROM Information_Schema.Tables
WHERE Table_Schema = 'YourSchema';
When executing this select, you'll get statements you can again execute to make the actual changes.
Hope this helps,
Mika
|
|
|
|
|
Good advice I don't know what that did not cross my mind.
Humble Programmer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
