|
I need your opinions about using transactions with Sql Server. I need to insert data to three tables and if anything fails I need to delete all inserted data. So I'm going to use transactions but I don't know where I should use it. I have three stored procedures for the job and I use typed DataSet in my application.
First option is use SqlTransaction and call three sproc in scope using QueriesTableAdapter. The problem is I'm not sure if this can rollback if anything fails.
Second option is don't do anything in application. Call a fourth sproc with all data and use transaction in that sproc and call all three sprocs using exec etc. Also the problem with this is the same, I don't know if it'll rollback using it like this.
What do you think? I appreciate any opinions.
|
|
|
|
|
Both will work fine, which to use is mainly a matter of oppinion and preference.
|
|
|
|
|
|
Are they still same if I use System.Transactions instead of SqlTransaction?
|
|
|
|
|
Hi,
I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory.
Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Regards,
hairy
|
|
|
|
|
Hello,
ROW_NUMBER(), PARTITION BY may be used for this...btw why you want multiple times I/O solution when you can do it in one...and why you are worrying about the system memory its what OS is there for...am still not convinced of your idea!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Adeel is right - your ? is not well formed. Memory is the least of your problems. Disk IO, network latency and trafic volumes will play a larger role in your issue.
If you have a large table/database to "transport" I suggest you investigate other transport methods other than read it into a UI and write it into another database. One method which will probably be faster is to BCP the table in/out of the databases.
Each database type has different methods available, I suppose the UI path may be valid if you are looking for a DB agnostic solution. I suggest some furhter research, try asking for recommendations of moving table/s xxx big between db1, db2 and db3 you might get some enlightment.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all,
I want to append the value '123' with the value in column A. Column A is having few NULL value rows. NUll values should be replaced by the new value and others should be appended. How to do this in all rows of column A?
Thanks in advance
|
|
|
|
|
Gosh you really need to pick up a basic book on SQL!
UPDATE myTable
SET myColumn = ISNULL(myColumn,'') + 'abc'
|
|
|
|
|
hi all,
i created table like that below
create table extable(empid int primary key identity(1,1),empname varchar(50) not null,salary money not null)
when i execute sp_help it already created one index for that table.
sp_help extable
PK__extable__3D9E16F4
Is it clustered index or non-clustered index ?
what is the use of that index, how can improve that performance of the retrieving data
This is haneef.............................................................
|
|
|
|
|
The primary key is, by default, created as a clustered index. It will be used for lookups using "empid" in your case.
Improving performance for retrieving data depends on how you lookup the data. For example, if you tend to lookup using empname put an index on that. If you lookup using empname and salary then create an index which includes both those columns.
This is obviously a simplistic answer - indexing is a fine art where you must balance performance.
|
|
|
|
|
Hi,
The clustered index is created on primary key. a table can have only one clustered index the reason the data is arranged in physical order, sql server will maintain index record this will contain all index values mean empid in particular order. When we fire an select query this will arrange the result set in following format and fetch the corresponding record. The proper implementation of clustered index will reduce the query response time.
The disadvantage is All the index values should present in Index Record Table( here is empids)
empid
101 ---------> 101 XXX India
102 ---------> 102 YYY USA
103 ---------> 103 ZZZ UK
Non Clustered index on table will arrange result set in heap sorting order this mean not all index values should not present in Index Record. This will occupies less memory space. We will usually created on Non primary keys.
-.Net Professional.
modified on Tuesday, May 20, 2008 3:10 AM
|
|
|
|
|
hi all,
i want to update the column value with the value already in that column in a table(based on where condition)
For example,
table 1
--------
id name
1 abc
2 xyz
Now i'm appending the value '123' with the value in the column 'name'. the result should be like
id name
1 abc123
2 xyz123
How to do this......
|
|
|
|
|
update table
set name = name + '123'
|
|
|
|
|
Thanks a lot....
now my sp is working fine
|
|
|
|
|
Hi All,
i Want to know that how can save the List in MS-Access Database?
i m using a CListCtrl for the list.
please help me fo this ,
i m waiting for ur helpful answers.
Thanks in advance.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
I am joining two tables with INNER JOIN and I am getting duplicate entries.
I used DISTINCT but its not working.
Do we have some other keywords for the same?
Thanks in advance,
|
|
|
|
|
You do not get duplicate entries if you can't DISTINCT them away.
At least one of the values in your SELECT list differs from the others, making the rows different.
DISTINCT will remove "duplicated" rows.
|
|
|
|
|
try to use TOP 1
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi, Try in this way, I am not sure this will resolve your question. Distinct will impact on performance ( number of cpu reads)
WHILE EXISTS (select * FROM Table1 GROUP BY col1,col2.., HAVING count(*) > 1)
BEGIN
SET ROWCOUNT 1
DELETE e FROM Table1 e.empid WHERE EXISTS (select empid from @tab i where i.empid = e.empid AND i.name = e.name GROUP BY i.empid, i.name HAVING count(*) > 1)
SET ROWCOUNT 0
END
|
|
|
|
|
Hello,
In deploying my application it is required that it attach a sql server database to the .net framwork sql engine because there is no servers in the client computer.
How can I accomplish this task (deploying the app with out having a full running instance of sql server)?
Thanks.
Dad
|
|
|
|
|
AFAIK you need a SQL server installed.
If you need to deploy a SQL Server, take a look at the Express Edition which IIRC is free to use.
|
|
|
|
|
the error is _CrtIsValidHeapPointer function returning FALSE.
for example:
<pre>ResultSet *rs = stmt->executeQuery();
int nCol = 0;
{
vector<MetaData> vMetaData = rs->getColumnListMetaData();
nCol = (int) vMetaData.size();
} //now deconstructing vMetaData, the error is like this:</pre>
Debug Assertion Failed!
Program: ...
File: dbgheap.c
Ling: 1132
Expression: _CrtIsValidHeapPointer(pUserData)
windwosXP sp3 vs.net 2003
Oracle11g OCCI
|
|
|
|
|
Try in the C/C++ forum.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
thanks
but it is no use in my code:
ResultSet *rs = stmt->executeQuery();
int nCol = 0;
try
{
vector<MetaData> vMetaData = rs->getColumnListMetaData();
nCol = (int) vMetaData.size();
}
catch (...)
{
}
the errors are the same as before!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
