32 GB of Memory

Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
In SQL Server, run:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'min server memory (MB)', 30720
go
reconfigure with override
go
Stop and start SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.|||Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||Correct. Looks like my cut and paste missed the mark. :-(
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ORxSH0QyHHA.988@.TK2MSFTNGP02.phx.gbl...
Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||What makes me more confused is your mention of "I am going to be
upgrading our servers from SP3a to SP4. ", any specific reason you
mentioned it, because you can allocate 32 GB of memory even if you are
on SP3a. Also you should be aware of Hot Add memory
Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
AWE:
899761 FIX: Not all memory is available when AWE is enabled on a
computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761|||Is it possible to take advantage of the additional memory on Windows Server
2000 Enterprise edition or 2003 only?
"dinu_babu@.hotmail.com" wrote:
> What makes me more confused is your mention of "I am going to be
> upgrading our servers from SP3a to SP4. ", any specific reason you
> mentioned it, because you can allocate 32 GB of memory even if you are
> on SP3a. Also you should be aware of Hot Add memory
> Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
> AWE:
> 899761 FIX: Not all memory is available when AWE is enabled on a
> computer that is running a 32-bit version of SQL Server 2000 SP4
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761
>
>

2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.
>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>>From an article online:
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>>From an article online:
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David|||In addition to the other posts, many of these thing are easy to try. The first one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an open transaction:
begin try
BEGIN TRAN
SELECT * FROM lkölkölk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>>From an article online:
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch= is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=2000cwa.googlegroups.com...
> > Hi,
> >
> > I have some questions about the new TRY-CATCH block syntax in SQL
> > Server 2005.
> >
> >>From an article online:
> >
> > Not all errors can be trapped by a TRY/CATCH block. The first types of
> > errors that are not trapped are compile errors, like a syntax error
> > within a batch of T-SQL statements. Another type of error is deferred
> > name resolution errors created by statement level recompilations. If a
> > process is terminated by a KILL command then a TRY/CATCH block does not
> > capture this error. Client interrupt requests or broken client
> > connections are also not trapped by the TRY/CATCH block. For errors
> > that are not trapped, SQL Server 2005 passes control back to the
> > application immediately, without executing any CATCH block code.
> >
> > 1.) After reading this, I am confused. For non-trappable errors, does
> > this mean that statements that occurred before the error are committed,
> > then control is passed back to the application? Or does it rollback
> > everything, then pass control to the application?
> >
> > 2.) Does every single DML statement require its own TRY-CATCH block,
> > just like you check for @.@.ERROR after every stmt (in previous versions
> > of SQL Server)?
> >
> > 3.) Also, how can you return an error code from within the CATCH block?
> > I can't seem to find this information.
> >
> > THANKS MUCH
> >|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegroups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
...
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David

2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.

>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.

> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.

> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David|||In addition to the other posts, many of these thing are easy to try. The fir
st one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is
terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an
open transaction:
begin try
BEGIN TRAN
SELECT * FROM lklklk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegroup
s.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =
first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch=
is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =
an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=
2000cwa.googlegroups.com...[vbcol=seagreen]|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegroups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
...
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David

2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.

>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH
1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>
|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.

> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.

> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David
|||In addition to the other posts, many of these thing are easy to try. The first one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an open transaction:
begin try
BEGIN TRAN
SELECT * FROM lklklk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>
|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =
first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch=
is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =
an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=
2000cwa.googlegroups.com...[vbcol=seagreen]
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David
|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegr oups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
....
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David

2005 Database Mirroring & Witness

I'm devlving into 2005 and specifically mirroring.

Having read the following article (
http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),
is mirroring a sensible option as it indicates that it should not be
used in a production environment, yet the SQL2005 seminars I've been to
indicate that this is a worthy option.

Do we also need a witness server, or can this be done (sensibly) with
two SQL2005 servers ?

Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.
If we go down the route of mirroring, it looks like we can get rid of
the SAN and Cluster and use the mirroring instead.

Any advice on the pro's / cons would be appreciated.

Thanks

RyanRyan,

I haven't done too much with mirroring yet but

> Do we also need a witness server, or can this be done (sensibly) with
> two SQL2005 servers ?

It is physically possible to do this with 2 servers (in fact it is
physically possible to do it on 1 server) but it really doesn't make sense.
The server can only be live if it has a quorum (2 out of 3) agree that it is
the live server. If the primary server is one machine and the second
machine contains the mirror server and the witness server both and the
second machine goes off-line you have effectively taken the primary server
off-line as well since it will no longer have a quorum.

HTH,
John|||"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1142346007.542792.217730@.e56g2000cwe.googlegr oups.com...
> I'm devlving into 2005 and specifically mirroring.
> Having read the following article (
> http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),
> is mirroring a sensible option as it indicates that it should not be
> used in a production environment, yet the SQL2005 seminars I've been to
> indicate that this is a worthy option.

It's worth playing with.

BUT, it's beta. Which means if you use it in a production evironment and
something goes wrong, the amount of support that MS will provide is limited.

> Do we also need a witness server, or can this be done (sensibly) with
> two SQL2005 servers ?

Generally you should have a witness.

> Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.
> If we go down the route of mirroring, it looks like we can get rid of
> the SAN and Cluster and use the mirroring instead.
> Any advice on the pro's / cons would be appreciated.
> Thanks
>
> Ryan|||What do we need licence wise for the witness server ? Will we be able
to use a standard licence ?|||You can use any edition for the witness.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1142422233.452068.120450@.z34g2000cwc.googlegr oups.com...
What do we need licence wise for the witness server ? Will we be able
to use a standard licence ?|||Ryan,

> What do we need licence wise for the witness server ? Will we be able
> to use a standard licence ?

The witness can be any edition of SQL Server 2005 including the free Express
Edition.

HTH,
John