sql rowcount: defaults

Showing posts with label defaults. Show all posts

Monday, March 19, 2012

[SQL2005] Binding defaults without using sp_bindefault

The SQL2005 documentation states the sp_bindefault will be gone in the next
version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
TABLE statements. But I seem to have run into a situation where that only th
e
stored procedure approach works.
script 1:
CREATE DEFAULT abc AS 1
script 2:
CREATE TABLE xyz(
id int IDENTITY(1,1) NOT NULL,
thingy int DEFAULT abc)
This will result in the following error: "The name "abc" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted."
It works perfectly when I use the sp_bindefault procedure instead, so I do
have a solution, but can it be done without? I am probably missing something
very simple, so any help is welcome. TIA!Check documentation for CREATE DEFAULT and you will see that this will also
disappear. What BOL is
trying to say is that default objects will disappear, while default constrai
nts is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
> The SQL2005 documentation states the sp_bindefault will be gone in the nex
t
> version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
> TABLE statements. But I seem to have run into a situation where that only
the
> stored procedure approach works.
> script 1:
> CREATE DEFAULT abc AS 1
> script 2:
> CREATE TABLE xyz(
> id int IDENTITY(1,1) NOT NULL,
> thingy int DEFAULT abc)
> This will result in the following error: "The name "abc" is not permitted
in
> this context. Valid expressions are constants, constant expressions, and (
in
> some contexts) variables. Column names are not permitted."
> It works perfectly when I use the sp_bindefault procedure instead, so I do
> have a solution, but can it be done without? I am probably missing somethi
ng
> very simple, so any help is welcome. TIA!
>
>
>|||Don't bind defaults, ADD them:
alter table <table name>
add constraint abc
default (1)
for <column name>
ML
http://milambda.blogspot.com/|||I could do that, but the whole idea for me was to define a centralized
default, so when I change that, the changes will be reflected in every colum
n
binded to that default.
"ML" wrote:

> Don't bind defaults, ADD them:
> alter table <table name>
> add constraint abc
> default (1)
> for <column name>
>
> ML
> --
> http://milambda.blogspot.com/|||Is it possible to bind a single default constraint to multiple columns in
multiple tables? I.e. I have a date column in every table which should be
filled with a default value if none is provided.
At the moment I made a default for it and linked the name of the default to
the date column. In my database design tool, I only have to change the
default value in one place if I ever decide the current one is not right
anymore. Let the tool create a script for me and it is changed.
With constraints I have to change the default value in every single table
(200+), while I could do it with just one simple change when using
sp_bindefault was allowed. Or can it be done more easily?
"Tibor Karaszi" wrote:

> Check documentation for CREATE DEFAULT and you will see that this will als
o disappear. What BOL is
> trying to say is that default objects will disappear, while default constr
aints is the way to go.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
>|||As Tibor states in his post the default as a SQL object is on its last breat
h
(so to speak), as for the default as a constraint it cannot be reused as if
it were an object.
A constraint is a declaration, and if your requirement really is
object-oriented (rather than declarative) then maybe you could get what you
need by using a user-defined CLR type. But only for a default? I wouldn't go
down that road.
While being aware of the benefits a default provides as an object (e.g.
reusability), I still think a default is merely an attribute of the
individual column, and should be treated as such - kept in the meta world.
ML
http://milambda.blogspot.com/|||Ah hold on, I know what to do ;-)
Currently I have a UDDT for a mandatory date column and bind a default to it
when I need to. The solution is to create two UDDTs, one with and one withou
t
the constraint. It's gonna be some work to convert 200+ tables, but it's a
solution ...
"PaulSand" wrote:
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>|||Oops, sorry for making a mess here, but that isn't a solution either ...
"PaulSand" wrote:
> Ah hold on, I know what to do ;-)
> Currently I have a UDDT for a mandatory date column and bind a default to
it
> when I need to. The solution is to create two UDDTs, one with and one with
out
> the constraint. It's gonna be some work to convert 200+ tables, but it's a
> solution ...
> "PaulSand" wrote:
>|||Mess? Aren't we learning anymore? :)
ML
http://milambda.blogspot.com/|||> Is it possible to bind a single default constraint to multiple columns in
> multiple tables?
No. In short:
Default objects are deprecated. Those does what you want to do.
Default constraints are supported and recommended way in the future. But you
create them at the
column level, so they cannot be defined-one-used-many.
You might want to post a wish to [url]http://lab.msdn.microsoft.com/productfeedback/.[/
url] The ANSI SQL
feature you are looking for is the "DOMAIN" (CREATE/ALTER DOMAIN), but that
is not implemented in
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:6D87E279-5C8F-4BB6-9394-28454BD4B89D@.microsoft.com...
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>

Saturday, February 11, 2012

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:
> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> > cases, this param "Specifies that all pending transactions be immediately
> > rolled back" and puts the db in single user mode while the stored proc
> > runs.
> >
> > I'm wondering why each of these has it different default value. I presume
> > that in both cases a value of 1 = true, meaning that it would do the
> > rollback. Am I correct in assuming this? In order to help me understand
> > this
> > better, I hope someone could tell me why I would use the default values,
> > and
> > in what cirucumstance I might not use the default values.
>
>|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:
>> archuleta37,
>> Your installation of the SQL Server 2000 Books Online needs to be
>> updated.
>> There were documentation problems with these procedures. See:
>> http://support.microsoft.com/kb/291160 Terminate Default
>> http://support.microsoft.com/kb/298093 Job Id
>> These KBs also have a link to a corrected version of the BOL:
>> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
>> RLF
>> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
>> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
>> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In
>> > both
>> > cases, this param "Specifies that all pending transactions be
>> > immediately
>> > rolled back" and puts the db in single user mode while the stored proc
>> > runs.
>> >
>> > I'm wondering why each of these has it different default value. I
>> > presume
>> > that in both cases a value of 1 = true, meaning that it would do the
>> > rollback. Am I correct in assuming this? In order to help me understand
>> > this
>> > better, I hope someone could tell me why I would use the default
>> > values,
>> > and
>> > in what cirucumstance I might not use the default values.
>>

Monday, March 19, 2012

[SQL2005] Binding defaults without using sp_bindefault

Saturday, February 11, 2012

@terminate param for log shipping stored procs

@terminate param for log shipping stored procs

@terminate param for log shipping stored procs

sql rowcount

Blog Archive

About Me