The Heap™ – Consultancy ©® - 2018-01-23 (page 2 of 2)

ypercubeᵀᴹ

2:02 PM

@PaulWhite seems like an inverse to me ;)

Paul White

@ypercubeᵀᴹ Even when the QUOTENAME character is single quote?

ypercubeᵀᴹ

@PaulWhite I don't understand. Explain?

Paul White

@ypercubeᵀᴹ dbfiddle.uk/…

QUOTENAME documentation.

ypercubeᵀᴹ

@PaulWhite oh, I hadn't tested with single quote. Interesting

sp_BlitzErik

@TomV oof, that's rough

gbn

2:14 PM

@ypercubeᵀᴹ single quote is never used to delimit object names

Paul White

@gbn So why is it one of the four options for QUOTENAME?

gbn

'myObject" is a string literal

@PaulWhite No idea, but it doesn't delimit object identifier

Paul White

Seems very odd.

sp_BlitzErik

i've only ever used it in dynamic sql to insert something as a string literal

but that's probably not what you're asking about

Paul White

Maybe there's a historical reason.

ypercubeᵀᴹ

2:16 PM

@gbn but it can be used in column aliases in SELECT.

Lamak

@PaulWhite we should ask @SeanGallardy-Microsoft to figure it out

gbn

@PaulWhite I suspect it was useful internally at one point docs.microsoft.com/en-us/sql/relational-databases/databases/…

Paul White

Anyway, I think it's fair to say that PARSENAME never returns a delimited identifier. But it's not explicitly documented that way. Probably just a consequence of metadata functions always (?) working that way.

gbn

@ypercubeᵀᴹ deprecated docs.microsoft.com/en-us/sql/database-engine/… "String literals as column aliases"

I suspect they go hand in hand

hot2use

2

Q: Is the function PARSENAME() the opposite of QUOTENAME()

In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author McNets had noticed, that some internal functions could handle quoted (e.g. [MyTable]) and unquoted...

gbn

2:20 PM

Although it has been deprecated since SQL Server 2005 technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx

Paul White

@gbn Replacements include expression [AS] 'column_alias'

gbn

Oh yeah, didn't actually read it 100%. Just knew something changed

Paul White

That catches a lot of people out.

hot2use

:-)

sp_BlitzErik

"a lot"

-_-

Paul White

2:21 PM

Erland hates it with a passion IIRC

gbn

I suspect the answer lies back in SQL Server 7/2000, before the threat to make SET QUOTE_IDENTIFIERS On

hot2use

@Lamak thanks for the edit

gbn

If anyone wants... microsoft.com/en-us/download/confirmation.aspx?id=51958

Lamak

@hot2use no problem

sp_BlitzErik

2:35 PM

@hot2use probably the only thing worse than auto play video is the auto download pdf

or that occasional dos window that pops up and disappears

Sean Gallardy - Microsoft

@hot2use Of course it's expensive ;)

@Lamak I honestly don't know the history

ypercubeᵀᴹ

@gbn unfortunately only the 'string_alias' = expression is under deprecation

Sean Gallardy - Microsoft

@sp_BlitzErik We call those, virii...

ypercubeᵀᴹ

The expression [AS] 'column_alias' is still valid and not under deprecation either.

Sean Gallardy - Microsoft

@ypercubeᵀᴹ unfortunately? How about a thank you... however, it'll be deprecated for 392729387498324 years and never leave because code written in the 1200's might still need to work.. you know that app from the 3rd part vendor that everyone says they will upgrade next year

ypercubeᵀᴹ

2:42 PM

@SeanGallardy-Microsoft unfortunately for the "only" part.

Sean Gallardy - Microsoft

;)

I wish we deprecated like other people... "Oh hi, we're deprecating this. Next patch it's gone. FIX YOUR CODE!!!1111eleveneleven"

sp_BlitzErik

do you really need all those standard edition users on 2008?

gbn

@SeanGallardy-Microsoft Sort your own client libraries out eh? osql vs isql vs SQLCmd vs ssms vs .net all have different SET options

Sean Gallardy - Microsoft

@gbn It's so you can always mix and match and still have the right style at the gala!

ypercubeᵀᴹ

@SeanGallardy-Microsoft Is there a plan to adjust the deprecation policy? I mean to change it so the list of things under deprecation includes a datetime or a version (e.g. this thing will be definitely gone in v. 2020, plan accordingly)

hot2use

2:46 PM

@sp_BlitzErik I guess that should read @gbn

Sean Gallardy - Microsoft

We could be like Apple, "I see your phone is running slower now that it's the older model...."

sp_BlitzErik

oh yeah, look at that

Sean Gallardy - Microsoft

@ypercubeᵀᴹ Not AFAIK

we'll still keep things indefinitely deprecated....

Lamak

@SeanGallardy-Microsoft but I trust you can make something up

Sean Gallardy - Microsoft

@Lamak lol - I take heart in your faith, sir!

Paul White

2:52 PM

The biggest impact of deprecation, as far as I am concerned, is that new shiny features likely won't work with deprecated stuff.

Hence the slow, lingering death of text, ntext, image for example.

sp_BlitzErik

i think those data types are more widely adopted than columnstore and hekaton combined

Paul White

Also, I guess, the theoretical lack of support for deprecated <x>

sp_BlitzErik

i still see them on client servers

Paul White

Sad!

Sean Gallardy - Microsoft

@PaulWhite spot on. Although there are other things that aren't deprecated but don't work together just out of their nature.

sp_BlitzErik

2:54 PM

@PaulWhite many such cases, etc.

Sean Gallardy - Microsoft

@sp_BlitzErik Preach.

sp_BlitzErik

@SeanGallardy-Microsoft like AGs and index rebuilds ;)

Paul White

How many versions did it take to get CASE added to Hekaton native? Four?

Sean Gallardy - Microsoft

@sp_BlitzErik :clears throat: Why are you rebuilding so many and so large of indexes so often? Doesn't everyone have 10GbE?

sp_BlitzErik

that's still only 1.2gb/s max

Paul White

2:56 PM

It's important to rebuild indexes regularly. You can never be 100% sure they were built right before.

3

sp_BlitzErik

unless you have some crazy round robin scheme

Sean Gallardy - Microsoft

@PaulWhite It's like he knows right when I take a sip of tea...

@sp_BlitzErik Team them

Paul White

Aaron used to do it to me all the time. Coffee out the nose hurts.

He made me fall off my chair once.

Sean Gallardy - Microsoft

LOL

sp_BlitzErik

sounds like bullying

Paul White

2:58 PM

Cynical in here this morning :)

Sean Gallardy - Microsoft

and it was online so that's cyber bullying

isn't that the death penalty now?

Paul White

issues random bans

sp_BlitzErik

especially to a protected pronoun like paul

Paul White

Call me pie!

Sean Gallardy - Microsoft

@PaulWhite Paul White, the Oprah of bans today! "You get a ban! and you get a ban! everyone gets a ban!"

sp_BlitzErik

2:59 PM

penguin pie or pie penguin?

Sean Gallardy - Microsoft

Either way, delicious

Paul White

@sp_BlitzErik Either. I am not unreasonable, after all

Sean Gallardy - Microsoft

Just ask your wife?

Paul White

Quite so

Sean Gallardy - Microsoft

1

Q: fn_dblog not showing records while LDF file is large

I need to find some specific transactions in the transactionlog file, so I use fn_dblog function. But the function is only returning a few rows. Meanwhile the LDF file for my database is a couple of gigabytes at least. I know the fn_dblog function is only searching the active part of the log: ...

sql-server transaction-log

sp_BlitzErik

3:02 PM

tempting

Sean Gallardy - Microsoft

People are getting really good at reading a log that isn't human readable

was that too snarky?

sp_BlitzErik

"hello i don't understand how transaction logs work but i'd like to find transactions in them"

Sean Gallardy - Microsoft

le sigh

I'm all for helping and teaching... but maybe not today

Those 3 am flights come up fast

sp_BlitzErik

where are you headed?

Sean Gallardy - Microsoft

Belleveue, WA if you want to come say hi

40f and raining all week :( fml

sp_BlitzErik

3:15 PM

i am still in brooklyn, so that's not a likely outcome

that is ideal weather, i don't know what you're on about

Sean Gallardy - Microsoft

smh

85 and sunny, that's ideal weather

sp_BlitzErik

armpit

Sean Gallardy - Microsoft

My body temp is naturally low :( so cold things make me cold

I'm not a space heater like some

I'm cold blooded, I need to sun myself on a rock every few hours

sp_BlitzErik

or just work inside a tanning bed

Sean Gallardy - Microsoft

I already have to wear spf 5million

I'm probably the whitest guy you know

hot2use

3:30 PM

I love discussing things

Sean Gallardy - Microsoft

and such, and whatnot?

Conor

Anybody have experience with a MariaDB galera cluster?

hot2use

gosh, it went silent in here

No, sorry.

Andy K

has anyone seen this kind of errors during a pg_restore?

WARNING: errors ignored on restore: 4

Sean Gallardy - Microsoft

Where is @EvanCarroll when he could actually be useful

Andy K

3:42 PM

pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE "FI-D0_ExchangeRates_LAST" OWNER TO postgres;


pg_restore: creating TABLE "dw.FI-D6_Inventory"

trying to do that

pg_restore.exe --host dwetl.postgres.database.azure.com --port 5432 --username "avostuffn@dwetl" --dbname "BI_DB" --no-password --no-acl -n public --verbose "C:\Users\KW\Desktop\bi_db.backup"

hot2use

Didn't you import as postgres user?

Andy K

as a connecting role, you mean? yes

CREATE ROLE postgres LOGIN
  ENCRYPTED PASSWORD 'md5d85ab409aae633bbe5b9d4c944256ab6'
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;

hot2use

You might need to add additional permissions to the avostuffn@dwetl account.

Andy K

@hot2use which kind?

hot2use

ALTER USER avostuffn@dwetl WITH SUPERUSER;

Andy K

3:48 PM

@hot2use 'k

got it

hot2use

and possibly..

Andy K

ERROR:  must be superuser to alter superusers
********** Erreur **********

ERROR: must be superuser to alter superusers
État SQL :42501

GRANT azure_superuser TO avostuff;

hot2use

so you'd have to login with an account that is superuser. This is normally the postgres login.

Andy K

1

A: Can't create a PostgreSQL Superuser role to get an Application installed and running

To explain the users and roles a little better for Azure Database for PostgreSQL: By default, when a server is created we have the following 3 roles defined which you can also see when you run "SELECT rolname FROM pg_roles;" – azure_pg_admin azure_superuser server admin login – the admin log...

wait

hot2use

I saw you modified postgres to not have the SUPERUSER role. That might have effectively shut you out...

Andy K

3:55 PM

correct @hot2use

postgres is a super user in previous db

damn

hot2use

115

Q: I forgot the password I entered during postgres installation

I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error: psql: FATAL: password authentication failed for user "hisham" hisham-agil: hisham$ psql Is there anyway to reset the password o...

postgresql postgresql-9.1 forgot-password

Might help

Sean Gallardy - Microsoft

I had someone IM me and say hello.. then not say another word and leave the conversation. WTF is going on today?

sp_BlitzErik

maybe they died

did the tsunami hit seattle?

Lamak

@sp_BlitzErik always the optimist

Sean Gallardy - Microsoft

I dunno they are in New York... so... you tell me @sp_BlitzErik

sp_BlitzErik

4:00 PM

want me to go check on them?

"hey, sean gallardy wanted me to check on you to make sure you're alright"

Sean Gallardy - Microsoft

LMFAO

My own personal SPNE team

Lamak

@sp_BlitzErik and to say hello too

Sean Gallardy - Microsoft

Special People Named Erik

I can't make this up... I'm not creative enough... but...

"Does SQL Server support Emoji characters?"

I'm done for the day.

Lamak

@SeanGallardy-Microsoft well....we are waiting

Sean Gallardy - Microsoft

I SAID GOOD DAY

Lamak

4:06 PM

YOU HADN'T SAID IT YET!

sp_BlitzErik

@SeanGallardy-Microsoft SELECT 'you' AS [💩]

Joe Obbish

@sp_BlitzErik please use QUOTENAME

@SeanGallardy-Microsoft hello

Sean Gallardy - Microsoft

@JoeObbish Good day

sp_BlitzErik

@JoeObbish 'no'

Joe Obbish

do you want sql injection in your poop?

sp_BlitzErik

4:23 PM

i don't know what you're insinuating

i'm a married man

Joe Obbish

I never insinuate

Sean Gallardy - Microsoft

@sp_BlitzErik How is Brent? ;)

Lamak

@SeanGallardy-Microsoft didn't know you had that kind of comment in you

Sean Gallardy - Microsoft

I'm full of all kinds of stuff

mostly guts and organs

sp_BlitzErik

and sql injection

Sean Gallardy - Microsoft

4:28 PM

for sure that

Lamak

@SeanGallardy-Microsoft nice try, microsoft bot

Sean Gallardy - Microsoft

DRATS! How'd you know?

ypercubeᵀᴹ

@AndyK did that CREATE ROLE postgres ... succeed? You should have got an error like role "postgres" already exists

Lamak

@SeanGallardy-Microsoft lucky guess

Andy K

4:44 PM

@ypercubeᵀᴹ nope

ypercubeᵀᴹ

@AndyK is it some special installation?

Oh, just noticed the azure thing

Anyway, the errors seem to be because you took the dump as user A and you are restoring as user B.

Read the docs: postgresql.org/docs/current/static/backup-dump.html

> Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)

Roles/users in postgres are not "inside" a database, they are independent of databases. They live in postgres instance. If you want to restore a db to another instance, you have to create the relevant roles first.

Andy K

@ypercubeᵀᴹ issue is the role is existing

but with a super user

and as you can see, azure_superuser

ypercubeᵀᴹ

And in the target instance is a simple user (not a supersuer)?

Andy K

@ypercubeᵀᴹ yes

azure do not give superuser to its role

ypercubeᵀᴹ

Are we talking about user postgres or azure_superuser?

Andy K

4:56 PM

@ypercubeᵀᴹ postgres

the closest to superuser is azure_pg_admin

:42446809 azure_superuser is Microsoft

ypercubeᵀᴹ

You are confusing us

Andy K

1 hour ago, by Andy K

1

A: Can't create a PostgreSQL Superuser role to get an Application installed and running

To explain the users and roles a little better for Azure Database for PostgreSQL: By default, when a server is created we have the following 3 roles defined which you can also see when you run "SELECT rolname FROM pg_roles;" – azure_pg_admin azure_superuser server admin login – the admin log...

@ypercubeᵀᴹ azure_superuser exists but no one can use it

ypercubeᵀᴹ

OK, don't use it then

Andy K

the closest to superuser is azure_pg_admin

ypercubeᵀᴹ

Why are we talking about somethign we can't use?

Andy K

4:59 PM

@ypercubeᵀᴹ just to give you the context

so this is what I did

CREATE ROLE postgres LOGIN
  ENCRYPTED PASSWORD 'md5d85ab409aae633bbe5b9d4c944256ab6'
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
GRANT azure_pg_admin TO postgres;

ypercubeᵀᴹ

and then?

Did the restore work or you got another error?

Andy K

I still have the issue

below

pg_restore: [archiver (db)] Error from TOC entry 269; 1259 17241 VIEW FI-D0_ExchangeRates_LAST postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be member of role "postgres"
Command was: ALTER TABLE "FI-D0_ExchangeRates_LAST" OWNER TO postgres;

ypercubeᵀᴹ

How did you run the restore?

Andy K

one sec

pg_restore --clean --if-exists --no-acl --host dwetl.postgres.database.azure.com --port 5432 --username "avostuff@dwetl" --dbname "BI_DB" --no-password --verbose "C:\Users\KW\Desktop\bi_dbv1.backup"

I used the --no-acl to get rid of these roles stuff but to no avail

ypercubeᵀᴹ

What if you replace --username "avostuff@dwetl" with --username postgres ?

Andy K

5:04 PM

@ypercubeᵀᴹ let me do that

ypercubeᵀᴹ

I got to go. If you haven't solved it by then, I'll be back in about 2 hours.

Andy K

@ypercubeᵀᴹ will do. thanks mate

pg_restore: connecting to database for restore
pg_restore: [archiver (db)] connection to database "BI_DB" failed: FATAL:  Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.
FATAL:  Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.

ypercubeᵀᴹ

I don't know.

Perhaps --username "postgres@dwetl"

or give the role postgres ot that user first

Andy K

@ypercubeᵀᴹ I will raise a question on dba.stackexchange.com and sent that to Azure postgres Twitter account

Master Database

5:34 PM

0

Q: New mobile navigation got vertical scroll bar

This look not so good in the new mobile navigation. There is an vertical scroll bar.

bug design

McNets

5:46 PM

Hey, my first popular question.

Hi @EvanCarroll

Evan Carroll

sup sup mcnets!

daymmnnnn that's a hot question

dezso

6:36 PM

→ 2 messages moved to Trash

Tom V

7:52 PM

Can anyone replace this question to meat stack exchange? — H. Pauwelyn 2 hours ago

For a moment I believed there really was a meat subsite

Evan Carroll

8:21 PM

man I'm going to write a book about all the shit I learned playing with SQL Server.

THANKS Microsoft.

hot2use

On a roll: Self-Learner awarded

3

Evan Carroll

I think I would highly suggest inquisitive open source users accustomed to having everything given to them on a silver platter with source code, debugger symbols, and an open platform play with Microsoft products.

I can almost understand arbitrary x86 assembly

a week later.

Stuff is really exciting and cool

sp_BlitzErik

8:55 PM

@EvanCarroll if the book turns a profit are you going to donate it all to postgres development?

Evan Carroll

for extra trolol

Actually, I could use the book to fund my own development of PostgreSQL!

You know there is a badge for asking 100 well received questions and only one person has it.

on dba.se

Socratic.

I think it'd be really hard to get that these days.

sp_BlitzErik

for you

Evan Carroll

yea, for me.

and for anyone else seeing as no one else has it.

sp_BlitzErik

i hope i never have that many questions about sql server that i can't answer on my own

Evan Carroll

... no one said you can't answer questions here on your own

ask them, work towards an answer with the borg

sp_BlitzErik

9:03 PM

the blogs must flow

Evan Carroll

And generally, I'd rather bother you, or Paul, or our Evil Empire Co. drone Sean than actually do the work myself.

sp_BlitzErik

i'll treat you to the same when i have to start using postgres :D

hopefully you can answer questions about something other than GIS

Evan Carroll

By all means, if no one has asked it, ask away. Just upvote me when I answer.

Happy to help out.

sp_BlitzErik

NO UPVOTES

Evan Carroll

THEN DIE SLOWLY.

with <3

PostgreSQL is really awesome. When you use it, you WIN.

You want to be a winner, right?

sp_BlitzErik

9:13 PM

what do i win?

bluefeet

@sp_BlitzErik his un-ending devotion

sp_BlitzErik

@bluefeet can't i just win a lifetime supply of boogers or something

bluefeet

brb gonna send you a big box of boogers

sp_BlitzErik

@bluefeet i wonder if i could clone you from your boogers

then i'd have a jr dba for life

:D

Evan Carroll

they don't like to be called jr. They like to be called Microsoft Certified

4

sp_BlitzErik

9:21 PM

that's a good one

bluefeet

@sp_BlitzErik ew, and no

sp_BlitzErik

fine then

dezso

9:58 PM

@PaulWhite whoever declined my latest flag, did you notice that the same link is included in the (self-)answer?

Paul White

@dezso Wasn't me. Will check into it later when at a real computer

sp_BlitzErik

@PaulWhite are you not on your production phone?

Paul White

Mobile interface is pretty poor for mod stuff

Evan Carroll

10:44 PM

pretty poor for all stuff, really.

well, I like knowing when people comment, upvote, downvote and the like it's good at notification

Paul White

11:33 PM

And for reading the heap

Sean Gallardy - Microsoft

11:46 PM

In the off chance anyone is around Bellevue, WA let me know, otherwise I'll see your virtual avatars on Monday.

Evan Carroll

@PaulWhite it doesn't do that at all afaik

@PaulWhite it just links out to the browser

Transcript for

The Heap™ – Consultancy ©®