rules on a table

In My table i'm collecting information about our customers.In that i have fields zip and phone.So i wanted to implemement a rule that zip should be atlaest of of 5 characters and phone should be 13 characters Including '-'s(333-333-3333).How can i implement these two rules on my table.First of all, decide if you really want to add those constraints. They will automagically limit your database to dealing with data like what you'd find in the United States.

If that is an Ok thing, then I would suggest that you add constraints to your table. You could use something like:ALTER TABLE myTable
ADD CONSTRAINT XCK01myTable CHECK (5 <= Len(zip))
, ADD CONSTRAINT XCK02myTable CHECK (phone
LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')Constraints like these make me nervous, since I see them as rather arbitrary, but they are much better done as constraints than as code because you can easily change them in the database if needed, instead of having to hunt down hundreds or thousands of snippets of code!

-patP|||I'm with Pat. I think you'll end up regretting this as the users start to complain about limiting their ability to enter data. The zip-code you might get away with, though what about zip-4 extensions? I don't think enforcing a 13 character phone number is going to make you a lot of friends with the users.|||True, but you can fix this in one place (the database) with one command (DROP CONSTRAINT) if it turns out to be a problem. This is far better than coding it into an executable or a web page, at least in my opinion.

-PatP|||Oh, I agree. Table constraints are the best places to store design flaws, hands down. :p|||I don't know that it's a design flaw to insure data consistency in the data layer, even if that means adding "another" layer to the data. It's isn't so much to cover design flaws as it is to allow the data group to insure data is consistent and secure for the enterprise.|||It's not a design flaw to enforce data integrity. I'm just concerned about enforcing this particular constraint, based on past experience. I guess I'd say it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users. Unless there is some sort of application process that depends upon the phone number being absolutely 13 characters, then why place an artificial limit on how the database can be used? You are locking out the possibility of international numbers, extensions, etc...|||i agree with blindman

"it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users"

what about the guy who paid a few thousand bucks to secure the number 1-800-BEST-DBA

this guy's going to be p1ssed if you force him to put the dash where you want him to put the dash

note for those who still don't get it: there is no dash on the phone keypad|||That's not an unnecessary constraint. 1800BESTDBA isn't a phone number. It's a way to represent a phone number. You can't dial it though. You can't feed it to a dialer or use it for customer service. Anyone looking at the number is going to wonder what the idiot was thinking putting it into the database like that anyway. If you want to have a descriptor for the phone number, then have one. He can put whatever he wants in there. 1-800-BEST-DBA, 1-800-STUPID-Q...whatever.

The number is 18003334444. The display handled on the front-end is 1-800-333-4444. The descriptiong, which can be displayed or not is 1-800-STUPID-Q.|||well, that just proves my point

the number is not 18003334444

it's 18002378322 -- you could look it up!

and yes, i sure can dial 1-800-BEST-DBA

even on my rotary phone!!

and of course your very descriptive "what the idiot was thinking..." reveals an attitude that might best be set aside when dealing with people in the real world

Rule: Max Number of Lines Per SP

A client believes that "a stored procedure should never have more than about
50 lines of code in it."
No rationalle behind that thought.
When pressed - she stated, "I heard that in a class once a long time ago."
I think this person is an idiot.
Is there or was there ever a time when a such a rule made sense for
technical reasons?
I'm just trying to see how such a belief might possibly make sense.
Thanks.= u r idiot
Message posted via http://www.webservertalk.com|||>> A client believes that "a stored procedure should never have more
than about 50 lines of code in it." No rationalle behind that thought.
<<
Actually, there is both a rationale and a history. Return with me now
to the 1970's when Software Engineering was being born. We would put
bugs into code and give them to people debug, and measure them.
Fifty lines is about one page of hardcopy print out. We found that
code modules that can be seen in toto are measurably easier to maintain
than those that were split across pages or screens. The longer the
module got, the harder the bugs were to find (I cannot remember the
formula, but it was greater than linear). University of Maryland and
SEI had some of the studies, as I recall.
This pattern held across languages, which meant COBOL, FORTRAN, Jovial
and Assembler in those days, then later Pascal and C.
Notice that I said "module", so subroutines, paragraphs or procedures
count as modules depending on your language.
In the case of SQL Server, this was the rule because T-SQL was never
meant to an application language. It is a very simple one-pass
compiler that was used to "fill in the holes" in the early product by
giving you triggers for DRI and other thigns we do declarative today.
.|||Joe,
<<Actually, there is both a rationale and a history>>
YOU have an interesting rationalle. So does everyone else who responded to
my OP, and I agree with all of them to date.
The idiot manager had no rationalle floating around inside her head beyond
"I heard it in a class once." We talked at length... she really had nothing
more than that.
I just wanted to see if I was missing something... apparently not.
Thanks Everyone!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114720755.550812.61610@.g14g2000cwa.googlegroups.com...
> than about 50 lines of code in it." No rationalle behind that thought.
> <<
> Actually, there is both a rationale and a history. Return with me now
> to the 1970's when Software Engineering was being born. We would put
> bugs into code and give them to people debug, and measure them.
> Fifty lines is about one page of hardcopy print out. We found that
> code modules that can be seen in toto are measurably easier to maintain
> than those that were split across pages or screens. The longer the
> module got, the harder the bugs were to find (I cannot remember the
> formula, but it was greater than linear). University of Maryland and
> SEI had some of the studies, as I recall.
> This pattern held across languages, which meant COBOL, FORTRAN, Jovial
> and Assembler in those days, then later Pascal and C.
> Notice that I said "module", so subroutines, paragraphs or procedures
> count as modules depending on your language.
> In the case of SQL Server, this was the rule because T-SQL was never
> meant to an application language. It is a very simple one-pass
> compiler that was used to "fill in the holes" in the early product by
> giving you triggers for DRI and other thigns we do declarative today.
> .
>|||Guadala Harry wrote:
<nothing of importance>
Your choice of words and tone are troublesome...
David G.|||On Thu, 28 Apr 2005 13:03:19 -0700, Guadala Harry wrote:

>A client believes that "a stored procedure should never have more than abou
t
>50 lines of code in it."
>No rationalle behind that thought.
>When pressed - she stated, "I heard that in a class once a long time ago."
>I think this person is an idiot.
>Is there or was there ever a time when a such a rule made sense for
>technical reasons?
>I'm just trying to see how such a belief might possibly make sense.
>Thanks.
>
Hi Guadala,
As Joe indicated, there is some rationale for this rule. But it should
be treated as a rule of thumb. The point is to reduce complexity.
Suppose you have a query like this:
SELECT a.Col1,
b.Col2,
..
e.ColN
FROM Table1 AS a
-- Some useful comments here
INNER JOIN Table2 AS b
ON b.Col8 = a.Col4
INNER JOIN Table3 AS c
ON ...
...
WHERE d.Col18 = 'Asdf'
AND e.Col4 < b.Col8
....
ORDER BY a.Col1,
b.Col2,
...
c.Col8
It's very readable, but probably more than 50 lines. But yoou coould
squeeze it in a lot less lines:
SELECT a.Col1, b.Col2, ..., e.ColN FROM Table1 AS a
INNER JOIN Table2 AS b ON b.Col8 = a.Col4 INNER JOIN Table3 AS c
ON ... WHERE d.Col18 = 'Asdf' AND e.Col4 < b.Col8 ....
ORDER BY a.Col1, b.Col2, ..., c.Col8
But would it be easier to read'
A reverse example: back in my mainframe / Cobol days, creating a report
(or an external interface) required you to move all fields in the
records from the read file to the corresponding field in the records to
be written. Code like this:
OUTREC.FIELD1 = INREC.FIELD1;
OUTREC.FIELD2 = INREC.FIELD2;
...
OUTREC.FIELD138 = INREC.FIELD138;
WRITE FILE (OUTFILE) FROM (OUTREC);
I had a colleague who took the 50-lines rule way too literally - he
would break this routine in three subroutines, and call them after each
other:
CALL FILL_OUTREC_PART1;
CALL FILL_OUTREC_PART2;
CALL FILL_OUTREC_PART3;
WRITE FILE (OUTFILE) FROM (OUTREC);
Senseless overhead, IMO.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<<Your choice of words and tone are troublesome>>
And ?|||And there are some general rules of respect and courtesy that everyone
should follow when posting. I think everyone understands your frustration -
we've all been there in one form or another (and perhaps even been the
source of frustration at times), but this isn't the forum for venting your
anger especially with insults, deserved or not.
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:O7$gY2ETFHA.2124@.TK2MSFTNGP14.phx.gbl...
> <<Your choice of words and tone are troublesome>>
> And ?
>

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

|||Does SQLServer 2005 have any support for boosting or bagging?
|||SQL Server doesn't have built in support for Bagging and Boosting. One way you can determine "how much" data you need for your particular problem is to reserve a test set and then create models with different amounts on training data. For example 1000, 2000, 4000, 8000, 16000, etc. cases. Graph the accuracy results of the models and see where the accuracy "flattens out". The point where accuracy stops increasing with additional data is the "ideal" amount of data for that particular problem.

Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

|||Does SQLServer 2005 have any support for boosting or bagging?
|||SQL Server doesn't have built in support for Bagging and Boosting. One way you can determine "how much" data you need for your particular problem is to reserve a test set and then create models with different amounts on training data. For example 1000, 2000, 4000, 8000, 16000, etc. cases. Graph the accuracy results of the models and see where the accuracy "flattens out". The point where accuracy stops increasing with additional data is the "ideal" amount of data for that particular problem.

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0
Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:

>I seem to have a rule on one of my columns which prevents me from entering a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0
|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:

> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:

> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum is
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:

> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>I seem to have a rule on one of my columns which prevents me from entering a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:
> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >I seem to have a rule on one of my columns which prevents me from entering a
> >value above 25000 in an integer column.
> >I am not able to find the rule so that I can delete it.
> >Is there a system table that I should look for?
> >This table was upsize from Access to SQL 7.0
>|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
>> Are you sure it's a rule you are looking for? If you used
>> the upsizing wizard, it should have implemented this as a
>> constraint.
>> What are you using to manage SQL Server and where are you
>> looking? You really don't want to address this by modifying
>> system tables.
>> To view information as well as constraints, rules on a
>> table, you could execute:
>> EXEC sp_help YourTable
>> To check for constraints, you can execute:
>> EXEC sp_helpconstraint YourTable
>> -Sue
>> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >I seem to have a rule on one of my columns which prevents me from entering a
>> >value above 25000 in an integer column.
>> >I am not able to find the rule so that I can delete it.
>> >Is there a system table that I should look for?
>> >This table was upsize from Access to SQL 7.0
>>|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:
> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >Sue,
> >I executed the two command that you suggested. There seem to be another
> >hidden constraint that I can't find.
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Are you sure it's a rule you are looking for? If you used
> >> the upsizing wizard, it should have implemented this as a
> >> constraint.
> >> What are you using to manage SQL Server and where are you
> >> looking? You really don't want to address this by modifying
> >> system tables.
> >> To view information as well as constraints, rules on a
> >> table, you could execute:
> >> EXEC sp_help YourTable
> >> To check for constraints, you can execute:
> >> EXEC sp_helpconstraint YourTable
> >>
> >> -Sue
> >>
> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> >> <Arne@.discussions.microsoft.com> wrote:
> >>
> >> >I seem to have a rule on one of my columns which prevents me from entering a
> >> >value above 25000 in an integer column.
> >> >I am not able to find the rule so that I can delete it.
> >> >Is there a system table that I should look for?
> >> >This table was upsize from Access to SQL 7.0
> >>
> >>
>|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum is
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
>> Constraints don't typically hide so...let's start over.
>> Could you please post what you are doing and what tool or
>> application you are using? And post the exact error message
>> you are receiving?
>> -Sue
>> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >Sue,
>> >I executed the two command that you suggested. There seem to be another
>> >hidden constraint that I can't find.
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Are you sure it's a rule you are looking for? If you used
>> >> the upsizing wizard, it should have implemented this as a
>> >> constraint.
>> >> What are you using to manage SQL Server and where are you
>> >> looking? You really don't want to address this by modifying
>> >> system tables.
>> >> To view information as well as constraints, rules on a
>> >> table, you could execute:
>> >> EXEC sp_help YourTable
>> >> To check for constraints, you can execute:
>> >> EXEC sp_helpconstraint YourTable
>> >>
>> >> -Sue
>> >>
>> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> >> <Arne@.discussions.microsoft.com> wrote:
>> >>
>> >> >I seem to have a rule on one of my columns which prevents me from entering a
>> >> >value above 25000 in an integer column.
>> >> >I am not able to find the rule so that I can delete it.
>> >> >Is there a system table that I should look for?
>> >> >This table was upsize from Access to SQL 7.0
>> >>
>> >>
>>|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:
> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >Sue,
> >I am inside enterprise manager. I try to change the quantity from 6000 to
> >26000 in an integer column. I get a message that says the current Maximum is
> >25,000.
> >We create a new column called quantityNew and with the same datatype and
> >content. QuantityNew can be changed with no problems.
> >
> >Arne
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Constraints don't typically hide so...let's start over.
> >> Could you please post what you are doing and what tool or
> >> application you are using? And post the exact error message
> >> you are receiving?
> >>
> >> -Sue
> >>
> >> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> >> <Arne@.discussions.microsoft.com> wrote:
> >>
> >> >Sue,
> >> >I executed the two command that you suggested. There seem to be another
> >> >hidden constraint that I can't find.
> >> >
> >> >"Sue Hoegemeier" wrote:
> >> >
> >> >> Are you sure it's a rule you are looking for? If you used
> >> >> the upsizing wizard, it should have implemented this as a
> >> >> constraint.
> >> >> What are you using to manage SQL Server and where are you
> >> >> looking? You really don't want to address this by modifying
> >> >> system tables.
> >> >> To view information as well as constraints, rules on a
> >> >> table, you could execute:
> >> >> EXEC sp_help YourTable
> >> >> To check for constraints, you can execute:
> >> >> EXEC sp_helpconstraint YourTable
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> >> >> <Arne@.discussions.microsoft.com> wrote:
> >> >>
> >> >> >I seem to have a rule on one of my columns which prevents me from entering a
> >> >> >value above 25000 in an integer column.
> >> >> >I am not able to find the rule so that I can delete it.
> >> >> >Is there a system table that I should look for?
> >> >> >This table was upsize from Access to SQL 7.0
> >> >>
> >> >>
> >>
> >>
>|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:
>> The only way I can think of that you can get a message like
>> that is through a trigger. If it was a rule or a constraint,
>> you'd get a much more verbose message indicating that it
>> violated a check constraint or a rule, the column it was on,
>> the name of the constraint if it violated a check
>> constraint, etc.
>> Check for triggers on the table. In Enterprise Manager,
>> right click on the table, select All Tasks and then select
>> manage triggers.
>> -Sue
>> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >Sue,
>> >I am inside enterprise manager. I try to change the quantity from 6000 to
>> >26000 in an integer column. I get a message that says the current Maximum is
>> >25,000.
>> >We create a new column called quantityNew and with the same datatype and
>> >content. QuantityNew can be changed with no problems.
>> >
>> >Arne
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Constraints don't typically hide so...let's start over.
>> >> Could you please post what you are doing and what tool or
>> >> application you are using? And post the exact error message
>> >> you are receiving?
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
>> >> <Arne@.discussions.microsoft.com> wrote:
>> >>
>> >> >Sue,
>> >> >I executed the two command that you suggested. There seem to be another
>> >> >hidden constraint that I can't find.
>> >> >
>> >> >"Sue Hoegemeier" wrote:
>> >> >
>> >> >> Are you sure it's a rule you are looking for? If you used
>> >> >> the upsizing wizard, it should have implemented this as a
>> >> >> constraint.
>> >> >> What are you using to manage SQL Server and where are you
>> >> >> looking? You really don't want to address this by modifying
>> >> >> system tables.
>> >> >> To view information as well as constraints, rules on a
>> >> >> table, you could execute:
>> >> >> EXEC sp_help YourTable
>> >> >> To check for constraints, you can execute:
>> >> >> EXEC sp_helpconstraint YourTable
>> >> >>
>> >> >> -Sue
>> >> >>
>> >> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> >> >> <Arne@.discussions.microsoft.com> wrote:
>> >> >>
>> >> >> >I seem to have a rule on one of my columns which prevents me from entering a
>> >> >> >value above 25000 in an integer column.
>> >> >> >I am not able to find the rule so that I can delete it.
>> >> >> >Is there a system table that I should look for?
>> >> >> >This table was upsize from Access to SQL 7.0
>> >> >>
>> >> >>
>> >>
>> >>
>>

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:

>I seem to have a rule on one of my columns which prevents me from entering
a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:

> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
>|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:

> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum i
s
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
>|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:

> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:
>