« first day (2193 days earlier)      last day (2661 days later) » 

1:34 AM
@bluefeet thanks will check it out
 
2:22 AM
IT IS I, EVAN.
PostGIS is too slow. =(
 
 
2 hours later…
4:51 AM
gawd, any gis guys here?
 
@PaulWhite so I managed to get up to 15 during this run ...
 
5:07 AM
when you've been postgising and you learn that = is bounding box equality...
fml.
 
 
5 hours later…
9:41 AM
just flagged this question for closing (Housekeeping):
5
Q: SQL Server 2005 Replication

KraigI am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected by vpn SQL Server 2005 domain1.local server 1 windows 2003 server 192.168.10.1 connected by vpn S...

"This question should be closed and/or deleted, as the question has no answer and is unlikely to be answered because SQL Server 2005 is no longer supported. (Housekeeping)"
 
10:24 AM
@hot2use If the community wants to close it that's fine, but I don't think there is any appropriate mod housekeeping to do
 
SQL Bits is in in April the middle of nowhere: sqlbits.com/Information/…
 
10:41 AM
@TypoCubeᵀᴹ interesting
no map, no locality name, nothing
 
@dezso locality name?
 
hiya
quick question
 
@dezso the home page has "Telford". But yeah, I guess it should have it on the Venue page as well ... And a map or link to a map would be nice
 
I'm trying to figure out a way to find if a varchar column does not start with a digit
the question is for postgresql
I found this,it works but it is bit weird ...
 
@TypoCubeᵀᴹ well
 
10:44 AM
SELECT * FROM dw."T13_tablename"
WHERE
(LEFT("Family_code", 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
then I look further and I found this
SELECT * FROM dw."T13_tablename"  WHERE "Family_code" NOT LIKE '%[0-9]%';
but the new one is only for sqlserver
I would like to know if there is similar as this regex with postgres
 
should be NOT LIKE '[0-9]%'
 
@AndyK SELECT 'bhkcdhkbkb' ~ E'^[^0-9]';
 
@dezso worked like a treat
 
it needs a different indexing strategy
@TypoCubeᵀᴹ that won't work in Postgres
 
10:49 AM
@dezso I meant that for SQL Server. That the initial % should be removed, for the 2 ways to be equivalent (or almost equivalent)
You can probably also use WHERE ("Family_code" < '0' OR "Family_code" >= ':')
 
@TypoCubeᵀᴹ that's true
 
you guys are amazing
simply amazing
 
@dezso The ~ is SIMILAR ?
 
@TypoCubeᵀᴹ no, SIMILAR TO is a bit different: Like LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string.
 
I think Erwin has an answer which compares the 3 alternatives (LIKE, SIMILAR, regex).
 
10:54 AM
so 'bla' SIMILAR TO '[0-9]%' should work, too
 
56
Q: Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Lucas KauffmanI had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if there is a way to rewrite this to become more performant. So I can avoid or and / or like?

So, this should work, too (and maybe the fastest):
WHERE ("Family_code" ~<~ '0' OR "Family_code" ~>=~ ':'`)
and off course
 WHERE NOT ("Family_code" LIKE '0%' OR "Family_code" LIKE '1%' OR ...)
 
@TypoCubeᵀᴹ especially suitable for some heapers who love these operators
 
Loving it
 
jeez
that's heavy
wow
 
11:11 AM
yey, one more hat. Epiphany!
 
11:23 AM
12
A: Should old questions that have no answers and are "too localized" or "too broad" be closed?

jcolebrandGreat question. Here's my $0.05 (I'm giving you more than a tupence so deal with it). If the question is old and good, then answer it if you can. If the question is old and poor but can be edited to be good, then edit it and answer it. If the question is old and off topic then vote for it to b...

@jcolebrand Well played sir.
 
@PaulWhite any chance you come to present at SQL Bits this year?
 
@TypoCubeᵀᴹ None. Unless I win Lotto or someone invents a transporter.
@TypoCubeᵀᴹ You have one more coming as well. Should suit you fine.
 
@PaulWhite ah, the "I feel fine!"
 
@TypoCubeᵀᴹ Yes. And if you haven't contributed to any tag wikis in the bash so far, you should consider it.
 
@PaulWhite my daughter will be excited. She has been asking for that since the hats started.
 
11:41 AM
@JackDouglas Ok. Thanks. The thing is, the question is (well was) valid. It's just going to be in the system and pushed up the queue by community ad absurdum.
@PaulWhite Ok. Might have a go at answering. Problem is all the reference material at Microsoft has gone.
 
11:53 AM
@hot2use I'm not saying the question should or shouldn't be closed (I haven't looked at it closely enough to say), just making you aware of that meta item. The 2005 documentation is still available (though archived), just not directly from BOL/MSDN/TechNet.
 
12:05 PM
@TypoCubeᵀᴹ that would fit perfectly @PaulWhite and @Lamak and all the others hanging from planet Earth upside down
@TypoCubeᵀᴹ isn't that 28 bytes 32 because of padding? dba.stackexchange.com/a/159852/6219
 
@dezso because the total should be a multiple of 8? Probably yes
 
12:23 PM
@PaulWhite Ta
 
@PaulWhite there are several transporters in existence
@PaulWhite this should be a hat
 
@Lamak you have to explain the joke. AGL.
 
I should, but I'm blue in the face, so I'm unable to
2
 
@Lamak That's funny because it refers to the error you made yesterday. Joke explained at sufficient length given mobile chat.
2
 
random quote from a comment on the interwebs:
> BTW, why did autocorrect change the word simply to "syphilis"?
 
 
2 hours later…
2:35 PM
quiet again today
 
@Lamak it's because of the snow
 
I wish there was snow here....instead we got a very sunny and hot day
 
nice, is that the view from your work or your house? (or you work from your house?, in which case the answer is both)
 
@dezso I do not envy you
it is quite sunny here
 
2:51 PM
@Lamak office window view
@AndyK where are you? Australia?
 
3:04 PM
France , @dezso despite my english first name, and to be more precise Poitiers
 
@dezso it's a nice view
 
@Lamak explain that joke
 
I like snow :)
 
3:20 PM
@Lamak ah. The place is the heart of the ex-GDR, not the most beautiful location
a bit of snow helps, for sure
 
Esta nevando
 
@billinkc jeezus, that comment confused the sh*t out of me for a moment
since I'm also in the spanish SO chat room
 
I should hang out in there as well
 
Or at least post the same picture and comment to complete the confusion
 
you should, I think that sadly you speak some better spanish than other users there
 
3:26 PM
That's unlikely to ever be stated again about my proficiency in any language
 
@billinkc it's more of a statement about the lack of proficiency in spanish for the other users though
anyway, you've been missing from here for a while....vacations?
 
Although it is amusing - of the 4 of us in our office, 3 speak Spanish of varying degrees. One lived in Spain for 2 years, one has native Mexicans for parents and I dabbled long ago
 
@billinkc isn't zero also a degree?
 
Switched jobs after the Summit (left Meagan and my WFH life) and the new one has severely cut into my fart around time
@dezso Only in the metric system
 
@billinkc googling WFH gives me "World Federation of Hemophilia"....but I think that it's also "work from home"...
anyway, good for Meagan!!
 
3:29 PM
@Lamak Weissenseer Fertigungs- und Handelsgesellschaft mbH
 
@billinkc where are you "working" now?
 
work from home. Anyways, I now have only myself to blame if I am unhappy at work since I am the owner of the mighty Sterling Data Consulting, LLC
 
@billinkc but...."of the 4 of us in our office"
 
The other 3 guys needed another on this contract so I hopped on and am now trying to be an OLTP type of person
We have a contract with a logistics company here in KFC. I had been on engagement with them 4 years ago so had a feel for what the client was like as well as the guys who I now partner with
It's been a fun ride for the past two months
Today though, it's just me and the 7 year old due to school closure
Oh, and to close the loop on the story so it's been interesting trying to keep up when the two fluent speakers converse (just to annoy the one in the room who speaks nothing but 'Murican)
 
@billinkc and did the guys you partner with know anything about you?
I think that their decision making skills aren't very advanced
 
3:37 PM
That's the scary thing, they did. 2 of them had been on a different contract but same client with me
 
googling "Sterling Data Consulting" leads me to "Sterling Data Storage"....
 
I own the domain but don't have a site up yet
I do have email though so I'm almost legitimate
 
@billinkc ah...well, congrats!
 
(Sterling is a double play on words. There's the Sterling silver association plus some of my Father's side comes from Stirling Scotland which was anglicized to Sterling during immigration)
And thank you
 
I see. Hope everything works out well
@billinkc hey, I'm an MVP now :)
11
 
3:41 PM
They let anyone in nowadays
Mega congratulations Senor
^^^ It's quite the catchy song if you haven't heard it
 
@billinkc yeah, I actually thought the same
I can't make it show my picture though. But I think that might be a filter that microsoft put on purpose ;)
 
Meagan had a ton of issues with it as well. The MVP site is truly the cobbler's children for MS sites
 
@Lamak congrats!
 
@billinkc I mean, I uploaded my picture and chose "everybody" in the options. I can see it if I log in to my profile, but if I search for it I get the generic avatar
@bluefeet thanks :)
 
Yeah, I can't remember what @mmarie did to get it to work. Maybe it was a timing thing, just took X number of days to work
Or the NDA had to be signed, something crazy
and totally non-intuitive
 
3:47 PM
@billinkc yeah maybe. I think I already signed the NDA, but well...it's not very intuitive
 
If I explicitly set the isolation level to serializable but I use a view that has a no lock hint for a table, what is the resulting iso level?
 
@billinkc There's no magic trick. You just have to upload your pic and wait a day or so. At some point is shows up
Congrats, @Lamak!
 
@mmarie I was hoping for a magic trick
 
You used your quota of magic getting the award
 
@billinkc hadn't thought about it like that
but it actually makes sense
 
3:55 PM
Might want to order a crate of bubble wrap and not leave the house for a few weeks
Have the wife open it too so you don't get paper cut and bleed out
 
@billinkc I already messed that up, I'm at work today....
 
que lastima
 
@billinkc My understanding is SET TRANSACTION ISOLATION LEVEL introduces a default isolation level for the duration of the session (or until you set it explicitly again). Any WITH (...) hints in a query are meant to override the default.
 
But my understanding is that hints are just that and the optimizer can say "that's cute" and ignore them
yeah?
Suppose the only way to know is to gin up a repro
 
@billinkc put some rum in it
 
4:02 PM
that too
 
So the question is whether SERIALIZABLE as set using the SET TRANSACTION statement is going (or likely) to beat UNCOMMITTED as specified with a table hint, correct?
 
@billinkc read uncommitted for the table in the view
 
The Oracle has spoken
2
 
@billinkc Almost all hints are actually directives. Counterexample: Index hints on a view are ignored.
 
Twice
 
4:06 PM
Doesn't mentioning Oracle invoke Jack Douglas?
If he posts the You Rang? image now I will be impressed.
 
I was actually going to find a delphi image but then I got distracted
 
Very nice.
 
That's cheating - like asking me if I have a picture of fried chicken
 
only listen, don't watch
 
4:32 PM
bye folks
 
4:50 PM
abominable ... there's a hat i can appreciate
@Lamak oh wow! seriously??? congrats!
 
@swasheck yup, for realz
 
dont forget us little people
@Lamak that's awesome. super happy for you
 
@swasheck it was just temporarily lack of microsoft's judgmente
 
@Lamak nah.
 
@Lamak For Access? Excel? :-p
3
 
4:54 PM
@Lamak holy shit dude, that's awesome
Great news!
@PaulWhite now we just have to trick him into making moderator ...
 
@Lamak congratulationes !
(my Spanish is worse than billinkc's :)
2
 
@jcolebrand I think Evan reserved the next available spot already.
 
touche sir
 
ignore this user (everywhere) has been such a godsend
 
Who said that?
:-D :-D :-D
 
5:00 PM
Echo of the past
 
the DOINK of heapers past
4
 
5:26 PM
@PaulWhite all of those
@jcolebrand thanks!
@TypoCubeᵀᴹ :)
 
5:39 PM
@Lamak Congratulations then 😎
 
@PaulWhite now it feels real
 
6:17 PM
1
Q: Non-english objects' name

MuriloI'd like to ask a question about a performance issue I'm facing. However my application was built in Portuguese, so my table and columns names are in Portuguese. Should I translate the tables and columns names on the code sample? I can do that, however it may be necessary post an entire table cr...

 
6:37 PM
@Lamak Of course man
 
@jcolebrand :)
 
7:06 PM
@Lamak congrats, Kamal!
 
thanks
 
@AndyK I vacationed several times in Vimoutiers, just south of Lisieux. Vive la France!
For the non-Americans here, that's -24°C right now.
 
@MaxVernon awesome!
 
@TypoCubeᵀᴹ yah, it's a bit fresh. With the windchill this morning it was -42°
 
@MaxVernon I've seen -34 (C) one winter in my hometown. The outside thermometer started working erratically after that.
 
@TypoCubeᵀᴹ lol, along with everything else!
 
7:56 PM
@MaxVernon sounds kind of chilly
 
@TypoCubeᵀᴹ Where is that?
@Lamak it's certainly not what I would call warm!
 
@MaxVernon northern Greece, altitude 650 (meters, not feet)
 
@TypoCubeᵀᴹ I thought it was Greece, but I was unaware of it ever getting so cold. Although the altitude certainly explains a lot of that.
 
Yes, the area is kind of unique in Greece. Different climate than the near-sea areas. Snow every winter, low temps but quite hot in summer (a few weeks or days)
 
does anyone know what the Like Clockwork hat is about yet? I've looked but can't seem to find any interesting tidbits. Clearly its a Clockwork Orange reference, but who knows what.
 
7:59 PM
Maybe you and your droogs need to do a bit of the ol ultraviolence on the close queue
 
@MaxVernon it's been conjectured is for doing something for 9 days (consecutive?)
 
@TypoCubeᵀᴹ ahhh. makes sense.
 
where "something" is vote / post / edit / some other action / any of many actions, it's unknown
It was first given the 9th day of the hats.
 
^^^^ me and my brother. I'm the one in the hat. ;-)
 
@MaxVernon why oh why didn't you put make up in your eye?
 
8:13 PM
@Lamak lol
 
user image
3
 
@Lamak thank you!
oh, and congrats, @PaulWhite on getting Epic a couple of weeks ago!
 
@MaxVernon sorry for the bad paint job, but the idea gets across
 
@Lamak I don't think that's a bad job at all!
 
 
1 hour later…
9:27 PM
SQL question
With a siren
 
I am trying to populate data into my DimDate table but it keeps throw an error of
Msg 208, Level 16, State 1, Line 5
Invalid object name 'dbo.DimeDate'.
Even though I have created the table
 
Is it DimDate or DimeDate?
 
@Dodi82 and what code produced this?
 
@TypoCubeᵀᴹ Hummmmmmm good observation!!
Thanks I will alter and back with the feedback
brb
@TypoCubeᵀᴹ I will post the code if I have new errors
brb again
 
9:36 PM
@Dodi82 AndriyM noticed the typo. I only make them ;)
 
in 3D
 
@TypoCubeᵀᴹ My life full of typos/ typoes!!
right in my DimDate table I've got DateKey as INT Primary Notnull and wanted to insert as DateKey = cast('19900101' as datetime)
O,O"
 
You can't store a date as an int. Tell me more of your findings
 
convert to Datetime?!
 
Orrrr 19990101 sure looks like an int to me
 
9:42 PM
So
 
If you are specifying a literal in the generated code, just put it without quotation marks and delimiters, so that it looks like an integer: INSERT INTO ... (DateKey, ...) VALUES (19900101, ...);.
And if you are inserting from another table where the dates are stored as dates or datetimes, then convert it to a string formatting it as YYYYMMDD, then convert that to int.
Something along the lines of INSERT INTO ... (DateKey, ...) SELECT CONVERT(int, CONVERT(char(8), SourceDateCol, 112)), ... FROM dbo.SourceTableName WHERE ...
 
@AndriyM I did and I have this error
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_DimDate'. Cannot insert duplicate key in object 'dbo.DimDate'. The duplicate key value is (19900101).
The statement has been terminated.
 
9:57 PM
Well, the error message is pretty clear.
If DateKey is the PK, you can't have duplicates in it.
 
I violated the primary key
it is but the dimdate is empty ?!
so as the column DateKey
 
If it's empty before the insert, then the issue is because you are inserting more than one row with the same DateKey.
 
Shall I leave it with no value on insertion
 
A PK must have a value.
 
yes, this is why I want to insert the value as
19900101
 
10:03 PM
Maybe the table is not really empty? You may have inserted that row once and it's in the table. You may now be trying to repeat the operation and it fails because you are attempting to insert the same PK value.
 
0 row(s)
@AndriyM I have selected the table but no rows exist
@AndriyM Shall I post my code ??
 
@Dodi82 Have you also verified that the table you were checking was the same that you were inserting into? I mean, maybe there are two tables in different databases (or different server instances) and you are looking at the wrong one when checking the row count.
 
Yes, I did check and also made so many refresh on the level of the database connection and the table itself!
However, I noticed that I have a column called Date and this was set to the same date format of the DateKey
DECLARE @start Date = '19900101', @years TINYINT = 20;
 
@Dodi82 Is it big?
 
@AndriyM Date
 
10:13 PM
St. Andriy
 
@billinkc St.billinkc :-)
 
Nope, pretty sure I'm the opposite of a saint
 
You are a good man my friend.
 
@Dodi82 I was asking about your code. If it's not big, fire away.
 
If it's big, fire a gist. or a pastebin.
 
10:16 PM
My code is fairly big, but I will fire it away
DECLARE @start Date = '19900101', @years TINYINT = 20;

;WITH src AS
(
SELECT TOP (DATEDIFF(DAY,@start, DATEADD(YEAR,@years, @start)))
  d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.number)-1, @start)
 FROM master.dbo.spt_values AS s1
 CROSS JOIN master.dbo.spt_values AS s2
),
W AS
(
  SELECT d,
    wd      = DATEPART(WEEKDAY,d),
    wdname  = DATENAME(WEEKDAY,d),
    wnum    = DATEPART(ISO_WEEK,d),
    qnum    = DATEPART(QUARTER, d),
    y       = YEAR(d),
    m       = MONTH(d),
    mname   = DATENAME(MONTH,d),
This is gents
 
As I said before, all rows that you are inserting must have different PK values, and the PK in your case is DateKey. You are assigning the same value of 1 to all rows that you are pulling from q1. Naturally, the server cannot accept that.
 
what should I do for the DateKey to be auto generated?
 
24 mins ago, by Andriy M
Something along the lines of INSERT INTO ... (DateKey, ...) SELECT CONVERT(int, CONVERT(char(8), SourceDateCol, 112)), ... FROM dbo.SourceTableName WHERE ...
 
Why do you need a DateKey in the first place? That Date column seems to be unique. Why not use that as a key?
 
@TypoCubeᵀᴹ It's probably an OLAP thing. Some kind of restriction or something regarding the PK type. I'm not very good at it, but our prototype fact tables all reference our DimDate table using ints.
@billinkc might know more.
 
10:24 PM
@AndriyM right, in case of omitting the DateKey and using Date instead
can I use it as a foreign key in the fact table?
 
@Dodi82 More specifically, DateKey = 1 in the main SELECT should be DateKey = CONVERT(...) (the thing in my example).
 
@AndriyM I am sorry for keep asking, but can I do the following
DateKey = CONVERT(int, CONVERT(char(8), SourceDateCol, 112)) within the last select
 
@Dodi82 Yes, that's what I'm saying, and SourceDateCol is d in your case.
 
ok dokey
@AndriyM Andriy my friend thank you
It worked!
 
Congratulations
 
10:30 PM
@TypoCubeᵀᴹ Fairly common in olap scenarios as it tends to result in better performance when querying the cube
 
@AndriyM as billinkc said : You are a saint
 
Thank you (and @billinkc) for the exaggeration.
 
@TomV I suppose it's also common to use int values like 19900101 and not identity or sequences, right?
 
@TomV Thank you for the explanation Tom
 
@TomV That may be one of the few cubes he knows little about.
 
10:32 PM
@TypoCubeᵀᴹ Yes, it could prevent unnecessary lookups and be achieved with a simple conversion
But I was mainly replying to the "why you need an extra key" rather than "could you have a better key"
 
or allows for an easy partitioning (by date) of fact tables.
 
But a surrogate (integer) key is quite common for any design a cube is based on
 
@TomV Yeah, I got that. This is a dimension table, so it doesn't hurt to have multiple keys, anyway (I suppose, as the saint said above, these cubes I know little about).
 
and even more importantly it allows for the distinction between "empty" and "unknown"
in the sense that "there was a value but I couldn't find it in the dimension" or "the value was empty and there isn't an empty record in the dimension"
for example you could have an empty product group on a product, but you don't have a "":"empty" product group vs there is a product group entered on the product but I can't find that in your dimension table
without surrogate keys they would both be linked to an automatic (unknown) dimension member
and allowing errors like that even increases processing time of the cube
long story, not sure you are interested :)
 
I know very little about this as well, even though we are using Analysis Services with the project I'm working on. (I hardly ever touch it.) Thank you very much for chiming in, @Tom.
 
10:45 PM
@AndriyM I'm going to bed,if you need to feel free to ask even though I'm a bit rusty when it comes to BI
There are others in here who know more than me
 
@TomV It's about time I went to bed too, so thank you, but no thank you.
@TomV They are no saint, though.
 
@AndriyM Good night Saint Andriy
@TomV Good night Tom
 

« first day (2193 days earlier)      last day (2661 days later) »