A Fine Slice Of SQL Server
Chris Adkin continues a series:
In my last post I outlined a number of architectural options for solutions that could be implemented in light of Microsoft retiring SQL Server 2019 Big Data Clusters, one of which was data pipelines that leverage Python and Boto 3. Before diving into these things in greater detail, lets take a recap on what S3 is.
Click through for a simple data pipeline example.
Comments closedEd Hansberry makes a list and checks it twice:
Lists in Power Query are something many people know nothing about. Power Query uses them all the time even though you may not realize it, so if you add some List knowledge to your quiver, you’ll be able to kick your Power Query skills up a notch.
In my work, I often see the need for counting words, especially today with so much online data. Perhaps you want to ensure your Amazon product listings have a maximum number of words in the descriptions or you want to count the words in a podcast. The method I’m going to show you will count anything in your data, so you can apply this pattern to any of your datasets.
Also known as map and reduce (but not quite MapReduce).
Comments closedRonen Ariely provides a solution:
However, the answer in this specific case was not optimal. Unfortunately in most cases in the forums, most people that come to ask a question, do not care about learning but only about the solution, even so in my opinion the road is just as important as the end point. The road (the learning) is what will help the person to solve the next issue and not just the current one – teach a man to fish and you feed him for a lifetime…
The op marked the answer he got and I assume that from his point of view the discussion ended, but I wanted to present the solution which might be tens time better in some cases, which is what I will do in this post…. so let’s start
I won’t dive too deeply into Ronen’s philosophical argument—you can definitely read about that in the post. I will say I am sympathetic to the argument at the margin and believe it’s worthwhile to know the superior solution.
Comments closedNathan Schoenack troubleshoots an error:
When trying to query an external table created for a generic ODBC external data source, the following error can be observed:
Message 7320, level 16, state 110, line 87
Unable to execute query “Remote Query” against OLE DB provider “MSOLEDBSQL” on link server “(null)”. 105082; Generic ODBC error: OdbcBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, ‘Error calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)column->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info: Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] The driver does not support this function. | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 239, active connections: 9’, Connection String: Dsn={DSN Name};Driver={Driver Name};uid=root;server=xxxxx;port=xxxx;database=xxxx.
Read on for a viable workaround.
Comments closedEitan Blumin dives into Powershell:
As part of my journey to get better at Powershell, I gave myself a task: Create a project utilizing Powershell at its core, to solve a complicated problem as elegantly as possible.
A complicated problem indeed presented itself: How to remotely control multiple computers without having to log into them? Make them perform any task that could possibly be needed? Control them from one central location? And make the solution as easily scalable as possible?
Read on to see what Eitan has learned and applied.
Comments closedHiram Fleitas looks at visual formatting in Azure Data Explorer:
Intro
Start with a free database at aka.ms/adx.free & run this demo query.
let mytable = datatable(key:string, number:int)['one', 1,'two', 2];mytable
Once you have that query, read on to see how you can visualize and format it.
Comments closedGilbert Quevauvilliers continues a series on determining who has access to what reports in Power BI:
This is the second part in my blog post series showing you how I created the Power BI Reports list.
In this blog post I am going to show you how I used PowerShell to get all the information of the App Names, reports and users that have permissions in the different Apps.
Users can get access to Power BI reports directly via the Share method, as well as via an App. I did this to ensure that I did not miss any reports that a user did have access to, and I could not show it!
Click through for the script, as well as an an explanation of how it all works.
Comments closedI still get a lot of questions from various Power BI developers about table storage modes and how table storage modes affect an entire model’s type. Here is a post to summarize all table storage modes / model types.
The following table storage options apply when creating a Power BI model.
There’s a brief summary in the tables, as well as additional notes below them.
Comments closedDimitri Furman shows off a DMV:
As part of our efforts to improve database engine supportability, we have added a new dynamic management view (DMV), sys.dm_os_out_of_memory_events. It is now available in Azure SQL Database and Azure SQL Managed Instance and will become available in a future version of SQL Server. If an out-of-memory (OOM) event occurs in the database engine, this view will provide details to help you troubleshoot the problem, including the OOM cause, memory consumption by the database engine components at the time of event, potential memory leaks (if any), and other relevant information.
Read on to learn more about it, as well as a corresponding Extended Event.
Comments closedHamish Watson does a document dump:
So what did we do here?
It searched our stored security events in the SecurityEvent table for all Accounts that had a successful login in the last 3 hours and we chose to display only the Account and number of log off events per Account in numerical order with the highest at the top.
So far I’ve introduced some new operators and things – but what is a really quick way to learn KQL?
Start with this post and just keep navigating forward. Hamish has ten posts in total.
Comments closed