UNION ALL returns "conversion... overflowed an int column"

I had a stored procedure throwing the following error:

The conversion of the varchar value '9999999999' overflowed an int column.  

There's two queries joined, so I split them up to see which one was throwing the error.  

Neither.  Of course.  : - \

If you break the query apart, both halves work.  

Okay, so let's figure this out:

exec sp_describe_first_result_set @tsql = N'myqueryhere'

exec sp_describe_first_result_set @tsql = N'myotherqueryhere'

Oh, hey look, varchar in one, int in the other.

Cause? My own stupidness from 7 years ago:

case when fielda is null or fielda = 0 then 0

else replace(replace(convert(varchar(14),fielda(,'.',''))

end

On one set of data, there are fields that have values, specifically the 9999999999; the second set returns nothing.  

So it tries to convert to the "least" datatype, the INT, and the 9,999,999,999 is too large for INT, so it fails.

[AWS] Athena aka Hive/Presto - renaming/mapping a JSON attribute named TIMESTAMP to be called TS

(More Athena stuff coming, probably)

Since I couldn't get Amazon's documentation example with  ColumnToJsonKeyMappings to work... (fortunately, the Hive documentation is better, and Athena uses Hive for DDL)

My JSON looks like this (which I'm not formatting, because Athena demands every row be on one line, with no fancy "line feeds" or "carriage returns")

{"event":"eventififer","globalid":"3fd6dce3-6650-4e3f-8d8d-a46d1baaca02","timeStamp":"2020-06-08T19:29:31.114Z"}

In this case, "timestamp" is a reserved word, so you have to query it like:
and "timestamp" >= '2020-06-08T18:30:55Z' 

So, in order to rename the field, you could either create a view on top of it, or rename it during the creation.

CREATE EXTERNAL TABLE myawsdatacatalog.mytablename(
event string ,
globalid string,
ts string)
partitioned by (dt string)

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties ("mapping.ts"= "timestamp")
LOCATION 's3://mybucket/my/folder/path/';

Yes, the ts is a string, because it has to meet their particular format EXACTLY. If I try TIMESTAMP for the data type for ts, it creates fine! But when I try to query it, I get the following. 

HIVE_BAD_DATA: Error parsing field value '2020-06-04T20:01:47.829Z' for field 3: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

If you need a workaround, I'd say to create it as string, then toss a view on top with: 
select from_iso8601_timestamp(ts) as ts

[Baking] Olga's Prune Cake

Interesting! I googled this (Because my copy of the recipe is typed up, which means Baba came across it and liked it, and so kept making it) and found these rather-similar recipes.  My copy is at least 35 years old, so I have no idea who gets the credit.  Except me, when I make it.  I get so much credit.   (I do dig the bit about soft-ball stage - we note the color change and do it then)

https://thepioneerwoman.com/cooking/make-this-cake-today-trust-me/

https://www.solofoods.com/recipes/spicy-buttermilk-prune-cake

First of all, I grew up thinking this was kinda gross, because who wants to eat baby food? Once I became a parent, I learned that baby food has no extra ingredients, making it ideal (and removing the food processor requirement from the recipe). 

Second of all - my word this is simple and delicious. We made it with a spoon and like 2 bowls in about 10 minutes. The icing takes a little longer and requires a large (5-quart+) bowl to make, because of the crazy reaction when boiling buttermilk + baking soda.  Remember, kids, heat makes reactions stronger!

1.5 cups sugar

1 cup salad oil (preference is Mazola corn oil, but Wesson will also work)

1 tsp salt

2 cups flour

1 tsp soda

1 tsp nutmeg

1/2 cup buttermilk

1 tsp cinnamon

3 eggs (jumbo)

1 cup prunes (we use 2 of the small jars of beech-nut)

1/2 cup nuts (we use pecan, in pieces)

1 tsp vanilla (preferred Mexican vanilla)

Combine sugar, oils and eggs in large bowl and mix well (I mix until the color starts to change.) Sift dry ingredients (so measure out 2 cups of flour into the sifter, pour the other dry on top, and then sift it all). Add prunes, buttermilk and vanilla. (This definitely thins out the batter and makes the whisk you used on the first part usable again). Stir in nuts. Bake at 350F for 45 minutes.

Topping/icing/frosting/whatever: 

1 cup sugar

1/2 tsp baking soda

1/2 cup buttermilk

1/2 cup butter or margarine (1 stick)

Put all in 4+ quart pot. Mix everything in. Boil for 5 minutes. At about 5 minutes the color changes to be more yellow, and at that point, we pour it over.

Poke a bunch of holes in cake, and, while this and the cake are still hot, pour this over the cake. 

The cake is together...

Making the foamy frosting...

After pouring the icing over...

And after it's absorbed.

[Powershell] Getting the full text out of a field in SQL when SSMS won't work

We have some code that is basically table-driven SSIS. The advantage is: no digging through SSIS! The disadvantage is that if someone decides to put in code instead of a stored procedure, it's hard to get it out of SSMS.

I wish I knew a better way to handle this, but this works fine.

#This uses the wondrous invoke-sqlcmd
. c:\powershell_scripts\invoke-sqlcmd2.ps1

$query = @"
SELECT source_sql FROM etl_me.dbo.data_flow_me_config where id = 1
"@

$fullquery = invoke-sqlcmd2 -ServerInstance mem-pr-cde-01 -Query $query


First, to get the data out, I tried:
$fullquery.source_sql

Then I realized I could do this, if I only had one column:
$fullquery[0]

But that can fall down.  This one is slightly better.  Add more columns if you need them - if they're nonexistent, it still works.

$fullquery|%{$_[0] #, $_[1], $_[2]
"-------------------"}