SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

OPENJSON and Versions

Featured Script

Python Script to generate test data

The Voice of the DBA

What do you do or think about on the way to work?

In the post-pandemic world, many of us don't have a commute every day. With Steve on vacation, this seemed like a good editorial to republish and get you to think about your world today. Maybe your commute is 5 minutes across the house. What do you think about then?

I am not sure how long your commute is to work each day. I recently changed jobs and my commute time doubled as my new position is all the way across town. I went from going 15 miles (24km) into work every day, to going 30 miles (48km) instead. I found myself with more time during my commute and some questions came to mind. Now I am sure there are some of you that have a lot further to go to get to work, but that isn’t what I wanted to talk about today.

I was driving to work a last month. Yes, I live in the U.S. and like a lot of people here, I drive to work. Anyway, I started thinking about what do other people do or think about during their commute. I know there are many people that take the bus or the train to work. Certainly this is true in other countries, still there are many here in the states that take mass transit also. So for them there is a whole range of things they could possible do on the way into work. Sleep, eat, read, do work (yuck!). Clearly for people driving, most of their attention needs to be on the roads and on what is going on around them.

As for me, I have my normal routine. I leave early. Usually not long after 6:00am, I am on the road. It seems if I can leave by 6:10 traffic isn’t too bad on the way in. I usually have my radio on, although not too loud. I tend to pray, think about the day. Yes, sometimes I do think about work. Then it occurred to me. What are all these other people doing? Who are they? Where did they come from? Where are they going? What is their story? What are they thinking about?

It amazes me how many people there are each day travelling to work. I am sure that some of you, that take a bus or train, see some of the same people every day. Perhaps you have the opportunity and have the boldness to talk with them and get to know them. For me, I don’t even get past seeing just cars. Rarely, do I look past the vehicle to person inside. I am just trying to get safely to work/home and avoid the crazies.

Recently some of my thoughts on the way in to work were:

Why did that person just cut me off? What is going on in their life that they acted that way? Do they really think they are so much more important than me that they are trying to run me off the road? Why do they think it is okay to drive so far over the speed limit and endanger everyone around them? Why is that person riding my bumper?

These are some of the things I have thought about during my commute. Sometimes these thoughts help me understand my follow commuter. Sometimes they just help past the time.

Share what you think about or do during your commute.

bkubicek

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

Featured Contents

How to create a SQL Server presentation in 5 minutes using AI

Daniel Calbimonte from SQLServerCentral

Introduction This SQLServerCentral article shows how to create a SQL Server presentation in 5 minutes using AI. AI is revolutionizing the world as it continues to evolve. In this article, we aim to answer the question: Is it truly possible to create a high-quality presentation using AI in such a short amount of time? Is […]

Webinar: The Impact of the Cloud on Databases

Press Release from SQLServerCentral

The PASS Data Community Summit is back in Seattle this November, with hundreds of speakers covering a wide variety of database topics, including cloud computing. We feature two of those speakers in this webinar: Bob Ward of Microsoft and Steve Jones of Redgate.

Working with NULL Values in SQL

Additional Articles from SQLServerCentral

In SQL and relational databases, NULL values are an important part of managing data and information stored in SQL tables. A NULL value is used as a placeholder for missing values or values that are unknown in a database column or cell.

From the SQL Server Central Blogs - Introduction to IaC: Deploying Data infrastructure to Azure using Terraform (Part 1 - Intro and prerequisites)

epivaral from SQL Guatemala

IntroductionOne integral part of modern Continuous Integration and Continuous Development (CI/CD) pipelines is the ability to create the underlying infrastructure as part of the pipeline in a consistent and...

From the SQL Server Central Blogs - Simplify Your Work with PowerShell Scripts: Running from the Command Line

Joyful Craftsmen from Joyful Craftsmen Blog

Introduction
You know that feeling when you have your PowerShell script written, saved and now you need to execute it? But every time you want to use the script, you...

Azure SQL Revealed: A Guide to the Cloud for SQL Server

Site Owners from SQLServerCentral

Access detailed content and examples on Azure SQL, a set of cloud services that allows for SQL Server to be deployed in the cloud. This book teaches the fundamentals of deployment, configuration, security, performance, and availability of Azure SQL from the perspective of these same tasks and capabilities in SQL Server. This distinct approach makes this book an ideal learning platform for readers familiar with SQL Server on-premises who want to migrate their skills toward providing cloud solutions to an enterprise market that is increasingly cloud-focused.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	OPENJSON and Versions
OPENJSON is a SQL Server 2016 TVF. I have a database on an SQL Server 2019 instance that is set to compatibility level 120. What happens when I run this command: SELECT openjson(oj.myjson) FROM mytable AS oj
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 Incomplete Standby

I have a backup of a database from SQL Server 2019 that I want to restore on another SQL Server 2019 instance. The version levels are the same. I run this command:

USE [master]
RESTORE DATABASE [sandbox3] FROM  DISK = N'D:\SQLBackup\New folder\sandbox_20210308.bak' WITH  FILE = 1
,  STANDBY 
,  NOUNLOAD,  STATS = 5
GO

What happens?

Answer: The database does not restore as a standby file is needed

Explanation: If you use the standby option, you need to provide a filename for storing the recovery efforts for the database. Ref: RESTORE ARGUMENTS - https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Featured Script

Python Script to generate test data

tggirish from SQLServerCentral

Very often there is a need to create test data manually. This script will provide a mix of names that will make more sense rather than randomly generating meaningless names.

def GenerateTestData(lstArgs):
intNumberOfRecordsToGenerate = 50
lstFirstName = ["Paul","John","Steve","Jane","Ronaldo","Christie","Alex","Elaine","Gloria",
"Kim","Steffi","Martina","Bill","Bob","Andre"]
lstMiddleName = ["Jordan","Hope","Zillah","Jonah","Jesse","Joshua","Levi","Mark","Matthew",
"Michael","Philip","Reuben","Simon","Samuel","Seth","Silas","Solomon","Stephen",
"Thomas","Zechariah"]
lstLastName = ["Hanks","Zhang","Balboa","Ford","Shephard","Franz","Prakash","Goop","Ogeriksky",
"Paulinette","Garbonza","Pizzat","Tiblace","Oakee","Zianyi"]
lstDept = ["IT","Sales","Marketing","Finance","HR","Audit","Controls","Operations"]
intLengthFirstName = len(lstFirstName)
intLengthMiddleName = len(lstMiddleName)
intLengthLastName = len(lstLastName)
intLengthDept = len(lstDept)
lstFullName,lstDepartment = [],[]
for intRecord in range(intNumberOfRecordsToGenerate):
strFirstName = lstFirstName[random.randint(0, intLengthFirstName)-1]
strMiddleName = lstMiddleName[random.randint(0, intLengthMiddleName)-1]
strLastName = lstLastName[random.randint(0, intLengthLastName)-1]
strDept = lstDept[random.randint(0, intLengthDept)-1]
strFullName = strFirstName + "-" + strMiddleName + " " + strLastName
lstFullName.append(strFullName)
lstDepartment.append(strDept)
print (strFullName)
dfEmployees = pd.DataFrame()
dfEmployees["EmployeeId"] = range(1001, intNumberOfRecordsToGenerate+1001)
dfEmployees["FullName"] = lstFullName
dfEmployees["Department"] = lstDepartment
dfEmployees["Age"] = random.sample(range(21, 100), intNumberOfRecordsToGenerate)
dfEmployees["Salary"] = random.sample(range(12000, 100000), intNumberOfRecordsToGenerate)
print (dfEmployees)
return(dfEmployees)
# End GenerateTestData()

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

BCP with ODBC 18? - Hello all. My org has identified ODBC driver version 17 as having security vulnerabilities. It looks as though 17.10.4.1 remedies these, but we're considering v18 anyways. It looks as though bcp requires version 17. Is this the case? Surely not, though I don't see otherwise.

Administration - SQL Server 2014

Maintenance Task Failed - how to log the error - I've a Multi step Maintenance plan of 5 steps called "Reindex". This automatically created an Agent job called "Reindex..Subplan_1" There's an email alert on failure of the Agent job. Is there any place in maintenance task design to put an alert in or do I always have to use add the alert to Agent task […]

Development - SQL Server 2014

Replace bad characters - Hi all I need help to replace bad characters with space in string. select col1, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) as [Position], substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1) as [InvalidCharacter], ascii(substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1)) as [ASCIICode] from myTable where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) >0 Here is the result: Col1 […]

SQL Server 2019 - Administration

Error 500 when accessing SQL Server 2019 database - Need help! - I've recently set up a SQL Server 2019 database for a project I'm working on. The issue I'm encountering is that whenever I try to access the database, whether it's through my application or directly using SQL Server Management Studio, I'm getting an "Error 500" message. This error appears to be quite generic and doesn't […]

Event ID 17806 - We have had SSPI errors across a dozen servers in an hour span. SQL error 17806 : Error code 0x8009030c – How to fix Per the above link : Stuck at the very first point... Cause of SQL error 17806 1.SQL Server engine account running with a Domain service account, and that account locked at […]

disable seeding mode - hello , On one of our servers with 4 Replicas 2 synchronous and 2 asynchronous we see too many waits with the "VDI_CLIENT_WORKER" type almost 93% of the wait statst do you have to deactivate the seeding mode to minimize these times ? What do you think of this recommendation?

SQLPackage Import Error - Hi After 1h the import fails with the error: Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 155, Level 15, State 1, Line 3 'AUTO_DROP' is not a recognized CREATE STATISTICS option. SQL Server 2019: 15.0.4316.3 sqlpackage.exe: 162.0.52.1 May the sqlpackage is to new but I didn't find the one who fits exactly. Thanks for […]

SQL Server 2019 - Development

MS Sync Framework Error - Failed to Execute the command BulkUpdateCommand - It turns out that the error is in a trigger on the Transfers table, so this can be ignored. Sorry if you've wasted your time reading this. Hi, I've got two databases at distant locations that sync evey few minutes using the Ms Sync Framework 2.1. I'm getting this error occurring: ERROR , SyncBPO, […]

Odd columns datatype precisions showing in SSMS View... - Okay... haven't posted in a long time and was hoping I'd have a better topic to share but... we're seeing issues when expanding the view columns in SSMS and it's showing the wrong precision. The view is pulling from underlying tables using DECIMAL(11,2) but the view shows the columns as DECIMAL(12,2) - what would cause […]

SQL code help to get one row for each sequential entry - Hi, I am looking for a sql help to generate one row per each sequental door_area, for a given employee, based on the sample data that looks like this: Current Output: Desired Output: Here is my code to test for sample data. CREATE TABLE dbo.emp_seq ( emp_id int, product_code int, clock_in datetime, clock_out datetime, door_area […]

SQL Azure - Administration

Connecting to SQL Azure - Best practice / method? - New to Azure SQL, so I'll start with that! We've had SQL on-premise for years and we're exploring moving to a SQL Instance in Azure (not a VM). A lot of our apps are desktop .Net apps that connect very simply over our internal network currently or VPN. We'd like to ditch that approach and […]

Reporting Services

Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 - Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 with Reporting Services extension installed. I am having trouble with parameters and the error message: "Must declare the scalar variable @TellMeWhyIdontlikeThisMonday". I'm also getting the error message: "Custom parameter layout was removed from the report. SQL Server 2014 Reporting Services and […]

SQL Server 2022 - Administration

Having problems with SQL Server 2022 CU5 (docker ubuntu image) - is it ready? - Ubuntu image came from here - https://hub.docker.com/_/microsoft-mssql-server?tab=description DBCC is failing - "can not create database snapshots" syspolicy_purge_history is failing due to a missing PowerShell installation master.sys.dm_server_services - is not listing the engine at all while listing agent running? Can't enable SQL Server Agent Alerts - most likely due to master.dbo.sp_MSsetalertinfo and msdb.dbo.sp_set_sqlagent_properties usage of registry

SQL Server 2022 - Development

Running 32 bit SSIS package - This is my first post in this forum, so hello everyone! Would be most grateful for help with the following issue: a simple SSIS package (created in Visual Studio 2019) imports a table from an ODBC Source (MySQL Server 5.xxx) into a database in a SQL Server 2022. ODBC Source is 32-bit, the package runs […]

Searching in date field provides inconsistent results - I have been working with SQL Server for too many years to count and this is baffling; perhaps someone has a clue on the cause. I am running this on SQL Server 2022. I put together a simple function to return a primary key integer for a lookup table that has mileage rates that are […]

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.