|
How to rollback triggers inside stored procedure...
Thanks ...
|
|
|
|
|
From SQL books Online:
Rollbacks in Stored Procedures and Triggers
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error 266 is generated. This error is not generated by the same condition in triggers.
A 266 error is generated when a stored procedure is called with an @@TRANCOUNT of 1 or greater and the procedure executes a ROLLBACK TRANSACTION or ROLLBACK WORK statement. This is because ROLLBACK rolls back all outstanding transactions and decrements @@TRANCOUNT to 0, which is a lower value than it had when the procedure was called.
If a ROLLBACK TRANSACTION is issued in a trigger:
All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.
The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
None of the statements in the batch after the statement that fired the trigger are executed.
A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed, except that STATIC or INSENSITIVE cursors are left open if:
CURSOR_CLOSE_ON_COMMIT is set OFF.
The static cursor is either synchronous, or a fully populated asynchronous cursor.
A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.
You must use the SAVE TRANSACTION statement to do a partial rollback in a trigger, even if it is always called in autocommit mode. This is illustrated by the following trigger:
CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
SAVE TRANSACTION MyName
INSERT INTO TestAudit
SELECT * FROM inserted
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION MyName
END
This also affects COMMIT TRANSACTION statements that follow a BEGIN TRANSACTION statement in a trigger. Because BEGIN TRANSACTION starts a nested transaction, a subsequent COMMIT statement applies only to the nested transaction. If a ROLLBACK TRANSACTION statement is executed after COMMIT, ROLLBACK rolls back everything to the outermost BEGIN TRANSACTION. This is illustrated by the following trigger:
CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
BEGIN TRANSACTION
INSERT INTO TrigTarget
SELECT * FROM inserted
COMMIT TRANSACTION
ROLLBACK TRANSACTION
This trigger will never insert into the TrigTarget table. BEGIN TRANSACTION always starts a nested transaction. COMMIT TRANSACTION commits only the nested transaction, while the following ROLLBACK TRANSACTION rolls everything back to the outermost BEGIN TRANSACTION.
BTW the SQL Books Online can be downloaded from microsoft[^]
I may be somewhat used, but I'm not used up!
R
|
|
|
|
|
Can I distribute MSDE and MDAC?
I have a client, a non-profit with a non-budget, that needs to track data on persons they've provided assistance to, but I'd rather not have them buy Access if there's no need for it. The need is simple - recipient name, ID, date, nature of service provided, etc. One table will probably do it, and since they're running XP Pro, I can probably set it up as a browser-based app using IIS. Only one of the staff will ever need to access it, so the two connection limit is no burden.
Is it okay to just create the db in SQL Server at home, export the db to their system, and install MSDE and MDAC on their machines? For that matter, I think MDAC is part of XP, so it really only needs MSDE.
"My child was Inmate of the Month at Mohave County Jail" - Bumper Sticker in Bullhead City, AZ
|
|
|
|
|
Check this[^] out.
Also, if it is for a non-profit, maybe contact your local MS office and see if they'll sponsor you some software.
Cheers,
Simon
"From now on, if rogue states want to buy weapons of mass destruction, they're going to have to go on eBay," Mr. Bezos said.
|
|
|
|
|
Thank you for the link, Simon! It appears that I'm legal. I got my copy as a user of VS6 Professional, so I'm legal to distribute MSDE with single-purpose applications. Something this simple will never be deemed a competitive product, and if I can't use it with IIS, I can always make do with VB. It's hardly worth the effort to fire up VC++ for anything this trivial.
"My child was Inmate of the Month at Mohave County Jail" - Bumper Sticker in Bullhead City, AZ
|
|
|
|
|
now i want to repair and compact the access2000 database in my app
and use ado to do these,who can tell me how to do?
thanks!
-----------------------------
Mr.Whelk
-----------------------------
Zhuhai City ,Guangdong ,China
|
|
|
|
|
if you can understand this, it was copied from MSDN.
Visual C++: Compacting an Access Database via ADO
Besides #import, the Msado15.dll (MDAC2.1), add the following #import statement to generate the wrapper classes for JRO to your .cpp classes (alternatively, you can generate the wrapper classes more efficiently by using the no_implementation and implementation_only attributes of the #import pre-processor statement):
#import "C:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace
Add the following (specifying your own source and destination database paths) to the .cpp file where you want to compact the database:
...
try
{
IJetEnginePtr jet(__uuidof(JetEngine));
jet->CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\nwind2.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\abbc.mdb;Jet OLEDB:Engine Type=4");
}
catch(_com_error &e)
{
::MessageBox(NULL, (LPCTSTR)e.Description( ), "", MB_OK) ;
}
Good Luck!
JW
|
|
|
|
|
thanks,i will try it!!
-----------------------------
Mr.Whelk
-----------------------------
Zhuhai City ,Guangdong ,China
|
|
|
|
|
hey,a question is:
if the access2000 database have a password, i want to the compacted database
have the same password ,i try following:
IJetEnginePtr jet(__uuidof(JetEngine));<br />
jet->CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\study\\bardemo\\Debug\\personal.mdb;Persist Security Info=False;Jet OLEDB:Database Password=simcos",<br />
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\study\\bardemo\\Debug\\personalbackup.mdb");
but after compacted the backup database lost its password.
how can i save the password?
help me ,thanks!
-----------------------------
Mr.Whelk
-----------------------------
Zhuhai City ,Guangdong ,China
|
|
|
|
|
try include the following property in your dest provider
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\study\\bardemo\\Debug\\personalbackup.mdb;Jet OLEDB:New Database Password=123456");
Good luck!
JW
|
|
|
|
|
it is ok.thanks very much!hope to make friend with you!
my email is :ehq@eastcompeace.com
thanks!
-----------------------------
Mr.Whelk
-----------------------------
Zhuhai City ,Guangdong ,China
|
|
|
|
|
You are welcomed, please check the dest file by Access 2000. Make sure the new password is function.
JW
|
|
|
|
|
It already has that resolution (actually 100 nanosecond resolution), but if you are displaying it in a datagrid the default column format does not show it. try:
private static String timeFormat = "MM/dd/yy HH:mm:ss:fff";<br />
private static int timeColumnWidth = 120;<br />
<br />
DataGridTableStyle ts1 = new DataGridTableStyle();<br />
foreach (DataColumn dc in dataTable.Columns)<br />
if (dc.DataType == typeof(DateTime))<br />
{<br />
columnFmt.Format = timeFormat;<br />
columnFmt.Width = timeColumnWidth;<br />
}<br />
ts1.GridColumnStyles.Add(columnFmt);<br />
ts1.MappingName = dataTable.TableName;<br />
ts1.ColumnHeadersVisible = true;<br />
ts1.ReadOnly = true;<br />
dataGrid1.TableStyles.Clear();<br />
dataGrid1.TableStyles.Add(ts1);<br />
dataGrid1.DataSource = dataTable;<br />
<br />
If you are trying to save this to SQL server, it can only handle a resolution of 3 ms in the SqlDateTime fields, Other databases may share the same lack of resolution or worse with thier 'Native' date/time columns You might have to convert the DateTimes to a long integer (DateTime.Ticks) and save that, converting back when you read it
-- Always be sure to maintain adequate insulation between lap and Laptop
Rob
|
|
|
|
|
Thanks a lot for your guidance.
and how can I fill a datetime field in a table programmatically with millisecond resolution for example in C# syntax?
Thank you in dvance.
|
|
|
|
|
Hi,All
RecordCount Property giving me -1 value
Please read my code and give me where i am doing wrong;
and also please comment on my Code(if their any mistakes)
Thanks in Advance..
/////////////This is my code To Test the Function////////////////////
_RecordsetPtr pRst;
CString strTemp;
pRst.CreateInstance(__uuidof(Recordset));
pRst=GetData(1);
if(pRst!=NULL)
{
int nCount=(int)pRst->RecordCount;
strTemp.Format("%d",nCount);
AfxMessageBox(strTemp);
}
else
{
AfxMessageBox("Recordset null");
}
pRst.Close();
pRst.Release();
////////////////////////////////////
///////////////////This is my funtion which will return me Recordset object///////////
_RecordsetPtr GetData(int nType)
{
CString strType;
strType.Format("%d",nType);
try
{
//COM Library Initialization
if(FAILED(::CoInitialize(NULL)))
{
//Log error
return NULL;
}//if(FAILED(::CoInitialize(NULL))
//Open the Connection
m_hr=m_pConnection.CreateInstance(__uuidof(Connection));
if(FAILED(m_hr))
{
::CoUninitialize();
//Log error
return NULL;
}
m_strCnnString=GetConnectionString();//It will give me the Connection String
_bstr_t cnnString(m_strCnnString);
_bstr_t bstrEmpty("");
m_hr=m_pConnection->Open(cnnString,bstrEmpty,bstrEmpty,adConnectUnspecified);
if(FAILED(m_hr))
{
m_pConnection.Release();
::CoUninitialize();
//Log error
return NULL;
}
//Get the Command objet
m_hr=m_pCommand.CreateInstance(__uuidof(Command));
if(FAILED(m_hr))
{
m_pConnection->Close();
m_pConnection.Release();
::CoUninitialize();
//Log error
return NULL;
}
m_pCommand->ActiveConnection=m_pConnection;
m_pCommand->CommandText="Sp_GetData";
m_pCommand->CommandType=adCmdStoredProc;
//Append the Parameters to Command object
//Type Parameter
m_pParam=m_pCommand->CreateParameter(_bstr_t("Type"),adInteger,adParamInput,4,_variant_t(strType));
m_pCommand->Parameters->Append(m_pParam);
m_hr=m_pRecordSet.CreateInstance(__uuidof(Recordset));
if(FAILED(m_hr))
{
m_pConnection->Close();
m_pConnection.Release();
::CoUninitialize();
//Log error
return NULL;
}
//Execute the SP
m_pRecordSet->CursorType=adOpenStatic;
m_pRecordSet=m_pCommand->Execute(NULL,NULL,adCmdStoredProc);
return m_pRecordSet;
}//try block
catch(_com_error &comExcep)
{
//Handle the error
}
catch(CException * GenExcep)
{
//Handle the error
GenExcep->Delete();
}
//CleanUp the objects
if(m_pCommand!=NULL)
{
m_pCommand.Release();
}
if(m_pConnection!=NULL)
{
m_pConnection->Close();
m_pConnection.Release();
}
::CoUninitialize();
return NULL;
}
//////////////////This is my StoredProcedure /////////
CREATE PROCEDURE [dbo].[Sp_GetData]
(
@Type[int]
)
AS
Begin
SELECT
Data1,
Data2,
Data3
Data4,
Data5
FROM
tblTestData
WHERE
(Data1=@Type)
End
GO
/////////////////////////////////////////////
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
anju
|
|
|
|
|
anju wrote:
RecordCount Property giving me -1 value
MSDN has an article:
PRB: ADO: Recordcount May Return -1[^]
You might also review the example on this site:
Filter and RecordCount Properties Example (VC++)[^]. Here is a snip of what I am talking about.
rstPublishers->CursorType = adOpenStatic;
TESTHR( rstPublishers->Open("publishers",strCnn,
adOpenStatic , adLockReadOnly,adCmdTable));
intPublisherCount = rstPublishers->RecordCount;
Hope this helps.
Nick Parker
May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
|
|
|
|
|
Hi Nick,
Thanks for your reply,
Still now RecordCount Property giving me -1;
what your provided information is good if i am getting
the recordset through "adCmdText or adCmdTable"
but here i am getting the recordset through "adSotredProc".
r u observed this difference?
i am getting full recordset with out any problem except
RecordCount.
Actually my problem was concentrated here....
//Execute the SP
m_pRecordSet->CursorType=adOpenStatic;
m_pRecordSet=m_pCommand->Execute(NULL,NULL,adCmdStoredProc);
return m_pRecordSet;
---In the above code.Eventhogh i assigned "adOpenStatic" to CursorType it is not replicating it is defaultly taking the
"adOpenForwardOnly".
//////////
_RecordsetPtr pRst;
CString strTemp;
pRst.CreateInstance(__uuidof(Recordset));
pRst=GetData(1);
if(pRst!=NULL)
{
int nCount=(int)pRst->RecordCount;
strTemp.Format("%d",nCount);
AfxMessageBox(strTemp);
}
else
{
AfxMessageBox("Recordset null");
}
pRst.Close();
pRst.Release();
/////////////////////////
----Can u tell where i have to set the CursorType?
Once again i am waiting for your reply...
thanks in advance
anju
|
|
|
|
|
Hello,
I have an app that uses an Access DB. I want to repair and compact the database but I can't seem to close the DB properly. I'm using CCommand:
m_Command.m_session.Close();<br />
m_Command.Close();
but the .ldb file remains... how can I make sure the link to the DB is removed???
Thanks!
---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com
|
|
|
|
|
I believe you need to close the session's datasource to close the connection, as well as the session.
R
|
|
|
|
|
And how do I do that?
Thanks!
---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com
|
|
|
|
|
When you created the session object, you must have passed it a DataSource Connection (CDataSource) instance, which you opened with the connection string specifying the providor ,db name, and path to the database. It is this connection that must be closed (not just the session instance) CDatasource::Close. If you used the CDataConnection class to open the session, this unfortunately hides its contained CDataSource (doesn't appear to expose either it or its close method), and you will have to destroy (delete) the CDataConnection to force it to close the connection (Open new instances using the intialization string used for the original connection.
R
|
|
|
|
|
I would like to access the data to SQL server on a remote machine. Are there any issues when connecting via an IP address vs. writing my own server and marshalling the queries through it. I would prefer to connect directly so I have all the power of recordsets locally in my application.
Some of the issues I would like to know about are:
1) Security - Does the data come in a raw format with the schema and everything?
2) Performance - What kind of overhead does the transfer make?
3) Usability - Is it really as easy as connecting to SQL server on a local machine?
4) Anything else I should know
Cheers,
Clint
|
|
|
|
|
1. Unless you enable protocol encryption the data is transferred a a binary stream (TDS- Tabular Data Services) protocol for the marshalling - SQL proprietary format. The Schema will also be there if it would have been there on a local call.
2. Performance is better than marshalling datasets, but will depend on the speed and available bandwidth of the network connection.
3.Security is more of an issue, and will depend on how you set up SQL. Best is NT authentication(Trusted_connection=yes in connection string), but this requires that the client either be in the same NT domain, or that the user on the client have an account on the server with identical username and password and that account has necessay priveleges to talk to the db in question.
4. Use TCPIP net protocol for best performance (Network=dbmssocn in connection string). Design the application to minimize round trips to the server. Use connection pooling on the client. Use stored procedures werever possible.
Read the SQL dos & donts article All of the advice applies...
Good luck
Rob
Trust NoOne...
|
|
|
|
|
Does anybody know how to force the SQL Server OLEDB provider to output narrow characters instead of wide ones? I need to test a couple of things.. 
I imagine it's a matter of tweaking the connection string/properties, but I just can't find it in the MSDN (as usual  ).
--
standing so tall, the ground behind
no trespassers, on every floor
a garden swing, and another door
she makes it clear, that everything is hers
A place of abode, not far from here, Ms. Van de Veer
|
|
|
|
|
Here is a resultset:
ID Name Value
---------------------------------
101 Name1 Value1
101 Name2 Value2
101 Name3 Value3
101 Name4 Value4
101 Name5 Value5
101 Name6 Value6
Could anyone tell me a way that could transform the above resultset in something like next one? (I'd prefer an SQL approach / not cursors and so on... but i appreciate them anyway).
Name1 Name2 Name3 Name4 Name5 Name6
----------------------------------------------
Value1 Value2 Value3 Value4 Value5 Value6
Is there any solution that don't use OLAP or extended procedures?
ThanX,
In MSAccess things are simple (see Crosstab query) - but in SQL Server 7 I don't know how to do it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
