SQLServerCentral.com Community Update for December 8, 2021

Problems displaying this newsletter? View online.

Featured Contents

Stairway to SQL PowerShell Level 10: Getting data in and out of SQL Server using SQL Server PowerShell
How a monitoring tool benefits the entire organization
Create a SQL Server PolyBase Scale-out Group Using Amazon Web Services
From the SQL Server Central Blogs - Building a pacemaker cluster to deploy a SQL Server availability group in Azure
From the SQL Server Central Blogs - Hiding Email with a Dynamic Data Masking Function–#SQLNewBlogger

Question of the Day

Ordering String_Split()

The Voice of the DBA

Daily Coping Tip

Leave a positive message for someone to find.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Worse Before Better

There was a thread on Twitter on career longevity and fixing issues inside an organization. IT notes that often there is a problem and someone is assigned or hired to fix them. Often when someone starts to dig in, they can fix some things and reduce the pain. That's good, as things get better.

However, as you gain more knowledge of the issue, sometimes you realize there are more fundamental problems. If you try to fix them, you might make things worse. This could result in a repeat of the process of worsening and bettering of the problems if new people are constantly assigned when things get worse. If you stick with the person that is working on underlying problems, things get better over the long term.

I think that's often been my experience. Short-term thinking, just trying to patch an issue, doesn't necessarily make things better over time. I certainly saw this with supporting Windows systems in the 90s. We often rebooted machines first, which "fixed" the problem. At least for then. Often the issue would repeat, but customers could reboot their own machines.

Is that the right approach? It could be, if a long-term fix requires lots of resources, perhaps a regular set of reboots from people is a better tradeoff. In a more complex system, usually, we find someone fixing a short-term software issue with code that is less reliable, or even less upgradable in the future. How many times do we see code that has a comment to "not touch this" or a note that the last developer didn't understand this, but it worked. We end up with spaghetti code that no one maintains, doesn't want to touch, and it inhibits our ability to grown and react to new requirements.

Good fundamentals make a difference in how we build and maintain systems. This is especially true in databases, as we have to live with their design for a long time. We need to be agile, but we should apply good database modeling and integrity practices as we do so. Making quick decisions on how to add something to a schema is fine, but do so in line with best practices and an eye on the future.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Stairway to SQL PowerShell Level 10: Getting data in and out of SQL Server using SQL Server PowerShell

Ben Miller from SQLServerCentral.com

In this level we will look at ways of getting data into and out of SQL Server. We will specifically look at the Database object and a cmdlet Invoke-Sqlcmd or Invoke-SqlCmd2. To get data in, we are going to leverage a PowerShell script that uses SMO to put data into a predefined table in SQL Server.

How a monitoring tool benefits the entire organization

Additional Articles from Redgate

Redgate estimate that the savings realized with a monitoring tool could be well over $85,000 / year*. Does your organization understand how a monitoring tool can impact almost every department?

Create a SQL Server PolyBase Scale-out Group Using Amazon Web Services

Additional Articles from MSSQLTips.com

In this article we look at how to create a scale-out SQL Server PolyBase solution on AWS

From the SQL Server Central Blogs - Building a pacemaker cluster to deploy a SQL Server availability group in Azure

dbafromthecold@gmail.com from The DBA Who Came In From The Cold

There are new Ubuntu Pro 20.04 images available in the Azure marketplace with SQL Server 2019 pre-installed so I thought I’d run through how to create a three node...

From the SQL Server Central Blogs - Hiding Email with a Dynamic Data Masking Function–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Dynamic Data Masking is a feature that provides...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Ordering String_Split()
In Azure SQL Database (as of Nov 2021), there is a new parameter in string_split(). The third parameter adds a new column to the results with a name of ordinal. If I want to guarantee that I get the results in an order, what do I add to this query: SELECT * FROM STRING_SPLIT('a,b,c,e,d', ',', 1) AS ss
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)

Database Snapshot Sparse Files

I have created a database snapshot and want to check if there is a sparse file. Where should I look for this information?

Answer: Check sys.database_files for the is_Sparse column

Explanation: The sys.database_files DMV has an is_sparse column. If this value is 1, then this is a sparse file. Ref: View the Size of the Sparse File of a Database Snapshot - https://docs.microsoft.com/en-us/sql/relational-databases/databases/view-the-size-of-the-sparse-file-of-a-database-snapshot-transact-sql?view=sql-server-ver15

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 2017 - Administration

Application query time out - I have an application that is attempting to perform a function (modifying records, so I'm assuming an update query) under a certain user's session and it is timing out. Is there a way to easily monitor what is happening in the database to examine what the issue might be, something to get the exact query […]

SQL Server 2016 - Administration

SQL Server and CloudStrike - Has anyone had issues with CloudStrike Anti-Virus on their SQL Servers? I've had a client install CloudStrike, and within 30 minutes the SQL Server became unresponsive and stopped accepting new user connections. The server has been running without issues for about 2 years. We had to do a forced Service Shut down from an existing […]

SQL Server 2016 - Development and T-SQL

best way to extract name and number - I have data in a field called user_name that consistently follows this format: (t) Joseph Kelly -14784 (tb) Shenna Brown -15488 (tb) Paul Ledford -15358 (b) Quadra Tomson -15316 (t) Tracy Edwards -15218 I can get the number out easily with "SUBSTRING([user_name], CHARINDEX('-',[user_name])+1, 25) as EmployeeID", e.g.: 14784, but I'm having trouble using the same […]

SQL 2012 - General

how to identify BottleNeck by using the data of sys.dm_os_performance_counters - There are many many data to show the SQL Server current performance, May I know how to use these counters to observe the SQL Server's performance? that is to say, which parameters show the IO bottleNeck and Disk bottleNeck?RAM bottleNeck etc ? it means the cntr_value exceed which value there will be a bottleneck, thanks!

how to solve error Conversion failed when converting the nvarchar value '24VAC/D - I work on SQL server 2014 I get error when run statement below error say Conversion failed when converting the nvarchar value '24VAC/DC' to data type int. I got error when execut dynamic sql EXEC (@SQL) so how to solve this error please data sample IF OBJECT_ID('dbo.TAllfeatures') IS NOT NULL DROP TABLE dbo.TAllfeatures IF OBJECT_ID('dbo.TCondition') […]

SQL Server 2019 - Development

SQL Query Help on Time based query - I have a following table (Contains script to generate the sample table with data): CREATE TABLE [dbo].[Test]( [Schedule_Start_Date] [varchar](8) NULL, [UserID] [int] NULL, [Code] [varchar](10) NULL, [Start_Time] [time](7) NULL, [Duration_Min] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1562, N'Shift', CAST(N'10:00:00' AS Time), 720) GO INSERT [dbo].[Test] ([Schedule_Start_Date], […]

Query help - create table assessments ( id integer not null, experience integer not null, sql integer, algo integer, bug_fixing integer, unique(id) ); INSERT INTO assessments VALUES(1, 3, 100, NULL, 50) INSERT INTO assessments VALUES(2, 5,NULL,100,100) INSERT INTO assessments VALUES(3,1,100,100,100) INSERT INTO assessments VALUES(4, 5,100,50,NULL) INSERT INTO assessments VALUES(5,5,100,100,100) I need the query to return xp | max […]

Query Performance Tuning - Hello All, I have a temp table inside my procedure and its taking lot of time. Could anyone suggest how to optimize this.. Below is my piece of code. declare @User table ( UserName varchar(200), InstalledDate varchar(max) ) insert into @User select main.UserName, left(main.installs,len(main.installs)-1) as "Installs" From ( select distinct ins2.UserName, ( select convert(varchar(200),ins.FirstSeenDate)+', ' […]

Data Masking - Hello All, I'm working on a project that the manager would like to mask some sensitive data. I know that there is a dynamic data masking function currently on the sql server 2019, but as far as I researched looks like it need to be done on a table column right? On my case the […]

SQL Server 2008 - General

Table Date field format - I created a date field [Datte] in SQL v2008. I then link with MS Access. No problem. Done this many times. However, this time, when I open the table in MS Access, the date displays as: 2021-03-15 (yyyy-mm-dd) All my other tables display like I want it: 03/15/2021 (mm/dd/yyyy) The field attribute is date, not […]

Reporting Services

ReportingService2010 soap call to set report credentials - I have written a routine to speed up the upload and download of reports from our reporting server. The problem is that the reports do not retain their credentials when you download them. So I've tried to update them after uploading the report, but get an error when the report is run. An error has […]

Get Full Name and Last Name in Report - Hello, i have tried multiple expressions from websites and couldn't get the real answer. What is the full expression that gives me the First and Last Name in SSRS expression: For ex: John Andrew Brown Hill = John Hill.

Help with Column Visibility Expressions - I have a parameter in my report labeled as PaySource. PaySource has 3 values associated to it, a Value of 0 = "Total", 1 = "Patient" and 2 = "Insurance". In my report, I have 3 columns - Patient, Insurance, and Total. I need help with my Column Visibility Expression on each of these columns. […]

SSRS 2012

Error Report - Hi All When i access to report server and i try to open report JV directly i get error, see att file, and when i acces with Modify in generator, i can run report

General

How do I use Workspaces in Azure Data Studio? - I've been working with Azure Data Studio (ADS) for maybe two years, but I've only yesterday learned about something called "workspace" in ADS. I ran into problems when trying to commit SQL code to a Git repo. ADS showed me two unrelated Git repos and I don't think that ADS knew which repo to put […]

This email has been sent to newsletter@newslettercollector.com. 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.