Best Practices Analyzer

I'd like to add my own rules to be reported on by Best Practices analyzer.
When will we be able to create custom rules?
Also need to be able to isolate to specific databases.
Thanks for your feedback Bruce.
We've considered user-defined rules and we intend to do it, but not in the
short term (i.e. not in the next release of BPA). Be assured we do have
extensibility in mind.
To scan only a few specific databases, you can type the name of the
databases you want as part of the registration of a SQL Server instance.
(semicolon delimited)
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"bruce" <bruce@.discussions.microsoft.com> wrote in message
news:449614D3-F129-470B-A845-3CA4C21096F8@.microsoft.com...
> I'd like to add my own rules to be reported on by Best Practices analyzer.
> When will we be able to create custom rules?
> Also need to be able to isolate to specific databases.
|||Does the BPA tool check user accounts for pasword strength?
"Christian Kleinerman [MS]" wrote:

> Thanks for your feedback Bruce.
> We've considered user-defined rules and we intend to do it, but not in the
> short term (i.e. not in the next release of BPA). Be assured we do have
> extensibility in mind.
> To scan only a few specific databases, you can type the name of the
> databases you want as part of the registration of a SQL Server instance.
> (semicolon delimited)
> - Christian
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "bruce" <bruce@.discussions.microsoft.com> wrote in message
> news:449614D3-F129-470B-A845-3CA4C21096F8@.microsoft.com...
>
>
sql

Best Practice Analyzer rule doesnt make sense

I'm running the Best Practice Analyzer on a sql 2k server in an attempt to
prepare it for 2005, and one of many rules that dont make sence is:
Object contains an INSERT statement without explicit specification of target
column list.
This rule checks objects for use of INSERT statements without explicit
specification of target column list.
When inserting into a table or view, it is recommended that the target
column_list be explicitly specified. This results in more maintainable code.
Exactly how do you explicitly specify a column? Here's the code at fault:
CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
SELECT * FROM #Tmp
Can anyone explain why it doesnt like this code?
moondaddy@.nospam.nospam
Try this:
INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
Both ID and Name are reserved words and should be avoided where possible.
Andrew J. Kelly SQL MVP
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> I'm running the Best Practice Analyzer on a sql 2k server in an attempt to
> prepare it for 2005, and one of many rules that dont make sence is:
> Object contains an INSERT statement without explicit specification of
> target column list.
> This rule checks objects for use of INSERT statements without explicit
> specification of target column list.
> When inserting into a table or view, it is recommended that the target
> column_list be explicitly specified. This results in more maintainable
> code.
> Exactly how do you explicitly specify a column? Here's the code at fault:
> CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
> SELECT * FROM #Tmp
> Can anyone explain why it doesnt like this code?
> --
> moondaddy@.nospam.nospam
>
|||Thanks. I think that did it.
btw: Good to see microsoft.public.sqlserver.integrationsvcs up and running!
Looks like I will spend some time there.
moondaddy@.nospam.nospam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23TmGwTL7FHA.2816@.tk2msftngp13.phx.gbl...
> Try this:
> INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
>
> Both ID and Name are reserved words and should be avoided where possible.
> --
> Andrew J. Kelly SQL MVP
>
> "moondaddy" <moondaddy@.nospam.nospam> wrote in message
> news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
>

Before Delete

I have 2 databases "Law","Rules" .. the second have tables which is linked
to the first one... so i want to deny Deleting of Record from first if it
has a child record in the other database...
I notice that there is no "Before Delete" trigger in sql server so how could
i control deleteing records from first database..
Second.. how could i roll-back Delete or update operation?
Did you consider a Foreign key Constraint for that ? If it is not
applicable you can do a ROLLBACK within a trigger and raise an error to
show up the error to the user.
http://groups.google.de/group/micros...18307e92ac868c
HTH, Jens Suessmeyer.
|||Instead of trying ot use a trigger, how about applying a foreign key
constraint instead? Then when you try to delete a row from the first
table you'll get an error if there's a dependent row in the second
table. It'll also be much faster than using a trigger.
On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
wrote:

>I have 2 databases "Law","Rules" .. the second have tables which is linked
>to the first one... so i want to deny Deleting of Record from first if it
>has a child record in the other database...
>I notice that there is no "Before Delete" trigger in sql server so how could
>i control deleteing records from first database..
>Second.. how could i roll-back Delete or update operation?
>
|||hi,
bradsbulkmail@.comcast.net wrote:[vbcol=seagreen]
> Instead of trying ot use a trigger, how about applying a foreign key
> constraint instead? Then when you try to delete a row from the first
> table you'll get an error if there's a dependent row in the second
> table. It'll also be much faster than using a trigger.
> On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
> wrote:
have you tried something like
SET NOCOUNT ON
CREATE DATABASE a
CREATE DATABASE b
GO
USE a
CREATE TABLE dbo.m (
Id int NOT NULL PRIMARY KEY ,
Descr varchar (10) NOT NULL
)
GO
USE b
GO
CREATE TABLE dbo.d (
ID int NOT NULL PRIMARY KEY ,
IdRif int NOT NULL
CONSTRAINT fk_d_m FOREIGN KEY
REFERENCES a.dbo.m (Id) ,
Descr varchar (10) NOT NULL
)
GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
?
the actual result is
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'a.dbo.m'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply