Thursday, October 8, 2020

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.

Tuesday, June 9, 2020

[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

Thursday, March 12, 2020

[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)


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.

Thursday, February 6, 2020

[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]
"-------------------"}

Tuesday, December 10, 2019

MS Teams - posting results of a SQL query as a table via Powershell.

(update 2019/12/10 13:33 - fixing the tables; the formatting I included got screwed up because Blogger decided to render it)

TL;DR - below is code that will post the results of a SQL query, as a table, in a channel within MS Teams.

Special thanks to http://mikeconjoice.com/2018/05/25/send-a-notification-to-microsoft-teams-via-powershell/, who had the original post/code I used.

First of all - MS, you should be embarrassed. All the documentation says to use Office 365 Connector Cards/Adaptive Cards, but it doesn't work for teams as of December 2019 ("Supported in Teams, not in Bot Framework."), and there's no sense that it'll ever happen, given there's a Connect/User Voice that's been open a year with no updates.

A) Here's some documentation that doesn't actually work for what we're doing.
https://docs.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/connectors-using
https://techcommunity.microsoft.com/t5/Microsoft-Teams/Microsoft-Teams-Incoming-Webhook-Message-Formatting/m-p/31984
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
https://messagecardplayground.azurewebsites.net/
https://stackoverflow.com/questions/58598592/microsoft-teams-escaping-underscores-in-text-posting-to-webhook

* Note there's a max of 20711 characters in the JSON, and 10 sections in the JSON. (Somewhere else on StackOverflow it says 28k for an image is the max size of that)
* Note there's also a limit how often you can post, and it's a "back-off" algorithm, IIRC.

So.... you want to post to a Teams channel automagically.  Should be simple, and it is!  Alas, it means you have to ignore most of the documentation.  Let's do this!

1) Here's how to hook it up with your channel.  Note that when I created a brand new "Team", it took about 5-10 minutes before I was able to add the webhook connector - prior to that, I got a "channel does not exist or has been deleted".
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
(scroll down)

2) Getting a basic post working - this works and will post text.  Note that you can split lines by using "/r/n" in the text, because it sort of supports formatting (see #3)

$webhook_url = "https://outlook.office.com/webhook/  ......" #URL when you added the connector

$json = @"
{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "text": "Hi Mom",
  "version": "1.0",
  "type": "AdaptiveCard"
}
"@

Invoke-RestMethod -Method post -ContentType 'Application/Json' -Body $json -Uri $webhook_url


3) Adding a table:
Basic HTML is supported.  And I mean BASIC. It supports less formatting than Outlook (I had a border and had to remove it).


So the JSON would look like:




4) Putting it all together, we can do something like this, which will query SQL Server using invoke-sqlcmd2, create the proper JSON, and post it to our channel (note the below SQL is incomplete):



5) And here's what the powershell script returns, if successful:

1

and in Teams...


If you don't do it right, you'll get errors like:
Invoke-RestMethod : Summary or Text is required.
or (the typical "you didn't do it perfectly") of:
Invoke-RestMethod : Bad payload received by generic incoming webhook.

Wednesday, November 13, 2019

[Powershell] loading a module if the SQL Server Agent can't do so automatically

I'm trying to use the SQL Server Agent to run a Powershell script that calls AWS.

But it failed.  

The term 'get-awscredentials' is not recognized as the   name of a cmdlet, function, script file, or operable program. Check the   spelling of the name, or if a path was included, verify that the path is   correct and try again.  

Which is odd because if I start a brand-new ISE on the box, it works.  Grr.

Okay, I must've forgotten the module.  But again, I don't need it with the ISE...

import-module awspowershell

But I got a different error:

import-module : The specified module 'awspowershell' was not loaded because no   valid module file was found in any module directory.  

Okay, so let's add it.

Where the heck is the module, anyways?
From the ISE, after loading the module by hand, run:

(Get-Module -ListAvailable awspowershell*).path


Then, now that we know that path:

import-module -name "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\awspowershell"
 
(note awspowershell is in there twice - it's looking for the... PSD1, I think?)

Tuesday, May 7, 2019

[Table Partitioning] Does WAIT_AT_LOW_PRIORITY work with Standard Edition?

(TL;DR - yes.  Yes, it does.)

As you hopefully know, back with SQL Server 2016 SP1, Microsoft opened the floodgates of formerly-only-enterprise-features (https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1). 

But with caveats.

For instance, IMOLTP (in-memory online transactional processing, aka "in-memory") tables are limited in the amount of RAM used. 

But how about WAIT_AT_LOW_PRIORITY?  That was introduced in 2014 to make table partitioning deal better with That-Dude-From-Accounting-Who-Kicks-Off-A-Massive-Query-On-Friday-at-5pm, which causes partitioning to hang on Saturday when you're trying to add and remove partitions.

Specifically, the notes don't say anything about it.  The closest they get is:
"WAIT_AT_LOW_PRIORITY used with ONLINE=ON only."

And ONLINE is an Enterprise-only feature.

So, does it or doesn't it?

For a simple example I actually used Aaron Bertrand's answer to a StackOverflow question:
https://dba.stackexchange.com/questions/155495/table-partitioning-in-sp1-for-sql-server-2016



So, what do we get?
Well, as pointed out by Joe Sack back in 2013(!), that feature will log to the Errorlog when it works. 
https://sqlperformance.com/2013/09/sql-indexes/lock-priority-sql2014

Let's try it out.  In the first window create the objects.  Once created, start a second window and open the transaction.  Then back to the first and run the SWITCHes and see what happens!

...Yup! It works in Standard!

Here are the messages I got:

Date 5/7/2019 3:41:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
An ALTER TABLE SWITCH statement was executed on database 'eif_workspace', table 'partitionexample' by hostname 'CH004547', host process ID 30812 with target table 'staging_FactInternetSales' using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.

, followed a minute later by: 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 1182627256. All blocking user sessions will be killed.

and 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
Process ID 63 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 1182627256.


I was figuring/hoping it'd work, but that example using ONLINE = ON gave me pause.
Proof, and a blog post!


Thursday, May 2, 2019

[Linked Servers] Kerberos Double Hop - SPNs and Trusted for Delegation

Yesterday I ran into the dread Kerberos Double-Hop when trying to set up a linked server.  Thought it was the standard "Add an SPN using the Microsoft Kerberos Configuration tool".  Which didn't fix it.

What the....

Turns out there's another potential setting you have to fix. 

In active directory, the service account that SQL Server is running under may need this setting:


Tuesday, February 26, 2019

[Powershell] Tracking down that failing app on another server using WMI's win32_process and Profiler

(Yes, I'm still using profiler in some cases)

As we continue to move off SQL Server 2008, we've found instances where we change all the application configs, yet something on ServerA is still querying ServerB.  And since our internal application doesn't have the connection string set to give an ApplicationName, we have no idea which one is misconfigured - or if one was missed entirely!

1) Set up a profiler trace on one side, making sure to grab ClientProcessID



2) Run this WMI query on ServerA, using Powershell. 


$loopcount = 1
$futuretime = (get-date).addseconds(60)
$processeses = gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" # run locally on the server making the calls for speed)
do {
$processeses += gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" -ComputerName "mem-qa-trdb-17"
$loopcount++while ((get-date-lt $futuretime)
$processeses | ogv

(using $loopcount will tell you how many times it ran in that minute)

You could run it remotely if you had the proper WMI rights set up (which is probably the case on WS2016+), but my fear is that it'd be slow enough that it wouldn't "catch" the app as it's running, if it's spinning up on its own, failing to connect, then quitting. 

If the app is just running and not starting/stopping, then it'll keep the same PID (process_id) and you can just run the first $processeses and filter it. 


3) When the ogv (out-gridview) runs, it'll pop up an interactive window.  Type the actual number for ClientProcessID you got from the profiler trace, in the "filter" box, and it should filter down to just the Process (and location of the executable) that you need to fix.  


Thursday, February 14, 2019

[EPR] Making the Extensible Powershell Repository work on a box with SQL Server 2017 since add-sqltable fails with Microsoft.SqlServer.Dmf

We're migrating the EPR (http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html) to a new box.  After 6 years, and it's still one of my best tools.  (Just drop a SQL or PS1 script in a folder, and it'll create a table and run it against all your servers).

Alas, Chad Miller's excellent add-sqltable started failing.  To the internet!

Could not load file or assembly 'Microsoft.SqlServer.Dmf"

Looking around, it appears that it's a problem with SMO.  On 2017, they split out SMO and SSMS.  Which means that installing SSMS 2017 doesn't get you SMO.  Supposedly installing it (install-module sqlserver) will do that, but I'd already did that and it wasn't working.

So, after fighting with it for a little bit, I decided to "get it working" and revisit later.

1) Install SSMS 2016 (which is when they started separating out SSMS)
2) Change the version from 11 to 13 in the script, as below: 

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} 
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"}