A Fine Slice Of SQL Server
Jess Pomfret takes us through some SPN pains:
But instead of getting a quick answer to my question, I just got the following error:
WARNING: [15:19:49][Get-DbaDatabase] Error occurred while establishing connection to dscsvr1 | The target principal name is incorrect. Cannot generate SSPI context.
Just reading the article brought back some bad troubleshooting memories for me… But as usual, I’m impressed that dbatools has a cmdlet or two to help with that troubleshooting.
Comments closedErik Darling notes that memory-optimized table variables can be useful in specific circumstances:
First, yes, they do help relieve tempdb contention if you have code that executes under both high concurrency and frequency. And by high, I mean REALLY HIGH.
Like, Snoop Dogg high.
Because you can’t get rid of in memory stuff, I’m creating a separate database to test in.
Been there. When tempdb object creation causes massive contention, this certainly alleviates the stress.
As Erik notes, there are some tradeoffs to this, meaning that you have a real decision to make rather than simply using memory-optimized user-defined table types as a starting point.
Comments closedRayis Imayev wants to pass a JSON array from one Azure Data Factory pipeline to another:
This next post came out of an error message during my attempt to pass a hard-coded array value between pipelines. Strangely, this use-case worked well in the pipeline that was already deployed in ADF, however, I was getting an error message while trying to test and execute this very same pipeline in a Debug mode.
Click through for the explanation.
Comments closedSoheil Bakhshi has achieved mass production:
I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.
Click through to see how to build an expression which iterates over all columns in all tables.
Comments closedDavid Selby walks us through non-trivial join scenarios in R:
Most joins are equi-joins, matching rows according to two columns having exactly equal values. These are easy to perfom in R using the base
merge()function, the variousjoin()functions in dplyr and theX[i]syntax of data.table.But sometimes we need non-equi joins or θ-joins, where the matching condition is an interval or a set of inequalities. Other situations call for a rolling join, used to link records according to their proximity in a time sequence.
How do you perform non-equi joins and rolling joins in R?
Click through for the answer using dplyr, sqldf, and data.table. H/T R-bloggers
Comments closedSynapse provides an exciting feature which allows you to sync Spark database objects to Serverless pools and to query these objects without the Spark pool being active or running. Synapse workspaces are accessed exclusively through an Azure AD Account and objects are created within this context in the Spark pool. In some scenarios I would like to share the data which I’ve created in my Spark database with other users for reporting or analysis purposes. This is possible with Serverless and in this article I will show you how to complete the required steps from creation of the object to successful execution.
Click through for the demonstration.
Comments closedSlava Murygin has two questions and two answers:
This post is just answering two simple questions:
1. Can Quest Software’s Spotlight successfully monitor SQL Server Analysis Server?
2. If it can, what SSAS parameters, databases’ and cubes’ details it monitors and provides information about?
First, it’s good to see Slava back in the saddle again. Second, click through for those answers. Slava also promises to check out some other SSAS monitoring tools, so stay tuned.
Comments closedPhil Seamark has a dashboard which will help understand Power BI dataset refresh times:
Have you ever wondered why a Power BI dataset refresh was taking so long? And more specifically, how much time did the refresh spend on various sub-tasks that aren’t that visible to you via the web-portal?
This article shares a technique you can use to capture events fired during a Power BI refresh and use the results in a Power BI report visualise the results. Have to love the idea of using Power BI to optimise and improve Power BI.
Click through to get the Power BI report and get step-by-step instructions on how to use it.
Comments closedHugo Kornelis digs into the sequence operator:
The Sequence operator reads all rows from all its inputs, in order, and without modification. It returns all rows it reads from its last input only.
This operator is typically used in execution plans where some branches are needed to do preparation work (e.g. to invoke a Table-valued Function operator) and then other branches produce the final output, or multiple branches need to do work in sequence (such as in wide data modification plans). The operators on the non-last inputs often do not produce any output at all, but even if they do the rows returned are discarded. Only rows returned from the last input are returned to the parent of the Sequence operator.
Note that this is quite difference from sequences of numbers in SQL Server, which act similarly to identity integers.
Comments closedEitan Blumin won’t be getting off this bus:
This is one of those things that I would have never expected to adopt, and yet here I am! Not just loving “infinite” loops, but also confident enough that I can convince you to feel the same!
It may sound very weird to most people at first, since it’s such an unorthodox way of using WHILE loops, and probably goes against what you consider to be coding best practices and standards.
I use this a fair amount as well for the reasons Eitan describes. The one thing to keep an eye out for, though, is that you are actually progressing toward an end goal. In other words, if you’re using WHILE(TRUE) to loop through batches of rows in a table by, say, minimum ID, make sure you increment that minimum ID or else you’ll be spinning your wheels for a while and not realize it. Not that I’ve ever done that before, of course…
Comments closed