The Heap™ – Consultancy ©®

6:33 AM

1

Q: How can i limit the number of rows a user can update?

One of our Business Research Analysts has a habit of accidentally updating without a where clause. Preferably the solution is better training and following a procedure like writing a SELECT before UPDATE. Or just security policies... However, those things aren't going to happen soon. So I woul...

Tom V

6

Q: Can you prevent more than X number of rows being updated in SQL Server?

Is there a way to configure SQL Server to limit the number of rows than an update statement can modify? Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 rows, they would receive an error or some other preventive message.

sql sql-server

Phil

7:30 AM

Mornin'

Andriy M

7:52 AM

Morning

dezso

@PaulWhite now that wasn't me

but, of course, these are always implied

Phil

8:12 AM

Help please:

0

Q: SQL Server - NTEXT columns and string manipulation

I have a table with an NTEXT column called comments. I have a second string, let's call it anothercomment that needs placing inside a given comments string after the word UPDATEHERE. Casting to nvarchar(max) truncates the comments string, so I cannot use the likes of CHARINDEX() (Msg 8152, Level...

sql-server sql-server-2008-r2 string

:-)

Andriy M

@Phil What do you mean by "Casting to nvarchar(max) truncates the comments string"? There should be no truncation converting ntext to nvarchar(max).

Tom V

Is anothercomment also ntext or varchar or a fixed string in your query?

Andriy M

I think it would help if we saw the actual attempts.

Phil

@TomV A VARCHAR

Tom V

Yes, create table and insert statements please :)

Phil

8:16 AM

@TomV With an 8k string?

Tom V

not necessarily

Andriy M

I'd be interested to see the CHARINDEX expression (including the conversion to nvarchar(max) where it was necessary)

Phil

` select CHARINDEX(cast(comments as nvarchar(max)),'UPDATEHERE')` gives `Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.`

Andriy M

Ah, that's easy. Just swap the arguments.

Phil

LOL

I'm dumb

2

Tom V

8:19 AM

:)

Andriy M

To be fair, CHARINDEX and PATINDEX are really counter-intuitive in that regard. All the other string functions accept the subject string as the first argument.

Andy K

morning

Tom V

@AndriyM Yes I remember swearing the first time I worked with NTEXT

Phil

Operand data type ntext is invalid for add operator.

So how do I concatenate3 strings back together again :P

Andriy M

If it's saying "add operator", then perhaps one of the operands ends up as a number (a numeric type) (and so the + is treated as addition rather than concatenation). You either need to cast it as a string explicitly or maybe you could use the CONCAT() function where the arguments are converted implicitly.

Phil

8:29 AM

@AndriyM I'm on 2008r2, so no CONCAT().

Andriy M

Ah, then it should be casting as a string then.

Again, if you show what you are doing, it might be easier to help

Phil

I'll see if i can put a long ntext value into sql fiddle

Tom V

@Phil could you try

-- Create the table
CREATE TABLE [dbo].[PhilsTable](
[comment] [ntext] NULL,
[anothercomment] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

GO

-- insert very long string
INSERT INTO [dbo].[PhilsTable] (comment, anothercomment) VALUES (N'This is a test UPDATEHERE This is the end of the test' + REPLICATE (N'x' ,1000000 ) , 'this goes in here');

-- verify data
SELECT * FROM [dbo].[PhilsTable];

-- perform replace
SELECT CAST(REPLACE(CAST(comment AS NVARCHAR(MAX)),'UPDATEHERE','UPDATEHERE' + anothercomment) AS NTEXT) FROM [dbo].[PhilsTable];

Andriy M

@TomV REPLICATE (CAST(N'x' AS nvarchar(max)), 1000000), or it won't work as expected.

Tom V

@AndriyM Hmm, still provides the same output though

But I admit I was unsure if that replicate was OK with ntext

Tried casting to ntext but that's obviously not allowed

Phil

8:39 AM

@TomV That insert truncates to 8000 characters

Tom V

but does the select work on your dataset?

Andriy M

@TomV What I mean is:

SELECT LEN(REPLICATE (     N'x'                  , 1000000));  -- returns 4000
SELECT LEN(REPLICATE (CAST(N'x' AS nvarchar(max)), 1000000));  -- returns 1000000

Tom V

@Phil And if you apply @AndriyM 's suggestion?

Phil

That works. I love you all! <3

5

ntext can die a horrible death!

5

Post an answer and i'll give you some love

Phil

9:16 AM

I don't normally ask questions on here, so goes to show how much it was doing my head in!

Andriy M

I like how Tom's answer got away without requiring the question to be updated with the details that actually helped to resolve the issue. "You are probably doing something wrong, try this." Very nice.

Tom V

@AndriyM Yeah I was thinking about how to word that or edit in phils comments from here and then thought "I'm not sure this 'll help that many people in the future so here goes"

The SO way, just post the code and be done with it :)

Andriy M

@TomV I was about to excuse myself from answering and I really hated doing that because Phil's question was otherwise well presented. I was really glad you took the trouble of answering it, so thank you, as it happens.

Tom V

9:50 AM

@AndriyM And I was a bit embarrassed to post a joint effort as an answer but figured you wouldn't mind, so I guess it worked out

Andriy M

Absolutely

Tom V

10:25 AM

I hope this OP stays around, I used to work with him and he's brilliant

3

Q: Why is the query_hash zero for EXEC statements?

I'm setting up a monitoring system for SQL Server using Extended Events to find heavy queries as 'production feedback' for our developers. I'm using the events sp_statement_completed and sql_statement_completed, with predicate filters on cpu_time, logical reads, etc. I was hoping to aggregate th...

sql sql-server-2014 extended-events

Phil

10:41 AM

@TomV I like it when awesome people arrive

dezso

12:44 PM

Nuke this, please

0

Q: Can't create view

I want to create a view like CREATE VIEW monitors_info AS SELECT monitors_id, nom FROM monitors; But, I got this message ERROR: No schema has been selected to create in. (using psql) I don't know what I miss, please help me.