Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.

Aaargh! Storedproc vs SQL in Gridview update

When I attempt to update using a stored procedure I get the error 'Incorrect syntax near sp_upd_Track_1'. The stored procedure looks like the following when modified in SQLServer:

ALTERPROCEDURE [dbo].[sp_upd_CDTrack_1]

(@.CDTrackNamenvarchar(50),

@.CDArtistKeysmallint,

@.CDTitleKeysmallint,

@.CDTrackKeysmallint)

AS

BEGIN

SETNOCOUNTON;

UPDATE [Demo1].[dbo].[CDTrack]

SET [CDTrack].[CDTrackName]= @.CDTrackName

WHERE [CDTrack].[CDArtistKey]= @.CDArtistKey

AND [CDTrack].[CDTitleKey]= @.CDTitleKey

AND [CDTrack].[CDTrackKey]= @.CDTrackKey

END

But when I use the following SQL coded in the gridview updatecommand it works:

"UPDATE [Demo1].[dbo].[CDTrack]

SET [CDTrack].[CDTrackName] = @.CDTrackName

WHERE [CDTrack].[CDArtistKey] = @.CDArtistKey

AND [CDTrack].[CDTitleKey] = @.CDTitleKey

AND [CDTrack].[CDTrackKey] = @.CDTrackKey"

Whats the difference? The storedproc executes ok in sql server and I guess that as the SQL version works all of my databinds are correct. Any ideas, thanks, James.

Not sure if it's the source of your problem, but shouldn't sp_upd_Track_1 and p_upd_CDTrack_1 be the same or was that a typo?

|||Yeah, typo, the correct name is specified in the storedproc and referenced correctly in the asp.

A Trigger Question

hello there, i am trying to write a swl trigger for update in my database
application...
there are 2 tables like this:
Table1: Stock
Column1: StockCode
Column2: Stockquantity
Table2: Group
Column1: GroupCode
Column2: StockCode
Column3: StockQuantity
Column4: GroupQuantity
Column5: OrderAmount
now i wrote a sql trigger like this:
----
--
create trigger StockUpdate
on [Group]
for update
as
declare @.Code char(10)
declare @.SCode char(10)
declare @.sq decimal(9)
declare @.Ch decimal(9)
set @.Code = (select GroupCode from Inserted)
set @.SCode = (Select StockCode from Inserted)
set @.sq = (select StockQuantity from Inserted)
set @.ch = (select OrderAmount from Inserted)
begin
update Stock
set StockQuantity = STockQuantity - @.sq*@.ch
from Stock
where Stock.Stockcode = @.Scode
end
----
--
now ets say i have tables filles like this:
Table1: Stock
StockCode: StockQuantity:
Stock001 10000
Stock002 8908
Stock003 20000
Table2:
GroupCode: StockCode: StockQuantity:
GroupQuantity: OrderAmount:
Group001 Stock001 20
1 0
Group001 Stock002 20
1 0
Group001 Stock003 1
1 0
Group002 Stock001 5
2 0
now when i write this command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
The trigger will increase the GroupQuantity Value by 2;
and also decrease the StockQuantity by (2*5 = 10) so the stockquantity will
be 9990. this trigger works quite well if theres only
one 'Group002' in the second table. but i want this trigger to work for all
columns when i write the command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
i get this error:
"Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
..
so what can i do to make this trigger for when theres more then 1 querry
results'
THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
EFFOERTS...
have a nice day!Serdar C.,
A trigger is not fired by each row affected, instead it is fired by each dml
operation (insert, update, delete), so you have to take in mind that
multirows can be affected bby the operation. Supposing that column
[StockCode] is the pk of table [Stock], then try:
update Stock
set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmount
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Serdar C." wrote:

> hello there, i am trying to write a swl trigger for update in my database
> application...
> there are 2 tables like this:
> Table1: Stock
> Column1: StockCode
> Column2: Stockquantity
> Table2: Group
> Column1: GroupCode
> Column2: StockCode
> Column3: StockQuantity
> Column4: GroupQuantity
> Column5: OrderAmount
> now i wrote a sql trigger like this:
> ----
--
> create trigger StockUpdate
> on [Group]
> for update
> as
> declare @.Code char(10)
> declare @.SCode char(10)
> declare @.sq decimal(9)
> declare @.Ch decimal(9)
> set @.Code = (select GroupCode from Inserted)
> set @.SCode = (Select StockCode from Inserted)
> set @.sq = (select StockQuantity from Inserted)
> set @.ch = (select OrderAmount from Inserted)
> begin
> update Stock
> set StockQuantity = STockQuantity - @.sq*@.ch
> from Stock
> where Stock.Stockcode = @.Scode
> end
> ----
--
>
>
> now ets say i have tables filles like this:
> Table1: Stock
> StockCode: StockQuantity:
> Stock001 10000
> Stock002 8908
> Stock003 20000
> Table2:
> GroupCode: StockCode: StockQuantity:
> GroupQuantity: OrderAmount:
>
> Group001 Stock001 20
> 1 0
> Group001 Stock002 20
> 1 0
> Group001 Stock003 1
> 1 0
> Group002 Stock001 5
> 2 0
>
> now when i write this command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
> The trigger will increase the GroupQuantity Value by 2;
> and also decrease the StockQuantity by (2*5 = 10) so the stockquantity wil
l
> be 9990. this trigger works quite well if theres only
> one 'Group002' in the second table. but i want this trigger to work for al
l
> columns when i write the command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
> i get this error:
> "Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated."
> ...
> so what can i do to make this trigger for when theres more then 1 querry
> results'
>
> THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
> EFFOERTS...
> have a nice day!
>
>
>|||Correction,
Sorry, I am making same mistake. We have to use an aggregate function here.
update Stock
set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
i.OrderAmount)
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Alejandro Mesa" wrote:
> Serdar C.,
> A trigger is not fired by each row affected, instead it is fired by each d
ml
> operation (insert, update, delete), so you have to take in mind that
> multirows can be affected bby the operation. Supposing that column
> [StockCode] is the pk of table [Stock], then try:
> update Stock
> set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmou
nt
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode = stock.Stockco
de)
> go
>
> AMB
> "Serdar C." wrote:
>|||Thanx so much... it is working now...
god bless you :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:563B331D-1FE1-43F5-B00C-2AA7E1EC9750@.microsoft.com...
> Correction,
> Sorry, I am making same mistake. We have to use an aggregate function
> here.
> update Stock
> set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
> i.OrderAmount)
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode =
> stock.Stockcode)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>

A tool...how long will this take...

Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||run a query with a nolock hint to see where you are in
the process.
Mark Baekdal
www.dbghost.com
>--Original Message--
>Is there a tool, in SQL 2K or third party, that can look
>at an UPDATE, or even DELETE, I'm about to run,
>and "estimate" how long it'll run. I started a massive
>UPDATE on 37 million rows on one column. I stared the
>UPDATE 14 hours ago, and it's still running. I can't
run
>a query to determine how many records have already been
>updated, because the update has a lock on the table.
>I was just wondering was there something I could've done
>beforehand, that could've told me how long this UPDATE
>would take. I've played with the Execution Plan
feature,
>and it does give some useful information, but I'm
>specifically looking for costs in terms of "time".
>Thanks
>Roz
>.
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> Is there a tool, in SQL 2K or third party, that can look
>> at an UPDATE, or even DELETE, I'm about to run,
>> and "estimate" how long it'll run. I started a massive
>> UPDATE on 37 million rows on one column. I stared the
>> UPDATE 14 hours ago, and it's still running. I can't
run
>> a query to determine how many records have already been
>> updated, because the update has a lock on the table.
>> I was just wondering was there something I could've done
>> beforehand, that could've told me how long this UPDATE
>> would take. I've played with the Execution Plan
feature,
>> and it does give some useful information, but I'm
>> specifically looking for costs in terms of "time".
>> Thanks
>> Roz
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> >--Original Message--
> >Roz
> >I would divide a long transaction into small. Have you
> checked transaction
> >log file? Did it grow?
> >SET ROWCOUNT 1000
> >WHILE 1 = 1
> >BEGIN
> >
> > UPDATE command
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> >END
> >
> >SET ROWCOUNT 0
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> Is there a tool, in SQL 2K or third party, that can look
> >> at an UPDATE, or even DELETE, I'm about to run,
> >> and "estimate" how long it'll run. I started a massive
> >> UPDATE on 37 million rows on one column. I stared the
> >> UPDATE 14 hours ago, and it's still running. I can't
> run
> >> a query to determine how many records have already been
> >> updated, because the update has a lock on the table.
> >>
> >> I was just wondering was there something I could've done
> >> beforehand, that could've told me how long this UPDATE
> >> would take. I've played with the Execution Plan
> feature,
> >> and it does give some useful information, but I'm
> >> specifically looking for costs in terms of "time".
> >>
> >> Thanks
> >> Roz
> >>
> >
> >
> >.
> >|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
>> Uri,
>> Yep, that's exactly what I did. I broke the Update into
>> 5000 records at a time. The Tlog is small, as it should
>> be since I'm Checkpointing quite frequently. But the
>> Update is still running. I guess it just takes this
>> long...
>> Roz
>>
>> >--Original Message--
>> >Roz
>> >I would divide a long transaction into small. Have you
>> checked transaction
>> >log file? Did it grow?
>> >SET ROWCOUNT 1000
>> >WHILE 1 = 1
>> >BEGIN
>> >
>> > UPDATE command
>> >
>> > IF @.@.ROWCOUNT = 0
>> > BEGIN
>> > BREAK
>> > END
>> > ELSE
>> > BEGIN
>> >
>> > CHECKPOINT
>> > END
>> >END
>> >
>> >SET ROWCOUNT 0
>> >
>> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> >> Is there a tool, in SQL 2K or third party, that can
look
>> >> at an UPDATE, or even DELETE, I'm about to run,
>> >> and "estimate" how long it'll run. I started a
massive
>> >> UPDATE on 37 million rows on one column. I stared
the
>> >> UPDATE 14 hours ago, and it's still running. I can't
>> run
>> >> a query to determine how many records have already
been
>> >> updated, because the update has a lock on the table.
>> >>
>> >> I was just wondering was there something I could've
done
>> >> beforehand, that could've told me how long this
UPDATE
>> >> would take. I've played with the Execution Plan
>> feature,
>> >> and it does give some useful information, but I'm
>> >> specifically looking for costs in terms of "time".
>> >>
>> >> Thanks
>> >> Roz
>> >>
>> >
>> >
>> >.
>> >
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> >--Original Message--
> >That will give you 7400 separate transactions. It could
> still take a while,
> >but you will not cause your transaction log to grow
> uncontrollably. Using
> >smaller chunks gives you more options. You could insert
> into (or update) a
> >"logging" table after each pass through the while loop.
> That would give you
> >the ability to know how many you have done and how many
> more rows are left
> >to process.
> >
> >--
> >Keith
> >
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> >> Uri,
> >>
> >> Yep, that's exactly what I did. I broke the Update into
> >> 5000 records at a time. The Tlog is small, as it should
> >> be since I'm Checkpointing quite frequently. But the
> >> Update is still running. I guess it just takes this
> >> long...
> >>
> >> Roz
> >>
> >>
> >> >--Original Message--
> >> >Roz
> >> >I would divide a long transaction into small. Have you
> >> checked transaction
> >> >log file? Did it grow?
> >> >SET ROWCOUNT 1000
> >> >WHILE 1 = 1
> >> >BEGIN
> >> >
> >> > UPDATE command
> >> >
> >> > IF @.@.ROWCOUNT = 0
> >> > BEGIN
> >> > BREAK
> >> > END
> >> > ELSE
> >> > BEGIN
> >> >
> >> > CHECKPOINT
> >> > END
> >> >END
> >> >
> >> >SET ROWCOUNT 0
> >> >
> >> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> >> Is there a tool, in SQL 2K or third party, that can
> look
> >> >> at an UPDATE, or even DELETE, I'm about to run,
> >> >> and "estimate" how long it'll run. I started a
> massive
> >> >> UPDATE on 37 million rows on one column. I stared
> the
> >> >> UPDATE 14 hours ago, and it's still running. I can't
> >> run
> >> >> a query to determine how many records have already
> been
> >> >> updated, because the update has a lock on the table.
> >> >>
> >> >> I was just wondering was there something I could've
> done
> >> >> beforehand, that could've told me how long this
> UPDATE
> >> >> would take. I've played with the Execution Plan
> >> feature,
> >> >> and it does give some useful information, but I'm
> >> >> specifically looking for costs in terms of "time".
> >> >>
> >> >> Thanks
> >> >> Roz
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >.
> >sql

A tool...how long will this take...

Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
Roz
Roz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>
|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz

>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
run[vbcol=seagreen]
feature,
>
>.
>
|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz

>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz

>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE

A tool...how long will this take...

Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz

>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
run[vbcol=seagreen]
feature,[vbcol=seagreen]
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,|||Roz
A good idea would be to display a running total of how many rows you have up
dated everytime you hit your 5000 transaction count. That way at least you w
ould have an idea how long it will take that way. (Too late now I know)
Regards
John|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz

>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE[vbcol=seagreen]
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
>
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE

A strange problem with updatable partitioned view.

Hi all,
I am working on a distributed database. I defined linked
servers, partitioned views etc. I can delete/insert/update
data from the view. Now the problem is if I add more ID
ranges to the partitioning column in the check, sometimes
it worked or sometimes it didn't. See the following sample
code:
-- Create linked server SERVER0,SERVER1 on two SQL
servers.
-- SERVER0 one one machine
exec sp_addlinkedserver 'SERVER0', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC OUT', @.optvalue='TRUE'
-- SERVER1 on another machine
exec sp_addlinkedserver 'SERVER1', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC OUT', @.optvalue='TRUE'
-- Create database Test_DB on each server.
-- ON SERVER1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
[ObjectID] <= 100)
GO
-- ON Server1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 )
GO
-- ON SERVER0: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
GO
--ON SERVER1: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
01-01',1)
It succeeds
-- ON both server, drop the checks
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 )
GO
-- On server1, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 )
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
01-01',1)
It succeeds
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
GO
-- On Server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
600)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
01-01',1)
It succeeds
-- On any server,
Delete vwtest
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
[ObjectID] between 40075 and 50074)
GO
-- On server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
[ObjectID] BETWEEN 50075 and 60074)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
01-01',1)
It will fail. the error message is "UNION ALL view vwtest
is not updatable becuase a partitioning column is not
found."
I am totally lost. Anyone knows how SQL server decides one
column is a partitioning or not. Here I used the same rule
but the result is different.
Any ideas? Thanks in advance.Peter,
did not go through your detailed post. However, I bet that you did your
modification with EM. It is known that when you do such changes in EM to
updateable partitioned view the EM does not do it right. Try use QA. If it
does not work, try recreate the view in QA.
HTH
Quentin
"Peter" <phe@.Visualplant.com> wrote in message
news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> Hi all,
> I am working on a distributed database. I defined linked
> servers, partitioned views etc. I can delete/insert/update
> data from the view. Now the problem is if I add more ID
> ranges to the partitioning column in the check, sometimes
> it worked or sometimes it didn't. See the following sample
> code:
> -- Create linked server SERVER0,SERVER1 on two SQL
> servers.
> -- SERVER0 one one machine
> exec sp_addlinkedserver 'SERVER0', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- SERVER1 on another machine
> exec sp_addlinkedserver 'SERVER1', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- Create database Test_DB on each server.
> -- ON SERVER1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> [ObjectID] <= 100)
> GO
> -- ON Server1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 )
> GO
>
> -- ON SERVER0: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> GO
> --ON SERVER1: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
> 01-01',1)
> It succeeds
> -- ON both server, drop the checks
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 )
> GO
> -- On server1, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 )
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
> 01-01',1)
> It succeeds
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> GO
> -- On Server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
> 600)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
> 01-01',1)
> It succeeds
> -- On any server,
> Delete vwtest
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
> [ObjectID] between 40075 and 50074)
> GO
> -- On server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
> [ObjectID] BETWEEN 50075 and 60074)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
> 01-01',1)
> It will fail. the error message is "UNION ALL view vwtest
> is not updatable becuase a partitioning column is not
> found."
> I am totally lost. Anyone knows how SQL server decides one
> column is a partitioning or not. Here I used the same rule
> but the result is different.
>
> Any ideas? Thanks in advance.
>|||Thanks for your reply.
However, I didn't change the constraint from EM. What I
did is that drop the constraint for all servers, then
create the constraint for all servers from QA. It worked
in some cases. It seems if I have more ID ranges or I have
ID ranges with negative value, it will fail. I tried to
recreate the view, it didn't work too.
The code I posted is exactly what I ran in QA.
>--Original Message--
>Peter,
>did not go through your detailed post. However, I bet
that you did your
>modification with EM. It is known that when you do such
changes in EM to
>updateable partitioned view the EM does not do it right.
Try use QA. If it
>does not work, try recreate the view in QA.
>HTH
>Quentin
>"Peter" <phe@.Visualplant.com> wrote in message
>news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> Hi all,
>> I am working on a distributed database. I defined
linked
>> servers, partitioned views etc. I can
delete/insert/update
>> data from the view. Now the problem is if I add more ID
>> ranges to the partitioning column in the check,
sometimes
>> it worked or sometimes it didn't. See the following
sample
>> code:
>> -- Create linked server SERVER0,SERVER1 on two SQL
>> servers.
>> -- SERVER0 one one machine
>> exec sp_addlinkedserver 'SERVER0', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- SERVER1 on another machine
>> exec sp_addlinkedserver 'SERVER1', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- Create database Test_DB on each server.
>> -- ON SERVER1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> [ObjectID] <= 100)
>> GO
>> -- ON Server1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 )
>> GO
>>
>> -- ON SERVER0: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> --ON SERVER1: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(10,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(110,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server, drop the checks
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 )
>> GO
>> -- On server1, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 )
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(11,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(111,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> GO
>> -- On Server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
>= -
>> 600)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(13,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(113,'2003-
>> 01-01',1)
>> It succeeds
>> -- On any server,
>> Delete vwtest
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
or
>> [ObjectID] between 40075 and 50074)
>> GO
>> -- On server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
OR
>> [ObjectID] BETWEEN 50075 and 60074)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(17,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(117,'2003-
>> 01-01',1)
>> It will fail. the error message is "UNION ALL view
vwtest
>> is not updatable becuase a partitioning column is not
>> found."
>> I am totally lost. Anyone knows how SQL server decides
one
>> column is a partitioning or not. Here I used the same
rule
>> but the result is different.
>>
>> Any ideas? Thanks in advance.
>
>.
>|||Peter,
Oops.
I saw you used Alter Table to add the constraint. Did you try to create the
constraint together with the table creation? Try that.
Quentin
"peter" <phe@.VisualPlant.com> wrote in message
news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
> Thanks for your reply.
> However, I didn't change the constraint from EM. What I
> did is that drop the constraint for all servers, then
> create the constraint for all servers from QA. It worked
> in some cases. It seems if I have more ID ranges or I have
> ID ranges with negative value, it will fail. I tried to
> recreate the view, it didn't work too.
> The code I posted is exactly what I ran in QA.
>
> >--Original Message--
> >Peter,
> >
> >did not go through your detailed post. However, I bet
> that you did your
> >modification with EM. It is known that when you do such
> changes in EM to
> >updateable partitioned view the EM does not do it right.
> Try use QA. If it
> >does not work, try recreate the view in QA.
> >
> >HTH
> >
> >Quentin
> >
> >"Peter" <phe@.Visualplant.com> wrote in message
> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> >> Hi all,
> >>
> >> I am working on a distributed database. I defined
> linked
> >> servers, partitioned views etc. I can
> delete/insert/update
> >> data from the view. Now the problem is if I add more ID
> >> ranges to the partitioning column in the check,
> sometimes
> >> it worked or sometimes it didn't. See the following
> sample
> >> code:
> >>
> >> -- Create linked server SERVER0,SERVER1 on two SQL
> >> servers.
> >> -- SERVER0 one one machine
> >> exec sp_addlinkedserver 'SERVER0', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >> -- SERVER1 on another machine
> >> exec sp_addlinkedserver 'SERVER1', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >>
> >> -- Create database Test_DB on each server.
> >> -- ON SERVER1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> >> [ObjectID] <= 100)
> >> GO
> >>
> >> -- ON Server1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 )
> >> GO
> >>
> >>
> >> -- ON SERVER0: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> --ON SERVER1: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (10,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (110,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server, drop the checks
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 )
> >> GO
> >> -- On server1, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 )
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (11,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (111,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> >> GO
> >> -- On Server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
> >= -
> >> 600)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (13,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (113,'2003-
> >> 01-01',1)
> >> It succeeds
> >>
> >> -- On any server,
> >> Delete vwtest
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
> or
> >> [ObjectID] between 40075 and 50074)
> >> GO
> >> -- On server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
> OR
> >> [ObjectID] BETWEEN 50075 and 60074)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (17,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (117,'2003-
> >> 01-01',1)
> >>
> >> It will fail. the error message is "UNION ALL view
> vwtest
> >> is not updatable becuase a partitioning column is not
> >> found."
> >>
> >> I am totally lost. Anyone knows how SQL server decides
> one
> >> column is a partitioning or not. Here I used the same
> rule
> >> but the result is different.
> >>
> >>
> >> Any ideas? Thanks in advance.
> >>
> >
> >
> >.
> >|||The result is the same. I have other aprtitioned tables
that work well. But the partitioned column of this table
has negative IDs. I think this is the reason.
>--Original Message--
>Peter,
>Oops.
>I saw you used Alter Table to add the constraint. Did
you try to create the
>constraint together with the table creation? Try that.
>Quentin
>
>"peter" <phe@.VisualPlant.com> wrote in message
>news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
>> Thanks for your reply.
>> However, I didn't change the constraint from EM. What I
>> did is that drop the constraint for all servers, then
>> create the constraint for all servers from QA. It worked
>> in some cases. It seems if I have more ID ranges or I
have
>> ID ranges with negative value, it will fail. I tried to
>> recreate the view, it didn't work too.
>> The code I posted is exactly what I ran in QA.
>>
>> >--Original Message--
>> >Peter,
>> >
>> >did not go through your detailed post. However, I bet
>> that you did your
>> >modification with EM. It is known that when you do
such
>> changes in EM to
>> >updateable partitioned view the EM does not do it
right.
>> Try use QA. If it
>> >does not work, try recreate the view in QA.
>> >
>> >HTH
>> >
>> >Quentin
>> >
>> >"Peter" <phe@.Visualplant.com> wrote in message
>> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> >> Hi all,
>> >>
>> >> I am working on a distributed database. I defined
>> linked
>> >> servers, partitioned views etc. I can
>> delete/insert/update
>> >> data from the view. Now the problem is if I add more
ID
>> >> ranges to the partitioning column in the check,
>> sometimes
>> >> it worked or sometimes it didn't. See the following
>> sample
>> >> code:
>> >>
>> >> -- Create linked server SERVER0,SERVER1 on two SQL
>> >> servers.
>> >> -- SERVER0 one one machine
>> >> exec sp_addlinkedserver 'SERVER0', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >> -- SERVER1 on another machine
>> >> exec sp_addlinkedserver 'SERVER1', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >>
>> >> -- Create database Test_DB on each server.
>> >> -- ON SERVER1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> >> [ObjectID] <= 100)
>> >> GO
>> >>
>> >> -- ON Server1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 )
>> >> GO
>> >>
>> >>
>> >> -- ON SERVER0: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> --ON SERVER1: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (10,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (110,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server, drop the checks
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 )
>> >> GO
>> >> -- On server1, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 )
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (11,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (111,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> >> GO
>> >> -- On Server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and
[ObjectID]
>> >= -
>> >> 600)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (13,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (113,'2003-
>> >> 01-01',1)
>> >> It succeeds
>> >>
>> >> -- On any server,
>> >> Delete vwtest
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
>> or
>> >> [ObjectID] between 40075 and 50074)
>> >> GO
>> >> -- On server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and
40074
>> OR
>> >> [ObjectID] BETWEEN 50075 and 60074)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (17,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (117,'2003-
>> >> 01-01',1)
>> >>
>> >> It will fail. the error message is "UNION ALL view
>> vwtest
>> >> is not updatable becuase a partitioning column is not
>> >> found."
>> >>
>> >> I am totally lost. Anyone knows how SQL server
decides
>> one
>> >> column is a partitioning or not. Here I used the same
>> rule
>> >> but the result is different.
>> >>
>> >>
>> >> Any ideas? Thanks in advance.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I have the same problem. My view is local and I can
insert using table names, but only read using view.

A simple Update query using a date - conversion from msaccess

I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query

update
timecard
set
TcdDate = #3/18/05#

TcdDate is defined as a date/time type

It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?Converting a whole database into SQL Svr?? Why dont u try ur hand at writing a DTS package and running it. It does everything for u automatically though u need to spend some time writing it. It is absolutely reusable.|||Your sql stmt should be :

UPDATE
timecard
SET
TcdDate= '3/18/05'

In sql server the datetime datatype is treated as string hence you need to wrap it in quotes.|||Argh! I thought I had tried that already. But, in retrospect, I had coded TcdDate = 3/18/05, and thus my problem lay with a couple of single quotes.

Sometimes it takes a fresh set of eyes to look at a problem.

Thank you for your help.|||FWIW it would be safer to stick to ISO date format (YYYYMMDD). This will insure that the month and date portions are recognized correctly regardless of the language settings on the SQL Server:

UPDATE
 timecard
SET
 TcdDate = '20050318'

Terri

a simple question about SQL Update statement

hi, everyone,

When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?

Can somebody help me? Thanks in advance!

Hmmm, I disagree with your observation. An UPDATE statement will not perform an INSERT. You must have some other code happening behind the scenes which is performing the INSERT.|||What's the VBS code that you used for UPDATE table? BTW, there is no SQL2003 server Should be SQL2005?

a simple insert/update trigger

Hi I am looking to create a simple trigger for both UPDATES and INSERTS
where any change to tbl1 will be inserted/updated in tbl2.
The insert works okay, with values added to both tables, however when I
perform an update, it seems to add an extra row in tbl2.
Here's the code:
CREATE TABLE [dbo].[tbl1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT, UPDATE
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
UPDATE tbl2
SET [Name] = Inserted.[Name],
[Team] = Inserted.[Team]
FROM Inserted
WHERE [ID] = Inserted.[ID]
---
What am I doing wrong? do I need to use IF UPDATE()?
Thanks
qh75Why do you want to use a single trigger? You can certainly do that
(modify your INSERT to insert the row only if it doesn't already exist)
but since you want totally different actions in the case of UPDATE and
INSERT it will surely be more efficient to use two triggers instead of
one.
Secondly, your tables as posted have no keys at all. Apparently even
the ID isn't declared as unique (the IDENTITY property doesn't actually
guarantee uniqueness) and the other columns are nullable. Even if that
constrain exists on ID it is definitely not safe to assume that a row
would be assigned the same ID in both tables. On the other hand you
have included the ID in the INSERT, which will fail unless you turn
IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY in
the second table or not.
The solution is to declare natural keys on both tables (presumably Name
and/or Team) and make those column(s) NOT NULL. Then join the two
tables on that key rather than the IDENTITY.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Why do you want to use a single trigger? You can certainly do that
> (modify your INSERT to insert the row only if it doesn't already
exist)
> but since you want totally different actions in the case of UPDATE
and
> INSERT it will surely be more efficient to use two triggers instead
of
> one.
I just thought it could be performed in one trigger, basically (without
getting into the identity stuff) I was looking for some logic I could
use in the one trigger to check for both inserts and both updates.

> Secondly, your tables as posted have no keys at all. Apparently even
> the ID isn't declared as unique (the IDENTITY property doesn't
actually
> guarantee uniqueness) and the other columns are nullable. Even if
that
> constrain exists on ID it is definitely not safe to assume that a row
> would be assigned the same ID in both tables. On the other hand you
> have included the ID in the INSERT, which will fail unless you turn
> IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY
in
> the second table or not.
I'll add a key to the tables and try that.
Thanks for the reply.
qh|||Anyhoo, this is what I came up with:
---
CREATE TRIGGER [tri_Update] ON [dbo].[tbl1]
FOR UPDATE
AS
IF UPDATE ([Team])
BEGIN
UPDATE tbl2
SET [Team] = Inserted.[Team]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
IF UPDATE ([Name])
BEGIN
UPDATE tbl2
SET [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
----
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
----
If you know of a way of combining the two triggers into one (if indeed
it can be done) please let me know.
Cheers
qh

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!