A Fine Slice Of SQL Server
Shabnam Watson troubleshoots an issue:
I was trying to trace a dataset I had published to Power BI service using SQL Server Profiler and I was getting this error:
Either the trace with the ID of ‘MicrosoftProfilerTrace1667261566’ does not exist in the server with the ID of ‘autopremiumhostnorthcentralus001-081’, or the user does not have permissions to access the object.
Read on for the solution.
Comments closedTeo Lachev builds on a prior post:
This blog builds upon my previous “Resolving Tabular Conversion Errors” and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2000, 133) Failed to resolve name ‘SYNTAXERROR’. It is not a valid table, variable, or function name.” All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use Tabular), so if we can get the script, we might be able to correlate the error.
Read on to see how you can capture that script and get the error. The technique is definitely not intuitive.
Comments closedTeo Lachev goes error-hunting:
A scheduled SSIS job that executes a massive DAX query to an on-prem Tabular server (Power BI can also generate this error) one day decided to throw an error “Source: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2020, 98) Calculation error in measure ‘Account Snapshot'[Average utilisation % of all CR active current accounts last 3 months]: The result of a conversion or arithmetic operation is either too large or too small.” At least we know the offending measure, but which row is causing the error? The query requests some 300+ measures for 120 million customers, so I thought someone might find the troubleshooting technique useful. Let’s ignore what the measure does for now except mentioning that it performs a division of two other measures.
Click through for the technique.
Comments closedMurder Forrest McDaniel Wrote:
“A duplicate despite a DISTINCT bodyguard…” one mutters. “This isn’t the normal level of foul play.”
Mods arrive to cordon off the scene. Twelve different butlers have already been accused, but each has a solid alibi, even MAXDOP.
Generic Protagonist paces back and forth. They’re waiting for the forensics report. Finally it arrives.
Of course, those forensics people don’t know what they’re doing, so you’d better click through and find the real killer.
Comments closedErik Darling has some thoughts on batching data modifications:
The whole point is to go a little bit easier on your server:
– Smaller transactions sent to the log file
– Shorter locking durations
– Less build up of the version store (optimistic isolation levels, triggers, etc.)
But there are thing that make batching impossible, or downright tricky.
Erik provides more details after the jump. This is all “stuff you should think about” rather than “reasons why you shouldn’t batch.”
Comments closedRecently, I attempted to update the Networking for an Azure SQL Server I use when delivering training. Specifically, I was attempting to set “Public network access” to “Selected networks,” add my client IP, and check the “Allow Azure services and resources to access this server” checkbox – found on the Networking blade for my new Azure server:
Fortunately, this was a case of “read the actual error message, understand the actual error.” It doesn’t always work out that way but it’s nice when it does.
Comments closedKenneth Fisher performs some diagnostics:
Dev: We are getting an error: SELECT permission was denied on the object MyObject.
Me: Ok, what instance, database, and account is getting the error?
Dev: The instance is InstanceA and we think the database is DatabaseA but we have no idea what the service account is.Now, watch this.
Watch and learn.
Comments closedPaloma Garcia Martin troubleshoots an error:
When you try to create a new database (*) using Azure Portal using non supported characters, you will see an error indicating characters that you cannot use on the database name.
But if you use SSMS tool, it doesn’t include these characters cheeking and it will not avoid you to use these non-supported characters on the database name.
Click through for an example of this error in action.
Comments closedMeagan Longoria reminds us that case sensitivity was a huge mistake:
Here’s the scenario: You are using Python, perhaps in Azure Databricks, to manipulate data before inserting it into a SQL Database. Your source data is a flattened data extract and you need to create a unique list of values for an entity found in the data. For example, you have a dataset containing sales for the last month and you want a list of the unique products that have been sold. Then you insert the unique product values into a SQL table with a unique constraint, but you encounter issues on the insert related to unique values.
Click through for an example and how to extricate yourself from this scenario. Python certainly is not the only language to do this, so it’s good to know even if you don’t plan on using or supporting Python.
Comments closedHolger Linke troubleshoots some problems:
The tempdb system database is a global resource available to users who are connected to Azure SQL Database or any instance of SQL Server. It holds temporary user objects that are explicitly created by a user or application, and internal objects that are created by the SQL Server database engine itself. The most common tempdb issue is running out of space, either regarding tempdb’s overall size quota or the transaction log.
The available tempdb space in Azure SQL Database depends on two factors: the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database. These are also the main factors to control if you are running out of tempdb space.
Click through for several error cases and how we can resolve them.
Comments closed