Press "Enter" to skip to content

Day: August 16, 2021

Fun with GO and Preventing SQL Batches from Parsing

Solomon Rutzky has an apropos post for Friday the 13th:

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.

Comments closed

Migrating a Replicated Database without Reinitializing

Jonathan Kehayias provides some tips on migrating (transactional) replicated databases when the cost of reinitializing is just too high:

The first part of building any plan is to know what specifically is being migrated or upgraded. This is important because the steps will be slightly different if you are only migrating or upgrading one part of the environment vs. the entire environment. It is also important to know where the distributor is for the configuration and whether or not the distribution database is going to be affected by the changes being made. In most cases it is a full environment migration to newer hardware or upgrade to newer release of SQL Server and the steps below will work. This is not the only way of migrating/upgrading large replicated databases but this is the one I have used for over 15 years at the date this post was written and it has been flawless.

Read on for more tips and a step-by-step process. With merge replication, of course, things are 40% more difficult.

Comments closed

The Cost of Measures in Power BI Live Connection Reports

Chris Webb explains the cost side of the ledger when it comes to measure creation:

You probably know that it’s a best practice to build your Power BI datasets in a separate .pbix file from your reports – among other things it means that different people can develop the dataset and reports. You may also know that if you are building a report in Power BI Desktop with a Live connection to a published dataset or Azure Analysis Services you can define your own measures inside the report. While this is very convenient, if you create too many measures there’s a price to pay in terms of query performance.

Click through for a demonstration of this.

Comments closed

Using the DAC from SSMS

Chad Callihan shows how you can configure the dedicated administrator connection and connect to it via SSMS in a time of need:

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.

Read on to learn more.

Comments closed

Reviewing Azure Purview Data Catalog Features

Angela Henry continues a series on Azure Purview:

The Data Catalog portion of Purview is where most people will spend their time. It provides the information about your organizations data assets in a searchable format. Depending on which level of Data Catalog you choose; you can also access a business glossary, lineage visualization, catalog insights, and sensitive data identification insights. This article will focus on the three different levels available within Data Catalog and offers scenarios demonstrating when you would use each offering.

Read on for the three tiers, all of which are currently free but that won’t stay the case.

Comments closed