Altering table structure with out deleting replication ?

Dear Members
Is there a way 2 update the table structure that is part of an article
without deleting the replication ?
Best Regards
Shahid Saleem
*** Sent via Developersdex http://www.codecomments.com ***
Shahid,
the best you can do is sp_repladdcolumn and sp_repldropcolumn. Combinations
of these can be used to alter existing column definitions (see
http://www.replicationanswers.com/AddColumn.asp). This all changes in SQL
Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Altering SQL Field value to Null (DateField)

Hi,
Can someone please help me in resolving this problem.

I am accessing an SQL server from a Web page, when I update a record I sometimes would like to replace a date with a null value. ie. Delete the date in the grid on the Web page and have it remove the date in the database.

I have looked around the web and on this forum and cannot find any information about doing this type of thing.

Someone help would be greatly appreciated.

Thanks..

Regards..

Peter Annandale.Hi Peter,

It depends on the code you're using, but you should be able to set it to DBNull.Value.

If this doesn't work, post your code and we'll try to help you sort it out.

Don|||Don,
since I posted I actually found an article posted by Moorstream in early July about the exact problem I am having. By applying the recommendations of salman_arshad it has fixed my problem.

Thanks for your quick response and assistance.

BTW I had teh right idea with the DBNULL.Value I just wasn't aplying it correctly.

Regards..

Peter Annandale

ALTER TABLE to Allow Null Values

I have an (Access 2003) database and I'm trying to update the schema of the database to allow null values in a column. The column already exists and currently will not allow null values. This is a distributed application (everyone has their own different MDB file) so I need to be able to modify the column through T-SQL.

My statement to try and do this is:
ALTER TABLE clients ALTER COLUMN state VARCHAR(255) NULL

However, when I view the table after running that SQL statement the table is still not allowing null values. Please don't tell me I need to drop the column before allowing null values.

Thanks,
Ryan

> I have an (Access 2003) database

Do you realize this group is about SQL Server?

AMB

|||Nope I just thought it was about T-SQL I didn't see that it was a sub-group of SQL Server. Sorry.
|||

No need to apologize.

There are differences between Access-SQL and T-SQL.

And of course, some Access applications use SQL Server for the backend (ADP Projects.) So at times, this would be the correct forumn. But for your particular question, one of the many Access forumns or NNTP groups 'might' be a better choice.

Alter table new column and update

Hi

for MS SQL 2000/2005

I am having a table (an old database, not mine) with char value for the column [localisation]

Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL

Now i have created a table [Localisation]

Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL

I am adding a new column to Users

ALTER TABLE [Users] ADD
[id_Localisation] int NULL

and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]

something like

UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)

Users.Localisation can have a NULL value (then no id_localisation return)

but it doesnt work because it returns > 1 row

thank you

how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly

thanks a lot

do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....

if so

5 times (for each tables)

ALTER TABLE [Users] ADD
id_Localisation int NULL

ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)

I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??

thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you

this application must work on 2000 and 2005

ALTER TABLE from sqlcmd script

Hi,

I'm trying to add a column to a table, then update that column with a
query. This is all within a single batch. Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added. We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")

However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.

If need be I can just do a SELECT INTO and add the column there, but
it's a pain in the butt and I'm moving a LOT of data just to do what I
want. And no, I can't go back to where the table is created and add
the column. Does anyone have any suggestions? TIA!

- JeffHi,

put a GO after the ALTER TABLEa nd you should be done.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jeff_in_MD (jfowler@.dsoftware.biz) writes:

Quote:

Originally Posted by

I'm trying to add a column to a table, then update that column with a
query. This is all within a single batch. Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added. We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")
>
However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.

The full story is that SQL Server never accepts a missing column. Still
you sometimes you get away with it. Why? Because of deferred name
resolution (one of the biggest misfeatures added in SQL 7). Deferred
name resolution means that if SQL Server finds a query in a batch, where
one or more tables are missing, it defers compilation until later, and
you will not get an error, unless execution reaches that query and the
table is still missing. Quite an aggravated cost for plain spelling
errors!

But if all tables in a query exists, SQL Server also requires that all
columns exist. Thankfully, there is no deferred name resolution on
columns!

The actual effect of these rules is a bit different in SQL 2000 and
SQL 2005, since in SQL 2000, the entire batch is always recompiled,
while SQL 2005 has statment recompile.

Anyway, the proper procedure in a case like yours is to put all
statements that refer to the new column in EXEC, so that they are
compiled after the new column was added. There is not really any
need to put the ALTER statement in EXEC though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>

alter column to not null that has null values

I have to change numeric columns in 2005 table to not null and default value 0.

What I usually do is an update on the columns setting value to 0 where is null. I know you can use 'with values' when adding a column with default 0 and not null to an existing table.

Can something like this be done for altering a column or do I need to do the update?

Thanks

You need to use UPDATE first and then ALTER. ALTER TABLE table ALTER COLUMN only supports changing the type definition, collation and nullability.

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
>

ALTER AND UPDATE together not working....

Hi ,
I have a Stored Procedure like this...
| CREATE PROCEDURE V_test AS
| Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
STAGE_memspan
| FROM membspan MS INNER JOIN STAGE_members MM
I | on SM.memno = MM.memno
|
| ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
NULL
| UPDATE STAGE_membspan
| SET Aidcode_Description = CL.[desc]
II | from SATGE_membspan SM, CODE_LOOKUP CL
| where SM.Aidcode = CL.code and CL.id = 'rp'
After executing this I am getting following error :
Invalid Column name 'Aidcode_Description'
If I execute I part seperately and II part seperately it work fine...
Thanks...!!!!.When the parser is examiningthe statement and validating all the =columnames etc tye ALTER has not yet been done, therefore when it =examines the update the column being referenced does not exist. Run =these as two separate batches and all will be fine. Not really sure why =you want an alter in a stored proc anyway since by definition it can =only be done once, and the major benefit of stored procs comes when they =are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message =news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> > I have a Stored Procedure like this...
> > | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno =3D MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char =(72)
> NULL
> > | UPDATE STAGE_membspan
> | SET Aidcode_Description =3D CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode =3D CL.code and CL.id =3D 'rp'
> > > After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> > If I execute I part seperately and II part seperately it work fine...
> > Thanks...!!!!.
> > > >=20|||Thanks Mike,
I came to know the reason and also I am not including ALTER in a stored
procedure...since its a one time execution process... Thanks again...
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:#2zPClITDHA.1912@.tk2msftngp13.phx.gbl...
When the parser is examiningthe statement and validating all the columnames
etc tye ALTER has not yet been done, therefore when it examines the update
the column being referenced does not exist. Run these as two separate
batches and all will be fine. Not really sure why you want an alter in a
stored proc anyway since by definition it can only be done once, and the
major benefit of stored procs comes when they are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message
news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> I have a Stored Procedure like this...
> | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno = MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
> NULL
> | UPDATE STAGE_membspan
> | SET Aidcode_Description = CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode = CL.code and CL.id = 'rp'
>
> After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> If I execute I part seperately and II part seperately it work fine...
> Thanks...!!!!.
>
>

Almost there (I think)...SQL Update problem...

I am trying to update a single field in a SQL database table. I created a SQLDataSource, configured Select and Update queries, and wrote some code in the script block to do the update after a button click. The SQLDataSource is in a contentplaceholder. What am I doing wrong in the data source, the script block, or both? Thanks so much in advance...

Here is the code for the SQLDataSource:

Dim ImageUploaded As Integer = 2

srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded

srcUpdateImageUploaded.Update()

Here is the code in the script block:

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = ?">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="EmilyTheKitty" PropertyName="Text" Type="Object" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Here is the error that I get:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 164: Dim ImageUploaded As Integer = 2
Line 165:
Line 166: srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded
Line 167: srcUpdateImageUploaded.Update()
Line 168:

Source File: C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx Line: 166

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +104
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5614

There is no @.ImageUploaded parameter in your SQLDataSource, See the modified code below

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="ImageUploaded" PropertyName="Text" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

I appreciate the response...but it didn't work. I tried the modified code you posted. I pasted it into my page, tried it, and got the "Object reference not set to an instance of an object" again. Here is the code that I copied out of my page (it's the same code you posted)...

<asp:SqlDataSourceID="srcUpdateImageUploaded"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

ProviderName="System.Data.SqlClient"

SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"

UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">

<UpdateParameters>

<asp:ControlParameterControlID="TextBox1"Name="ImageUploaded"PropertyName="Text"Type="Int32"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="UserName"PropertyName="Text"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

|||

where you are trying to update your code?? is it in the page load event ?? and also try to change asp:controlParameter into asp:FormParameter. If still not solved pls paste the whole code I will look into it

|||

Okay...I changed my mind on how I want to do this. I did away with the SQL data source connection object and I want to do this entirely with code in the script block. There is a command button that is clicked which invokes the following code. A textbox is included on the page and it is called by the code. The new error I get is this:

" Error updating table. Must declare the scalar variable "@.updatevalue". "

Here is the entirety of the code that is called:

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.updatevalue "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

|||

Okay, I fixed my own problem. I also figured out how these lines are put together so I am beyond merely cutting and pasting code in from books. Here is the correct code (corrected lines in bold, italics, and underlined):

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.ImageUploaded "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

cmd.Parameters.AddWithValue("@.username", username)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

Allowing an exception to a trigger

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

Hello Echo88,

Echo88:

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

A short answer is, yes, you would solve this with an additional column.

A slightly more detailed answer would be, check your "architecture" because your access paths here are "imbalanced". I mean, it sounds like sometimes you directly write to the table, sometimes you write through the stored procedure. My advice is go with stored procedures only. Great chance is your trigger's logic here really belongs to its own procedure which you would call instead of straight writing to the table.

Hope this makes sense. -LV

|||

Thanks for the advice. All writes to any table are done through stored procedures, except triggers because I need to access the trigger tables, but I do it for code maintenance reasons - it just simply easier for to pack it up that way.
Anyway, I am not in love with adding columns after my "architecture" has already been established and I found a better to handle the conditional update. I remember that MS SQL handles UPDATES by placing the new row in the INSERTED trigger table and the old row in the DELETED. I simply just compared the two rows and obtained the results I needed.

All tables under partitioned union view get locked - how to reduce?

Hi. We are using a partitioned view across a number of tables. They are
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McGy
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McGy" <anon@.anon.com> wrote in message
news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McGy wrote:
> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McGy
[url]http://mcgy.blogspot.com[/url]
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McGy wrote:
a
compile.
is
procedures, 1
will
>|||McGy (anon@.anon.com) writes:
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McGy
[url]http://mcgy.blogspot.com[/url]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McGy (anon@.anon.com) writes:
> Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||McGy (anon@.anon.com) writes:
> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

All SSIS packages failing after Windows Update!

Starting saturday all of our SSIS packages on a server (64-bit) starting failing (hundreds of them) the error is:

Precompiled script failed to load. Attempting to reload the script with updated data. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

That Knowledgebase link talks about SP2 fixing the issue but we have SP2 already on the server. The sysdtslog90 table is just packed with these as each script inside each package is getting the same error. Looking at the system log the following were installed as part of windows update shortly before the errors started occuring:

- Update for Windows Server 2003 x64 Edition (KB936357)

- Security Update for Windows Server 2003 x64 Edition (KB926122)

- Microsoft .NET Framework 3.0: x64 (KB928416)

- Security Update for Microsoft .NET Framework, Version 2.0 (KB928365)

- Security Update for Excel 2003 (KB936507)

- Update for Outlook 2003 Junk Email Filter (KB936557)

If I open the package and manually recompile the scripts it works fine.... if I need to do that for every script that is going to likely be several days of just opening the packages one by one and recompiling each script (over 100 packages, each with at least 5 script objects).

|||

I think this is the problem: - Security Update for Microsoft .NET Framework, Version 2.0 (KB928365)

See the sticky post at the top of this forum. I suspect that's what caused the issue, and of course, it's not uninstallable. There's a hotfix listed in that post that you could try applying.

|||

I read that post when I first got the error - but it sounds like that is only if you do not have SP2 install... Am I reading that KB/sticky wrong?

I'll try applying the mentioned hotfix anyway - sure beats massive recompiling by hand

|||Won't let me install those updates since I already have a newer hotfix installed..|||

Hi Chris,

You're saying that those packages fail with "

Precompiled script failed to load. Attempting to reload the script with updated data. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

"

But that message is actually a warning not an error. It should not fail the package just warn that we are trying to workaround a problem we have in the scripts due to the .net change mentioned in the KB article. Can you paste more of the package execution output to this thread so I can try and understand what error is triggered?

Thanks,

Silviu Guea [MSFT], SQL Server Integration Services

|||

Correct... sorry the packages were not failing - however I have everything setup to email on warnings (sometimes those warnings are very useful, for example when it detects dupes in a lookup) so everyone has been getting hit with tons of email.

So .. basically are you saying I can ignore that warning? (I can hard code pkg to ignore that particular warning)

|||

Hi Chris,

You should ignore the warning. We put it there to let you know we are doing something with the script and to help diagnose the fact that the machine has the .net framework change mentioned in the KB article from the announcement found at the top of the SQL Server IS thread.

Thanks,

Silviu Guea [MSFT] SQL Server Integration Services