Design your systems and processes for auditability (2021-10-25)

SQLServerCentral Newsletter for October 25, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Design your systems and processes for auditability]( - [SQL String functions in SQL Server, Oracle and PostgreSQL]( - [Dealing with Failed SQL Migrations in MariaDB or MySQL]( - [From the SQL Server Central Blogs - #PowershellBasics: Adding data to a string using subexpressions.]( - [From the SQL Server Central Blogs - Power BI, Maps, and Publish to Web]( Question of the Day - [A Simple Error]( Featured Script - [Compare Patch levels across all installed sql instances]( The Voice of the DBA  Recognize that you have a choice about what to prioritize 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. Burning Out I haven't burned out, but I've been close. There was a time a few years back when I was working a lot, traveling a lot, trying to watch activities with my kids, making time for my wife, and more. While I have had stretches were I feel like that (like the last month), usually I have calm periods that balance my out. Across about 18 months, I realized I hadn't gotten enough breaks and worked with my boss to lighten the load. During the last 18 months under the pandemic, I've had a few busy times, and some stress, but overall, I've managed to cope well. [My coping tips]( have helped, and I've made a conscious effort to demand less of myself and slow down. It helps that I work for a UK company that values balance and tries to ensure employees work hard, but not too hard. That hasn't been the case for everyone this past year. There is [a blog about developer burnout](, noting that a lot of developers shouldered a larger workload this past year. I think the move to remote work is stressful, and it can be hard to adjust to expectations, or know what we should expect. There is often a feeling when you work remotely that you need to do more, because no one else can see how hard you are working. There is also a temptation to take some of the time that you used to spend commuting and get a few "extra" things done each day. Many managers haven't known how to adapt to remote work and can add to stress with either more work, higher expectations, or a lack of awareness of how employees feel. Add to all of this the challenges of managing kids, juggling noise and space to work with others in the house, and it's no wonder many people felt some burnout in the last year. The article gives some stages of burnout, a way to [measure yourself](, and some strategies for coping. While a lot of these may feel like common sense, when we are overwhelmed and busy, we often forget common sense. It's easy to dismiss the effectiveness of simple strategies. It's also easy to underestimate just how much better you will feel by taking even small steps to combat burnout. I know there are some very poor work environments and awful managers. If you are in these situations, I'd urge you to look hard to find a new position somewhere, anywhere. Even another bad job will give you a change of scenery and can help in the short term. Even in the poor jobs, however, I've often had others that felt the same way in my team or others. Talking, sharing, and bonding with others in a similar situation can help, if for no other reason than you can share, vent, and empathize with each other. Burnout is a real problem among technology workers. It's not a personal failure, but it is something that you have to recognize in your situation. It is also important to make changes and find ways to reduce the stress of your situation over time. There aren't usually quick fixes, but there are ways to change your life to better handle the situation. If you are struggling, please reach out to friends, family, or others and get help. Things can get better. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Design your systems and processes for auditability]( David.Poole from SQLServerCentral I have worked in more than one regulated industry, and since the banking crisis of 2008, I have witnessed a sea change in the approach to regulation. The UK Financial Services Authority (FSA) was seen to be a toothless tiger. The UK government replaced the FSA with two separate bodies, each with its own more […] [External Article]( [SQL String functions in SQL Server, Oracle and PostgreSQL]( Additional Articles from MSSQLTips.com In this article we look at how to use string functions to concatenate, replace, trim and get substrings in SQL Server, Oracle and PostgreSQL. [External Article]( [Dealing with Failed SQL Migrations in MariaDB or MySQL]( Additional Articles from Redgate Although it is easy to get started with Flyway, there are times when real-life can trip you up. Discover the fastest ways to restore the previous version of the database, to recover from a failed Flyway migration that leaves the database in an indeterminate state, and then how to adapt your database development process to avoid these problems. [Blog Post]( From the SQL Server Central Blogs - [#PowershellBasics: Adding data to a string using subexpressions.]( Kenneth.Fisher from SQLStudies I’m working on a project right now where I want to add the date/time to the end of a filename. ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [Power BI, Maps, and Publish to Web]( Meagan Longoria from Data Savvy October 2021 is mapping month over at Workout Wednesday for Power BI. As part of our challenges, we build a sample report and use the Publish to Web functionality...   Question of the Day Today's question (by Steve Jones - SSC Editor):  A Simple Error What does this do? CREATE TableA (id int, mystring varchar(100)) 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) SSIS Catalog Objects What types of objects are stored in the SSIS Catalog? Answer: Projects, packages, parameters, environments, and operational history Explanation: The SSIS catalog stores projects, packages, parameters, environments, and operational history. Ref: SSIS Catalog - [Discuss this question and answer on the forums](  Featured Script [Compare Patch levels across all installed sql instances]( Alexander Safronov from SQLServerCentral This script allows you to compare patch levels across instances. DECLARE @Value sql_variant SELECT @Value = value_in_use FROM master.sys.configurations WHERE [name] = 'xp_cmdshell' IF @Value = 0 BEGIN EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE END DECLARE @ServerNameList NVARCHAR(1000) = '"Server01","Server02","Server03"' DECLARE @PowerShellFile NVARCHAR(300) = 'C:TempGet-PatchLevel.ps1' -- local folder on sql instance DECLARE @PatchLevelFile NVARCHAR(100) = 'C:TempPatchLevel.txt' DECLARE @OLE INT DECLARE @FileID INT DECLARE @Command NVARCHAR(4000) DECLARE @SQLServerRegistryKeyPath VARCHAR (256), @SQLVersion VARCHAR (56), @InstanceName VARCHAR (56), @SQLPath VARCHAR (356) DECLARE @InstalledInstances TABLE (InstanceName NVARCHAR(200)) DECLARE @Instances TABLE (MachineName sysname, InstanceName NVARCHAR(200), [Version] NVARCHAR(50)) SET NOCOUNT ON; IF EXISTS(SELECT 1 from tempdb.sys.objects WHERE [name] like '#FileContents%') DROP TABLE #FileContents CREATE TABLE #FileContents (Instance nvarchar(200), [Server] nvarchar(200), [Version] nvarchar(200)) EXEC ('xp_cmdshell ''del "' + @PatchLevelFile + '"'', no_output') EXEC ('xp_cmdshell ''del "' + @PowerShellFile + '"'', no_output') -- get all installed sql instances SET @Command = 'powershell.exe "Get-Service | Where-Object {$_.Name -like ''MSSQL$*''}"' INSERT INTO @InstalledInstances (InstanceName) EXEC xp_cmdshell @Command DELETE FROM @InstalledInstances WHERE InstanceName IS NULL OR CHARINDEX('SQL Server', InstanceName, 1) = 0 UPDATE @InstalledInstances SET InstanceName = RTRIM(LTRIM(SUBSTRING(InstanceName, CHARINDEX('(', InstanceName, 1) + 1, LEN(InstanceName) - CHARINDEX('(', InstanceName, 1) - 1))) DECLARE InstalledInstances CURSOR FOR SELECT InstanceName FROM @InstalledInstances OPEN InstalledInstances FETCH NEXT FROM InstalledInstances INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLServerRegistryKeyPath = N'SoftwareMicrosoftMicrosoft SQL Server' + @InstanceName + 'Setup' -- Get SQLPath for installed instance EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,@SQLServerRegistryKeyPath ,'SQLPath' ,@SQLPath OUTPUT; SET @SQLPath = RIGHT(@SQLPath, LEN(@SQLPath) - CHARINDEX('MSSQL', @SQLPath, 1) + 1) SET @SQLPath = REPLACE(@SQLPath, 'MSSQL', '') SET @SQLServerRegistryKeyPath = N'SoftwareMicrosoftMicrosoft SQL Server' + @SQLPath + 'Setup' -- create PowerShell file SET @Command = ' $ServerNameList = ' + @ServerNameList + ' #IF the output folder does not exist then create it $OutputFolder = "c:Temp" $DoesFolderExist = Test-Path $OutputFolder $null = if (!$DoesFolderExist){MKDIR "$OutputFolder"} foreach($ServerName in $ServerNameList) { $Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(''LocalMachine'',$ServerName) $RegKey = $Reg.OpenSubKey(''' + @SQLServerRegistryKeyPath + ''') $Value = $RegKey.GetValue(''PatchLevel'') ''' + @InstanceName + ','' + $ServerName + '','' + $Value | out-file "' + @PatchLevelFile + '" -Append }' EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PowerShellFile, 8, 1 EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Command EXECUTE sp_OADestroy @FileID EXECUTE sp_OADestroy @OLE SET @Command = 'powershell.exe -ExecutionPolicy Bypass -File "' + @PowerShellFile + '"' EXEC xp_cmdshell @Command, no_output -- delete PowerShell file EXEC ('xp_cmdshell ''del "' + @PowerShellFile + '"'', no_output') FETCH NEXT FROM InstalledInstances INTO @InstanceName END CLOSE InstalledInstances; DEALLOCATE InstalledInstances; -- insert data from output PatchLevel file BULK INSERT #FileContents FROM 'c:tempPatchLevel.txt' WITH (DATAFILETYPE = 'widechar', FIELDTERMINATOR = ',', ROWTERMINATOR = 'n' ) SELECT DISTINCT @Command = STUFF( ( SELECT ',' + InstanceName + '' FROM @InstalledInstances ORDER BY InstanceName FOR XML PATH('') ), 1,1,'') -- horizontal Pivot output SET @Command = ' SELECT Server, ' + @Command + ' FROM (SELECT [Instance], Server, Version FROM #FileContents) p PIVOT ( MAX(Version) FOR Instance IN (' + @Command + ') ) AS pvt ORDER BY pvt.Server' EXEC(@Command) -- vertical Pivot output SET @Command = REPLACE(@ServerNameList, '"', '') SET @Command = ' SELECT [Instance], ' + @Command + ' FROM (SELECT [Instance], Server, Version FROM #FileContents) p PIVOT ( MAX(Version) FOR [Server] IN (' + @Command + ') ) AS pvt ORDER BY pvt.[Instance]' EXEC(@Command) -- Set it back IF @Value = 0 BEGIN EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE END GO [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 - Development [Creating appropriate data type from varchar(max)]( - This is something I am thinking someone may have done already. Basically we have data loader which loads data from any source but the data type in the table will always be varchar(max). if it is db to db transfer then the table will have correct data type as source. This was perhaps designed long […] SQL Server 2016 - Development and T-SQL [Extract data from XML field]( - Hello, I have a SQL view with a column called GlobalCountryRegionXML. Here is an example of a value stored in this field:- United Kingdom UK-IE United States NORAM From this, I would like two further columns showing the following results (in the same single row) separated by a comma:- […] [How to get YYYYMMDDHHMMSS]( - This is getting the data I need, but not the format. select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') 10212021102437 I want YYYYMMDD before the time portion. Thanks. [SSIS File System Task - Permission Inheritence]( - I have an SSIS package which outputs a flat file to the file system into a "staging" folder. The package then runs a File System Task to move this file from the Flat File Destination "staging" location to another "collection" path specified in an SSIS Parameter. The package works successfully. However, the resulting file that […] [Alter Procedure With Invalid Object Name]( - We are trying to update a stored procedure to reference objects that don't yet exist in preparation for when they will exist. The problem is SSMS doesn't like that and gives the error that there are invalid objects. Is there a way to trick it into allowing the invalid references? Development - SQL Server 2014 [Conversion failed when converting the varchar value to data type int.]( - Hi, I've tried using cast but can't seem to get the syntax right. Here is the query below: SELECT PurchaseOrder AS Customer, [JobNumber] AS ROM#, Counter_Type as Countertop, ((Length/12)*(16)) as Price, LnFt AS Reason, CASE WHEN LnFt = '1' THEN ' SHIPPING DAMAGE' WHEN LnFt = '2' THEN 'SHIPPING SHORT ON DELIVERY' WHEN LnFt = […] SQL 2012 - General [named pipes connect error]( - Inherited an estate. Just trying to logon to one of the SQL servers. It's not a named instance. Trying to connect via SSMS & osql (local to the SQL box) - connecting with either: SSMS (windows user onto hostname, hostname,1433 or hostname\mssqlserver). It's windows auth only. osql -S. -E osql -S hostname -E osql -S […] [How to make row number start by 1 instead of 0 when make union all?]( - I work on SQL server 2012 i face issue i can't arrange rows from 1,2,3,4,5 current arrange is 0,1,2,3 for rownumber i need row number start by 1 then 2,3,4,5,etc select H.lifecycleid,H.ZPartID,H.ZLC,H.ProccessingDate,ROW_NUMBER() OVER(Partition by H.ZPartID ORDER BY H.ProccessingDate DESC) AS Row_Number into #arrangeHistory from #TempLC t inner join parts.LifeCycleHistory H on H.ZPartID=t.ZPartID --3---get data related […] SQL Server 2019 - Administration [Need the (default) port to connect - why is this not implicit.]( - So I have 2 instances (1 x 12, 1 x 19). Both have the default port set to 1433. Same settings on each. I connect in from a central server. On one, I must specify the port the other I don't need to. Just wondering why. I would have presumed the default port is implied? […] SQL Server 2019 - Development [Rank values based on dates (grouping)]( - Hello friends, I need some help. I need to rank all titles for each week. Later on, I need to pull top20 titles for each week in PowerBI, however for some reason it doesn't let me to do it there (it pulls top20% only). I am trying to find a way around and group it […] [Replace apostrophe in user input field]( - I have an Access front end with SQL backend. The user input field is text. He inputs: don't mess up. When I run the SQL update to update the field I get the SQL injection error. My code: strSQL =  _ "UPDATE [Orders] " & _ " SET [Orders].[Comment] =  '" & Me.Comment & "' & _ " WHERE [Orders].[OrderId] = '" & strOrderId & "';" DoCmd.RunSQL strSQL The Comment text […] [DATEDIFF with GETWEEK worth of data]( - I have such an issue. I have units, on sale date and also how many weeks the unit is on sale. However, my current formula with DATEDIFF grabs only dates that are from '09-20' and some from '09-21' (if we take a week of 1 month example), but I need to change it so it […] [Update field based on next 5 rows]( - Hi, I have table below: ID  Postcode  PostcodeNext 1    LL11 2    LL12 3    LL13 4    LL14 5    LL15 6    LL16 . . . I need to update PostcodeNext column for each row in database with next 5 postcodes as string, so ie […] Reporting Services [Please help with grouping totals and details]( - I am trying to get the same layout going in ssrs as in the picture below. Basically there is a parent group on business area/line and then it groups on event type too and does the totals on the top line. right underneth the totals for the event type it goes into the detail to […] [SQL REporting SErvices 2017 , how do I deny permission to users]( - I set site level and user level Security entry for domain users, but still user is able to access the repots.. How do I mitigate this... i want to give access to only few user groups and not all user groups..   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -