Risk in moving tempdb to raid 0

Hi all,
I wanted to shift tempdb from 0+1 to raid 0 array. I am not worrid about the transactions or rollback. if tempdb raid 0 array crashes, and I put new disk raid 0 array in place of crashed one will server start without any problems? Any other concerns except the transactions are losts.

Cheers..PrakashVery surprising no reply....|||RE: Very surprising no reply....
Q1 If tempdb raid 0 array crashes, and I put new disk raid 0 array in place of crashed one will server start without any problems?

Well, some folks are on holidays; but here is a quick answer:

A1 Maybe.

It is best to extensively test any new configuration (especially less redundant ones) for failure recovery issues prior to implementation. Some obvious considerations in this case would pertain to what else may reside on the tempdb raid 0 array with tempdb e.g.(if Model, Master, etc., or some transaction logs of production DBs reside on the same array, there will be various different issues to address). One may also expect possible issues related to how production DBs / stored procedures, etc., will fail in an environment that suddenly loses tempdb with many production stored procedures in progress. For example, it is possible that some production stored procedures have been written in ways that may cause issues with inconsistent data in production DBs (in the event of the sudden loss of tempdb during a period of high production activity). Barring such kinds of issues, tempdb is rebuilt from the model DB each time Sql Server services start up normally (incidentally, ver. 6.5 supports tempdb in ram).

Returning errors from a stored procedure using transactions

I'm having a brain cramp.
I'm writing a stored procedure that will do an insert into two tables in a
parent-child relationship. I want either both inserts to succeed or both to
fail. Obviously, a transaction is required. If an error occurrs any time
during the SP execution, I want to return the error to the application in th
e
same manner as SQL Server would if I wasn't using a transaction. So, my SP
looks something like:
DECLARE @.TheError INT
BEGIN TRAN
INSERT Parent...
SET @.TheError = @.@.ERROR
IF @.TheError = 0
BEGIN
INSERT Child...
SET @.TheError = @.@.ERROR
END
IF @.TheError = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
If I Rollback the transaction, how do I return the error to the application
such that the CLR will generate the appropriate SQL Exception in my
application?
BOL wasn't clear about this.
Thanks,
BobThe original error will be returned to the application no matter what you
do. You can't _trap_ errors in SQL Server 2000, only handle them.
If you want to have really short code and get the behaviour you want, you
can use:
SET XACT_ABORT ON
BEGIN TRAN
INSERT Parent...
INSERT Child...
COMMIT TRAN
If there is an error, the transaction will automatically be aborted and
rolled back, and the error is still raised to the application. Note that no
further code will be executed after the statement where the error happens.
Jacco Schalkwijk
SQL Server MVP
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:920AF55A-7551-4B05-AE55-C77D49D35391@.microsoft.com...
> I'm having a brain cramp.
> I'm writing a stored procedure that will do an insert into two tables in a
> parent-child relationship. I want either both inserts to succeed or both
> to
> fail. Obviously, a transaction is required. If an error occurrs any time
> during the SP execution, I want to return the error to the application in
> the
> same manner as SQL Server would if I wasn't using a transaction. So, my SP
> looks something like:
> DECLARE @.TheError INT
> BEGIN TRAN
> INSERT Parent...
> SET @.TheError = @.@.ERROR
> IF @.TheError = 0
> BEGIN
> INSERT Child...
> SET @.TheError = @.@.ERROR
> END
> IF @.TheError = 0
> BEGIN
> COMMIT TRAN
> END
> ELSE
> BEGIN
> ROLLBACK TRAN
> END
> If I Rollback the transaction, how do I return the error to the
> application
> such that the CLR will generate the appropriate SQL Exception in my
> application?
> BOL wasn't clear about this.
> Thanks,
> Bob
>|||Jacco,
Thank you. I vaguely remembered something like that, but since BOL didn't
explicitly say that, I wasn't sure. I'm glad I asked because I had completel
y
forgotten about XACT_ABORT.
To maximize reuseability, I usually write my SPs to do insert/update
operations on a single table and let my application code handle the
transactions, but in this case, a parent without a child row is not valid
from a business perspective, so I decided to let the SP do both inserts.
Bob
"Jacco Schalkwijk" wrote:

> The original error will be returned to the application no matter what you
> do. You can't _trap_ errors in SQL Server 2000, only handle them.
> If you want to have really short code and get the behaviour you want, you
> can use:
> SET XACT_ABORT ON
> BEGIN TRAN
> INSERT Parent...
> INSERT Child...
> COMMIT TRAN
> If there is an error, the transaction will automatically be aborted and
> rolled back, and the error is still raised to the application. Note that n
o
> further code will be executed after the statement where the error happens.
>
> --
> Jacco Schalkwijk
> SQL Server MVP

Returning Duplicate Records

I have a Transactions table w/the following columns (all VarChar):
CustomerID,
Customer_Name,
User_Name
And and Admin table w/the following columns (VarChar):
User_Name,
Company_ID
Now I want to return any records where the CustomerID is duplicated for
different Customer_Names when Grouped by Company_ID. Here's a sample of wha
t
the returned data will look like:
Company_ID Customer_ID Customer_Name
R9 321 Ted Smith
R9 321 Rob Wand
Here's my query:
SELECT COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME,
COUNT (CUSTOMER_ID) AS NUM_OCCUR
FROM TRANSACTIONS
INNER JOIN ADMIN ON
ADMIN.USER_NAME = TRANSACTIONS.USER_NAME
GROUP BY COMPANY_ID, CUSTOMER_NAME, CUSTOMER_ID
HAVING (COUNT (CUSTOMER_ID) > 1)
ORDER BY COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME
This query only gets me half way there, as I can only visually inspect
what's returned. Here's a sample of the returned data:
Company_ID Customer_ID Customer_Name Num_Occur
R12 1 Jim Jones 2
R9 1000 Chris B 3
R9 1000 Brian P 5
R9 1001 Dave B 8
In this example, I ONLY want to return records 2 & 3 where the Company_ID,
and Customer_ID are the same, but the Customer_Names differ.On Thu, 19 Jan 2006 08:07:03 -0800, Eric wrote:

>I have a Transactions table w/the following columns (all VarChar):
>CustomerID,
>Customer_Name,
>User_Name
>And and Admin table w/the following columns (VarChar):
>User_Name,
>Company_ID
>Now I want to return any records where the CustomerID is duplicated for
>different Customer_Names when Grouped by Company_ID. Here's a sample of wh
at
>the returned data will look like:
>Company_ID Customer_ID Customer_Name
>R9 321 Ted Smith
>R9 321 Rob Wand
>Here's my query:
>SELECT COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME,
> COUNT (CUSTOMER_ID) AS NUM_OCCUR
>FROM TRANSACTIONS
>INNER JOIN ADMIN ON
> ADMIN.USER_NAME = TRANSACTIONS.USER_NAME
>GROUP BY COMPANY_ID, CUSTOMER_NAME, CUSTOMER_ID
>HAVING (COUNT (CUSTOMER_ID) > 1)
>ORDER BY COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME
>This query only gets me half way there, as I can only visually inspect
>what's returned. Here's a sample of the returned data:
>Company_ID Customer_ID Customer_Name Num_Occur
>R12 1 Jim Jones 2
>R9 1000 Chris B 3
>R9 1000 Brian P 5
>R9 1001 Dave B 8
>In this example, I ONLY want to return records 2 & 3 where the Company_ID,
>and Customer_ID are the same, but the Customer_Names differ.
Hi Eric,
Try if this works for you:
SELECT a.Company_ID, t.CustomerID, t.Customer_Name
FROM Admin AS a
INNER JOIN Transactions AS t
ON t.User_Name = a.User_Name
WHERE EXISTS
(SELECT *
FROM Transactions AS t2
WHERE t2.CustomerID = t.CustomerID
AND t2.User_Name <> t.User_Name)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP