SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Restore and Upgrade With NoRecovery

The Voice of the DBA

You Always Have a Software Pipeline

I was working with a customer recently and they said they didn't want to set up a pipeline. Somehow they wanted magically to see changes that developers made to their database appear in their production database without making any effort to build or configure a pipeline in a tool like Octopus Deploy.

As I was thinking about how to find them a solution they would accept, I realized that I've always had a pipeline for software. Early on, this was a copy of all the files in a folder to the client for Clipper and dBase apps. Later, my pipeline was copying around all the VB6 files from a .zip file and executing a few SQL scripts that were sent to me in emails.

At one point, I had developers just tell me the software pipeline was them copying files to a server while I had to move all the changes from the QA server to production. I didn't like that one for two reasons. One was that the developers would sometimes forget to move a change from dev to QA, despite claiming they'd tested the application. Two was that the developers sometimes moved too much to QA, and had changes to the schema that they hadn't finished in their app. SQL Compare couldn't help me in either of those cases.

Over the years, I learned that a slightly thought-out pipeline is better than nothing. I wrote my scripts to simulate what Flyway does today, executing the scripts I'd placed in a folder. I added logging that would capture output results and save them for later review. I learned how tools such as Team City or Jenkins could execute the same thing I could but in a more repeatable, reliable manner. After all, my reliability at 4:55 p.m. on Friday isn't as good as it is at 2 pm on a Tuesday.

DevOps is about learning and taking advantage of tooling. Look at what works in your pipeline and find repeatable, reliable ways of accomplishing those tasks without humans doing more than clicking a button to approve something. It's about using your creativity to improve the process and codifying it in a way. It's about being better than we were last week while ensuring the process isn't dependent on any particular person. After all, I might leave for vacation.

Or find another job.

Steve Jones - SSC Editor

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

Featured Contents

The SQL Developer Experience: Beyond RDBMS

Press Release from SQLServerCentral

Microsoft has built an amazing platform with Azure SQL Database and has recently announced an offer to use this for free. Read about the capabilities and options with this cloud database platform.

Integrating Python with MongoDB

Anshumali Ambasht from SQLServerCentral

This article gives an overview of MongoDB and outlines steps to integrate python with MongoDB

New Microsoft Sandbox Uses Natural Language LLMs for SQL Queries

Additional Articles from SQL Server Magazine

From the SQL Server Central Blogs - Parameterize your Databricks notebooks with widgets

Meagan Longoria from Data Savvy

Widgets provide a way to parameterize notebooks in Databricks. If you need to call the same process for different values, you can create widgets to allow you to pass...

From the SQL Server Central Blogs - Creating an Empty Repo in GitHub–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

I saw someone struggling with getting started with a Visual Studio project and Azure DevOps. They got a conflict, which I’ll show and then get you started with an...

Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis

Steve Jones - SSC Editor from SQLServerCentral

Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Restore and Upgrade With NoRecovery
I have a SQL Server 2019 backup. I decide to restore this to a SQL Server 2022 server using the NORECOVERY option since the STANDBY option doesn't work. What happens when I run this?
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 Low Deadlock Priority

Deadlock priorities range from -10 to 10. If I run this, what is my priority?

SET DEADLOCK_PRIORITY LOW;
GO

Answer: -5

Explanation: This sets the priority to the equivalent of -5. This connection could be chosen as the victim if other processes have the same priority of -5, but will not be chosen if other priorities are less than -5. Ref: SET DEADLOCK PRIORITY - https://learn.microsoft.com/en-us/sql/t-sql/statements/set-deadlock-priority-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 2016 - Administration

Monitoring Transaction Log usage? - I'm curious is there any way for me to monitor the TLog generation in my database? I am trying to pin point which sessions/SQL queries which generated huge amount of transaction log. I have alerts to notify if log space is running out, but I'm trying to find out which particular session or which query […]

SQL Server 2016 - Development and T-SQL

Insert a new record in a temp tableq - Hi, Not sure if this is possible. I'm looking to insert a new row into a temp table if a field on the record read is a specific value. Example Row reads Name, Address, Date, Flag first row = Jane, 1 Main St, 10/5/23, z second row = Tarzan, 12 Main St, 10/4/23, x Now, […]

Administration - SQL Server 2014

sources\sxs is not a valid installation folder - I'm trying to add a SQL feature via programs features - SQL Server - change - add. I have mounted the ISO successfully. When I click add it asks me to specify the installation folder so I browse to J:\sources\sxs and click ok. I keep getting the message that sources\sxs is not a valid installation […]

SQL Server 2019 - Administration

Replication subscription attempt fails with master key Error: 15581 - Hello experts, I'm trying to create a replication subscription and got this error. Does anyone know how I can fix it without inadvertently messing up any encryption that the SQL Server has? Please create a master key in the database or open the master key in the session before performing this operation. Changed database context […]

SQL Server 2019 - Development

T-SQL | Group Invoices with concern dates that overide the issue date - I have a table with Invoice Documents per Project, in which i need to get statistics per project , year , month with sql. The difficulty is that for some of the invoices the user will have entered concerned dates that must be override the issued month of the Invoice Document and must be splitted […]

Select statement result change as insert statement values - Hi All, Looking for following results. Can you please help in this. Original 1 A US Expected result 1,'A','US' create table #tbl_Test (ID int, Name varchar(20),City varchar(20)) insert into #tbl_Test values (1,'A','US') Select * from #tbl_Test Expected result 1,'A','US'

Select a distinct list of values from tables, return the dataset - I am trying to select a distinct list of values from multiple tables, combine them into a single table with all data starting in row one instead of the data being returned on new rows for each distinct data set. This is what I am current getting by using a union: This is how I […]

Setting up a view with parameters - Hi Folks, I'm not very well versed in SQL so I come here to the experts for advise. I have a nice little query that is currently using hard coded values for customer name, start and end dates. I need to be able to pass those values as parameters which I don't know how to […]

Stored procedures in Analysis server - I have written a stored procedure to format data and return them as a table with custom columns. In this sp, I use #temp table to do some formatting / calculations. Now our powerBI report writers want to access to this SP from Analysis service. For them our admin created SQL analysis service. PowerBI users […]

How to check if SQL is installed on any of my multiple windows servers - I have about 400 windows servers. How can I check if SQL Server is installed on any of those servers. Thanks.

Correct syntax for this Contains statement - I want to search a table alias doc, column name Result, where doc.Result is full text indexed, using Contains, and I want to find where there is the term BSA and a % sign (percent sign, literally) within 5 words of each other. Do I have to escape the % sign like [%] ? that seems to come back […]

Reporting Services

SSRS server randomly get disconnected (sql server 2016) - I have a problem with my SSRS server that randomly can't connect to my data source. It works all the time but sometimes I get an error in my reports that indicate that the server failed to connect. Any tips or leads to identify the source of this problem? Here is my connection string: Data […]

General

Fetch API in Node.js: Making HTTP Requests - I'm working on a Node.js application where I need to make HTTP requests to external APIs. While I'm familiar with using the Fetch API in browsers, I'm not sure how to use it in a Node.js environment. Here's a simplified example of what I want to do: const fetch = require('node-fetch'); async function fetchData() […]

SQL Server 2022 - Development

Query web API and return JSON data - curl -X GET "https://api.server.com/v1/markets/quotes?symbols=AAPL,VXX190517P00016000&greeks=false" \ -H 'Authorization: Bearer ' \ -H 'Accept: application/json' How do i run this Rest Json API in sql server directly ? I believe its a combination of using he below, but i could not figure out the last syntax. sp_OACreate, sp_OAMethod sp_OAGetProperty Python version is here : # Version 3.6.1 […]

How to get 2nd max value if 1st max is null - Hello All, I have written below query to get agent data who completed max tickets for each group. But some times the agent email address is null or blank. So In that case I need to consider the 2nd max tickets completed agentname. If this email also null then 3rd max tickets completed agent. Could […]

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.