SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Switching Database context in transaction

The Voice of the DBA

Cranky Curmudgeons

Today we have a guest editorial from Grant Fritchey as Steve is away on his sabbatical.

If you’re haunting the internet as I do, you probably hear how DBAs are an aging population. Further, because we’re aging, we’re not ready to embrace all the wonderful new technologies that are clearly superior in every possible way. Hence, this is why ElasticSearch and MongoDB haven’t taken over the world.

Now, I’m not going to argue. The DBA demographic is aging. We’re getting older. Yes, also, that means that a certain section of us are becoming cranky curmudgeons (CC). The CC will regale you with all the ways that they had to use stone knives and bear skins for disaster recovery back in the day. However, this is not applicable to everyone.

Let me sidestep for a moment. I little while ago I finally got off my behind and got my Ham radio license (KC1KCE, that’s my call sign). If you think the DBA population is aging and creating CC at a surprising rate, you should see the Ham community.

Over the recent holiday break, I took the time to really play with my radio. What was I doing you ask? OK, you’re probably not asking, but I’m going to tell you anyway. The first thing I did was get my Raspberry Pi 4 set up with a newer version of Raspbian (Debian Unix specific to the Raspberry Pi). With that I was able to configure Winlink, a software for sending email through the radio. I also was experimenting with FT8, a digital mode where the radio is controlled by a computer. In FT8, you send extremely short signals (28 characters max, and you thought Twitter was a pain). The whole idea is to use low power, small signals, as a way to make communication across large distances easier. I was also using another digital mode, PSK31, where you can type into a computer program and it gets transmitted through your radio. Yet another way to extend the reach of the radio beyond what’s possible through voice (called Single Side Band) or Morse code (called CW). All this through a tiny box that fits in the palm of my hand.

Anyone still here? Why am I talking about all this boring radio junk? Because, this CC was expanding his Linux skill set while learning new programs, new hardware, and new ways to communicate using a computer and a radio.

TLDR: CC can learn.

Same thing goes for data and databases. Don’t assume that because the population is aging that it’s not keeping up. Besides, the whole reason we’re not letting you implement ElasticSearch is because you youngsters haven’t properly secured it. See, we CC have been around the block a time or two, so we’re watching out for you.

Grant Fritchey

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

Featured Contents

Stable Marriages Using SQL Server

Glen Cooper from SQLServerCentral

In this article, we see how SQL can be used to determine matches between two groups, given a set of preferences.

Prevent SQL Server Blocking using Lock_Timeout

Additional Articles from MSSQLTips.com

Concurrency | T-SQL | Code Samples - In this tip we look at a way to minimize the length of blocking using SQL Server lock timeout.

Comments and More in PowerShell

Additional Articles from SimpleTalk

Comments are helpful when programming in any language, and PowerShell is no exception. In this article, Greg Moore demonstrates how to use comments to document code and to add prerequisites in PowerShell with #Requires.

From the SQL Server Central Blogs - Permissions required for row counts to show up on Object Explorer Details

Kenneth.Fisher from SQLStudies

I ran into a rather obscure permissions problem the other day. Since I wasn’t able to figure it out on ... Continue reading

From the SQL Server Central Blogs - Memory Optimizer Advisor

SQLEspresso from SQLEspresso

Previously I wrote about In-Memory Optimized Tables, in this blog I am going to discuss how to determine which tables could benefit from being In-Memory by using a tool...

Question of the Day

Today's question (by Thomas Franz):
	Switching Database context in transaction
Which database are you using, after executing the following statements? USE tempdb BEGIN TRANSACTION USE master ROLLBACK SELECT DB_NAME() AS current_database
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)

Changing the Data Frame

I have this data frame in R:

> fantasy.playoffs
                Team  Score           Opp
1 Way0utwest Cowboys 135.40          
2         SSC Ravens  66.26 Green Machine
3              Ditka  85.20          
4   Orange Engineers 111.52     Mexicanos

I then run this:

> fantasy.playoffs$OppScore <- c(NA, 66.26, NA, 111.52)

What happens?

Answer: A new column called OppScore is added to the data frame with those values

Explanation: This syntax will add a new column to the data frame with those values.

> fantasy.playoffs
                Team  Score           Opp
1 Way0utwest Cowboys 135.40          
2         SSC Ravens  66.26 Green Machine
3   Orange Engineers  85.20          
4          Mexicanos 111.52     Mexicanos
> fantasy.playoffs$OppScore <- c(NA, 66.26, NA, 111.52)
> fantasy.playoffs
                Team  Score           Opp OppScore
1 Way0utwest Cowboys 135.40                 NA
2         SSC Ravens  66.26 Green Machine    66.26
3   Orange Engineers  85.20                 NA
4          Mexicanos 111.52     Mexicanos   111.52

Ref: R Data Frames - https://www.guru99.com/r-data-frames.html

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 - Development

Report Development - Hi to all. I have not worked with SQL for a few years and am not familiar with the new development tools. I have downloaded Visual Studio 2017. I wanted to create a Report Project but do not see this option. Can you point me in the direction of a tool that will allow me […]

Automate Import of LIBOR Rates - Does anyone have any recommendations for sites which publish the daily LIBOR rates in a form which is easy to consume and import? I want to automate this, ideally.

SQL Server 2016 - Administration

SSAS processing Cubes using DTExec vs. SSDT 2017 tools? - Hello We have Analysis Server cubes on SQL 2016/Windows 2019 environment. Using dtexec to process the weekly cubes often fails or if it succeeds then Last Update date is not stamped (Only Last processed date is refreshed) The Error from dtexec is: Description: Server: The current operation was cancelled because another operation in the transaction […]

SQL Server 2016 - Development and T-SQL

Replace cr/lf not working with Japanese characters - I tried many different ways and built a function to basically go character by character checking the unicode values of 13,10 but why doesn't this simply work. tried char(13/10), nchar(13/10), etc... SELECT REPLACE(REPLACE(txt,NCHAR(0x0D),''),NCHAR(0x0A),'') broke, REPLACE(REPLACE(wrks,NCHAR(0x0D),''),NCHAR(0x0A),'') wrks FROM ( SELECT NCHAR(65404) + NCHAR(13) + NCHAR(10) + NCHAR(65438) + NCHAR(65412) txt, N'test value' wrks ) data

Administration - SQL Server 2014

Archive CLOB data - Dear All, How to archive a table having 10million records with one CLOB column? To select of 1 day records (approx~400) it takes around 5 minutes and without the CLOB column it completes under a second.

Development - SQL Server 2014

substring + chrindex - Good morning all i need your expertise to help me on a code the goal is to extract part of a chain create table DATACONTENT ( line varchar(max) ) insert into DATACONTENT values ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIAN_1\C842183\CARREFOUR SANTE REPRISE\160010210006\documents entrants\Courriers divers_12470290.pdf') insert into DATACONTENT values ( '\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000010\Volvo Allianz\VAZP100027\documents entrants\001-Doc 02 mars 2018 à 1640_22459358.pdf') insert into DATACONTENT […]

SQL 2012 - General

Facing connectivity issues - Hi All, I am seeing below connectivity error while executing queries on a database. how can we fix this. ERROR: Transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) Brief background: If we connect to a jump server and […]

SQL Server 2012 - T-SQL

SPN troubles - once more... - HEllo, I've been looking trough the forums on topics about setting and removing SPN's In the end i've found a good tool that created a working SPN (Kerberos Configuration Manager - found it here http://houseofbrick.com/microsoft-made-an-easy-button-for-spn-and-double-hop-issues/ But because i'm often trying stuff it sometimes gets fd up... sorry.. The ony thing i'm left with now is 2 […]

SQL Server 2019 - Development

Unix Timestamp to Datetime Conversion - Hi, I have a table with one of the column [last seen] nvarchar datatype and data imported from .csv file. Data in the [last seen] column is in Unix Timestamp in MilliSecs format [1575944000000] I need to convert it from unix timestamp to datetime format. Tried changing the datatype of the column from nvarchar […]

Storing results in tables with dynamic names - Hello, I have this loop which creates a bunch of matrices based on the month_nbr (in this case 1-36). What i want to do is to store the 36 results from this loop into separate tables with the names Matrix_1, matrix_2 and so on. I need these tables stored as i will multiply them using […]

Help with SQL query - Hi Guys, Here is my "Sample" source data. ID,DKey,SKey,InvType ,PID,PAmount,CAmount,AAmount 1 ,123 ,456 ,Write-Off,789,$505 ,$767 ,$262 2 ,958 ,258 ,Invoice , , ,$621 , Here what I want or the end result ID,DKey,SKey,InvType ,PID,PAmount,CAmount,AAmount 1 ,123 ,456 ,Invoice , , ,$767 , 1 ,123 ,456 ,Write-Off,789,$505 , ,$262 2 ,958 ,258 ,Invoice , , ,$621 […]

SSRS 2016

calculated field based on values - My report is based on a stored procedure and the columns are outputted to a table in the report. I would like to create a expression as a percentage which I require help with? At the moment, I have a shared dataset pointing to the SP, so if possible, can this expression be created in […]

SSDT

Dynamic Connection String for Dataflow Task - Hey Guys, I've been tasked with doing a task where I connect to a number of different sql servers, pull audit logs, and combine them into one table on our DBA instance. I've seen MANY posts about this online, read through, followed the steps that I can mirror on my own here but it doesn't […]

Integration Services

Data Stage to SSIS Migration. I want to use the existing unix scripts in SSIS - Hi, I am working on a project migrating from data stage to SSIS. There are several scrips written in Unix that I want to reuse in SSIS (if it ca be and if it's a good idea). Is there any way I can use the same in SSIS task or what should be the way […]

SQL Server and Sharepoint

Usage of Performance Point report on Share point site - I dont use share point regularly so this could be easy but struggling to find the answer. I am trying to find out who is using our sharepoint sites and i can see how to get audit data. This includes who is accessing documents, links etc. but we have few performance point reports (.aspx). This […]

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.