best practice analyzer feedback

Hi I've been running the BPA for a few weeks now and have followed all the
recommendations to correct my databases, but I consistently get the following
2 Non Compliance errors.
1. One or more of the databases failed the scan. It is recommended that data
and log files be kept on separate drives.
I've tried to move the log files but it won't let me even when nobody is
using the databases.
2. One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT
setting! It is generally recommended to explicitly set NOCOUNT option to ON
at the beginning of a trigger.
Here is the trigger reffered to:-
CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
FOR INSERT
AS
IF @.@.ROWCOUNT=0
RETURN
SET NOCOUNT ON
INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
FROM Inserted
As can be seen the SET NOCOUNT ON is included and it won't work if it's at
the beginning of the trigger.
The rest of the tool seems to work OK.
1 - Try to detach database first then move log file, then attach database
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> Hi I've been running the BPA for a few weeks now and have followed all the
> recommendations to correct my databases, but I consistently get the
following
> 2 Non Compliance errors.
> 1. One or more of the databases failed the scan. It is recommended that
data
> and log files be kept on separate drives.
> I've tried to move the log files but it won't let me even when nobody is
> using the databases.
>
> 2. One or more triggers is either setting NOCOUNT to OFF or missing
NOCOUNT
> setting! It is generally recommended to explicitly set NOCOUNT option to
ON
> at the beginning of a trigger.
> Here is the trigger reffered to:-
> CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
> FOR INSERT
> AS
> IF @.@.ROWCOUNT=0
> RETURN
> SET NOCOUNT ON
> INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
> SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
> FROM Inserted
> As can be seen the SET NOCOUNT ON is included and it won't work if it's at
> the beginning of the trigger.
> The rest of the tool seems to work OK.
>
>
|||Thanks t, detach and attach worked great. Do you have any idea about the
NOCOUNT problem?
jez
"t" wrote:

> 1 - Try to detach database first then move log file, then attach database
> "jez123456" <jez123456@.discussions.microsoft.com> wrote in message
> news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> following
> data
> NOCOUNT
> ON
>
>
|||Hi
It is an interesting pattern... I'll open a tracking item to see if we can
relax the check for the next release.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:D67E5CBA-C13E-4272-A100-B613BD7F4E82@.microsoft.com...[vbcol=seagreen]
> Thanks t, detach and attach worked great. Do you have any idea about the
> NOCOUNT problem?
> jez
> "t" wrote:
database[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
it's at[vbcol=seagreen]