Alter Table

I want to disable all the triggers of a data base (and enable all of then
after the execution of a process).
I can find out the name of the user tables looking for then in the
sysobjects table and then I can run "alter table <name> disable all" but I
have to repeat it with all the tables.
My question is: Can I write a store procedure to do this? I tried to do it
with a cursor with the name of all the tables but it doesnt't work because
the alter table expects a table name, not a variable.
Thank you.Hi,
Try out this script.
declare @.x varchar(255)
select @.x = @.x + 'alter table '+name+ 'disable trigger all'
from master.dbo.sysobjects where type='u'
exec (@.x)
go
Thanks
Hari
MCDBA
"Alberto" <alberto@.nospam.com> wrote in message
news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> I want to disable all the triggers of a data base (and enable all of then
> after the execution of a process).
> I can find out the name of the user tables looking for then in the
> sysobjects table and then I can run "alter table <name> disable all" but I
> have to repeat it with all the tables.
> My question is: Can I write a store procedure to do this? I tried to do it
> with a cursor with the name of all the tables but it doesnt't work because
> the alter table expects a table name, not a variable.
> Thank you.
>|||I thought you might need a cursor to do this. How can you tell what would
need a cursor and what would not ?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VcsuluDHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try out this script.
>
> declare @.x varchar(255)
> select @.x = @.x + 'alter table '+name+ 'disable trigger all'
> from master.dbo.sysobjects where type='u'
> exec (@.x)
> go
> Thanks
> Hari
> MCDBA
>
> "Alberto" <alberto@.nospam.com> wrote in message
> news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> > I want to disable all the triggers of a data base (and enable all of
then
> > after the execution of a process).
> >
> > I can find out the name of the user tables looking for then in the
> > sysobjects table and then I can run "alter table <name> disable all" but
I
> > have to repeat it with all the tables.
> >
> > My question is: Can I write a store procedure to do this? I tried to do
it
> > with a cursor with the name of all the tables but it doesnt't work
because
> > the alter table expects a table name, not a variable.
> >
> > Thank you.
> >
> >
>

alter column syntax for enabling cascades

Hi,
I have a question about sql syntax on how to enable both
ON Delete cascade and On Update Cascade for already existing data tables.
One table contains all the users:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
The next table contains user protigraphs
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
My QUESTION is how to add both on delete cascade constraint and on update
cascade constraints to
the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
exists for this table.
Thank you
Vadim"Vadim" <vadim@.dontsend.com> wrote in message
news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a question about sql syntax on how to enable both
> ON Delete cascade and On Update Cascade for already existing data tables.
> One table contains all the users:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
> The next table contains user protigraphs
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> My QUESTION is how to add both on delete cascade constraint and on update
> cascade constraints to
> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
> exists for this table.
>
You just need to drop the existing constraint and create a new one. The
only trick is finding the name of the constraint, since it is
system-generated.
sp_help user_pictures
--discover the system-generated name of the constraint
alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
And don't use cascade updates, since you shouldn't be updating primary keys
in the first place.
David|||David,
Thank you for your reply.
I was hoping to be able to write a GENERAL script that would enable cascade
updates and deletes, sp_help might not be an option since the script will
have to be run at multiple customers' locations.
What could be other options? maybe to write a stored procedure that moves
the data to a temp table from the user_pictures table and properly creates
the new table and moves the data back.
I was wondering if it was possible to specify both options on delete and on
update since if user_name changes in the users table the same change would
then propagate to the user_pictures table.
Thanks again
Vadim
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RbR%23S1DHHA.4620@.TK2MSFTNGP04.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> You just need to drop the existing constraint and create a new one. The
> only trick is finding the name of the constraint, since it is
> system-generated.
> sp_help user_pictures
> --discover the system-generated name of the constraint
> alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> And don't use cascade updates, since you shouldn't be updating primary
> keys in the first place.
> David|||"Vadim" <vadim@.dontsend.com> wrote in message
news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> David,
> Thank you for your reply.
> I was hoping to be able to write a GENERAL script that would enable
> cascade updates and deletes, sp_help might not be an option since the
> script will have to be run at multiple customers' locations.
> What could be other options? maybe to write a stored procedure that moves
> the data to a temp table from the user_pictures table and properly creates
> the new table and moves the data back.
> I was wondering if it was possible to specify both options on delete and
> on update since if user_name changes in the users table the same change
> would then propagate to the user_pictures table.
>
Sure it's possible, it's just not usually a good idea to modify primary
keys. However if you are doing it, then you should probably cascade the
change.
To write a general script you will need to use a bit of dynamic SQL. Like
this:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
go
declare @.constraint varchar(30)
declare @.sql varchar(2000)
set @.constraint =
(
select object_name(constid) from sysforeignkeys
where rkeyid = object_id('dbo.users')
and fkeyid = object_id('dbo.user_pictures')
)
set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constrai
nt +
']'
exec (@.sql)
go
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
on update cascade
David|||David,
Thank you very much for such a detailed reply, This is what I was looking
for.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Sure it's possible, it's just not usually a good idea to modify primary
> keys. However if you are doing it, then you should probably cascade the
> change.
>
> To write a general script you will need to use a bit of dynamic SQL. Like
> this:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
>
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> go
> declare @.constraint varchar(30)
> declare @.sql varchar(2000)
> set @.constraint =
> (
> select object_name(constid) from sysforeignkeys
> where rkeyid = object_id('dbo.users')
> and fkeyid = object_id('dbo.user_pictures')
> )
> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constr
aint
> + ']'
> exec (@.sql)
> go
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> on update cascade
> David|||... and remember to name your constraints in the future... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vadim" <vadim@.dontsend.com> wrote in message news:%23ir%234z1DHHA.3836@.TK2MSFTNGP02.phx.gbl
..
> David,
> Thank you very much for such a detailed reply, This is what I was looking
> for.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>

alter column syntax for enabling cascades

Hi,
I have a question about sql syntax on how to enable both
ON Delete cascade and On Update Cascade for already existing data tables.
One table contains all the users:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
The next table contains user protigraphs
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
My QUESTION is how to add both on delete cascade constraint and on update
cascade constraints to
the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
exists for this table.
Thank you
Vadim
"Vadim" <vadim@.dontsend.com> wrote in message
news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a question about sql syntax on how to enable both
> ON Delete cascade and On Update Cascade for already existing data tables.
> One table contains all the users:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
> The next table contains user protigraphs
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> My QUESTION is how to add both on delete cascade constraint and on update
> cascade constraints to
> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
> exists for this table.
>
You just need to drop the existing constraint and create a new one. The
only trick is finding the name of the constraint, since it is
system-generated.
sp_help user_pictures
--discover the system-generated name of the constraint
alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
And don't use cascade updates, since you shouldn't be updating primary keys
in the first place.
David
|||David,
Thank you for your reply.
I was hoping to be able to write a GENERAL script that would enable cascade
updates and deletes, sp_help might not be an option since the script will
have to be run at multiple customers' locations.
What could be other options? maybe to write a stored procedure that moves
the data to a temp table from the user_pictures table and properly creates
the new table and moves the data back.
I was wondering if it was possible to specify both options on delete and on
update since if user_name changes in the users table the same change would
then propagate to the user_pictures table.
Thanks again
Vadim
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RbR%23S1DHHA.4620@.TK2MSFTNGP04.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> You just need to drop the existing constraint and create a new one. The
> only trick is finding the name of the constraint, since it is
> system-generated.
> sp_help user_pictures
> --discover the system-generated name of the constraint
> alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> And don't use cascade updates, since you shouldn't be updating primary
> keys in the first place.
> David
|||"Vadim" <vadim@.dontsend.com> wrote in message
news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> David,
> Thank you for your reply.
> I was hoping to be able to write a GENERAL script that would enable
> cascade updates and deletes, sp_help might not be an option since the
> script will have to be run at multiple customers' locations.
> What could be other options? maybe to write a stored procedure that moves
> the data to a temp table from the user_pictures table and properly creates
> the new table and moves the data back.
> I was wondering if it was possible to specify both options on delete and
> on update since if user_name changes in the users table the same change
> would then propagate to the user_pictures table.
>
Sure it's possible, it's just not usually a good idea to modify primary
keys. However if you are doing it, then you should probably cascade the
change.
To write a general script you will need to use a bit of dynamic SQL. Like
this:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
go
declare @.constraint varchar(30)
declare @.sql varchar(2000)
set @.constraint =
(
select object_name(constid) from sysforeignkeys
where rkeyid = object_id('dbo.users')
and fkeyid = object_id('dbo.user_pictures')
)
set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint +
']'
exec (@.sql)
go
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
on update cascade
David
|||David,
Thank you very much for such a detailed reply, This is what I was looking
for.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Sure it's possible, it's just not usually a good idea to modify primary
> keys. However if you are doing it, then you should probably cascade the
> change.
>
> To write a general script you will need to use a bit of dynamic SQL. Like
> this:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
>
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> go
> declare @.constraint varchar(30)
> declare @.sql varchar(2000)
> set @.constraint =
> (
> select object_name(constid) from sysforeignkeys
> where rkeyid = object_id('dbo.users')
> and fkeyid = object_id('dbo.user_pictures')
> )
> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
> + ']'
> exec (@.sql)
> go
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> on update cascade
> David

alter column syntax for enabling cascades

Hi,
I have a question about sql syntax on how to enable both
ON Delete cascade and On Update Cascade for already existing data tables.
One table contains all the users:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
The next table contains user protigraphs
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
My QUESTION is how to add both on delete cascade constraint and on update
cascade constraints to
the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
exists for this table.
Thank you
Vadim"Vadim" <vadim@.dontsend.com> wrote in message
news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a question about sql syntax on how to enable both
> ON Delete cascade and On Update Cascade for already existing data tables.
> One table contains all the users:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
> The next table contains user protigraphs
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> My QUESTION is how to add both on delete cascade constraint and on update
> cascade constraints to
> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
> exists for this table.
>
You just need to drop the existing constraint and create a new one. The
only trick is finding the name of the constraint, since it is
system-generated.
sp_help user_pictures
--discover the system-generated name of the constraint
alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
And don't use cascade updates, since you shouldn't be updating primary keys
in the first place.
David|||David,
Thank you for your reply.
I was hoping to be able to write a GENERAL script that would enable cascade
updates and deletes, sp_help might not be an option since the script will
have to be run at multiple customers' locations.
What could be other options? maybe to write a stored procedure that moves
the data to a temp table from the user_pictures table and properly creates
the new table and moves the data back.
I was wondering if it was possible to specify both options on delete and on
update since if user_name changes in the users table the same change would
then propagate to the user_pictures table.
Thanks again
Vadim
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RbR%23S1DHHA.4620@.TK2MSFTNGP04.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a question about sql syntax on how to enable both
>> ON Delete cascade and On Update Cascade for already existing data tables.
>> One table contains all the users:
>> CREATE TABLE DBO.USERS(
>> USER_NAME VARCHAR(20) PRIMARY KEY,
>> FIRST_NAME VARCHAR(40),
>> LAST_NAME VARCHAR(40)
>> )
>> The next table contains user protigraphs
>> CREATE TABLE DBO.USER_PICTURES(
>> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
>> PICTURE IMAGE NOT NULL
>> )
>> My QUESTION is how to add both on delete cascade constraint and on update
>> cascade constraints to
>> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
>> exists for this table.
> You just need to drop the existing constraint and create a new one. The
> only trick is finding the name of the constraint, since it is
> system-generated.
> sp_help user_pictures
> --discover the system-generated name of the constraint
> alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> And don't use cascade updates, since you shouldn't be updating primary
> keys in the first place.
> David|||"Vadim" <vadim@.dontsend.com> wrote in message
news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> David,
> Thank you for your reply.
> I was hoping to be able to write a GENERAL script that would enable
> cascade updates and deletes, sp_help might not be an option since the
> script will have to be run at multiple customers' locations.
> What could be other options? maybe to write a stored procedure that moves
> the data to a temp table from the user_pictures table and properly creates
> the new table and moves the data back.
> I was wondering if it was possible to specify both options on delete and
> on update since if user_name changes in the users table the same change
> would then propagate to the user_pictures table.
>
Sure it's possible, it's just not usually a good idea to modify primary
keys. However if you are doing it, then you should probably cascade the
change.
To write a general script you will need to use a bit of dynamic SQL. Like
this:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
go
declare @.constraint varchar(30)
declare @.sql varchar(2000)
set @.constraint =(
select object_name(constid) from sysforeignkeys
where rkeyid = object_id('dbo.users')
and fkeyid = object_id('dbo.user_pictures')
)
set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint +
']'
exec (@.sql)
go
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
on update cascade
David|||David,
Thank you very much for such a detailed reply, This is what I was looking
for.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
>> David,
>> Thank you for your reply.
>> I was hoping to be able to write a GENERAL script that would enable
>> cascade updates and deletes, sp_help might not be an option since the
>> script will have to be run at multiple customers' locations.
>> What could be other options? maybe to write a stored procedure that moves
>> the data to a temp table from the user_pictures table and properly
>> creates the new table and moves the data back.
>> I was wondering if it was possible to specify both options on delete and
>> on update since if user_name changes in the users table the same change
>> would then propagate to the user_pictures table.
> Sure it's possible, it's just not usually a good idea to modify primary
> keys. However if you are doing it, then you should probably cascade the
> change.
>
> To write a general script you will need to use a bit of dynamic SQL. Like
> this:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
>
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> go
> declare @.constraint varchar(30)
> declare @.sql varchar(2000)
> set @.constraint => (
> select object_name(constid) from sysforeignkeys
> where rkeyid = object_id('dbo.users')
> and fkeyid = object_id('dbo.user_pictures')
> )
> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
> + ']'
> exec (@.sql)
> go
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> on update cascade
> David|||... and remember to name your constraints in the future... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vadim" <vadim@.dontsend.com> wrote in message news:%23ir%234z1DHHA.3836@.TK2MSFTNGP02.phx.gbl...
> David,
> Thank you very much for such a detailed reply, This is what I was looking
> for.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>>
>> "Vadim" <vadim@.dontsend.com> wrote in message
>> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
>> David,
>> Thank you for your reply.
>> I was hoping to be able to write a GENERAL script that would enable
>> cascade updates and deletes, sp_help might not be an option since the
>> script will have to be run at multiple customers' locations.
>> What could be other options? maybe to write a stored procedure that moves
>> the data to a temp table from the user_pictures table and properly
>> creates the new table and moves the data back.
>> I was wondering if it was possible to specify both options on delete and
>> on update since if user_name changes in the users table the same change
>> would then propagate to the user_pictures table.
>> Sure it's possible, it's just not usually a good idea to modify primary
>> keys. However if you are doing it, then you should probably cascade the
>> change.
>>
>> To write a general script you will need to use a bit of dynamic SQL. Like
>> this:
>> CREATE TABLE DBO.USERS(
>> USER_NAME VARCHAR(20) PRIMARY KEY,
>> FIRST_NAME VARCHAR(40),
>> LAST_NAME VARCHAR(40)
>> )
>>
>> CREATE TABLE DBO.USER_PICTURES(
>> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
>> PICTURE IMAGE NOT NULL
>> )
>> go
>> declare @.constraint varchar(30)
>> declare @.sql varchar(2000)
>> set @.constraint =>> (
>> select object_name(constid) from sysforeignkeys
>> where rkeyid = object_id('dbo.users')
>> and fkeyid = object_id('dbo.user_pictures')
>> )
>> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
>> + ']'
>> exec (@.sql)
>> go
>> alter table user_pictures
>> add constraint fk_user_pictures_users
>> foreign key (user_name) references users(user_name)
>> on delete cascade
>> on update cascade
>> David
>

Allowing remote connections to Sql Server from command line

Hi,

Sql Server doesn't allow remote connections by default. I'm looking for a way to enable remote connections without using the UI. Ideally, I would like a command line tool or a script, though writing a C# tool would be better than nothing.

The way to do this though the UI is to go to the Surface Area Configuration > Services and Connections > MSSQLSERVER > Database Engine > Remote Connections and select "Local and remote connections" and "Using TCP/IP only".

Does anyone know how to do this programmatically?

Thanks,

Ann

Yes, check out the SAC utility:

http://msdn2.microsoft.com/en-us/library/ms162800(SQL.90).aspx

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

Allow null value option in Report Parameters

Hi,
I have very simple parameter driven report that accepts a null value.
When I enable "Allow null value" option under VisualStudio's Report Parameter, the null checkbox is checked by default when I run the report.
Is there anyway that I can uncheck the null check box by default via Visual Studio?
I know I can disable this from Report Property -> Parameter -> Has Default in Report Manager, but I would really like to do this from Visual Studio.
Thank you!You would need to provide a non-null default value for the parameter.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"thepacket" <thepacket@.discussions.microsoft.com> wrote in message
news:06A83613-51EF-4A1A-B23F-61AF592AFC81@.microsoft.com...
> Hi,
> I have very simple parameter driven report that accepts a null value.
> When I enable "Allow null value" option under VisualStudio's Report
Parameter, the null checkbox is checked by default when I run the report.
> Is there anyway that I can uncheck the null check box by default via
Visual Studio?
> I know I can disable this from Report Property -> Parameter -> Has Default
in Report Manager, but I would really like to do this from Visual Studio.
> Thank you!
>

Allow database to assume primary role'

Can any one explain this..
Enable the 'Allow database to assume primary role'
Thanks
NOOR
Hi Noor,
This term is used in Logshipping.
Allow database to assume primary role :-
This lets the destination database become a new log shipping source database
and thus permits a possible future role reversal between the primary and
secondary servers. When you select this option, specify the secondary
server's transaction-log file share as the location for transaction-log
backups from the new source database.
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
> Can any one explain this..
> Enable the 'Allow database to assume primary role'
> Thanks
> NOOR
>
|||Thanks Hari.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#WHrWKHfEHA.2812@.tk2msftngp13.phx.gbl...
> Hi Noor,
> This term is used in Logshipping.
> Allow database to assume primary role :-
> This lets the destination database become a new log shipping source
database
> and thus permits a possible future role reversal between the primary and
> secondary servers. When you select this option, specify the secondary
> server's transaction-log file share as the location for transaction-log
> backups from the new source database.
> Thanks
> Hari
> MCDBA
>
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
>