The SQL Developer Experience: Beyond RDBMS (2023-10-13)

SQLServerCentral Newsletter for October 13, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [The SQL Developer Experience: Beyond RDBMS]( - [Integrating Python with MongoDB]( - [New Microsoft Sandbox Uses Natural Language LLMs for SQL Queries]( - [From the SQL Server Central Blogs - Parameterize your Databricks notebooks with widgets]( - [From the SQL Server Central Blogs - Creating an Empty Repo in GitHub–#SQLNewBlogger]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( 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 [SQLServerCentral Article]( [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. [SQLServerCentral Article]( [Integrating Python with MongoDB]( Anshumali Ambasht from SQLServerCentral This article gives an overview of MongoDB and outlines steps to integrate python with MongoDB [Technical Article]( [New Microsoft Sandbox Uses Natural Language LLMs for SQL Queries]( Additional Articles from SQL Server Magazine  [Blog Post]( 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... [Blog Post]( 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 cover]( [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 - [ [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 " \ -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 […]   [RSS Feed]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -