Manage Your Business Rules in T-SQL Query (2022-09-02)

SQLServerCentral Newsletter for September 2, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Manage Your Business Rules in T-SQL Query]( - [The Uses of Dependency Information in Database Development]( - [SQL WAITFOR Command to Delay SQL Code Execution]( - [From the SQL Server Central Blogs - Import a CSV with a Header Row using BCP–#SQLNewBlogger]( - [From the SQL Server Central Blogs - The book that made me love goal-setting]( Question of the Day - [DBCC UPDATEUSAGE]( Featured Script - [Schema Only Database Backup]( The Voice of the DBA  Daily Coping Tip Forgive someone who hurt you in the past I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Potholes in the Road of Progress This editorial was originally published on 24 Jul 2017. It is being republished as Steve is on holiday. There is always a chance that you, in your work as a technologist, will discover something new. If it turns out to be wonderful and useful, you may be tempted to sit back with a glow of satisfaction and wait for the acclaim. In fact, you’re just at the start of a long battle for your discovery to be recognised. It isn’t just that breakthroughs are frequently ignored, but once you get over that problem, that they are sometimes met with active hostility. Why would anyone be hostile to a scientific or technical advance? For every Einstein or Darwin, there are equal and opposite fools, eminent in their branch of science, who do their level best to delay advances. I find some of these people fascinating. The absurd Professor Dionysius Lardner (1793 –1859) decided that the great railway engineer Brunel was incompetent, and temporarily managed to convince scientists that the voyage directly from New York to Liverpool by steam liner would be impossible. His final ignominy was in saying at an inquest that a fatal boiler explosion happened because the affected engine was struck by lightning. Almost a decade after Joseph Lister discovered that surgery failed not through the ‘miasma’ but through unsterile surgical implements, many eminent surgeons such as Samuel Gross, distinguished professor of surgery at Jefferson University Medical College in Philadelphia, continued to wear street clothes, without masks and gloves, and using unsterile surgical equipment. We somehow get a sneaking admiration for someone like Sir George Biddell Airy (1801 – 1892), Astronomer Royal from 1835 to 1881, who put back the science of chronology and computing several decades by poisoning the British government against the work of Babbage and ‘Longitude’ Harrison. He even managed to bury Thomas Fowler’s discovery of the unique properties of the balanced ternary system for mechanical calculators. His most famous mistake was to contribute to the Tay Bridge disaster by giving entirely incorrect advice to the designer, Gouch, about the severity of the weather in that location. Dr A A Griffith (1893 - 1963), who headed the aircraft engine department at the Royal Aircraft Establishment after WW1, put back the development of the jet engine by several years. Griffith rejected Whittle’s proposals for a jet engine, reporting to the UK government that a jet engine could never work. In consequence, the government refused to fund its development. The list goes on and on. There seems to be a strong human instinct that is averse to new ideas and discoveries, and many who somehow believe that by vociferous opposition that they can "Photoshop" them out of existence. I wish I could say that discoveries always reach the light, but I believe that, in science or technology, it is only true if there exists a counter-force to the instincts to resist change: a healthy, altruistic community of peers. Phil Factor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Manage Your Business Rules in T-SQL Query]( tomaz.kastrun from SQLServerCentral Motivation At some point in the carrier, we have come across the problem of hard-coded values in SELECT or WHERE clauses. And we all agree that these hardcoded values must be parametrised. This bad habit usually backfires when we need to troubleshoot a query. These hardcoded values are usually a business role baked in the […] [External Article]( [The Uses of Dependency Information in Database Development]( Additional Articles from Redgate Dependency information will allow you to avoid errors during a database build or tear-down, by ensuring you create or remove objects in the right order. It will also help you to avoid future 'invalid object' errors, because it will allow you to check that no database alterations have introduced broken references, during Flyway migrations. [External Article]( [SQL WAITFOR Command to Delay SQL Code Execution]( Additional Articles from MSSQLTips.com In this article, we look at the SQL Server WAITFOR command to allow delays in processing either for a specified time or a set amount of time. [Blog Post]( From the SQL Server Central Blogs - [Import a CSV with a Header Row using BCP–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started. I... [Blog Post]( From the SQL Server Central Blogs - [The book that made me love goal-setting]( JenMidnightDBA from SQL Awesomesauce Pete Wilkinson’s book “Unstoppable” finally made visualizations and goals make sense to me. Here's a review! The post The book that made me love goal-setting appeared first on Jen McCown.   Question of the Day Today's question (by Steve Jones - SSC Editor):  DBCC UPDATEUSAGE What does DBCC UPDATEUSAGE do? 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 Migration Tool to Use Which migration tool is suited to assess my SQL Server data estate and lift and shift my estate to SQL Server on Azure VMs? Answer: Azure Migrate (online hub) Explanation: The Azure Migrate online portal is the place to lift and shift (and track) your migration of on-premise SQL Servers to Azure VMs. Ref: Compare SQL data migration tools - [Discuss this question and answer on the forums](  Featured Script [Schema Only Database Backup]( Daniel Brink from SQLServerCentral Use DBCC CLONEDATABASE to make a Schema Only Database Backup --Step 1: Clone your database --Here the "Demo" database is being copied as "Demo_Clone" --Refresh SSMS's Object Explorer to see the new clone in the list of databases. dbcc clonedatabase(Demo, Demo_Clone) with verify_clonedb; --Step 2: Make the clone writable --All clones are read-only by default, so this is required -- if you are going to use the clone for anything alter database [Demo_Clone] set read_write; /* When developers or testers say they need a empty copy of the database without any data in it, what they really mean is that they want the schema AND all the basic reference data and config tables needed to run their code. So at this point you can also copy in some reference data from the source database into your clone. e.g. insert into Demo_Clone..Accounts select * from Demo..Accounts */ --Step 3: Create a full backup backup database [Demo_Clone] to disk = N'C:\temp\Demo_SchemaOnly_20220821.bak'; --Step 4: Remove the clone before the DBA includes it into maintenance plans :) drop database [Demo_Clone]; [More »](  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 - Administration [Unable to Communicate to SQL 2005 via Linked Server]( - Hi all, I'm having issues communicating via a linked server from a Windows 2019 Standard server running SQL 2017 Standard to a Windows 2003 R2 Enterprise server running SQL 2005 Standard. The operating system on the source server was just upgraded from Windows 2012 R2. Error: OLE DB provider "SQLNCLI11" for linked server "server" returned […] SQL Server 2016 - Administration [Extended Events: using ATTENTION Event to log external apps timeouts.]( - The purpose I created the attached Session and one of the queries to view data and periodically reload it to the table WAS BECAUSE once in a couple of weeks we have had this nasty bottleneck in our environment when instead of an average of 40 or 50 timeouts (from ASP web pages/IIS) per day […] SQL Server 2016 - Development and T-SQL [SQL convert Rows to columns]( - I have seen several posts on this many of which suggest Pivot. I am trying to mimic a MS Access report that used VBA to format the columns. I have been working on this for awhile and I am still no closer to solving it, here is the Problem. Some of my attempts have involved […] SQL Server 2019 - Administration [SQL2019 Std Quantity License Keys and multiple backups servers]( - Hi All, I'm in need of knowing how many SQL2019 Standard license key I need in my scenario; I'm hoping someone could help so I'm compliant with Microsoft key usages. I'm not a SQL person, I'm the DB Admin but falls upon me to provide software to others. My server speck's: 4x HP DL360 G9s, […] SQL Server 2019 - Development [Calculated column vs. Running Total column]( - I'm in discussion with my development team regarding the best way to model staff members taking annual leave. Rather than post a lot of DDL stuff it's easier to describe narratively. I personally think that the best way to model this is as follows. A table called StaffLeaveEntitlement, where a member of staff has their […] [How to rename a Windows folder using TSQL]( - Hello, I need help to rename a folder name from 20220822, which is the current date to anything, "newdirname", for example. "C:\Test\20220822\" I would like to use a sql script. Or, if anyone knows how to get this done using PowerShell, I will appreciate feedback. Thanks [How to make pivot for features dynamically based on feature name and feature val]( -     sssss I work on sql server 2019 i can't make pivot for feature values based on feature name . i need to apply pivot dynamically because may be tomorrow add new features so it will depend on features on table . for original part id i have 4 features as ProgramMemoryOriginal,NumberCoresOriginal,NumberADCsOriginal,NumberDACsOriginal […] SQL Azure - Administration [Azure Data Studio - SQL Profiler - save or export results to table]( - I can launch SQL profiler (Alt P) using Azure Data Studio and can see the system logs on the screen. However, when I stop the trace session on the screen, and change the [Select View] option on the top right, all the data that was collected initially gets lost. How to save the results of […] Azure Data Factory [Question about data factory html request]( - Hello! We have used Logic apps to make http request to our clients system and retrieve xml data from it. Is it also possible to do on data factory? Flow is like that: Make http request --> request response body is xml data --> take that xml data and store it in blob storage as […] Reporting Services [Subscriptions are 'disabled' and setting to enabled instantly revert to disabled]( - We have SSRS 2019, some of our subscriptions (across multiple reports) are currently 'disabled' and other subscriptions for same reports are ok so cannot see any commonality. If we via the browser mark the subscription as enabled, then refresh browser it is back in disabled mode. However if we 'edit' the subscription then simply click […] [Ratio from Metrix]( - Hello Calculating ratio from metrix - or if you have other way of doing it please ratio is SUM of A for 2018 divided by SUM of B for 2018 multiplied by 100 for percentage purpose Please see attached file to see whats given and required 75 = ( 300/400)*100 Thanks General [No experience with programming; how difficult is SQL for new prospect?]( - I'm looking at a career change. I have bachelors degree in healthcare management and pre counseling. My mental health is going down the drain and do not think I can sustain myself long term in the field of addiction. I was planning on going to grad school for counseling, but entry level data analyst positions […] [MS Visual Basic OR MS Visual C# ??]( - Hi everyone, So for some time now I have been learning SSIS and I have worked on various tasks and created some projects on it without using Script Task. What I have learned so far is that to achieve what I am trying to do via SSIS built-in tasks, I can get much better results […] Continuous Integration, Deployment, and Delivery [CI/CD to deploy database using Gitlab]( - Hello, I want to do CI/CD do deploy database (sql server) and ssis package using Gitlab , but the problem i don't know how can i start and also i didn't fount any tuto on the internet. Can someone help me how can i start to do that ? and also if possible to do […] Integration Services [Transfer SQL Table Data into Excel]( - Hi everyone, I have designed a SSIS package that transfers SQL table data into an excel file. I am fetching data via this query: /****** Script for SelectTopNRows command from SSMS ******/ SELECT CONVERT(NVARCHAR(4000),[LIST_TYPE]) AS LIST_TYPE ,CONVERT(NVARCHAR(4000),[LIST_CATEGORY]) AS LIST_CATEGORY ,CONVERT(NVARCHAR(4000),[LIST_SUBCATEGORY]) AS LIST_SUBCATEGORY ,CONVERT(NVARCHAR(4000),[LIST_DESCRIPTION]) AS LIST_DESCRIPTION ,CONVERT(NVARCHAR(4000),[LIST_VERSION]) AS LIST_VERSION ,CONVERT(NVARCHAR(4000),[T2_CODE]) AS T2_CODE ,CONVERT(NVARCHAR(4000),[T2_TYPE]) AS T2_TYPE ,CONVERT(NVARCHAR(4000),[T2_NAME]) […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -