Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.

Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.

Handle error in t-sql

Hi,

I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).

If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?

Thanks

Yes, I believe that feature was added in SQL Express/2005, but I'm not familiar enough with it to give examples. Normally, I would not create a SQL query that would ERROR, but it might return an empty resultset, or other indicator that it failed. If I need to catch a true error, then catch it in a try/catch block in your code.

Hand needed with t-sql

Is it possible to write these two blocks of code as one? The only
difference between them is the AND clause: AND is_trade_date = 1 versus AND
is_sett_date = 1.

Cheers,

David

IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_trade_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_sett_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ENDDavid (auto87829@.hushmail.com) writes:
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus
> AND is_sett_date = 1.

If my guess of the logic is right:

IF EXISTS (SELECT 1
FROM calendar
WHERE calendar_date = @.date
AND (is_trade_date = 1 OR @.trade_dates = 0)
AND (is_sett_date = 1 OR @.trade_dates = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Why not:
IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date AND
(is_trade_date = 1 OR is_sett_date = 1)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END

Ricardo

"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END|||The following block should do the trick:
IF EXISTS ( SELECT 1
FROM calendar
WHERE calendar_date = @.date AND
(
( is_trade_date = 1 AND
@.trade_dates = 1
)
OR
( is_sett_date = 1 AND
@.trade_dates <> 1
)
)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

although not very elegant...

Ricardo.
"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END