tag:blogger.com,1999:blog-14341131329810050342024-03-05T06:07:17.608-06:00The Baking DBA - select * from flourPut butter in your mixer, add sugar, set on low. Write a stored procedure to keep track of jobs that won't run. Add flour. Use a DMV to find unused indexes. Add eggs. Bake in an MDF with 20% free space.Unknownnoreply@blogger.comBlogger345125tag:blogger.com,1999:blog-1434113132981005034.post-33467680531447986952023-10-20T14:23:00.003-05:002023-10-20T14:23:33.446-05:00Azure SQL Database - ports you didn't realize you needed for your firewall request.<p> Azure is fun. FUN, I say!</p><p><br /></p><p>Ports: </p><p>1433 - standard SQL port</p><p>11000-11999 - ports if you're using Proxy instead of Redirect</p><p>14000-14999 (!) - ports for the DAC (Dedicated Admin Connection)</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-40696632080511124092023-10-20T11:33:00.005-05:002023-10-27T14:04:58.662-05:00Mermaid (the language) is life!<p>Just found out about this the past month. </p><p>I like diagrams for my documentation, and I detest making it. I also would like to build it via script, since that's more useful.</p><p><br /></p><p>Sample:<br /></p><p>graph TD;</p><p> A-->B;</p><p> A-->C;</p><p> B-->D;</p><p> C-->D;</p><p><br /></p><p>Which produces: </p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEibdgLJQtbGNk5N8Hvk6bxbFB_Ffcae9QbPnM6Xf8vIJ03B-9iC9GPmbhv8FPksiYsRwAGxaPDPCRioCikQ3Kd_s85MIR8Tf9D6fYDJowadThb-58mh9TNo7Nd54wjfZ8eF8V8mac8ZlcCzz5QwD0MiNmj6iQS2G0HiZF5IDp2xSYCXxeYVCBddLFuP96I" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="659" data-original-width="378" height="240" src="https://blogger.googleusercontent.com/img/a/AVvXsEibdgLJQtbGNk5N8Hvk6bxbFB_Ffcae9QbPnM6Xf8vIJ03B-9iC9GPmbhv8FPksiYsRwAGxaPDPCRioCikQ3Kd_s85MIR8Tf9D6fYDJowadThb-58mh9TNo7Nd54wjfZ8eF8V8mac8ZlcCzz5QwD0MiNmj6iQS2G0HiZF5IDp2xSYCXxeYVCBddLFuP96I" width="138" /></a></div><br /><br /><p></p><p>Pretty cool right? </p><p>VSCode supports it (load the extension "Markdown Preview Enhanced"), but Jira and Confluence don't support it natively.... </p><p>But there's a workaround!</p><p>In Chrome, load the extension Mermaid Previewer (https://chrome.google.com/webstore/detail/mermaid-previewer/oidjnlhbegipkcklbdfnbkikplpghfdl)</p><p>There are a couple of tweaks. For one, you need to enable sandbox mode in the extension. </p><p>For Confluence, I put the site as my\.confluence\.site and a selector of <span face=""Segoe UI", "Segoe UI Web (West European)", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-serif" style="background-color: whitesmoke; color: #242424; font-size: 14px;">pre > code</span>. To add it, I create a markdown block, but within it do this:</p><p>```mermaid</p><p>graph LR</p><p>A --> B</p><p>```</p><p>(update 2023/10/27) another way that seems to work for confluence is to make a regular code block ( for me, type ``` by hand, then do it with just the "graph LR / A --> B"), and in Mermaid Previewer as <span style="background-color: whitesmoke; color: #242424; font-family: "Segoe UI", "Segoe UI Web (West European)", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-serif; font-size: 14px;">div.container</span>, but I don't know how that affects other "containers". the nice thing about doing it the other way is that (for some reason) you HAVE to have the ```mermaid, which means it can't accidentally go crazy with it.</p><p><br /></p><p>For Jira, my selector is <span face=""Segoe UI", "Segoe UI Web (West European)", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-serif" style="background-color: whitesmoke; color: #242424; font-size: 14px;">pre.code-java</span>, since by default our code blocks are Java. You can look at the element in Chrome developer mode (right-click, choose "inspect").</p><p>Then, within the Jira ticket, just do:</p><p>graph LR</p><p>A --> B </p><p>(NOTE THE SPACES!) No idea why it doesn't act the same between them, but for me it's fine.</p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-22804588487765349412023-10-18T13:38:00.001-05:002023-10-18T13:38:03.969-05:00SSIS and Parameters and where to set them<p> SSIS has a LOT of ways to set configs. Parameters & variables, and then those can be overridden at different levels.</p><p><br /></p><p>in order (from lowest to highest) - bottom of this list supercedes the top. You can see WHERE it was overridden because at that level, the parameter name is in <b>bold</b>. </p><p></p><ol style="text-align: left;"><li>SSIS Package level</li><li>SSIS Project level</li><li>XML Configs? (I don't remember where these can be set)</li><li>SSISDB - right-click on the project within integration catalog and choose "Config"</li><li>SSIS Job Step configuration</li></ol><p></p><div><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-79086982407407698282023-10-17T13:54:00.002-05:002023-10-17T13:54:29.641-05:00Azure Data Factory - how to ACTUALLY use storage event triggers to copy data from Blob Storage to Azure SQL Database (Azure SQL DB)<p> This post is in response to an incredibly frustrating interaction with Azure.</p><p>I figured I would do something incredibly simple - import a parquet file from blob storage into Azure SQL DB. When the file is written to Blob Storage (using CETAS, fwiw), it should activate the storage trigger, which should kick off the pipeline. </p><p><br /></p><p>The problem is that it won't work as the tutorial (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal) shows. It says it can't find the file. Why not? Well, it wasn't firewalls or anything else. It's that a linked service dataset, in "Sink" requires a container name - and the trigger sends the container name also. So I kept getting messages that it couldn't find \mycontainer\mycontainer\myfolder\myfile.parquet . I found a single message on Stack Overflow (https://stackoverflow.com/questions/67294521/azure-data-factory-how-to-get-new-file-location-from-triggerbody-folderpath/74738786) on how to solve it. So, here we go: </p><p>package parameters:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjRqRBlwgKOypHDzOOylkz38Z08Tx4mFxzKvloEC7JoZo6W6Ha5dAg7r0JCERamInjp1M42k6UFQukKDIqGdGGARBXDRTG0syQ7kNbNIxiw7Y8XnjVqbBuk0Q-lQeLBzOdnpJ8bcSDfBMbJoc7QR0mhQG0bmt3qYdf0Ylx8-c6cRYJ9l1gUD_7bjmirHoo" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="222" data-original-width="696" height="102" src="https://blogger.googleusercontent.com/img/a/AVvXsEjRqRBlwgKOypHDzOOylkz38Z08Tx4mFxzKvloEC7JoZo6W6Ha5dAg7r0JCERamInjp1M42k6UFQukKDIqGdGGARBXDRTG0syQ7kNbNIxiw7Y8XnjVqbBuk0Q-lQeLBzOdnpJ8bcSDfBMbJoc7QR0mhQG0bmt3qYdf0Ylx8-c6cRYJ9l1gUD_7bjmirHoo" width="320" /></a></div><br /><br /><p></p><p><br /></p><p><br /></p><p>Source:<br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEirGnNSZ_p0eVWmsskusY58s4Eei_4UJuaDI8o_M747plZFc5Bok4QFWLASCZrf70hwoDPNiom_CyzNuCaDLPLaOUiuB0p-MELam7yNRREyNWomswiWFy12qKPwBEvVR63-B-EztfOAPx_pc5S2r7z3I3S9Gl1KXXTHSNs2E_-FO_YBKTjl1fKSePBIjwc" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="176" data-original-width="691" height="82" src="https://blogger.googleusercontent.com/img/a/AVvXsEirGnNSZ_p0eVWmsskusY58s4Eei_4UJuaDI8o_M747plZFc5Bok4QFWLASCZrf70hwoDPNiom_CyzNuCaDLPLaOUiuB0p-MELam7yNRREyNWomswiWFy12qKPwBEvVR63-B-EztfOAPx_pc5S2r7z3I3S9Gl1KXXTHSNs2E_-FO_YBKTjl1fKSePBIjwc" width="320" /></a></div><br /><span style="background-color: #eff6fc; color: #242424; font-family: "Segoe UI"; font-size: 13px; font-weight: 600; text-wrap: nowrap;">@pipeline().parameters.trigger_folder_path</span><p></p><div><span style="background-color: #eff6fc; color: #242424; font-family: "Segoe UI"; font-size: 13px; font-weight: 600; text-wrap: nowrap;">@pipeline().parameters.trigger_file_name</span></div><div><span style="background-color: #eff6fc; color: #242424; font-family: "Segoe UI"; font-size: 13px; font-weight: 600; text-wrap: nowrap;"><br /></span></div><div><span style="background-color: #eff6fc; color: #242424; font-family: "Segoe UI"; font-size: 13px; font-weight: 600; text-wrap: nowrap;"><br /></span></div><div>My Source Dataset:</div><div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjOXyeS7E3s6Rg-O603mNdWULA6h9qpL_iDmZvh9E40USu6U7k1rxxulMWRlvAhlKYqbiJZThVg6DaNxRHG3xAjSEWaiAT3BFtbd5Dm7OkDvRv9MrMIpnOKLQF74pIpriGfSDC-jH7IZpaurFGJxJrx9gdwLS1uMExfr4sp_KcpRSao0dwEmTjey4daErI" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="206" data-original-width="989" height="67" src="https://blogger.googleusercontent.com/img/a/AVvXsEjOXyeS7E3s6Rg-O603mNdWULA6h9qpL_iDmZvh9E40USu6U7k1rxxulMWRlvAhlKYqbiJZThVg6DaNxRHG3xAjSEWaiAT3BFtbd5Dm7OkDvRv9MrMIpnOKLQF74pIpriGfSDC-jH7IZpaurFGJxJrx9gdwLS1uMExfr4sp_KcpRSao0dwEmTjey4daErI" width="320" /></a></div><br /><br /></div><div><br /></div><div><br /></div><div><br /></div><div>Sink:</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjkMW14lmDveLhQhVzU9h5faJPvjmshnI0eAN-wgciSHLxQ-U104AY3J5RzJdfD0l29UYeVH-qgMv7uxCMjC0uba86R2KDtqk8bWSH6qmVGTBZogUaJZQrhnKAG-G_Eo84Cfh5r20fPt7U2F3WCidJ4DGVNk8zINVbCugTKk62twPEW5-atRi3_q6DFJ-0" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="122" data-original-width="505" height="77" src="https://blogger.googleusercontent.com/img/a/AVvXsEjkMW14lmDveLhQhVzU9h5faJPvjmshnI0eAN-wgciSHLxQ-U104AY3J5RzJdfD0l29UYeVH-qgMv7uxCMjC0uba86R2KDtqk8bWSH6qmVGTBZogUaJZQrhnKAG-G_Eo84Cfh5r20fPt7U2F3WCidJ4DGVNk8zINVbCugTKk62twPEW5-atRi3_q6DFJ-0" width="320" /></a></div><br /><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjSgWcDFl0-i5KiDKyDG_WKdbclvFxfLau79-Er6iLK7_9bKRaGeH7i4dhnuoaghiqfwJn1MfEJxMIHegyPSAEqr3X84tJm553j74_ZKBGsfBfOlW4j3t6of_BWIJZN579CYl0vjntiheJn1Zogx3VTwdAvMnHHqCDcAubdNCJwgHIHIDYyHpEF24daNH0" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="575" data-original-width="322" height="240" src="https://blogger.googleusercontent.com/img/a/AVvXsEjSgWcDFl0-i5KiDKyDG_WKdbclvFxfLau79-Er6iLK7_9bKRaGeH7i4dhnuoaghiqfwJn1MfEJxMIHegyPSAEqr3X84tJm553j74_ZKBGsfBfOlW4j3t6of_BWIJZN579CYl0vjntiheJn1Zogx3VTwdAvMnHHqCDcAubdNCJwgHIHIDYyHpEF24daNH0" width="134" /></a></div><br />And the last part in the trigger that makes it all work:</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgclL6flXTXGnA3KoM2E3PNP5bbVeNNd10z-j920tlvtO1duacDCOmex9y4AuUMNy4K8m3ELm3JtvsnkzfIbve5zyyhKCiNqhYZYKnOcTvm-o_NlD94kXwbNH9j403kVtPCgM0b6q4FZUU1jW40Hmd_xxnX6ZV6cpmik5z2v1oaDM6WbiEEBjnQlLdK1Xg" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="217" data-original-width="568" height="122" src="https://blogger.googleusercontent.com/img/a/AVvXsEgclL6flXTXGnA3KoM2E3PNP5bbVeNNd10z-j920tlvtO1duacDCOmex9y4AuUMNy4K8m3ELm3JtvsnkzfIbve5zyyhKCiNqhYZYKnOcTvm-o_NlD94kXwbNH9j403kVtPCgM0b6q4FZUU1jW40Hmd_xxnX6ZV6cpmik5z2v1oaDM6WbiEEBjnQlLdK1Xg" width="320" /></a></div><br />Here's what to put for trigger_folder_path, as per Steve Johnson on SO. No, you don't have to change the YAML, you can modify the values above: </div><div>@substring(triggerBody().folderPath,add(indexof(triggerBody().folderPath,'/'),1),sub(length(triggerBody().folderPath),add(indexof(triggerBody().folderPath,'/'),1)))</div><div><br /></div><div><br /></div><div>Why it's that hard to find/use, I have no idea. </div><div>All Hail Steve Johnson!</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-88246549111160030802023-06-01T16:00:00.001-05:002023-06-01T16:00:30.551-05:00[rant] Freaking Microsoft.Graph cmdlets for 1.27 - I swear, y'all.<p> Never, for the love of god, run update-module -all. </p><p><br /></p><p>I went from 1.23 to 1.27 and my scripts broke.</p><p>First problem: incompatibility with Microsoft.Graph.Authentication. I had multiple copies of it, and uninstall older versions didn't work - had to uninstall ALL of microsoft.graph (not easy, whhhhyyyy), then reinstall WITH "-requiredversion 1.27" (or was it 1.27.00?). Otherwise it still grabbed the wrong versions somehow.</p><p><br /></p><p>Second problem: invalid filter clause</p><p>get-mgchat : Invalid filter clause</p><p>At line:1 char:1</p><p>+ get-mgchat -all -PageSize 50 -filter "lastUpdatedDateTime gt '2023-05...</p><p><br /></p><p>why? Because between 1.23 and 1.27, they stopped accepting the datestring being in quotes. Now, if it is, it doesn't work. What the everliving...</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-79836533199131905762023-05-24T15:49:00.003-05:002023-05-24T15:49:48.283-05:00Patching Visual Studio via Powershell<p> </p><p>Basic script to update all your Visual Studio instances. On some servers I have/need 3.</p><p>The VSSetup is the magic, courtesy of Microsoft (and hence the prereqs), the rest is just running their installer. </p><p><br /></p><p><br /></p><p>#this didn't find the 2022 install. Run as ISE admin</p><p>Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -force</p><p>set-psrepository psgallery -InstallationPolicy Trusted</p><p>Install-Module VSSetup </p><p><br /></p><p>get-vssetupinstance|%{</p><p>$installpath = $_.installationpath</p><p>"$installpath"</p><p>Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "update --passive --norestart --installpath ""$installpath"""</p><p>}</p><p><br /></p><p><br /></p><p>#this should work, but doesn't. Just kicks back instantly.</p><p>#Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "updateall --passive --norestart"</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-23689087514713437532023-05-22T14:34:00.001-05:002023-05-22T14:34:05.835-05:00Making about:config a trusted sites<p> I need this because IE tries to load about:config twice, which throws errors in the secure mode of IE/Edge, and I needed to script a way around it. <br /><br />Thanks to Joe for the tip on quotes.<br /><br /></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">if (-not (Test-Path -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'))</span></p><p><span style="font-family: courier;">{</span></p><p><span style="font-family: courier;"> $null = New-Item -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'</span></p><p><span style="font-family: courier;">}</span></p><p><span style="font-family: courier;">Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank' -Name "about" -Value 2 -Type DWord</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-58967649534390519262023-04-27T00:25:00.004-05:002023-04-27T00:25:55.633-05:00Azure Managed Instance to Parquet using CETAS and t-sql <p>And we're back! Not that anybody noticed. 'Scool. IP agreements and the like. </p><p><br /></p><p>I'll make it brief since I logged on at 11pm to get this working.</p><p>TL;DR - the below lines will allow you to <b>query a table on your MI</b>, <b>creating Parquet files in Azure blob storage</b>. <b>And you can query it! </b>Next up is partitioning over time, etc, etc. But this is freaking fantastic. I have a python script I wrote that does it, but it's nowhere as nice/easy as this.</p><p>Why do you care? Because it's a fantastically easy way to archive older data to blob storage, and I suspect (need to test) that if you do it right, you can then have it go to cool/archive storage via a lifecycle setup, so that if you need it much later, you can. Parquet is a columnar compressed format that can then feed other things like Snowflake, Hadoop, Redshift Spectrum, AWS Athena, etc. For me it's a platform-agnostic, portable, highly-compressed data format that I can use to better archive older data. </p><p>CETAS: Create External Table As Select</p><p><br /></p><p>1) Walking the dog and reading my RSS feeds on Feedly, I see this:<br />https://azure.microsoft.com/en-us/updates/azure-sql-general-availability-updates-for-lateapril-2023/</p><p>2) reading through, go to this:<br /><a data-bi-an="body" data-bi-tn="undefined" href="https://aka.ms/CETAS" style="background-color: white; box-sizing: inherit; color: #0062ad; font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px; transition: color 0.15s ease-in-out 0s;">SQL Managed Instance now supports CETAS (“Create External Table As Select”) functionality. This feature allows users to export data from local database tables into parquet and CSV files in Azure storage and creates external tables for easy querying of the exported data through data virtualization.</a></p><p>3) Run back home and log in. My poor dog was out of breath.</p><p>4) After dinking for 30 minutes trying to get my local copy of the az powershell module to work with the command, (still isn't working, seriously, wtaf), and being unable to use cloud shell since it requires cloud storage and permissions are locked down too much, I finally use -UseDeviceauthentication and run these commands on my instance:</p><p># Enable ServerConfigurationOption with name "allowPolybaseExport"</p><p>Set-AzSqlServerConfigurationOption -ResourceGroupName "myresource_group_name" -InstanceName "myManagedInstanceName" `</p><p>-Name "allowPolybaseExport" -Value 1</p><p><br /></p><p>5) Verify it works.</p><p>SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';</p><p>Returns 1!</p><p><br /></p><p>6) now, I use the following t-sql, pretty much verbatim from their example and a SAS token. The only magic I've added is the 10 minutes on how to use a SAS token. </p><p style="margin: 0px;">USE my</p><p>
GO</p><p>CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password would go here';</p><p>
GO<br />
CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]<br />
WITH IDENTITY ='SHARED ACCESS SIGNATURE',<br />
SECRET = 'sp=rwl&st=2023-04-27T04:37:23Z&se=2023-05-26T12:37:23Z&spr=https&sv=2021-12-02&sr=c&sig=stringofstuffremovedhahahahahthisisfromthesastokenA%3D' ; --Removing leading '?'<br />
GO</p><p> </p><p>CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]<br />
WITH (<br />
LOCATION = 'abs://mycontainer@mystorageacct.blob.core.windows.net',<br />
CREDENTIAL = [CETASCredential] );<br />
GO</p><p> </p><p>CREATE EXTERNAL FILE FORMAT [CETASFileFormat]<br />
WITH(<br />
FORMAT_TYPE=PARQUET,<br />
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'<br />
);<br />
GO</p><p> </p><p>-- Count how many rows we plan to offload<br />
SELECT COUNT(*) FROM my.dbo.table</p><p>CREATE EXTERNAL TABLE mytable_polybase_example</p><p>
WITH (<br />
LOCATION = 'I/get/multiple/paths/for/multiple/tables!/',<br />
DATA_SOURCE = [CETASExternalDataSource],<br />
FILE_FORMAT = [CETASFileFormat])<br />
AS <br />
SELECT <br />
*<br />
FROM <br />
my.dbo.table</p><p> </p><p>-- you can query the newly created external table<br />
SELECT COUNT (*) FROM mytable_polybase_example;</p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-40422035558253211792023-03-02T14:21:00.000-06:002023-03-02T14:21:21.658-06:00[WAT] fun with declare @blah = varchar!Another in my "WAT" file. (Go find it on youtube, you'll laugh and groan) <div><br /></div><div>What happens if you don't assign a length to varchar? I swear I'd learned that it became varchar(30).<br />Running on SQL Server 2014 SP3. <b>Bold </b>are the selects that are getting returned.</div><div><br /></div><div><span style="font-family: courier;">DECLARE @blah VARCHAR </span></div><div><span style="font-family: courier;">SET @blah = '123456' </span></div><div><b><span style="font-family: courier;">SELECT @blah </span></b></div><div><span style="font-family: courier;">DECLARE @date DATETIME=getdate() </span></div><div><b><span style="font-family: courier;">SELECT CONVERT(VARCHAR, @date, 112) </span></b></div><div><span style="font-family: courier;">SELECT @blah = CONVERT(VARCHAR, @date, 112) </span></div><div><b><span style="font-family: courier;">SELECT @blah </span></b></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">1 </span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">2030302</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">2</span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-76377762387976184242023-02-23T18:29:00.001-06:002023-02-23T18:29:53.934-06:00Severity 10 - using Extended Events to get code errors<p>I wish I knew whom to thank for this. An absolute genius idea. Probably Ozar, possibly Bertrand, could be White or a bunch of other #SQLFamily. Warning: once you use this, you'll want it on all your servers. </p><p>Premise: run an Extended Event looking for all errors over Severity 10, saving in a 10mb memory buffer within the SQL Server instance. Keep the rolling last errors so you have a decent amount of history. I believe the original was Severity 10 and above, but that one was too chatty for me, whereas 11+ seems to be ideal. But I refer to it as my "Sev10" code, so here we are. This includes all sorts of stuff you won't normally see - errors from stored procedures, errors in replication, errors in SSMS, etc. </p><p>Testing it is easy: create session & start it, run "select 1/0", then run the query.</p><p>I do have a version that I run that saves logs out, but honestly, that's a different post. This Xevent uses 10mb from the Ring Buffer, constantly filling and keeping the last 10mb of errors. Performance hit seems minimal, and we're doing millions of transactions a day.</p><p>Performance has always been the kicker on this - using Xquery directly against the ring buffer is slow as heck, and even after solving that via temp tables, I still ran into problems with one particular server. That made me realize I don't appear to have posted this. By splitting it via NODE to multiple rows to a temp table, then querying the temp table to shred the XML, the performance is vastly improved. In my case, from 8 minutes to 16 seconds. And on most servers, under 5. </p><p>Other thoughts - you might consider adding another WHERE clause to the ADD EVENT; maybe filter end-users' machines or SSMS. I like having it, but you do you. Turns out SSMS is damn chatty sometimes, also. "View server state permission was denied " and "Cannot drop the table '#SVer'" abound.</p><p>Want to run this in Azure SQL DB? Sure, just do it against each database, replacing the "ON SERVER" with "ON DATABASE". The table names you query against change slightly (it's commented), and the time zone calculation doesn't work yet. Booooooooo.</p><p>I'm working on a collector, just haven't finished yet.</p><p>Bonus! Frames! Take the Frames provided in the Event_Data, and go use their amazing code to tell you exactly what piece of code in what function/stored procedure, and what line, caused the issue. Is it in a stored procedure calling a stored procedure calling a function? This will tell you where and what. Genius, and kudos to Tom. https://straightforwardsql.com/posts/investigating-errors-with-extended-events/</p><p>Cheers!</p><p><br />
<script src="https://gist.github.com/mbourgon/5bd2d9c2456b2c613d8ccf36e403e98a.js"></script>
</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-52653183244817375912023-02-17T09:44:00.005-06:002023-02-17T09:44:34.704-06:00Azure SQL Managed Instance - a simple Extended Event to track logins<p> This is a barebones Xevent (Extended Event) . I'm not using blob storage, just the existing "eh, you have a couple hundred meg worth of ring buffer you can use". But I needed to see who was using one of our dev instances. I thought we'd moved everything over to the Azure SQL DBs, since there's a ton of reasons to do so.</p><p>Here's some quick off-the-cuff reasons why I prefer Azure SQL DB to Managed Instance:</p><p></p><ol style="text-align: left;"><li>Spins up/down FAST. Seconds to minutes. Have a 20gb database? Takes 2 minutes. 30gb? 3.</li><li>More availability! Technically more servers!</li><li>Serverless options! Allows you to just pay for the price of storage, not compute. Doubly useful for QA/Dev boxes that aren't always used. Also allows us to auto scaleup/down as traffic changes.</li><li>Works with our super-limited networking.</li></ol><p></p><p>Yes, there are downsides. CLR, Replication, Agent, Mail, others. But the wins of using Azure SQL DB are very tangible. < / soapbox > </p><p><br /></p><p><br /></p><p><br /></p><p>Anyhow, script:</p><p>@@</p><p><span style="font-family: courier;">/* </span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">CREATE EVENT SESSION [Logins] ON SERVER </span></p><p><span style="font-family: courier;">ADD EVENT sqlserver.login(</span></p><p><span style="font-family: courier;">ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name,sqlserver.username))</span></p><p><span style="font-family: courier;">ADD TARGET package0.ring_buffer(SET max_memory = 4096) --max_event_limit threw error!</span></p><p><span style="font-family: courier;">go</span></p><p><span style="font-family: courier;">ALTER EVENT SESSION [Logins] </span></p><p><span style="font-family: courier;">ON SERVER </span></p><p><span style="font-family: courier;">STATE = start ;</span></p><p><span style="font-family: courier;">GO*/</span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">if object_id('tempdb..#xevent') is not null</span></p><p><span style="font-family: courier;"> DROP TABLE #xevent</span></p><p><span style="font-family: courier;">CREATE TABLE #xevent (target_data XML)</span></p><p><span style="font-family: courier;">INSERT INTO #xevent ( target_data)</span></p><p><span style="font-family: courier;">SELECT target_data</span></p><p><span style="font-family: courier;"> FROM sys.dm_xe_session_targets AS st</span></p><p><span style="font-family: courier;"> INNER JOIN sys.dm_xe_sessions AS se</span></p><p><span style="font-family: courier;"> ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))</span></p><p><span style="font-family: courier;">WHERE</span></p><p><span style="font-family: courier;">se.name = 'Logins'</span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">DECLARE @count VARCHAR(10) </span></p><p><span style="font-family: courier;">SELECT @count = target_data.value('/RingBufferTarget[1]/@eventCount','varchar(10)') </span></p><p><span style="font-family: courier;">FROM #xevent --null means it's not on or not "installed"</span></p><p><span style="font-family: courier;">PRINT 'eventCount = ' + @count</span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">;with events_cte as(</span></p><p><span style="font-family: courier;">SELECT </span></p><p><span style="font-family: courier;">ed.c.value('(@timestamp)[1]', 'DATETIMEOFFSET') at TIME ZONE 'Central Standard Time' AS err_timestamp,</span></p><p><span style="font-family: courier;">--xevents.event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],</span></p><p><span style="font-family: courier;">ed.c.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,</span></p><p><span style="font-family: courier;">ed.c.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,</span></p><p><span style="font-family: courier;">ed.c.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,</span></p><p><span style="font-family: courier;">ed.c.value('(action[@name="database_name (Action)"]/value)[1]', 'nvarchar(max)') AS nt_username,</span></p><p><span style="font-family: courier;">ed.c.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS username</span></p><p><span style="font-family: courier;">, ed.c.query('.') AS event_data --we leave this off since it makes this run SO MUCH LONGER.</span></p><p><span style="font-family: courier;">FROM #xevent a</span></p><p><span style="font-family: courier;">cross apply (select CAST(target_data as XML) as event_data) AS xevents</span></p><p><span style="font-family: courier;">CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS ed(c)</span></p><p><span style="font-family: courier;">)</span></p><p><span style="font-family: courier;">SELECT *</span></p><p><span style="font-family: courier;">--, events_cte.event_data</span></p><p><span style="font-family: courier;">from events_cte </span></p><p><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">order by err_timestamp desc;</span></p><p>@@</p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-79845658107303088222023-02-08T16:38:00.001-06:002023-02-08T16:38:30.742-06:00Azure SQL Database Auditing gotchas.A couple of notes when using Azure SQL DB Auditing <div>1) this is actually kinda cool, overall. I plan on mining this in the future for code changes, etc. </div><div>2) You can filter it! predicateexpression is the term you're looking for, available via the powershell module
3) if you're trying to add predicates, it uses SQL Audit syntax. In our case, we wanted to ignore a particular stored procedure call, so we used: </div><div>"(NOT [statement] like '%myspnamehere%')" </div><div>4) BEFORE YOU DO THIS and add the predicate: turn all the audits off on that "server" first. </div><div> The database AND the server-level.
The regular AND the microsoft. </div><div><br /></div><div>We had a heck of a time figuring out why it wasn't working right. </div><div>Between this and making sure the Database-Level Auditing was off, this worked. </div><div><br /></div><div>Disconnect-AzAccount myguidhere</div><div>Connect-AzAccount -Tenant myguidhere</div><div><guidthatismyaccountid><br /></guidthatismyaccountid></div>Set-AzContext -SubscriptionId Get-AzSqlServerAudit -ResourceGroupName "ourRGname" -Servername "ourSERVERname" <div><br /></div><div><div>Set-AzSqlServerAudit -ResourceGroupName "ourRGname" -ServerName "ourSERVERname" -PredicateExpression "(NOT [statement] like '%myspnamehere%')" -WorkspaceResourceId "ourworkspaceidforourLogAnalyticsTarget" -LogAnalyticsTargetState ENABLED</div><div><br /></div><div>Set-AzSqlServerMSSupportAudit -ResourceGroupName "ourRGname" -ServerName "ourSERVERname" -WorkspaceResourceId "ourworkspaceidforourLogAnalyticsTarget" -LogAnalyticsTargetState ENABLED</div></div><div><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-25332130450981312712022-10-28T17:13:00.004-05:002022-10-28T17:16:05.034-05:00Azure SQL Database - something like sp_send_dbmail<p> I needed the ability to send emails from Azure SQL DB, which doesn't support it. The long-term goal is to use LogicApps or SendGrid or something like that, but while I wait for permissions and the like, let me share this instead. It acts like sp_send_dbmail, and will even run queries in the database (or other databases on that server!) and send them from your on-prem server.</p><p>This is based on others. They had the awesome parts, my parts are the crappy glue making it work. : )</p><p><br /></p><p>The emailqueue table looks like this:</p><div style="text-align: left;"><span style="font-family: courier;">CREATE TABLE [dbo].[EmailQueue](<br /><span style="white-space: pre;"> </span>[Id] [INT] IDENTITY(1,1) NOT NULL,<br /><span style="white-space: pre;"> </span>[Recipients] [VARCHAR](250) NOT NULL,<br /><span style="white-space: pre;"> </span>[Cc_recipients] [VARCHAR](250) NULL,<br /><span style="white-space: pre;"> </span>[Email_Subject] [VARCHAR](250) NOT NULL,<br /><span style="white-space: pre;"> </span>[Email_body] [VARCHAR](MAX) NULL,<br /><span style="white-space: pre;"> </span>[Email_body_format] [VARCHAR](10) NULL,<br /><span style="white-space: pre;"> </span>[Query] [NVARCHAR](MAX) NULL,<br /><span style="white-space: pre;"> </span>[profile_name] [VARCHAR](250) NULL,<br /><span style="white-space: pre;"> </span>[QueueTime] [DATETIME2](7) NOT NULL,<br /><span style="white-space: pre;"> </span>[SentTime] [DATETIME2](7) NULL,<br /><span style="white-space: pre;"> </span>[Importance] [VARCHAR](10) NULL,<br /><span style="white-space: pre;"> </span>[attach_query_result_as_file] [BIT] NULL,<br /><span style="white-space: pre;"> </span>[query_attachment_filename] [VARCHAR](200) NULL,<br /><span style="white-space: pre;"> </span>[query_result_header] [BIT] NULL,<br /><span style="white-space: pre;"> </span>[query_result_width] [INT] NULL,<br /><span style="white-space: pre;"> </span>[query_result_separator] [VARCHAR](2) NULL,<br /><span style="white-space: pre;"> </span>[query_result_no_padding] [BIT] NULL,<br /><span style="white-space: pre;"> </span>[bcc_recipients] [VARCHAR](255) NULL,<br /><span style="white-space: pre;"> </span>[execute_query_database] [VARCHAR](255) NULL,<br /><span style="white-space: pre;"> </span>[from_address] [VARCHAR](MAX) NULL,<br />PRIMARY KEY CLUSTERED <br />(<br /><span style="white-space: pre;"> </span>[Id] ASC<br />)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]<br />) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]<br />GO<br />ALTER TABLE [dbo].[EmailQueue] ADD CONSTRAINT [Co_Importance] DEFAULT ('Normal') FOR [Importance]<br />GO<br />ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((0)) FOR [attach_query_result_as_file]<br />GO<br />ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((1)) FOR [query_result_header]<br />GO<br />ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((0)) FOR [query_result_no_padding]<br />GO<br /></span></div><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;">And the SP: </p><p><span style="font-family: courier;">CREATE PROCEDURE [dbo].[log_dbmail] </span></p><p><span style="font-family: courier;"> @profile_name sysname = NULL, </span></p><p><span style="font-family: courier;"> @recipients VARCHAR(MAX) = NULL, </span></p><p><span style="font-family: courier;"> @copy_recipients VARCHAR(MAX) = NULL,</span></p><p><span style="font-family: courier;"> @blind_copy_recipients VARCHAR(MAX) = NULL,</span></p><p><span style="font-family: courier;"> @subject NVARCHAR(255) = NULL,</span></p><p><span style="font-family: courier;"> @body NVARCHAR(MAX) = NULL, </span></p><p><span style="font-family: courier;"> @body_format VARCHAR(20) = NULL, </span></p><p><span style="font-family: courier;"> @importance VARCHAR(6) = 'NORMAL',</span></p><p><span style="font-family: courier;"> @sensitivity VARCHAR(12) = 'NORMAL',</span></p><p><span style="font-family: courier;"> @file_attachments NVARCHAR(MAX) = NULL, </span></p><p><span style="font-family: courier;"> @query NVARCHAR(MAX) = NULL,</span></p><p><span style="font-family: courier;"> @execute_query_database sysname = NULL, </span></p><p><span style="font-family: courier;"> @attach_query_result_as_file BIT = 0,</span></p><p><span style="font-family: courier;"> @query_attachment_filename NVARCHAR(260) = NULL, </span></p><p><span style="font-family: courier;"> @query_result_header BIT = 1,</span></p><p><span style="font-family: courier;"> @query_result_width INT = 256, </span></p><p><span style="font-family: courier;"> @query_result_separator VARCHAR(2) = ' ',</span></p><p><span style="font-family: courier;"> @exclude_query_output BIT = 0,</span></p><p><span style="font-family: courier;"> @append_query_error BIT = 0,</span></p><p><span style="font-family: courier;"> @query_no_truncate BIT = 0,</span></p><p><span style="font-family: courier;"> @query_result_no_padding BIT = 0,</span></p><p><span style="font-family: courier;"> @mailitem_id INT = NULL OUTPUT,</span></p><p><span style="font-family: courier;"> @from_address VARCHAR(MAX) = NULL,</span></p><p><span style="font-family: courier;"> @reply_to VARCHAR(MAX) = NULL</span></p><p><span style="font-family: courier;">AS </span></p><p><span style="font-family: courier;">INSERT INTO dbo.EmailQueue</span></p><p><span style="font-family: courier;">(</span></p><p><span style="font-family: courier;"> Recipients,</span></p><p><span style="font-family: courier;"> Cc_recipients,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>Bcc_recipients,</span></p><p><span style="font-family: courier;"> Email_Subject,</span></p><p><span style="font-family: courier;"> Email_body,</span></p><p><span style="font-family: courier;"> Email_body_format,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>Query,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>Execute_Query_Database,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>attach_query_result_as_file,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>query_attachment_filename,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>query_result_header,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>query_result_width,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>query_result_separator,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>query_result_no_padding,</span></p><p><span style="font-family: courier;"> profile_name,</span></p><p><span style="font-family: courier;"> QueueTime,</span></p><p><span style="font-family: courier;"> SentTime,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>Importance</span></p><p><span style="font-family: courier;">)</span></p><p><span style="font-family: courier;">VALUES</span></p><p><span style="font-family: courier;">( @recipients, -- Recipients - varchar(250)</span></p><p><span style="font-family: courier;"> @copy_recipients, -- Cc_recipients - varchar(250)</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@blind_copy_recipients,</span></p><p><span style="font-family: courier;"> @subject, -- Email_Subject - varchar(250)</span></p><p><span style="font-family: courier;"> @body, -- Email_body - varchar(max)</span></p><p><span style="font-family: courier;"> @body_format, -- Email_body_format - varchar(10)</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@execute_query_database,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@attach_query_result_as_file,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query_attachment_filename,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query_result_header,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query_result_width,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query_result_separator,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@query_result_no_padding,</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@profile_name, -- profile_name - varchar(250)</span></p><p><span style="font-family: courier;"> CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME), -- QueueTime - datetime2</span></p><p><span style="font-family: courier;"> NULL, -- SentTime - datetime2</span></p><p><span style="font-family: courier;"><span style="white-space: pre;"> </span>@importance</span></p><p><span style="font-family: courier;"> )</span></p><p><span style="white-space: pre;"> </span></p><div><br /></div><p><br /></p><p>The powershell that pulls it all together: </p><p><script src="https://gist.github.com/mbourgon/e7180f8c975d159c653091f50f64030b.js"></script></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-36308969377464552542022-05-12T23:27:00.001-05:002022-05-12T23:27:32.032-05:00Read your Microsoft Teams messages using Microsoft Graph and Powershell<p> I suck at time cards. Part of it is that I'm jumping between 30 tasks at once - help this team, help that team, training, documentation, and my sprint work. At the end of the day I have a bare-ass notebook with a few cryptic lines about what I did today - if I'm lucky. </p><p>But a lot of it is available elsewhere. We're all virtual now, so all my interactions are either voice calls, meetings, or chats. I want to list out what I did in a given day so I can reconstruct it - how much time I spent helping whom.</p><p><br /></p><p>Enter Microsoft Graph. It's an API that selects from it. There's a set of cmdlets that let you do quite a bit. I really do wish the get-mgchatmessagedelta properly handled "-filter" to make this faster, but I'll cope.</p><p><br /></p><p>This will rip through ALL your chats, then for each one get the most recent 200 messages, filter it down to the past day or two, then toss it out on a format-table. </p><p>My pain, your gain. It's always fun when there's 2 google results for something. </p><p><br /></p><pre class="default s-code-block" style="border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: var(--lh-md); margin-bottom: calc(var(--s-prose-spacing) + 0.4em); margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><code class="hljs language-perl" style="border: 0px; box-sizing: inherit; font-family: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">Install-Module Microsoft.Graph
Import-Module Microsoft.Graph.Teams
$RequiredScopes = @(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"Chat.ReadBasic"</span>, <span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"Chat.ReadWrite"</span>)
Connect-MgGraph -Scopes $RequiredScopes
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#at this point a browser window should appear - allow it.</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#I had to find my user id in graph explorer UI by running GET V1.0 https://graph.microsoft.com/v1.0/me</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#unsure how to get it otherwise - but you don't need it with get-mgchat</span>
get-mgchat
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#take one of those IDs</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#let's look at this chat:</span>
get-mgchat -ChatId <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">19</span>:deadbeefb2d949d88d4455f5279e5d8b@thread.v2
get-mgchatmessage -ChatId <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">19</span>:deadbeefb2d949d88d4455f5279e5d8b@thread.v2
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#this nests and walks through properly, strips HTML, but lord this will be slow. And shows more than one line per message, even when I try now to.</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#By default you can get all your chats by running get-mgchat. -all and -pagesize 50 is required for the module to paginate the request and get you everything. But in my case it grabbed all 2000 chats. The -first 5 is for testing.</span>
$tzone = Get-TimeZone <span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"># conversion from GMT to local time. https://jdhitsolutions.com/blog/powershell/7962/convert-to-local-time-with-powershell/</span>
$mychats = get-mgchat -all -PageSize <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">50</span> |<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">select</span> -first <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">5</span>
$all_chat_info = @() <span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#force-setting to an array</span>
$all_chat_info = <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">foreach</span> ($chat in $mychats) {
$chatinfo = get-mgchat -ChatId $chat.id <span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#get base details about the CHAT itself</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#set some details about the chat itself for the later query</span>
$chatname = $chat.Topic
$members = $chat.Members
$chattype = $chat.chattype
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#now get every message from that chat since midnight yesterday. Note LastModifiedDateTime is GMT. The jdhit page says -($tzone...), but all I had to do was .tolocaltime() ... I think.</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#the -top 200 -pagesize 50 is to get the most recent 200 messages, and again you have to paginate. </span>
$recentchatmessages = get-mgchatmessage -ChatId $chat.id -top <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">200</span> -pagesize <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">50</span> |where {$_.LastModifiedDateTime.tolocaltime() -<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">gt</span> (get-date).date.AddDays(-<span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">1</span>)} <span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"># all from after midnight yesterday |select -first 5</span>
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#and now use select expression to add the above fields and parse the below fields, stripping out HTML (but I can't seem to only get the first line in OGV)</span>
$recentchatmessages | <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">select</span> @{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'LastModified'</span>;Expression={($_.LastModifiedDateTime.tolocaltime())}}, @{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'ChatName'</span>;Expression={($chatname)}}, @{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'members'</span>;Expression={($members)}}, @{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'ChatType'</span>;Expression={($chattype)}},
@{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'From'</span>;Expression={($_.from.user.displayname)}}, @{Label=<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'Body'</span>;Expression={ ($_.Body.content -<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">split</span> <span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'\n'</span>)[<span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">0</span>] -replace <span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'<[^>]+>'</span>,<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">''</span>}}
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#@{Label='From';Expression={($_.from.user.displayname)}}, @{Label='Body';Expression={( ($_.Body.content -replace '<[^>]+>','').split([Environment]::NewLine)|select -first 1)}}</span>
}
$all_chat_info|<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">format</span>-table
<span class="hljs-comment" style="border: 0px; box-sizing: inherit; color: var(--highlight-comment); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#and now close/disconnect</span>
Disconnect-MgGraph</code></pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-74819137398260058822022-04-20T13:23:00.002-05:002022-04-20T13:23:24.913-05:00Xquery - a surprising difference between Azure SQL Database and Azure Managed Instance with Quotes<div>I'm moving processes into Azure. Given that it's me, probably poorly. But I'm using this as an opportunity to improve things where possible, specifically looking at moving some processes to Azure SQL Database. </div><div>Why? I love them for several reasons: </div><div><br /></div><div><ul style="text-align: left;"><li>Simpler</li><li>Cheaper</li><li>Quick to create</li><li>Quick to modify the instance (seconds, not hours)</li></ul></div><div><br /></div><div><br /></div><div>Yes, there are still gotchas (Elastic Jobs, no cross-db queries, no CLR, can't replicate from it), but overall? Soooo nice.</div><div><br /></div><div>---</div><div><br /></div><div>I was trying to get some Xquery parsing working, and ran into a weird error:</div><div><span style="font-family: courier;">"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"</span></div><div><br /></div><div>Weird. Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now. And because the SP specifically has a SET QUOTED_IDENTIFIER ON; </div><div><br /></div><div>Okay, let's take alook online.</div><div><br /></div><div><a href="https://stackoverflow.com/questions/47091181/jobs-failing-due-to-incorrect-settings-quoted-identifier">https://stackoverflow.com/questions/47091181/jobs-failing-due-to-incorrect-settings-quoted-identifier</a><br /></div><div><br /></div><div>Yeah, that's the problem. And indeed, my code has similar xquery to the example:</div><span style="font-family: courier;">set @myval = @xml.[value]('(*/A[B="C"]/D[E="F" or G="H"]/I)[1]', 'varchar (35)');</span><div><br /></div><div>What's the fix? Use two single-quotes instead of the double quote.</div><div><span style="font-family: courier;">set @myval = @xml.[value]('(*/A[B=''C'']/D[E=''F'' or G=''H'']/I)[1]', 'varchar (35)');</span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-39405846595918918942022-03-05T09:58:00.001-06:002022-03-05T09:58:11.527-06:00We stand with Ukrainegrowing up, my Baba always pronounced it weirdly to me - you-craw-een. Unsurprisingly, this turns out to be the right way.
watching the footage,i cant help but feel for them. I don't know how this is going to shake out, but they're in the right and russia is wrong.
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhOxpm4sTFmA3RGHwietPYWiDFI2M1MaAlDANUGpE00D6M-0mGEN2uYScpKY6HJ80nzI_2-Tx4SX5Bh8kv0LYPPJwxE7cIL9eDrHXt-N1l27V7OF1x2NpoFtzftUrePpaxSFuuixGfhuu0jMy-5aA2zl_Em64cl8bq_SahblbgEgd7is9AKRvwGyHdu=s276" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="600" data-original-height="183" data-original-width="276" src="https://blogger.googleusercontent.com/img/a/AVvXsEhOxpm4sTFmA3RGHwietPYWiDFI2M1MaAlDANUGpE00D6M-0mGEN2uYScpKY6HJ80nzI_2-Tx4SX5Bh8kv0LYPPJwxE7cIL9eDrHXt-N1l27V7OF1x2NpoFtzftUrePpaxSFuuixGfhuu0jMy-5aA2zl_Em64cl8bq_SahblbgEgd7is9AKRvwGyHdu=s600"/></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-58357487055727295392022-01-21T15:35:00.004-06:002022-01-21T15:45:03.221-06:00SSRS - getting the ACTUAL connection strings for ALL your datasources in SSRS<p>File under "and this is why I'm not an MVP"...<br /></p><p>Yeah, it's been a few months. Azure, Powershell, Python, Parquet, and the entire team turning over. </p><p>However, now I get to deal with... SSRS? Really? Yeah, until we figure out how to convert them to PowerBI or something Azure-centric that requires no effort. Seriously, I can't believe Microsoft doesn't have this. It's DUM. D-U-M-B, dum.</p><p><br /></p><p>But I digress.</p><p><br /></p><p>Today: comparing your old SSRS Datasource connection strings to the new server, since they don't match and you don't think you can just backup/restore over ReportServer.</p><p><br /></p><p><script src="https://gist.github.com/mbourgon/b0fb93736354040ce7242673f8e90107.js"></script></p><p><br /></p><p>and , in case THAT doesn't work.</p><p>----------------------------------------------------------</p><p>#to install the MS module you need:</p><p>#Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)</p><p><br /></p><p><br /></p><p><br /></p><p>$SSRS_Servername = "yourservernamehere"</p><p><br /></p><p>#query cribbed from https://dba.stackexchange.com/questions/138236/get-ssrs-datasources-from-reportserver</p><p>$SSRS_list_datasources = @"</p><p>/*</p><p>Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?</p><p>With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,</p><p>to document the usage or to search for a specific server/database.</p><p><br /></p><p>Please remark: Querying the ReportServer database directly is not a supported way.</p><p>Works with SSRS 2005 and higher version ReportServer databases.</p><p>Requieres select rights on the "Catalog" table in ReportServer database.</p><p>*/</p><p><br /></p><p>-- Connection strings of all SSRS Shared Datasources</p><p>;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause.</p><p> (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'</p><p> ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'</p><p> AS rd)</p><p>,SDS AS</p><p> (SELECT SDS.name AS SharedDsName</p><p> ,SDS.[Path]</p><p> ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF</p><p> FROM dbo.[Catalog] AS SDS</p><p> WHERE SDS.Type = 5) -- 5 = Shared Datasource</p><p><br /></p><p>SELECT CON.[Path]</p><p> ,CON.SharedDsName</p><p> ,CON.ConnString</p><p>FROM</p><p> (SELECT SDS.[Path]</p><p> ,SDS.SharedDsName</p><p> ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString</p><p> FROM SDS</p><p> CROSS APPLY </p><p> SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)</p><p> ) AS CON</p><p>-- Optional filter:</p><p>-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'</p><p>ORDER BY CON.[Path]</p><p> ,CON.SharedDsName;</p><p>"@</p><p>. C:\Powershell_Scripts\invoke-sqlcmd2a.ps1</p><p><br /></p><p>$datasource_list = invoke-sqlcmd2 -query $SSRS_list_datasources -ServerInstance $SSRS_Servername -Database ReportServer</p><p><br /></p><p><br /></p><p>$DataSource_Details = @()</p><p>$DataSource_Details += foreach ($datasource in $datasource_list) {</p><p>$ConnectString = Get-RsDataSource -ReportServerUri "http://$SSRS_Servername/ReportServer" -Path $datasource.Path</p><p>$connectstring|select @{Label='SharedDsName';Expression={$datasource.SharedDsName}} ,@{Label='Path';Expression={$datasource.path}}, @{Label='ConnectionString';Expression={$_.ConnectString}} , @{Label='Username';Expression={$_.UserName}} </p><p>}</p><p><br /></p><p>$DataSource_Details|format-table</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-34798565208300570432021-06-09T11:48:00.001-05:002021-06-09T11:48:41.480-05:00SQL Workbench /J & Aurora & MariaDB driver getting "could not load system variables" - FIXED!<p> Using the 2.7.3 MariaDB java connector with SQL Workbench /J, I was trying to reach my aurora instance but kept getting this error:</p><pre style="border-radius: 6px; box-sizing: border-box; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 11.9px; line-height: 1.45; margin-bottom: 0px !important; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 16px;"><code style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; border-radius: 6px; border: 0px; box-sizing: border-box; display: inline; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 11.9px; line-height: inherit; margin: 0px; overflow-wrap: normal; overflow: visible; padding: 0px; word-break: normal;">could not load system variables</code></pre><p></p><ul style="text-align: left;"><li>https://community.dremio.com/t/mysql-source-creation-fails/6738/2</li><li>https://github.com/sysown/proxysql/issues/2523</li><li>https://github.com/sysown/proxysql/issues/2009</li><li>https://stackoverflow.com/questions/63283846/issue-integrating-mariadb-client-with-mysql-db</li></ul><div>Finally found an answer here:</div><ul style="text-align: left;"><li>https://jira.mariadb.org/browse/CONJ-824</li></ul><div>As per Diego Dupin:</div><p></p><div class="issue-data-block activity-comment twixi-block expanded" id="comment-165134" style="border-bottom: 1px solid rgb(193, 199, 208); margin: 0px; padding: 10px;"><div class="twixi-wrap verbose actionContainer" style="margin: 0px; padding: 0px 0px 0px 20px; position: relative;"><div class="action-body flooded" style="margin: 10px 0px 0px; overflow: auto hidden; padding: 0px;"><p style="margin: 10px 0px 0px; padding: 0px;"></p><blockquote><p style="margin: 10px 0px 0px; padding: 0px;">Aurora proxy has a known race condition issue that results in skipping other queries in proxy buffer. connection might be in hang waiting for query response. During authentication, socket has a timeout, that will result throwing the error you describe.</p><p style="margin: 10px 0px 0px; padding: 0px;">So if `usePipelineAuth` or `useBatchMultiSend` is enable, it might work ... or not. All pipeline options (`usePipelineAuth` and `useBatchMultiSend`) must be disabled when using aurora. Problem has been reported to aurora a few times without correction.</p><p style="margin: 10px 0px 0px; padding: 0px;"><span class="icon-default aui-icon aui-icon-small aui-iconfont-link" style="background-position: 0px 0px; background-repeat: no-repeat; border: none; color: #344563; display: inline-block; font-size: 0px; height: 16px; line-height: 0; margin: 0px; padding: 0px; position: relative; vertical-align: text-bottom; width: 16px;"></span></p></blockquote><p style="margin: 10px 0px 0px; padding: 0px;"><span class="icon-default aui-icon aui-icon-small aui-iconfont-link" style="background-position: 0px 0px; background-repeat: no-repeat; border: none; color: #344563; display: inline-block; font-size: 0px; height: 16px; line-height: 0; margin: 0px; padding: 0px; position: relative; vertical-align: text-bottom; width: 16px;"></span></p><p style="margin: 10px 0px 0px; padding: 0px;"><br /></p><p style="margin: 10px 0px 0px; padding: 0px;">What worked for us was adding this after the DB name </p><p style="margin: 10px 0px 0px; padding: 0px;"><span style="background-color: #f4f5f7; color: #172b4d; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Oxygen, Ubuntu, "Fira Sans", "Droid Sans", "Helvetica Neue", sans-serif; font-size: 14px;">usePipelineAuth=false&useBatchMultiSend=false</span></p><p style="margin: 10px 0px 0px; padding: 0px;"><span style="background-color: #f4f5f7; color: #172b4d; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Oxygen, Ubuntu, "Fira Sans", "Droid Sans", "Helvetica Neue", sans-serif; font-size: 14px;">like this:</span></p><p style="margin: 10px 0px 0px; padding: 0px;"><span style="background-color: #f4f5f7; color: #172b4d; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Oxygen, Ubuntu, "Fira Sans", "Droid Sans", "Helvetica Neue", sans-serif; font-size: 14px;">jdbc:mysql://127.0.0.1:3307/mydbname?usePipelineAuth=false&useBatchMultiSend=false</span></p></div></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-21746737537526032342021-06-04T18:27:00.001-05:002021-06-07T08:09:39.722-05:00Powershell - using ODBC and the Simba Driver to query AWS Athena<p> I was running into issues with the Linked Server lopping off long JSON that I'm having to pull out from the raw files. I can't explain it - doesn't appear to be SSMS. See previous post</p><p><br /></p><p>But I needed to automate this, rather than use SQL Workbench, save to "Excel" (it was XML), then opening it again and saving it so that instead of 250mb, it's 30mb. Runs against the previous month, one day at a time (walking the partitions), and then saves to a file. You got your Athena, your ODBC, your Export-Excel...</p><p><br /></p><p><br /></p><p>#This has two requirements:</p><p># 1 - simba installed as an ODBC driver, called "your_simba_athena_dsn_name_here". Free from Amazon for this use.</p><p># 2 - ImportExcel. Requires Powershell V4 (V5?), but for sure v3 won't work.</p><p>#Get-ODBC-Data2 is just a slight modification of Anders' code that includes the UID/PWD. Thanks!</p><p>#https://www.andersrodland.com/working-with-odbc-connections-in-powershell/</p><p>#modified because we have to pass in user/password to Athena.</p><p>function Get-ODBC-Data2{</p><p> param(</p><p> [string]$dsn,</p><p> [string]$param,</p><p> [string]$query=$(throw 'query is required.')</p><p> )</p><p> $conn = New-Object System.Data.Odbc.OdbcConnection</p><p> $conn.ConnectionString = "DSN=$dsn;$param"</p><p> $query</p><p> $conn.open()</p><p> $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)</p><p> $ds = New-Object system.Data.DataSet</p><p> (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null</p><p> $conn.close()</p><p> $ds.Tables[0]</p><p> }</p><p><br /></p><p> #Set start and end. Those can be whatever, but I made it one month for simplicity</p><p> [datetime]$start_date_yyyymmdd = (get-date).addmonths(-1).tostring("yyyy-MM-01")</p><p> [datetime]$end_date_yyyymmdd = $start_date_yyyymmdd.addmonths(1).tostring("yyyy-MM-01")</p><p> #[datetime]$end_date_yyyymmdd = (get-date).addmonths(0).tostring("yyyy-MM-01")</p><p> $filename = "Myfile_" + $start_date_yyyymmdd.tostring("yyyyMM")</p><p> #set a variable so that as we walk through, we have an array to save everything to.</p><p> $formattedresults=@()</p><p> </p><p> #loop through the days</p><p> while ($start_date_yyyymmdd -lt $end_date_yyyymmdd) {</p><p> $startdate = $start_date_yyyymmdd.tostring('yyyy-MM-dd')</p><p> "starting $startdate"</p><p> $query = @"</p><p> select json_extract_scalar(myfield, '$.fieldjsonparsed') as fielda, dt,</p><p> fieldb, fieldc</p><p> from myschema.mytablename</p><p> where dt='$startdate'</p><p>"@</p><p> </p><p> $results = get-odbc-data2 -query $query -dsn "your_simba_athena_dsn_name_here" -param "uid=youraccesskeyhere;PWD=yoursecretkeyhere" </p><p> #save non-systemdatarow version to our main array</p><p> $formattedresults +=$results|select-object fielda,fieldb,fieldc </p><p> $start_date_yyyymmdd = $start_date_yyyymmdd.adddays(1)</p><p> }</p><p> </p><p> #export using ImportExcel module, which unfortunately needs at least higher than V3</p><p> $formattedresults| export-excel -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path c:\temp\$filename.xlsx</p><div><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-59427574324224413652021-06-04T11:06:00.003-05:002021-06-04T18:15:47.931-05:00EXECUTE AT against Athena via Linked Server and "does not support the required transaction interface"<p> TLDR - using EXECUTE AT with Athena via linked server. I used https://www.mssqltips.com/sqlservertip/6191/query-aws-athena-data-from-sql-server to get it set up and it works! But there are some issues.</p><p><br /></p><p><br /></p><p>1) Partition your tables in Athena. Better performance if you have a decent amount of data</p><p>2) This means you can't just do "select from <linkedserver>...tablename", as that doesn't tell Athena to do partition elimination</p><p>3) Once you're using OPENQUERY, if you run into issues because it doesn't know what to expect, you can use CAST or CONVERT to force metadata (aka put "cast(fielda as varchar(500))" if it thinks the field is only 255 characters.</p><p>4) That doesn't help with over varchar(8000). You need to move to EXECUTE AT instead, but that seems better then OPENQUERY as it appears to just pass the data back - I got rid of my CASTs in the athena query. Plus, dynamic SQL is easier! You will have to enable RPC OUT for the Linked Server.</p><p>5) I couldn't do INSERT INTO EXECUTE AT until I disabled Distributed Transactions in that Linked Server.</p><p><br /></p><p><br /></p><p>Update 2021/06/04 - and there's something screwy with EXECUTE AT. It runs without the CAST statements and doesn't complain , but doesn't appear to save everything - I have a handful of rows (out of 100k, mind you) that don't match up - one of the fields is lopped off. It's the most annoying thing, since my other option is SQL Workbench which WORKS but isn't easily automated. After futzing with it for a bit, I wound up moving over to a pure Powershell solution - I'll post that in a few.</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-84199683977093728092021-03-25T15:41:00.003-05:002021-03-25T15:41:59.095-05:00System_health - parsing out queryProcessing node<p>Needed this today, my pain = your gain.</p><p><br /></p><p>System_health has a TON of useful info. However, it's buried in XML, and my shredder (see other posts with the system_health tag) doesn't go this deep. </p><p>sp_server_diagnostics runs every 5 minutes, and there are other posts on getting some of the data out. But I needed more, and I needed more than just "right now". System_health saves 32 or so, in 5 minute increments.</p><p><b>This is not complete</b>. But you do get max workers, max in use, max idle, memory allocations, and non-preemptive wait types, both by count and duration.</p><p>Enjoy!</p><p><br /></p><p>if object_id('tempdb..#systemhealthsessiondata') is not null</p><p> DROP TABLE #systemhealthsessiondata</p><p><br /></p><p>SELECT CAST(xet.target_data AS XML) AS XMLDATA</p><p>INTO #SystemHealthSessionData</p><p>FROM sys.dm_xe_session_targets xet</p><p>JOIN sys.dm_xe_sessions xe</p><p>ON (xe.address = xet.event_session_address)</p><p>WHERE xe.name = 'system_health'</p><p><br /></p><p>SELECT C.query('.') EventXML</p><p>, T.C.value('@timestamp', 'datetime')</p><p><br /></p><p>, T.C.value('(data/value/queryProcessing/@maxWorkers)[1]','varchar(255)') as maxWorkers</p><p>, T.C.value('(data/value/queryProcessing/@workersCreated)[1]','varchar(255)') as workersCreated</p><p>, T.C.value('(data/value/queryProcessing/@workersIdle)[1]','varchar(255)') as workersIdle</p><p>, T.C.value('(data/value/queryProcessing/@tasksCompletedWithinInterval)[1]','varchar(255)') as tasksCompletedWithinInterval</p><p>, T.C.value('(data/value/queryProcessing/@pendingTasks)[1]','varchar(255)') as pendingTasks</p><p>, T.C.value('(data/value/queryProcessing/@oldestPendingTaskWaitingTime)[1]','varchar(255)') as oldestPendingTaskWaitingTime</p><p>, T.C.value('(data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','varchar(255)') as hasUnresolvableDeadlockOccurred</p><p>, T.C.value('(data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','varchar(255)') as hasDeadlockedSchedulersOccurred</p><p>, T.C.value('(data/value/queryProcessing/@trackingNonYieldingScheduler)[1]','varchar(255)') as trackingNonYieldingScheduler</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="MEMORY_ALLOCATION_EXT"]/@waits)[1]','varchar(255)') as MEMORY_ALLOCATION_EXT_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="RESERVED_MEMORY_ALLOCATION_EXT"]/@waits)[1]','varchar(255)') as RESERVED_MEMORY_ALLOCATION_EXT_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="OLEDB"]/@waits)[1]','varchar(255)') as OLEDB_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="WRITELOG"]/@waits)[1]','varchar(255)') as WRITELOG_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="PAGEIOLATCH_SH"]/@waits)[1]','varchar(255)') as PAGEIOLATCH_SH_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="ASYNC_NETWORK_IO"]/@waits)[1]','varchar(255)') as ASYNC_NETWORK_IO_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="IO_COMPLETION"]/@waits)[1]','varchar(255)') as IO_COMPLETION_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="PAGELATCH_EX"]/@waits)[1]','varchar(255)') as PAGELATCH_EX_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="PAGEIOLATCH_EX"]/@waits)[1]','varchar(255)') as PAGEIOLATCH_EX_ct</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byCount/wait[@waitType="CXPACKET"]/@waits)[1]','varchar(255)') as CXPACKET_ct</p><p><br /></p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="SQLTRACE_WAIT_ENTRIES"]/@waits)[1]','varchar(255)') as SQLTRACE_WAIT_ENTRIES_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="CLR_AUTO_EVENT"]/@waits)[1]','varchar(255)') as CLR_AUTO_EVENT_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION"]/@waits)[1]','varchar(255)') as HADR_FILESTREAM_IOMGR_IOCOMPLETION_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="TRACEWRITE"]/@waits)[1]','varchar(255)') as TRACEWRITE_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="ASYNC_NETWORK_IO"]/@waits)[1]','varchar(255)') as ASYNC_NETWORK_IO_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="WRITELOG"]/@waits)[1]','varchar(255)') as WRITELOG_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="PAGEIOLATCH_SH"]/@waits)[1]','varchar(255)') as PAGEIOLATCH_SH_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="OLEDB"]/@waits)[1]','varchar(255)') as OLEDB_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="PAGEIOLATCH_EX"]/@waits)[1]','varchar(255)') as PAGEIOLATCH_EX_ms</p><p>, T.C.value('(data/value/queryProcessing/topWaits/nonPreemptive/byDuration/wait[@waitType="LCK_M_SCH_S"]/@waits)[1]','varchar(255)') as LCK_M_SCH_S_ms</p><p><br /></p><p>/*</p><p> <byDuration></p><p> <wait waitType="SQLTRACE_WAIT_ENTRIES" waits="2559734" averageWaitTime="5898" maxWaitTime="304986" /></p><p> <wait waitType="CLR_AUTO_EVENT" waits="42" averageWaitTime="148452579" maxWaitTime="1161135755" /></p><p> <wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="6283026" averageWaitTime="500" maxWaitTime="38514" /></p><p> <wait waitType="TRACEWRITE" waits="1656275" averageWaitTime="1892" maxWaitTime="15678" /></p><p> <wait waitType="ASYNC_NETWORK_IO" waits="170646385" averageWaitTime="4" maxWaitTime="34341" /></p><p> <wait waitType="WRITELOG" waits="322871416" averageWaitTime="1" maxWaitTime="166927" /></p><p> <wait waitType="PAGEIOLATCH_SH" waits="204943649" averageWaitTime="1" maxWaitTime="157813" /></p><p> <wait waitType="OLEDB" waits="996781932" averageWaitTime="0" maxWaitTime="158157" /></p><p> <wait waitType="PAGEIOLATCH_EX" waits="32693084" averageWaitTime="1" maxWaitTime="121494" /></p><p> <wait waitType="LCK_M_SCH_S" waits="10554" averageWaitTime="4387" maxWaitTime="94983" /></p><p> </byDuration></p><p> </nonPreemptive></p><p> <preemptive></p><p> <byCount></p><p> <wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="1935312" averageWaitTime="0" maxWaitTime="206" /></p><p> <wait waitType="PREEMPTIVE_OS_CRYPTOPS" waits="1391671" averageWaitTime="0" maxWaitTime="143" /></p><p> <wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="912266" averageWaitTime="0" maxWaitTime="188" /></p><p> <wait waitType="PREEMPTIVE_OS_WRITEFILE" waits="817138" averageWaitTime="30" maxWaitTime="145310" /></p><p> <wait waitType="PREEMPTIVE_OS_GETPROCADDRESS" waits="637470" averageWaitTime="0" maxWaitTime="15" /></p><p> <wait waitType="PREEMPTIVE_XE_CALLBACKEXECUTE" waits="629446" averageWaitTime="0" maxWaitTime="9" /></p><p> <wait waitType="PREEMPTIVE_OS_AUTHORIZATIONOPS" waits="560759" averageWaitTime="0" maxWaitTime="59" /></p><p> <wait waitType="PREEMPTIVE_OS_CLOSEHANDLE" waits="494332" averageWaitTime="1" maxWaitTime="124284" /></p><p> <wait waitType="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT" waits="464831" averageWaitTime="2" maxWaitTime="129" /></p><p> <wait waitType="PREEMPTIVE_OS_REVERTTOSELF" waits="96931" averageWaitTime="0" maxWaitTime="36" /></p><p> </byCount></p><p> <byDuration></p><p> <wait waitType="PREEMPTIVE_OS_WRITEFILE" waits="817138" averageWaitTime="30" maxWaitTime="145310" /></p><p> <wait waitType="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT" waits="464831" averageWaitTime="2" maxWaitTime="129" /></p><p> <wait waitType="PREEMPTIVE_OS_CLOSEHANDLE" waits="494332" averageWaitTime="1" maxWaitTime="124284" /></p><p> <wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="1935312" averageWaitTime="0" maxWaitTime="206" /></p><p> <wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="912266" averageWaitTime="0" maxWaitTime="188" /></p><p> <wait waitType="PREEMPTIVE_OS_FILEOPS" waits="18032" averageWaitTime="16" maxWaitTime="3488" /></p><p> <wait waitType="PREEMPTIVE_OS_REPORTEVENT" waits="2032" averageWaitTime="135" maxWaitTime="102489" /></p><p> <wait waitType="PREEMPTIVE_OS_CRYPTOPS" waits="1391671" averageWaitTime="0" maxWaitTime="143" /></p><p> <wait waitType="PREEMPTIVE_OS_FLUSHFILEBUFFERS" waits="1924" averageWaitTime="60" maxWaitTime="3880" /></p><p> <wait waitType="PREEMPTIVE_OS_CREATEFILE" waits="3048" averageWaitTime="26" maxWaitTime="6397" /></p><p> </byDuration></p><p> </preemptive></p><p> </topWaits></p><p>*/</p><p><br /></p><p>FROM #SystemHealthSessionData a</p><p>CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)</p><p>--cross apply T.C.nodes('/event/data') as E(D)</p><p>where </p><p> T.C.query('.').value('(/event/@name)[1]', 'varchar(255)') in ('sp_server_diagnostics_component_result')</p><p>and C.value('(data/text)[1]','varchar(255)')= 'QUERY_PROCESSING'</p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-1289448076050466222020-10-08T18:37:00.004-05:002020-10-08T18:37:57.789-05:00UNION ALL returns "conversion... overflowed an int column"<p>I had a stored procedure throwing the following error:</p><p>The conversion of the varchar value '9999999999' overflowed an int column. </p><p><br /></p><p>There's two queries joined, so I split them up to see which one was throwing the error. </p><p>Neither. Of course. : - \</p><p>If you break the query apart, both halves work. </p><p><br />Okay, so let's figure this out:</p><p>exec sp_describe_first_result_set @tsql = N'myqueryhere'</p><p>exec sp_describe_first_result_set @tsql = N'myotherqueryhere'</p><p><br /></p><p>Oh, hey look, varchar in one, int in the other.</p><p><br /></p><p>Cause? My own stupidness from 7 years ago:</p><p>case when fielda is null or fielda = 0 then 0</p><p>else replace(replace(convert(varchar(14),fielda(,'.',''))</p><p>end</p><p><br /></p><p>On one set of data, there are fields that have values, specifically the 9999999999; the second set returns nothing. </p><p>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.</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-51155278977772130392020-06-09T10:22:00.001-05:002020-06-09T10:22:18.287-05:00[AWS] Athena aka Hive/Presto - renaming/mapping a JSON attribute named TIMESTAMP to be called TS(More Athena stuff coming, probably)<br />
<br />
Since I couldn't get Amazon's documentation example with <span style="background-color: #eaeef3; color: #3a3a3a; font-size: 0.8rem;"> ColumnToJsonKeyMappings</span> to work... (fortunately, the Hive documentation is better, and Athena uses Hive for DDL)<br />
<br />
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")<br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">{"event":"eventififer","globalid":"3fd6dce3-6650-4e3f-8d8d-a46d1baaca02","timeStamp":"2020-06-08T19:29:31.114Z"}</span><br />
<br />
In this case, "timestamp" is a reserved word, so you have to query it like:<br />
<span style="font-family: Courier New, Courier, monospace;">and "timestamp" >= '2020-06-08T18:30:55Z' </span><br />
<br />
So, in order to rename the field, you could either create a view on top of it, or rename it during the creation.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">CREATE EXTERNAL TABLE myawsdatacatalog.mytablename(</span><br />
<span style="font-family: Courier New, Courier, monospace;">event string<span style="white-space: pre;"> </span>,</span><br />
<span style="font-family: Courier New, Courier, monospace;">globalid string,</span><br />
<span style="font-family: Courier New, Courier, monospace;">ts string)</span><br />
<span style="font-family: Courier New, Courier, monospace;">partitioned by (dt string)</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'</span><br />
<span style="font-family: Courier New, Courier, monospace;">with serdeproperties ("mapping.ts"= "timestamp")</span><br />
<span style="font-family: Courier New, Courier, monospace;">LOCATION 's3://mybucket/my/folder/path/';</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
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. <br />
<br />
<span style="font-family: Courier New, Courier, monospace;">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]</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
If you need a workaround, I'd say to create it as string, then toss a view on top with: <br />
<span style="font-family: Courier New, Courier, monospace;">select from_iso8601_timestamp(ts) as ts</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-14286941782136167872020-03-12T21:13:00.001-05:002020-03-12T21:13:43.066-05:00[Baking] Olga's Prune CakeInteresting! 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)<div>
<a href="https://thepioneerwoman.com/cooking/make-this-cake-today-trust-me/">https://thepioneerwoman.com/cooking/make-this-cake-today-trust-me/</a></div>
<div>
<a href="https://www.solofoods.com/recipes/spicy-buttermilk-prune-cake">https://www.solofoods.com/recipes/spicy-buttermilk-prune-cake</a></div>
<div>
<br /><div>
<br /></div>
<div>
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). <div>
<br /></div>
<div>
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!</div>
<div>
<br /></div>
<div>
1.5 cups sugar</div>
<div>
1 cup salad oil (preference is Mazola corn oil, but Wesson will also work)</div>
<div>
1 tsp salt</div>
<div>
2 cups flour</div>
<div>
1 tsp soda</div>
<div>
1 tsp nutmeg</div>
<div>
1/2 cup buttermilk</div>
<div>
1 tsp cinnamon</div>
<div>
3 eggs (jumbo)</div>
<div>
1 cup prunes (we use 2 of the small jars of beech-nut)</div>
<div>
1/2 cup nuts (we use pecan, in pieces)</div>
<div>
1 tsp vanilla (preferred Mexican vanilla)</div>
<div>
<br /></div>
<div>
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.</div>
<div>
<br /></div>
<div>
Topping/icing/frosting/whatever: </div>
<div>
1 cup sugar</div>
<div>
1/2 tsp baking soda</div>
<div>
1/2 cup buttermilk</div>
<div>
1/2 cup butter or margarine (1 stick)</div>
<div>
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.</div>
<div>
<br /></div>
<div>
Poke a bunch of holes in cake, and, while this and the cake are still hot, pour this over the cake. </div>
<div>
<br /></div>
<div>
<img height="400" src="blob:https://www.blogger.com/0bc9f10f-7fa0-40e7-83b6-82e440442562" width="300" /></div>
<div>
<br /></div>
<div>
The cake is together...</div>
<div>
<br /></div>
<div>
<img height="300" src="blob:https://www.blogger.com/ce7dea7c-1374-4b49-ba85-2b353e6d3ba2" width="400" /></div>
<div>
<br /></div>
<div>
Making the foamy frosting...</div>
<div>
<br /></div>
<div>
<img height="300" src="blob:https://www.blogger.com/570e7720-724e-476e-9632-50d12e8e0905" width="400" /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
After pouring the icing over...</div>
<div>
<br /></div>
<div>
<img height="299" src="blob:https://www.blogger.com/6942c7d0-8d62-4750-957e-e6d07cf5311f" width="400" /></div>
<div>
<br /></div>
<div>
And after it's absorbed.</div>
<div>
<br /></div>
<div>
<img height="300" src="blob:https://www.blogger.com/9e16c8e2-54cb-4589-8413-39d5f82136f5" width="400" /></div>
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1434113132981005034.post-13731389220688885392020-02-06T16:35:00.000-06:002020-02-06T16:38:34.671-06:00[Powershell] Getting the full text out of a field in SQL when SSMS won't workWe 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.<br />
<br />
I wish I knew a better way to handle this, but this works fine.<br />
<br />
#This uses the wondrous invoke-sqlcmd <br />
. c:\powershell_scripts\invoke-sqlcmd2.ps1<br />
<br />
$query = @"<br />
SELECT source_sql FROM etl_me.dbo.data_flow_me_config where id = 1<br />
"@<br />
<br />
$fullquery = invoke-sqlcmd2 -ServerInstance mem-pr-cde-01 -Query $query<br />
<br />
<br />
First, to get the data out, I tried: <br />
$fullquery.source_sql<br />
<br />
Then I realized I could do this, if I only had one column:<br />
$fullquery[0]<br />
<br />
But that can fall down. This one is slightly better. Add more columns if you need them - if they're nonexistent, it still works.<br />
<br />
$fullquery|%{$_[0] #, $_[1], $_[2]<br />
"-------------------"}Unknownnoreply@blogger.com1