Experimenting with Go-SQLCMD (2023-04-21)

SQLServerCentral Newsletter for April 21, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Experimenting with Go-SQLCMD]( - [SQL Prompt Safety Net Features for SSMS: Tab Coloring]( - [Extract SharePoint List Data and save to SharePoint Folder as CSV using Power Automate]( - [From the SQL Server Central Blogs - ChatGPT Lied To Me]( - [From the SQL Server Central Blogs - PASS Data Community Summit 2023 Registration is Open]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Percentile Memory Grant Feedback]( The Voice of the DBA  A Third Break One of the most amazing benefits of working at [Redgate Software]( is the ability to take a sabbatical every five years. One of our staff [wrote about this recently](, and I found myself reflecting back on mine, as well as thinking forward. The article notes that many people either learn or travel during theirs. That was somewhat of my experience, where I spent my first one learning skills and volunteering at home. My [one-year look back]( is still interesting to revisit today. Unfortunately, my flagpole base failed in strong winds (sad face) and broke the pole. It's still on my list to rebuild a new one. I still look back on my volunteer time with fondness and try to get back to [Habitat](every year. My second was avoiding travel, since I'd traveled a lot the year before. I ended up with the last sabbatical before the pandemic, coming back to work as our office closed. I stayed home, [worked on learning and projects](, though I did take a trip to Las Vegas to celebrate my wife's birthday. Six weeks away from work seems like a lot. Before Redgate, I'd have thought that this was a huge burden on the employer and fellow employees. However, we've had multiple people on sabbatical and we cope. We pick up the slack, and things continue to run. As with maternity (and paternity) leave, it's not as big a burden as this American used to think. It is very refreshing, and each time I've felt rejuvenated. I've been ready to get back to work, talking with Redgate customers and speaking at events. To me, this is a great way to encourage retention among loyal employees, as well as a way that can create more diversity of thought among your employees. Where they travel, the things they learn, even the change of pace in their mind often bring them back to work with new perspectives and ideas. I just crossed my fifteenth year at Redgate, so I'm due for my third sabbatical. I haven't thought about it, and I am not likely to take it this year. This does take some planning, both in my personal life and at work, so I have found I usually need 5-6 months to decide on something and get plans in place. What will I do this time? I'm not sure. What would you suggest? I am thinking to travel this time for part of the trip. My wife and I had an amazing travel time in 2022, and there are so many amazing places in the world that I'd like to visit. I am also tempted to try and fit in some learning as well, perhaps a week spent in some sort of educational endeavor. No matter what I decide, I am grateful for the opportunity and look forward to another break that helps my work-life balance, balanced. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Experimenting with Go-SQLCMD]( Steve Jones - SSC Editor from SQLServerCentral Steve takes a look at go-sqlcmd, the newest evolution of the sqlcmd command line tool. [External Article]( [SQL Prompt Safety Net Features for SSMS: Tab Coloring]( Additional Articles from Redgate The Tab Coloring component of SQL Prompt’s SSMS Tab management could save you from accidentally doing something apocalyptic on a production server, ever again. [External Article]( [Extract SharePoint List Data and save to SharePoint Folder as CSV using Power Automate]( Additional Articles from MSSQLTips.com Sometimes you might have your data within a SharePoint list, and you would prefer this dataset to be within a SharePoint folder instead for reasons like reporting in Power BI or Excel. You need to be able to move the data automatically from the SharePoint list to the SharePoint folder. [Blog Post]( From the SQL Server Central Blogs - [ChatGPT Lied To Me]( Grant Fritchey from The Scary DBA I’ve been seeing more and more noise about how ChatGPT can help with technical stuff, writing code, all that. Well, I wanted to know how best to set up... [Blog Post]( From the SQL Server Central Blogs - [PASS Data Community Summit 2023 Registration is Open]( Steve Jones - SSC Editor from The Voice of the DBA You can register today for the 2023 PASS Data Community Summit. This year the event is in Seattle, Nov 14-17, and in-person only. The event was a lot of... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Percentile Memory Grant Feedback Percentile Memory Grant feedback was introduced in SQL Server 2022 as a way to adjust the memory grant feedback not to the last execution, but as a percentile of history. What compatibility levels does this work under? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Thomas Franz) Swapping values of variables I have two variables and want to swap / exchange their values, what will be the result of the following statements DECLARE @i INT = 1 , @j INT = 2 -- swap variables SELECT @i = @j , @j = @i SELECT @i AS i, @j AS j  Answer: @i = 2, @j = 2 Explanation: It is counterintuitive, but even if you are using a single SELECT statemet, the assignments will be done one after the other, as if you would have written multiple SET statements. SQL Server first set @i (originally 1) to the value of @j (2) and then sets @j to the new value of @i (2 instead of the original 1), so you end up with a 2 in both variables. If you really want to swap the variables, you need a third variable as temporary storage: DECLARE @i INT = 1 , @j INT = 2 , @t INT -- swap variables SELECT @t = @i , @i = @j , @j = @t SELECT @i AS i, @j AS j -- returns 2 and 1 BTW: the UPDATE statement is not prone to this behavior, there you can swap two columns without any problems in a single statement: CREATE TABLE #tmp (i INT, j INT) INSERT INTO #tmp (i, j) VALUES (1, 2) UPDATE #tmp SET i = j , j = i SELECT i, j -- returns 2, 1 FROM #tmp AS t DROP TABLE IF EXISTS #tmp [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 [Where can I download SQL server 2017 Developer edition?]( - I am setting up a new laptop and I need SQL server 2017 developer so that it is the same version as prod . However I can't find a download anywhere. Anyone have a source, preferably at MS. SQL Server 2016 - Development and T-SQL [Can SSIS Check for Viruses?]( - Apologies if it is a silly question. We periodically receive an Excel file from a customer. There is now a requirement to store it as a BLOB object in SQL Server for the reference. I would use Exchange Server for it: ask the customer to email the file to the Exchange Server and SSIS - […] SQL Server 2019 - Administration [auto refresh a query result (F5) in SSMS]( - i have a job that load data into a table and a separate table that keep tracks of errors (when there's issues with the data loading). i have a query select this error table and display the columns as needed. instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the […] [SQL Server 2019 HA]( - Looking for a HA solution for SQL server for our branch offices. Current setup has our 3 offices running independent standalone servers with SQL server. No redundancy in place. My aim is to centralise the servers in 2 locations. Location A and B has one server each. Both running windows server vm and sql in […] [Looking for recompiling stored procedures]( - I have been tasked with the job of finding the top X number of stored procedures that are being recompiled regularly on a group of servers. I've tried several times to Google this and keep coming across "How To" articles on setting stored procedures for recompile, which is not what I want. I'm trying to […] [if then else issue]( - I'm trying to use the if then else to check if a member exist in a server role. if it does, do X, else do Y. but not getting the desire outcome. BEGIN DECLARE @member nvarchar(50); USE [master] SELECT @member = members.name FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id […] SQL Server 2019 - Development [Count Number of Records]( - Hi I am trying to write a SQL query that returns the number of working days between two dates. The approach I am taking is the following... Financial markets are open on non-holidays so doing a select distinct on trade dates would give me the list of all non-holiday dates. I could create a separate […] [Find the records based on group by Student ID]( - Data looks like below Status 1 is Active and 2 is Clean For one Student ID we may have one active, one clean status; I want to find the records only with Clean status for a student ID. Student Id        Student_Status 12345        […] [problem of slowness in my requests]( - hello, I am having a major performance issue , with this part of code which is still blocked on my server , Attached is the part of the code that is causing the problem. DELETE R FROM #RESULTS_DEM AS R INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM WHERE ( NOT EXISTS ( SELECT […] SQL Azure - Development [Copy DB from Prod to Test !!]( - Good morning. I would like your advice. I work on Azure Sql. We have 3 environments (Dev, Test and Prod) Each environment has a specific tenant. Every month, I would like to copy the database from Production to the Test environment. What solution do you recommend to perform this task? Regards General [Partition in sql]( - I have the following data frame: df = pd.DataFrame({'A': [6,0, 4,2, 8, 2, 6,0, 4,8], 'B': range(0, 12), 'C': ['a', 'b','c', 'd', 'e','a', 'b','c', 'd', 'e']}) A B C 0 6 0 a 1 0 1 b 2 4 2 c 3 2 3 d 4 8 4 e --------------- partition by C 5 2 […] [cross join sql]( - I'm attempting to use INNER JOIN to join numerous tables. Here is the code: IF OBJECT_ID('tempdb..#tmpRecData') IS NOT NULL DROP TABLE #tmpRecData --STEP 1 SELECT DISTINCT pr.ChainID, pr.StoreID, pr.SupplierID, pr.ProductID, MAX(CAST(pr.ActiveLastDate AS date)) AS 'Active Date' --ChainID, SupplierID, StoreID, InvoiceDate, InvoiceNumber, SupplierInvoiceDate, SupplierInvoiceNumber INTO #tmpRecData FROM dbo.[ProductPrices_Retailer] AS pr LEFT JOIN ProductIdentifiers iden ON pr.ProductID […] Analysis Services [how to get previous value in a fact table for a measure in mdx]( - Hello Guys! I want to write an mdx query with a calculated Measure. Here is my fact table: How to implement a mesaure with the rule below : If CurrentAtt != PreviousAtt then PreviousValue else CurrentValue  I mean by previous the value in the previous period. Thank you so much! any suggestion please ? Integration Services [I am having a issue with a SSIS Package]( - I have a big sql script that i am doing a flatfile destination on to create a csv. I have case statements and about 4 columns that I have used the FORMAT command on some dates, when I run the script in in SQL manager it all looks great and when i copy the script […] SQL Server 2022 - Development [The place of SMALLDATETIME]( - SMALLDATETIME is a datatype that gets very little love online and few people recommend it. Kendra Little is right to warn about the rounding up/down of seconds. Microsoft explicitly recommends DATETIME2 over SMALLDATETIME on the Microsoft Learn page. However, it is only 4 bytes in length and gives accuracy to a minute and DATETIME2(0) is […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -