SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

File Backups and Offline Files

The Voice of the DBA

Daily Coping Tip

Let go of being busy. Allow yourself to take some breaks today

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.

Solving Real Problems

Today we have a guest editorial as Steve is on holiday.

I spend a lot of time working out how best to implement data management, database development, deployment automation and all the rest. It’s my job to figure out the most efficient ways to do get these things done. As part of that, I can tell you that there are superior methods. For example, isolated development, where each developer can make, or break, a database all on their own, is a better way to work. There’s tons of research to back me up on this assertion. I can show you tests, validations, all sorts of evidence on my side, to prove this statement beyond the shadow of a doubt.

Then reality steps in.

You may believe me, but, you’re dealing with 3, 5, 20 years of technical choices, that means you have linked servers, cross database queries, and all sorts of other dependencies in the system. Isolating an environment on a developer laptop, while a lovely thought, just isn’t going to happen. Not without a ton of other work up front. So, your choice is, a shared environment, where lots of developers are touching the same stuff, and automate from there, or no automation at all.

My answer is simple. Deal with the reality in your environment. Yes, there are better ways to get things done. Extended Events, just for example, is wildly superior to Profiler/Trace. Yet, you’re working in SQL Server 2008R2. To solve real problems, you must stick with the tools and methods that are going to work in your situation. This is the real world.

Yes, I would advocate that you start making incremental changes to move towards best practices, whatever they may be, from automating deployments to monitoring your queries. This is why myself and others spend the time and effort to find the best ways to get things done. However, we all, especially you, have to be aware that there is best and there is necessary. While X may be a “best practice”, you’re doing Y because you have to.

So, solve your real problems using real solutions. Just aim towards moving in the direction of better solutions. That’s all anyone should ask of you.

Grant Fritchey

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

Featured Contents

Conditional Formatting with SSRS

Thom A from SQLServerCentral

An introduction on how to implement condition formatting of cells, such as the font and background colour or font weight, within SSRS using SSRS Expressions.

Heaps in SQL Server: Part 3 Nonclustered Indexes

Additional Articles from SimpleTalk

Uwe Ricken continues his series on heaps. This time he demonstrates a common scenario where the query against a heap is faster than a clustered index.

From the SQL Server Central Blogs - Data Lakehouse and Synapse

James Serra from James Serra's Blog

I am starting to see this relatively new phrase, “Data Lakehouse”, being used in the data platform world. It’s the combination of “Data Lake” and “Data Warehouse”. In this...

From the SQL Server Central Blogs - FILESTREAM v FileTable –#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. I was really interested in the FILESTREAM technology...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	File Backups and Offline Files
I have a database on SQL Server 2019 that has two filegroups. In the Archive filegroup, I have three files: FGArchive1, FGArchive2, and FGArchive3. I take Archive 1 offline with this code: ALTER DATABASE FGTest MODIFY FILE (NAME = N'FGArchive1', OFFLINE) GO What happens if I try to run a backup of this file with this code? BACKUP DATABASE [FGTest] FILE = N'FGArchive1' TO DISK = N'D:\SQLBackup\FGARchive1.bak' WITH NOFORMAT, INIT, NAME = N'FGTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by BTylerWhite)

Updating Python Dictionary Values

I have the following nested dictionary in Python 3:

people = {1: {'last_name': 'Dactyl',
              'first_name': 'Teri',
              'age': 27
              },
          2: {'last_name': 'Zerd',
              'first_name': 'Liz',
              'age': 22
              }
          }

Which of the following options will allow me to update Teri's last name to set the value to "Dactill" rather than "Dactyl"?

Answer: people.update({1: {'last_name': 'Dactill', 'first_name': 'Teri', 'age': 27}})

Explanation: We can use the update method as demonstrated by this solution:

people.update({1: {'last_name': 'Dactill', 'first_name': 'Teri', 'age': 27}})

The update method requires key/value pairs to overwrite the existing keys or values. This is not the simplest method to accomplish our goal. There will likely be another question soon demonstrating the easier method. The pop method is incorrect as pop is intended to remove specified items from the dictionary and requires the keyname as a parameter. The following code would also update Teri's last name with the following solution:

people[1]['last_name'] = 'Dactill'

References:

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

Replication - I would like to know if any one in this group has worked with Qlik attunity Replicate it is a Data Replication Software. Thanks in Advance!

How does sql server agent access the azure storage blob - Good morning all , I must configure an automated backup to an azure blob storage in the backup script I have correctly configured the credential now what right is needed for the sql server agent startup account to access the storage azure blob thank you for your help

SQL Server 2017 - Development

How long will a view stay cached in memory? - I've inherited a stored procedure that is using a view several times to load temp tables. I'm thinking I can increase performance by loading a temp table with the data from the view and then use the temp table instead of the view. Im just not sure if once a view is called if it […]

SQL Server 2016 - Administration

trace flag -T460 not working - So this is probably me being a div. 2016 SP1 I've enabled -T460 in my starting params via SSCM & restarted. I can see this "applied" in the "registry start up parameters..." line in the errorLog. When I try an insert that results in the "string or binary data would be truncated" error I still […]

SQL Server 2016 - Development and T-SQL

SUM and JOIN 2 columns with different tables but did not SUM Qty - Good day! Need help re SUM and JOIN 2 columns with different tables but did not SUM qty and duplicates row result here's the sample data tables; Transfer Shipment table; TransTO - TransFROM - ItemNo - Desc - Desc2 - VariantCode - Qty Location1 HO Item01 Active Black […]

Formatting dates returned in query - I have a statement below in a query that is returning the invoiced date as 2020-08-24 22:00:10.493 How can I get those query results in MM-DD-YYYY which I think is format 101? SELECT I.InvoiceDate

SQL Server 2019 - Administration

Can't delete auto created statistics on SQL Server 2019 - Hi, I'm have sysadmin rights but it is not possible for me to delete auto created statisitcs. I receive the message, that the statistic doesn't exits or I do not have enough rights to do this. drop statistics [company$Sales Line].[_WA_Sys_00000001_73A22229]; What can I do to delete the auto created statistics.

SQL Server 2019 - Development

How to write query for multiple rows into a single row for different columns - How to write query for multiple rows into a single row for different columns in SQL Server While storing in table, I am inserting like below. CountryIDCityID TownID 10 6 11 10 6 12 10 3 11 10 3 12 10 3 13 11 9 11 11 9 12 11 14 11 11 14 12 […]

Creating a unique Batch ID for Update - Hi There, I'm trying to write a query that sets a batch of rows to have a single uniquely generated ID on the fly. Tried lots of things and just can't get it right. Below are sample scripts to create the table and insert some test data along with two update queries that do not […]

Reporting Services

Condition Based Parameter - SSRS - Hi, I have a situation that I am unable to find any solution by browsing. I have an internal parameter @Category which has to be passed null value to StoredProc if we select @subCategory option "All". If we select any other value in @SubCategory drop down, I need to pass the selected value to Dataset […]

General

Import a large SQL query definition from a text file - The query definition has about 75 lines. It is part of an Excel VBA routine in which I connect to a MS SQL database using ADO. One reason to go with an external file is to avoid adding the line break characters such as " & _ at the end of every line. I […]

Powershell

Need Powershell script to change SQL Server service password - Need sample Powershell script to change PWDS for SQL Server services (on many SQL Servers). We need to modify the SQL Server 'services' passwords (SQL Server, SQL Agent, IS, RS, AS) on several dozen SQL Servers. I am local admin and SQL sysadmin on every server - and I have all current service accts & […]

remove lines from flat file with no header - I have a file that has a delimiter of bar (|) and has no headers. I need a way to remove bad data rows if it meets criteria. Then create a new file with rows removed, and keep a log of the bad rows. This is my criteria for rows I don't want in the […]

Integration Services

Unable to cast COM object of type error using SSIS ODBC Connection to PostgreS - how to avoid Unable to cast COM object of type error using SSIS ODBC Connection to PostgreSQL I want load data from postgres to sql server using ssis package . In ssis solution I have 50 plus dft for loading data from postgres to sql server . source is postgre connecting using odbc connection and […]

Certification

Gaining knowledge - All, I would appreciate some opinions on a few questions. I appreciate that some of it's subjective and I'm not looking for definitive answers, just some advice or opinions. I've had a bit of experience of reading CV's and recruiting staff but not much. I'm currently unemployed looking for a new job. I have several […]

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.