|
Check out this transaction documentation from the MSDN site.
Note especially this paragraph:
Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.
Over time, I've learned to always use named transactions as a matter of course. When I use them in stored procedures, I often name them after the procedure; so in procedure pr_execute_code_a, I'd probably use
BEGIN TRANSACTION peca
This only minimizes the chance of a naming conflict, so you still have to be careful, but you get the idea. With named transactions, you're free to wrap stored procedures in other ones at will and still guarantee data integrity; the inner stored procedures can roll back just their own data if that's appropriate, and the outermost procedure has complete control over its own transaction; it can roll everything back or not.
When I am creating an "open" stored-procedure API, I always make sure that the procedure returns some non-zero value (always 1 if I'm not returning specific error codes) in case of an error, in addition to a RAISERROR call and/or a rollback of a transaction. That way calling code (which may be an enclosing procedure) has enough information to roll back a transaction. You know how to get this from an EXECUTE statement, right?
<br />
DECLARE @returnCode AS INT<br />
<br><br />
-- ...<br />
-- ...<br />
<br><br />
EXEC @returnCode = pr_DoSomething<br />
--<br />
IF (@returnCode <> 0) BEGIN<br />
-- roll back your transaction, if appropriate<br />
-- raise an error, if appropriate<br />
RETURN(1) -- if appropriate<br />
END -- IF<br />
In your case, if you're the one controlling and using the code and the inner procedures are always called by some other, you may not need transactions in your inner procedures at all, only in the outermost one(s).
Regards,
Jeff Varszegi
EEEP!
|
|
|
|
|
Hi Jeff ,
Thank you alot of ur reply, it was reallly great
Wish u luck
Thanks Alot
Mahmoud Manasrah
|
|
|
|
|
|
I am using a dateset.
Heres the problem:
Lets assume theres a table T, with one row in it. I populate my dataset by using Fill().
In my software I now delete the row (from the dataset only).
If I use the Update() with the dataset, the row will be removed from the DB. This is fine.
But what happens if I do this:
1) Call Fill() to populate the dataset.
2) Delete the row from the dataset.
3) Add a new row to the dataset.
4) Delete this new row again.
The problem is that there will now be two rows in the dataset that is flagged to be deleted when Update() is called. But only one of these will be in the database!
If I call Update() now, the system will try to delete both from the DB, but since only one of them is available in the DB to delete, a Concurrency exception will be thrown (since no rows will be affected when the system tries to delete the nonexistant row).
What is the best practice in these kind of situations?
Thanks for your time.
|
|
|
|
|
I am having to come up with factors that may be involved when you are transferring data from one database architecture to another. In other words, if I wanted to transfer to RDBMS from a network architecture what do I need to look for?
How can I find out this information - if I wanted to research? I have been trying to find it, but I must be putting in the wrong keywords for my search.
Thank you,
ibok23
|
|
|
|
|
Hi,
How do I check whether a login exists for a particular database in MSDE?
Code:
SqlConnection objConn = new SqlConnection(strCon);
objConn.Open();
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = objConn;
//eg: if login 'priya' does not exist in database PRIYA how do i do it
objCmd.CommandText = @" EXEC sp_addlogin @loginame ='abb',@passwd ='abb',@defdb = 'ABB' EXEC sp_grantdbaccess N'abb', N'abb' EXEC sp_addrolemember N'db_owner', N'abb'" ;
objCmd.ExecuteNonQuery();
Thankx in advance
Priya
|
|
|
|
|
One thing that come to my mind now is using sp_who which return information about users and if the given name does not exists, it retun 1.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
sp_who only shows logged in users, not all existing users
Jeff Martin
Triple20 Software
|
|
|
|
|
2 quick ways to find out if a user exists in SQL Server.
1) check the sysusers table:
select count(uid)
from sysusers
where name = 'NameYouAreSearchingFor'
if it returns 0, the user doesn't exist. But since MS doesn't recommend querying the sys tables directly (so they can change in later versions), you should use #2
2) use the sp_helpuser stored proc
exec sp_helpuser 'NameYouAreSearchingFor'
if it returns something, your user exists.
Jeff Martin
Triple20 Software
|
|
|
|
|
Hi,
How do I check whether a database exists before attaching a database ?
Thankx in Advance.
Priya
|
|
|
|
|
IF (NOT(EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE [name] = 'Northwind'))) BEGIN
-- attach your database
END -- IF
This information only applies to SQL Server. This is just off the top of my head-- not using databases today. You might poke around throught the INFORMATION_SCHEMA views to see if there's some view containing that stuff too; Microsoft always recommends using the information schema over accessing system tables directly.
Regards,
Jeff Varszegi
EEEP!
|
|
|
|
|
|
Will this allow checking for a database that doesn't have any tables?
Regards,
Jeff Varszegi
EEEP!
|
|
|
|
|
It should. I haven't tested it, but I don't see why it wouldn't work. information_schema is just a view into the system tables.
Jeff Martin
Triple20 Software
|
|
|
|
|
I understand that, I'm not a newbie. I was asking you to explain why you think that selecting from INFORMATION_SCHEMA.TABLES is a good way to get a list of databases on the server. It only returns a list of tables from the current database.
I'm really not trying to be snotty, honest. I still think I should say that INFORMATION_SCHEMA is really nothing but a database user; it's the views created with that user that are the collective view into the structure of the database.
Regards,
Jeff Varszegi
EEEP! An Extensible Expression Evaluation Package
|
|
|
|
|
I was wondering if their is a way to install the Ole DB provider on a machine which does not have microsoft project in not installed.or is there a way to open a .MPP file on a machine which does not have microsoft project installed?
any help in this regard is most welcome.
Vinay
|
|
|
|
|
How do I use #temptable or @var functionality through openquery? There is a linked server from SQL Server 2000 to Sybase 12 using Sybase.ASEOLEDBProvider.
Executing an openquery statement from MS SQL will retrieve Sybase data; however, I have no advanced functionality available. When I try to use temporary #tables or declare @vars it returns this error...
Server: Msg 7357, Level 16, State 2, Line 1, Could not process object '…OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Sybase.ASEOLEDBProvider', Query=…
Linked Server configuration:
Provider options: AllowInProcess is checked
Server options: Collation Compatible, Data Access, RPC and RPC Out are checked
Does anyone have a suggestion or alternative for this functionality? Thank you very much for your input.
Janya
|
|
|
|
|
I need to create a series of linked tables in an Access database using ADOX.
I have the ADOX files from C. Antollini's article.
I tried the following code, everything seems to work okay until the actual append call.
// cat is a catalog defined as per ADOX.h
// ConnectString is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False"
CString sTbl = dlgLst.m_sTable;
CString sConnectSrc, sConnectTrg;
sConnectSrc.Format (ID_ADO_CONNECTSTR, m_sDBFile);
sConnectTrg.Format (ID_ADO_CONNECTSTR, sDBFile);
LPCTSTR lpTblName = sTbl;
LPCTSTR lpConnectSrc = sConnectSrc;
LPCTSTR lpConnectTrg = sConnectTrg;
CADOXCatalog cat;
cat.Open (sConnectSrc); // this is the Database in which the table definision is to be created
_TablePtr tbl;
tbl.CreateInstance(__uuidof(Table)); // Create the instance of a table
tbl->PutName (lpTblName); // give it a local name
tbl->put_ParentCatalog(cat.m_pCatalog); // define the parent catalog
// some debugging info, could also use TRACE
AfxMessageBox (sConnectTrg);
AfxMessageBox (sConnectSrc);
tbl->Properties->GetItem(_T("Jet OLEDB:Create Link"))->Value = true; // make it a linked table
tbl->Properties->GetItem(_T("Jet OLEDB:Link Datasource"))->Value = lpConnectTrg;
tbl->Properties->GetItem(_T("Jet OLEDB:Remote Table Name"))->Value = lpTblName;
// Upto this point everything executes. At least, I do not get any complaints
try
{
cat.m_pCatalog->Tables->Append (_variant_t((IDispatch *)tbl));
cat.m_pCatalog->Tables->Refresh();
}
catch(_com_error &e)
{
TCHAR szBuf[300];
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
sprintf(szBuf, "\n\tSource : %s \n\tDescription : %s \n ", (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
CString sErr = CString (szBuf);
AfxMessageBox (sErr);
}
This is causing an error saying "Not a Valid File Name" from the JET engine. The two file names are correct, and the files are both valid databases. Also, the table to be linked exists in the database being linked to.
I am using VC6 and MDAC 2.8.
Any pointers would be most welcome.
|
|
|
|
|
Okay, I goofed up...
The correct value for the Jet OLEDB:Link Datasource is just the name of the database, not the connection string.
Oh yes, this will work only for linking to Access Databases. If you are trying to link to a non-Access data source, you need to fill in the "Jet OLEDB:Link Provider String" instead of the Data source. I found this little tidbit at this location:
MSDN Page on creating linked tables via ADOX
|
|
|
|
|
I have an application that allows people to import data and make changes to a database. I have been told by my boss that I need to add the ability to undo a data import/change from my application. What I was thinking of doing was backing up or copying the database when the application starts and then allowing the user to restore this backup/copy. Is this the best way to do it? I know sql server creates a backup everytime you make a change to the database. I don't know where to start or even how to write the code to do this (should it be a stored procedure...?). Any help would be great. I am using C#. Or is there a way to just do an undo from the transaction log?
|
|
|
|
|
I don't think you need to backup when your application start. Just set proper recovery option for your database. See online help for SQLServer to see which one suit you , then just run RESTORE command from your appliaction whith SqlCommand class. There are examples of using this class in this site/MSDN/googling. This way you could recover to different time and many optionn you can use. Another way which I think is better is using SqlTransaction class . For sample and how to use it see SqlConnection.BeginTransaction() in MSDN. You can set different point and restore to them in your application.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Hi
I am having no luck in finding some way (or tool) that will allow me to import an Oracle DMP file into SQL DB or convert the file into some form that will allow me to recreate the Oracle DB on the SQL DB (Only the schema is needed, so not working with data)
Any help in this regard is greatly appreciated.
Ta
John
|
|
|
|
|
Best method to get the schema (and the data for that matter) would be to use a DTS task....Simply set Oracle as the source, SQL as the destination, and elect to import ALL objects....
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Except that DTS will talk to ORACLE, not a DMP file, so if you don't have ORACLE client installed, this won't work.
Rich's suggestion is a good one, though.*
Steve S
*I can't believe I just wrote that
|
|
|
|
|
Guys,
Pls, Kindly try this insert statement and tell me what's the problem. Everytime I insert this data it gave me an error message Server: Msg 511, Level 16, State 1, Line 1 Cannot create a row of size 9113 which is greater than the allowable maximum of 8060.The statement has been terminated.
code:
create table tmplog
(
strlog varchar(8000)
)
;
insert into tmlog
values ('START OF CU-0 RECOVERYPRM_0 E000 0300 0000 0000 79 E8 00PRM_0 E141 DD00 5600 0BD0 79 E8 00PRM_0 E241 DD00 5600 0BD0 79 E8 00PRM_0 E341 DD00 5600 0BD0 79 E8 00PRM_0 E441 DD00 5600 0BD0 79 E8 00PRM_0 E541 DD00 5600 0BD0 79 E8 00PRM_0 E541 DD00 5600 0BD0 79 E8 00PRM_0 E641 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E841 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 EA41 DD00 5600 0BD0 79 E8 00PRM_0 EA41 6400 0000 0000 79 E8 00PRM_0 E841 0001 1303 474C 79 E8 00PRM_0 E841 0001 2503 474C 79 E8 00PRM_0 EE41 DD00 5600 0BD0 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 5400 0000 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 5001 6200 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 4000 7400 0001 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 EE13 0014 0001 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0420 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0000 7A E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EC41 DD00 5600 0BD0 79 E8 00CU RECOVERY COMPLETEPRM_0 EB41 5000 0000 001E 7A 68 00PRM_0 EE41 6074 0430 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 0403 474C 79 68 00PRM_0 EE41 0600 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 02E7 401C FFFF 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0100 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0200 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0211 040C FFFF 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 A410 0000 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 6430 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0455 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 7440 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 07E9 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0222 64AC FFFF 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0200 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0300 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0215 0000 000A 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0225 0400 000A 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0300 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 0D03 474C 79 68 00PRM_0 E841 0001 1C04 6414 79 68 00PRM_0 EE41 6074 0450 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0450 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2804 6414 79 68 00PRM_0 E841 0001 2D03 474C 79 68 00PRM_0 EE41 6074 0430 0400 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0217 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0227 0000 0060 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0400 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 1504 6414 79 68 00PRM_0 E841 0001 2703 474C 7A 68 00PRM_0 E841 0001 1803 474C 7A 68 00PRM_0 E841 0001 1003 474C 7A 68 00PRM_0 E841 0001 2804 6414 79 68 00PRM_0 EE41 0800 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2F04 6414 79 68 00PRM_0 E841 0001 0703 474C 79 68 00PRM_0 E841 0001 2403 474C 79 68 00PRM_0 E841 0001 2C04 6414 79 68 00PRM_0 EE41 0700 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 C000 0000 0E42 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2403 474C 79 68 00PRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE00 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EB00 60C6 0000 0202 79 68 00 ')
please,please,please
I word count the entire data en it only gives me 4535 characters with spaces any my variable is varchar(8000). I know I overlooked somthing but I can't find.
ASAP;
/Dabsukol
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
