"Resetting" an obsolete snapshot

Berb,
running a snapshot agent in this case will just add the
new article and is different from a reinitialization.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks, Paul. That's just what I needed to know.
One more related question...
Will this "lock" the database tables? In other words, will the snapshot
process, in this case, prevent use of the database while it is in progress?
Brent
"Paul Ibison" wrote:

> Berb,
> running a snapshot agent in this case will just add the
> new article and is different from a reinitialization.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Brent,
this'll lock the table unless you are using transactional replication with
the concurrent snapshot option.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

"Multiple-Step operation cannot be generated Check each status value" Error

Hi All,

I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"

For your reference I have used the following statement to add the RowGuid field

Alter table <tablename>
Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())

Can anyone please help me.

Thanks

SatheshPost your update statement, and any trigger code on this table.|||Where's Brett when we need him?

Logically, columns don't have any order within a table. It so happens that the current implementation of Microsoft SQL Server has a physical order, but that's implementation dependant. Relying on a column order (logical or physical) is a bug waiting to happen!

The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order. Once you add columns so that the rowid is no longer the last column, your assumed order bytes you. If you specify the columns via a column list, this won't be a problem.DROP TABLE BadExample
GO
CREATE TABLE BadExample (
comment NVARCHAR(25)
)
GO

INSERT INTO BadExample
SELECT 'Simple case'

SELECT * FROM BadExample
GO

ALTER TABLE BadExample
ADD RowGuid uniqueidentifier ROWGUIDCOL NOT NULL
DEFAULT (newid())

INSERT INTO BadExample (comment)
SELECT 'Not as simple example'

SELECT * FROM BadExample
GO

ALTER TABLE BadExample
ADD myDate DATETIME NOT NULL
DEFAULT GetDate()
GO

INSERT INTO BadExample (comment, myDate)
SELECT 'This works!', '1950-01-02'

SELECT * FROM BadExample
GO

INSERT INTO BadExample
SELECT 'This fails', '1960-03-04'

SELECT * FROM BadExample-PatP|||The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order.How did you come up with that from his post!?

"case when" vs multiple update

Which one is faster and cost less for tables with a lot of rows:
1. update atable
set acol = case when cond1 then 1 when cond2 then 2 ..... end
2. update atable set acol = 1 where cond1
update atable set acol = 2 where cond2
update atable set acol = 3 where cond3
....>> Which one is faster and cost less for tables with a lot of rows:
Test it. One UPDATE statement with a CASE tends to be faster than multiple
ones.
Anith|||1 is probably cheaper. But it depends on whether you have a WHERE clause and
the selectivity, how
efficient indexes you have to drive that where clause and also how many rows
you modify.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
> Which one is faster and cost less for tables with a lot of rows:
> 1. update atable
> set acol = case when cond1 then 1 when cond2 then 2 ..... end
> 2. update atable set acol = 1 where cond1
> update atable set acol = 2 where cond2
> update atable set acol = 3 where cond3
> .....
>|||it depends, as usual.
look at the execution plans. If at least one of those updates scans the
whole tables, combine them into one update.
On the other hand, if every simple update accesses the table via a
highly selective index and touches just a handful of rows, it could be
better leave them as is, because the combined statement might be
executed as an expensive table scan|||The table may have up to 10 million rows. And the conditions may be only up
to 30s. So it sounds a low selective.
"Tibor Karaszi" wrote:

> 1 is probably cheaper. But it depends on whether you have a WHERE clause a
nd the selectivity, how
> efficient indexes you have to drive that where clause and also how many ro
ws you modify.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
>|||I'm not sure what you mean by "And the conditions may be only up to 30s". Ar
e you saying that the
SELECT statement will typically return about 30 rows from a table with 10,00
0,000 rows? If so, the
query has very high selectivity (it returns relatively few rows). In that ca
se, I'd go with my
original recommendation. If you cannot create that index as a clustered inde
x, a non-clustered index
should be efficient as well (thanks to the high selectivity).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:5D4A2719-76F6-46BD-9249-94CFA8BE8656@.microsoft.com...
> The table may have up to 10 million rows. And the conditions may be only u
p
> to 30s. So it sounds a low selective.
> "Tibor Karaszi" wrote:
>

"Case insensitive" index

How to create "case insensitive" index? I mean, how to create a table index that will effectivelly index table by mains that differences between small and capital letters are ignored in query like:
SELECT name,surname WHERE UPPER(surname)=UPPER(?)Dobar dan :)

Check this (http://asktom.oracle.com/pls/ask/f?p=4950:8:5884730836779251727::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:1560806455855,) link.