SQLServerCentral.com Community Update for January 16, 2023

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Detecting the Variable Type

The Voice of the DBA

Daily Coping Tip

Learn something new and share it with others

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.

Exit Lines

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

Hasta la vista, baby. A pretty good exit line, at least if you’re Arnold. For the rest of us, once we make the decision to move on it’s time for the standard formula; notify our manager, wait to see if there is an exit interview, clean up and hand off any open tasks, and send out a final email letting people you’ve worked with know how to contact you (and catching up on your LinkedIn connections). All while – usually – trying to avoid burning any bridges on the way out.

Most of know that burning bridges is a waste of time and energy and few do it deliberately. What about accidentally though? A long time ago the network administrator of a company I worked with was unhappy with the direction things were taking and decided to seek a new position. Nothing wrong with that. Then he sent his final email and part of it was that he was “moving on to greener pastures and more money”. Not incendiary at all, perhaps it was intended to be funny (and it was, a little it), but it didn’t resonate well.

It’s definitely tempting to do something less formulaic and more memorable when it’s time to go. My sign-off email once included a link to the Killer Bean video which probably resulted in a small drop in companywide productivity on a Friday afternoon!

That has me wondering if you have a great exit line that you use, or one that you’ve seen used? Or an interesting exit tradition at someplace you’ve worked? SQL you later, alligator.

Andy Warren

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

Featured Contents

How to work with web information, maps, and images in Power BI

Daniel Calbimonte from SQLServerCentral

In this article, you will learn how you can add the map and image visuals to your Power BI report and have them update as you select different data values.

How to Find Missing Rows in a Table

Additional Articles from Brent Ozar Unlimited Blog

When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table:

Bucket Sort Algorithm in Python to Sort a List of Array Values

Additional Articles from MSSQLTips.com

Learn about how to implement a simple bucket sort algorithm using Python code to sort a list of array values.

From the SQL Server Central Blogs - Data Céilí Dublin 2023

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

Today we have launched Data Céilí (pronounced kay-lee) Dublin 2023, Ireland’s free, community led, Microsoft Data Platform event. We tried in 2020 but certain global events prevented us from...

From the SQL Server Central Blogs - Identify Unused Indexes across all Databases

Matthew McGiffen from Matthew McGiffen DBA

I had a request regarding how to identify unused indexes across all databases on a SQL Server instance (rather than just the current one). I’ve written this script before...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Detecting the Variable Type
If I have a variable, and I want to get the type programmatically from T-SQL, what should I do? Meaning, I have a variable that gets assigned as the result of a function call: select @a = somefunction() Which T-SQL function can I use to determine the base type, precision, scale, and length of @a?
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)

The ANSI PADDING Table

I run this code to create a table and insert rows. What are the results that I see from the query?

SET ANSI_PADDING ON GO CREATE TABLE dbo.ANSIPADDINGON(charcol CHAR(20), varcharcol VARCHAR(20)) go INSERT dbo.ANSIPADDINGON (charcol, varcharcol) VALUES ('c test','v test ') GO SELECT '[' + a.charcol + ']', '[' + a.varcharcol + ']' FROM dbo.ANSIPADDINGON AS a GO

Answer: [c test ] and [v test ]

Explanation: The results are:

[c test ] and [v test ]

The ANSI_PADDING option set to on will pad out the fixed size character column to the full size. For variable character fields, no trailing blanks are trimmed. Ref: SET ANSI_PADDING - https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql?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 2017 - Administration

Always on AG - I have availability group configured with multiple secondary replica's. Looking to additional secondary replica to Azure. Is that possible?

SQL Server 2016 - Administration

Can log backups cause Operating system error 32? - Hello experts, I am seeing sporadic (every other day or every few days) errors like this one for a backup job: Operating system error 32(The process cannot access the file because it is being used by another process.). I have read that the usual suspect is antivirus or similar scanning. But the timing of the […]

SQL Server 2016 - Development and T-SQL

Invoke a Tableau dataset refresh from a SQL Agent Job - Hello, we have a nightly SQL Agent Job that refreshes our database on SQL Server 2016 Server. We also have a tablue timer that begins refreshing a Tableau dataset but when the Tableau starts its dataset refresh before our SQL Agent job completes it slows down our nightly SQL Agent job. Is there a way […]

Return 80 % match - I have data with a column (VenSN) that stores Y, N, Null, (N with serial number ie 'N 23423545'). I want to return all the records for each vendor that has 80% or more of the total records for that vendor containing 'Y' in VenSN column. How do I do this in a query? […]

Development - SQL Server 2014

Starting a new FileTable - I have a database with two versions - a production version and a development version. As I fix and upgrade, I regularly copy data from the production version into the development version, so that I have current data to experiment on, and when it's time for a new rollout, I then delete the production verion […]

SQL Server 2019 - Administration

Always on SQL Managed Instance - is Always on support for the SQL managed instance. Distributed Queries are supported for SQL managed instances.

SQL Server 2019 - Development

ISNULL in case statement - Hi, I have this SQL fragment SELECT COL1, CASE WHEN Table1.COL2 = 0 then 'ZERO' WHEN Table1.COL2 = 1 then 'ONE' ELSE '' END AS MyResult FROM Table1 Which is the best way to treat Table1.COL2 NULL values as if they were 1 ? Is this the best way ? WHEN ISNULL(Table.COL2,1) then 'ONE' Thank […]

EncryptByKey KeyGUID string truncate - Hi Folks EncryptByKey (Key_GUID('Name_SymKey'), @Dex) @Dex has 9000 char length error: Msg 8152, Level 16, State 10, Line 360 String or binary data would be truncated. The statement has been terminated. it happens when we are inserting encrypted value to a varbinary(max) column in a table any idea ? appreciated

SQL Azure - Development

Azure Pricing, SQL Server seems pretty high? - Hi, I'm really new to this and I think I've got this wrong, wondering if you can help me. I've got an SQL database I'm wanting to put in Azure. It's 32gb and would be accessed by maybe thirty staff avg 20 times a day during normal working hours by a locally installed program. […]

Reporting Services

How to use SQL on 2015 MacBook? - Hello I am trying to start learning SQL, but have run into some problems. When I went to download mySQL on their website it said it couldn’t on my computer, I’m assuming because it’s a 2015 MacBook Air running on 10.12.6. Should I install and older version or do something else? I downloaded dbeaver and […]

PostgreSQL

Upgrade from RDS Postgres 11 to 14 - Hi, Would like to know if there are any code changes needed to upgrade RDS postgres 11 to 14.

SQL Server 2022 - Administration

Ola Hallengren Script for SQL Server 2022 - Hello, is it necessary to install the latest version of Ola Hallengren maintenance solution to use this on the sql server 2022? Or can is use a version from 2020? I think we got some work to do if we had to use the newest version. I can't find any release notes from the latest […]

Always on SQL Managed Instance - Hi, i need to do POC on the SQL managed instance in my office Is Always on support for the SQL managed instance. Distributed Queries are supported for SQL managed instances. Please help me asap on this.

SQL Server 2022 - Development

Error using python in SQL2022: DLL load failed on import: pylink and px_call - I'm getting an error trying to execute python scripts inside SQL Server 2022, and would appreciate any suggestions. After following this guide (for Python only): https://tomaztsql.wordpress.com/2022/12/05/using-r-and-python-in-microsoft-sql-server-2022/ I get the error message below any time I try to execute a python script in Sql2022, for example: EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python'; The error message is: Msg […]

SQL Converting Columns to Rows based on specific column value - Hallo, I have a problem to fill a new column from a previous column where the values are calculate. Current output: Required output: As you can see when there is a accordance beetween previous year/month in column1 (weample 0, 1 etc.) it shold dispay in the row in column2. When we have in current year […]

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.