SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Install SQL Server using Powershell Desired State Configuration (DSC)

VishnuGupthanSQLPowershellDBA from SQLServerCentral

This article shows the basics of using PowerShell Desired State Configuration to install a SQL Server.

How Snowflake Creates and Detects Deadlocks

Additional Articles from MSSQLTips.com

In this article, we look at how Snowflake handles deadlocks and the process of creating a deadlock and detecting a deadlock.

Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database

Additional Articles from Redgate

When you are integrating Flyway into an existing SQL Server SSDT development, you don't necessarily have to change everything at once. The development team might continue to use the SSDT tools, but Flyway will soon take over the deployments. This means that any automated processes will need to be able to handle both DACPACs and Flyway migration scripts with equal grace. In this article, I'll demonstrate how to automatically extract a versioned DACPAC from each new Flyway version of a database.

From the SQL Server Central Blogs - Query Store – Useful Tool for DBAs

Rohit Garg from MSSQLFUN

SQL Server is a robust and flexible database management system that offers a variety of features and capabilities to help organizations effectively manage their data. One of the key...

From the SQL Server Central Blogs - Pulling Azure Analysis Services logs from Azure Log Analytics into PowerBI using Kusto Query Language (KQL)

sqlrunner from SQLRunner

We have very large Analysis Services(SSAS) cubes with billions of records and hundreds of users so we need to be able to monitor the performance of queries. 95% of...

Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen

Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen

Greg Larsen from SQLServerCentral

Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Secure Enclave
When using Always Encrypted in SQL Server 2019, where is the secure enclave located?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Getting the Current Context in go-sqlcmd

The new open-source sqlcmd, written in go-lang, uses contexts to run queries. You can run this to get the contexts that are set up on your system:

 sqlcmd config get-contexts

However, this doesn't tell you which context is current, as you can see here:

sqlcmd context listing

How can I determine which context is active?

Answer: use "sqlcmd config current-context"

Explanation: Current-context is the sub command that returns the active context. You could have multiple contexts connected to the same server, or you could have multiple servers that are similar. Ref: go-sqlcmd - https://learn.microsoft.com/en-us/sql/tools/sqlcmd/go-sqlcmd-utility?view=sql-server-ver16

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 - Development and T-SQL

import from CSV - special chars - My data csv file sometimes comes in with special characters and French accents too (è ë à ç ) And then this special apostrophe in surnames e.g. O’Brien. They go in the database as this: Oâ€™brien I am using SSIS for import. Where do I need to make changes to allow these special chars? The tables have […]

SQL Server 2019 - Administration

Upgrade to SQL2019 from 2012 and everything is slow - what to look for? - We have recently started a process of planning the upgrade of our four-node SQL2012 availability group to SQL2019. The main database is pretty large (a few TB) and we've spent years tuning the SQL to get the performance to where it needs to be such that huge complex procedures absolutely fly on SQL2012. We've had […]

DBCC CHECKDB error 2576 allocation errors - Getting the following after the execution of the DBCC CHECKDB: Msg 2576, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:17) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617208115200 (type Unknown), but it […]

AD permissions updates CVE-2021-42291 and AO Clusters/GL - I have an SQLSERVER Always on Environment. In AD it's logging: The directory service detected an LDAP modify request for the folling object that normally would have been blocked for the following security reasons. The client included an nTSecurityDescriptor attribute in the add request but did not have explicit permissions to write one or more […]

Database snapshot, TDE, and sys.databases - Something I was looking at on a dev server where I was taking some snapshots and where we have TDE enabled and operational on the underlying database. Querying sys.databases shows the is_encrypted field as zero? Reading through the TDE documentation there is no exception that snapshots are NOT encrypted that I could find, but now […]

SQL Server 2019 - Development

Query to Insert Data Based on CustomerID from one table to another - Hello All, Below is the my existing table structure from which I need to insert records to a new table I need a query to insert records into a new table for each customer for same date. For example considering above data structure query should insert records into a new table as below based on […]

Encryption insert driving me mad - Hi all, I'm wondering if someone can help me with this, I have a table (very simple) CREATE TABLE [api].[Employee]( [EmployeeID] [int] NULL, [CreatedDate] [datetime] NULL, [Title] [varchar](10) NULL ) ON [PRIMARY] GO I've then gone and encrypted the Title column in SSMS using Randomized encryption type with a key in our Azure Key vault, […]

Most performant way to join a DATE to an INT - Hello and thanks in advance if you can help. I guess its somewhat acceptable in dimensional modeling to use the INT data type when creating a date dimension surrogate key. I can't say I agree with this but here we go. I need to join tables on DATE and INT columns. What is the most […]

Cumulative Sum by Groups - HEY, I HAVE TWO TABLES: CUSTOMERS AND USERS PER CUSTOMER I NEED TO CREATE TWO CUSTOMERS GROUPS: DVIR & RON - 'CRNTER GROUP', AND DANNY & DAVID 'OTHER GROUP'. AFTER THAT I NEED TO SUM THE NUMBER OF USERS FOR EACH GROUP. I STARED BY USING UNION ALL AND PARTITION BY: WITH "CUST-CTE" AS ( […]

Trigger Email After the record is inserted/deleted/updates from a table - Hi, I have following requirement. There is Sql server DB table OrderDetails. It has date, UserName , Details, OrderID columns. Case 1: I want to send email alert whenever any new record is inserted then email should be sent with below details. The new records date and time of insertion, UserName who inserted the record, […]

Triggers applied to primary and secondary tables causing rollback on primary - Apologies for the title, I'll explain more clearly. I have two tables and each has a trigger applied. The first table trigger is a simple audit style (AFTER INSERT/UPDATE) trigger that takes a copy of the modified record and inserts it into a Log Table. On the Log Table, I'm using another (AFTER INSERT/UPDATE) trigger […]

Calculate a sum based on time periods in CASE statement - Hello everyone, I have a situation where I am trying to calculate SUM(History Quantity) based on MIN Posted Date + specific number of weeks. For example, how many quantities were sold within the start date of '2022-05-22' + 8 weeks The code that I have now, but need to understand how to make CASE statement work because […]

SSRS 2016

Unable to connect SSRS - Hi, We are using SSRS 2016 version, it's running good and suddenly it's throwing exception as below when we are trying to generate reports, after restarting the SSRS again it's working good. 2023-05-18 22:07:42.9021|INFO|1|library|Dump on: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException Microsoft.ReportingServices.Modeling.InternalModelingException Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException 2023-05-18 22:07:42.9021|INFO|1|library|Do not dump on: Microsoft.PowerBI.ReportServer.WebApi.Catalog.CatalogAccessExceptionSystem.Threading.ThreadAbortException System.Web.UI.ViewStateExceptionSystem.OutOfMemoryException System.Web.HttpExceptionSystem.IO.IOExceptionSystem.IO.FileLoadException Microsoft.SharePoint.SPException Microsoft.ReportingServices.WmiProvider.WMIProviderException System.AppDomainUnloadedException 2023-05-18 22:07:42.9021|INFO|1|library|Minidump location: C:\windows\TEMP\ […]

Design Ideas and Questions

Database Design Help - Hello, I am looking for some input on a database I am attempting to design for a video game. I have most of the design basically done, but I am having trouble finishing this last part. The basis of the database is around the entities "item", "fluid", "node", and "building". The schema related to these […]

SQL Server 2022 - Development

Determining Time Worked Between Time Pairs During Certain Hours of Higher Pay - We have nurses pulling night shifts. They get bonus pay (more $$$ per hour) during the hours of 10pm and 5am. They check in and we capture their check-in time and check out time. How can I calculate the amount of time the worked during the overtime pay hours? I know SQL pretty OK - […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.