2005: forbidding INSERTs and DELETEs

Please help.
I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?
Thank you for information
/RAM/No permissions exist when a new object is created. Only privileged users
such as the object owner, sysadmin role members, the database owner and
db_owner role members have access until permissions are granted. To grant
only UPDATE permissions:

GRANT UPDATE ON dbo.MyTable TO SomeRole

--
Hope this helps.

Dan Guzman
SQL Server MVP

"R.A.M." <r_ahimsa_m@.poczta.onet.plwrote in message
news:4ltpa2hsjphrsifi2voj6kdr0vfcuh2f0c@.4ax.com...

Quote:

Originally Posted by

Please help.
I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?
Thank you for information
/RAM/

|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?

CREATE TRIGGER update_only FOR INSERT, DELETE AS
RAISERROR('INSERT and DELETE not permitted on this table!', 16, -1)
ROLLBACK TRANSACTION

--
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|||On Thu, 06 Jul 2006 13:42:39 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>Please help.
>I have a table with single row. I need to allow only UPDATEs of the
>table, forbid INSERTs and DELETEs. How to achieve it?
>Thank you for information
>/RAM/

Hi RAM,

Dan already replied how to do this with GRANT and DENY. If you also must
keep the database owner and administrators from accidentally inserting
or deleting a row, add the following trigger:

CREATE TRIGGER NoInsertOrDelete
ON SingleRowTable
AFTER INSERT, DELETE
AS
IF @.@.ROWCOUNT = 0 RETURN
ROLLBACK TRANSACTION
RAISERROR ('Don''t add rows to or remove rows from this table!', 16, 1)
GO

--
Hugo Kornelis, SQL Server MVP

2005 transactional replication statement delivery

Hi There

WHen creating publciations under 2005 i saw a very interesting option under stament delivery, for inserts , updates , deletes there is an option that simply says insert/update/delete statement.

I could find very little in BOL about this under "Article Properties", is this what it soudns like ? FOr example if you say :

update Table set Cloumn = 'whatever', this will not trigger the update sp for each row at the subscriber , will it actually deliver the update statements and literally do the update/insert/delete statement at the subscriber?

Thanx

Yes. It is equivalent to supply value 'SQL' for parameter @.ins_cmd/@.upd_cmd/@.del_cmd in sp_addarticle. For more information, please take a look at the document for sp_addarticle http://msdn2.microsoft.com/en-us/library/ms173857.aspx.

Peng

|||just an FYI, using SQL instead of the custom stored procedures has a negative perf impact, it's primarily used for non-SQL server subscribers. Stick with the custom stored procs if you can.|||

HI Greg

That is a very interresting point you bring up.

I was thinking of it exactly for performance, often in replication we perfom an update statement on the publisher that may take 10 minutes to execute and update say 2 millions rows. However this causes replication latency of up to half an hour at the subscriber at the execution of the MSupd_repl proc 2 million times is slow sometimes each execution of the proc can take a tenth of a second which is alot when you have millions of rows to update, performing the same update at the subscriber would take 10 minutes.

So i actually thought this option could help but apparantly not ?

|||

Hi Dietz,

Change delivery method to "SQL" won't help the performance as replication will still deliver the changes row by row. It means that, in your example, we still call update 2 million times. To solve this problem, you can utilize the SQL 2005 functionality "Publishing Stored procedure Execution in Transactional Replication". More info can be found here (http://msdn2.microsoft.com/en-us/library/ms152754.aspx). It will require you to use stored procedures for your update though.

Peng

|||

The performance you're seeing has nothing to do with SQL vs custom stored procs. In fact, if you used SQL, I bet it owuld be much slower since with SQL, multiple commands are batched then sent via one parameterized statement. With custom stored procs, the proc is compiled once, with SQL, you're pretty much guaranteed that no two batches will be the same, thus you'll have a ton of compilations.

What you're experiencing can be worked around by replicating the execuction of a stored procedure. http://msdn2.microsoft.com/en-us/library/ms152754.aspx.

|||

Thanx Pend and Greg for the feedback, but how is this different from the usual method if it will still call update 2 millios times. DO you mean that instead of calling the MSupd sp on the sunscriber it delivers and update statement for each row?

I am aware of the sp publication method which was available in 2000 i thought this was somehow a similar concept. Thanx

|||If you issue a single update statement at the publisher that affects 2 million rows, you will get 2 million update statements, one for each row, replicated to the subscriber regardless whether you use SQL or custom stored procedures. If you do these batches often, not only can you experience long latency but your distribution database will fill up quickly. In these cases, it's recommended to replicate the execution of a stored procedure if latency and disk space is an issue. By putting your update statement in a proc, and replicating proc execution, rather than send 2 million deletes, it sends the proc call to the subscriber. Now you only have one command instead of 2 million.|||

Hi Greg

Yes that is wagt i thought, however we are getting rid of replication, but good to know.

Thanx

2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.

2005 Fastest communications

Hey,

We do 99.9% inserts. We have a C++ app that communicates with the DBs via ODBC.

Using Sql 2000, it works well. We have another faster server to test with Sql 2005. For some reason, 2005 does not come close to handling the inserts as well as 2000. I've done the statistics and other 2005 config changes. Both boxes are quads with hyper threading. I know it's not the box b/c the 2005 box ran great with Sql 2000.

Is there any reason Sql 2005 would not communicate with our app as well as Sql 2000 would? I'm running out of things to consider. I've done the MAX DOP, etc, it has not mattered. It doesn't compare. We do very rapid inserting.

Thanks!

If you are using same client stack against different servers (2005 vs. 2000), engine guys might find an answer to your question.