@JackDouglas I've never been in that file messing around :O

@ypercube Thanks, checking that out!

(also sorry for late reply)

(Also, my question was about limiting access to one db alone).

@ypercube thanks, I didn't know about that

@JackDouglas I'm learning myself.

Postgres permission/privileges system is quite more complicated than MySQL (that I used to work with before)

The SQL-Server's one, never managed to comprehend.

Agreed. And I don't think the names are very informative... But with great complexity comes great power.

@ypercube I'm sorry to see Varoufakis go - interesting chap.

@JackDouglas No one liked him (I mean the other EU ministers)

yes

he was a bit too keen on truth and facts for their liking I think

@JackDouglas Sony Kapoor agrees with you: twitter.com/SonyKapoor/status/617944212781273088

@JackDouglas Yes me too. Not popular with the other EU FMs, but that was a plus.

This might interest you: nytimes.com/2015/07/03/business/dealbook/…

Not sure how accurate it is

@ypercube "...Yanis, if you keep talking about the debt, a deal will be impossible, Mr. Dijsselbloem said..." - that's why politics is bonkers

While trying to reproduce the issue in this question, I encountered a situation where the CREATE TABLE statement gives me a warning that the table's maximum row size exceeds 8060 bytes, but I can't really exceed that in a subsequent INSERT:

USE tempdb
GO
CREATE TABLE dbo.test (
  --f0 int,
  f01 char(500),  f02 char(500),  f03 char(500),  f04 char(500),
  f05 char(500),  f06 char(500),  f07 char(500),  f08 char(500),
  f09 char(500),  f10 char(500),  f11 char(500),  f12 char(500),
  f13 char(500),  f14 char(500),  f15 char(500),  f16 char(500),
  f17 varchar(500),  f18 varchar(10)--,  f19 varchar(500),  f20 varchar(500)
);
GO
INSERT INTO dbo.test (
  f01,  f02,  f03,  f04,
  f05,  f06,  f07,  f08,
  f09,  f10,  f11,  f12,
  f13,  f14,  f15,  f16,

SQL Server playing safe?

you can't store more than 8k in a single row as it exceeds the page size

@JackDouglas @ybercube I hadn't realised he was the same Greek economist who designed the online markets for Steam.

yes, same guy

@JamesLupolt wow, strange world

^^^ have I been fair with this guy?

Morning all

Thanks @Mark, I'll leave it there then

Morning @Concerned

@TomV Yes but the question is, why does it warn me the maximum row size for that table exceeds the allowed maximum when I create the table, and yet I can't (= "it's impossible to") actually exceed the allowed maximum in that case.

My INSERT above inserts a row of the largest possible size (for that table).

isn't it more than 8060 bytes (your row)?

@ypercube Yes, but it still gets inserted.

My guess is the varchar(500)'s contents may actually be stored outside the main storage. But then why the warning?

yes, I think they are stored off-row

The warning is perhaps for performance.

or a remnant of previous versions

msdn.microsoft.com/en-us/library/ms186981%28v=sql.105%29.aspx

Yes, that explains a lot. Not the second sentence of the warning (INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit) but that may indeed be a leftover from earlier versions. (Or it should have been worded more clearly.)

Why are people so dumb? (mute question, I know)

but I need the first two before and after. Except when I need three

I think they mean when there are not enough rows either before or after the specified ID, more rows should be retrieved from the other side

yes, but they still need order by

to make the total of five

the query by Rob farley will not work as expected.

(even in the case where ther are enough rows on both sides)

I'd say it will work as expected in very specific cases

Like when there's five rows and you have specified the ID in the middle :)

heh ;)

afternoon

what could this mean?

@ypercube you are very patient - if i see a bunch of code without any easily visible structure, I usually refrain from trying to understand it

and did you mean a separate question?

@JackDouglas @ypercube There was a long interview with him on Econtalk a couple years ago. I just hadn't made the connection that it was the same person until now.

I imagine his memoirs of this period will be very interesting.

As an aside, Valve runs SQL Server and has an opening for a DBA/systems developer type job right now. : )

@ypercube, @AndriyM Another version, just for fun

select L.id
from (
     select top(5) U.id
     from (
          select *
          from (
               select lag(L.id, 4) over(order by L.id) as id4a,
                      lag(L.id, 3) over(order by L.id) as id3a,
                      lag(L.id, 2) over(order by L.id) as id2a,
                      lag(L.id, 1) over(order by L.id) as id1a,
                      L.id as id0,
                      lead(L.id, 1) over(order by L.id) as id1e,
                      lead(L.id, 2) over(order by L.id) as id2e,

@dezso could be spam - it's getting harder to be sure

@dezso yes, I meant question

@MikaelEriksson nice

I realized I have no idea how it works ;)

Don't worry, your version is the better one :)

ah, the trick is the column names.

yep

mornin

@JackDouglas do these completely garbled spam messages work? I mean on this site?

do you mean "are we failing to pick them up"?

we do occasionally get flags from SO mods saying "this post is by a clever spammer"

the hardest ones are those involving several accounts - where the question exists to justify the answer

@MikaelEriksson To my not perfectly trained eye, that seems like a valid alternative worth posting rather than just fun.

@JackDouglas no, not really

I mean that I'm repelled by an answer if I cannot understand the sentences in it - that doesn't look like good/effective marketing

@AndriyM Oh no... then I actually have to explain it.

BTW, the answer by @ypercube is way better when you have an index on id so... no.

@MikaelEriksson Yes, it helps mine too.

@dezso there is a temptation to click on the link to see if it makes sense of the answer!

eav, xml or json?

it's been quiet here

WHOO HOO!!!!!!

datastax.com/dev/blog/new-in-cassandra-3-0-materialized-views

/sarcasm

@swasheck how do you even get those links?

what does that mean?

news.ycombinator.com

So, how was your 4th of july?

do you want the real answer?

I think so

do you want to give me an imaginary one?

well i'm feeling salty so here it goes

oh boy

Grabbing popcorn for this one

let me preface by saying that i understand that i do have a privilege to live here. not everyone sees it that way and they have fair, valid points. there are things that i enjoy about living here. having said that, 4th of july celebrations blow my mind. communities and sub-communities spend an aggregate of millions of dollars for bouncy houses and fireworks for their own independent celebrations.

neighborhoods are filled with teenagers who dont care that anyone else has to sleep and they set off illegal fireworks into the wee hours of the morning.

@swasheck Couldn't agree more on that point

@swasheck well, we don't celebrate 4th of July. At least, we didn't till now

in short --- 4th of july is supposed to celebrate the birth of our nation, but usually just degenerates into a lot of bombast over individual satisfaction over the needs of the community. i couldnt think of a more fitting display of american-ism than this

@Lamak yeah. PKs FTW

exactly

@swasheck Not to mention the sloppy drunk people

congrats, by the way. all it took was a proctology exam on uruguay

@swasheck and an exam to the stomach area of messi ;-)

anyway, it was a good game

@KrisGruttemeyer i see that as falling under the jurisdiction of "individual satisfaction." having been sloppy drunk in the past, i understand, a bit. however, as i get older i see more and more the fact that our own "rugged individualism" is cannibalizing itself

@Lamak i only saw pictures of the attempted evisceration

@swasheck They were shooting off fireworks until almost 1am here. Fireworks JUST became legal this year in NY. Being from NC, I don't really care, I've always had them or been able to travel to SC to get the big ones.

The point I was getting at with the drunks would be the ones that get behind the wheel of a car or boat. My parents have a lake house and 2 boats on Lake Norman in NC, a kid was killed while wakeboarding by a drunk boat driver on Saturday,

@Lamak what's your take on messi, by the way. so many people claim that he just can't win the big matches ... but every time i watch him play, he just looks like a magician. you can't deny his skill, and the argentine side is stacked so he's not playing with slouches ... but he's not yet tasted national glory.

@swasheck you should've seen the game. It was quite exciting (for us)

@Lamak i couldnt find coverage. i dont think local Telemundo aired it

@swasheck He shines when he has the right guys with him. The rest of the team has big names, but they don't know how to properly play the way messi needs. That doesn't mean that we shouldn't expect more from him though

@KrisGruttemeyer yes. it happens in higher concentrations during holidays, but drinking and driving (anything) is a problem in our country

@Lamak it's almost harder for him to win with a team full of great skill players. i get the sense that he expects more out of his national teammates because they are so good. if they all sucked then maybe he really could just put the team on his shoulders. that doesn't explain success with barca, though

so maybe not

@swasheck it's because he runs the barca, so they do what's best for his type of game. He's really talented, but gets frustrated to quickly (he doesn't react badly, but his performance drops)

@Lamak interesting take. i'd not noticed that but i'm only just now coming into the football|futbol|fußball mainstream (we'll call it 2012). when he's on, it's almost impossible to take him off the ball.

damn, I wanted to say "too quickly"

i understood

@swasheck yeah, but my ego, man

@Lamak shall i flag it as offensive?

@swasheck anyway, we have to thank higuain for his penalty kick

@swasheck it would be appropriate

@Lamak "penalty kick"

or something ;-)

just watched the video ... messi's reaction was priceless

@swasheck It's kind of amusing to watch non-RDBMS slowly reinvent all the RDBMS features, sometimes including the ones they initially insist they don't need. Although Cassandra seems like it's good software.

@swasheck even though sanchez's PK is the one that got the more public attention, matias fernandez's one was top notch.

Mongo finally has row-level locking now. Maybe MVCC next?

@JamesLupolt agreed. it's pretty amusing

@Lamak great strike. that's fairly unstoppable from the line

@JamesLupolt awesome. so we can lose writes in multiple versions

Is it possible (inside SQL SERVER trigger) to know which table it is attached to ?

(Why ? I have an AUDIT script (via trigger via COLUMNS_UPDATED()) and I don't want the table name to be hardcoded : i.imgur.com/oKu2gq7.png

what do you want to do?

Hi Lamak

When a table is updated ( column) , save the old value and the new value in audit table

I already succeeded

/************************************************************
 * Code formatted by SoftTree SQL Assistant © v7.0.158
 * Time: 06/07/2015 22:45:40
 ************************************************************/

USE [piki]
GO
/****** Object:  Trigger [dbo].[audit]    Script Date: 07/06/2015 22:40:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================

But all I have ( for now) is the colName which was updated.

now I have to create select [the col] from deleted and insert it to newValue

same for deleted

[the col] is calculated as you can see

but the table name is hardcoded

I don't want that

Is there anything like select the table which current trigger is attached to ?

@RoyiNamir but....you have to hardcode the table on the trigger anyway... (ON dbo.YourTable)

why does it matters?

I don't think I understand

I want to change this :

  SELECT COLUMN_NAME  AS NAME
	        FROM   INFORMATION_SCHEMA.COLUMNS
	        WHERE  TABLE_NAME = 'AgeGroups'

to something like :

  SELECT COLUMN_NAME  AS NAME
	        FROM   INFORMATION_SCHEMA.COLUMNS
	        WHERE  TABLE_NAME =GetCurrentAttachedTable()

yes, I understand. I'm asking why....You still need to hardcode the table name to create the trigger on the first place

I want to apply this trigger to other 1000 tables we have

you can't.

I know , I will have to tun it manually

or via ms_foreach table

a trigger starts with: CREATE TRIGGER [dbo].[YourTrigger] ON [dbo].[YourTable]

oh I see your point

(too tired , silly thought of myne)

One more little thing , what about multiple udpates ?

How would I "join" each row to its deleted and inserted ?

Im not going to check each table's PK

I mean if someone updates 5 rows , I need to track each row with it's corresponding new value / old value from inserted deleted.

Any idea ?

yeah, googling ;-). Here are some results (obviously, this are just some, you should choose the one that fits your needs the most)

@Lamak Thank you very much. the solution from the first link you provided (stackoverflow.com/a/19737882/859154) is working great. I've just tested it

I had nothing to do with that, just a lucky link

Yes. I must say that I did search in the morning. But all results where like long if updated(col1) then... if updated(col2)....

Which I didn't want

@RoyiNamir that's why I said it was lucky ;-)

:-)

When I think about it I can use create the trigger as string (later to be exec with chained table name which will be via ms_spForEachTable , and this way I can do it for all 1K tables we have

I think I found a flaw

Aaron said that the only true safe formats for datetime is :

YYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

The script save datetime of operation as :

SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
               + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

Which can fail.

(under certain conditions )