A Brief Introduction to PowerShellAI, an Intelligent Command-line (2023-09-06)

SQLServerCentral Newsletter for September 6, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Brief Introduction to PowerShellAI, an Intelligent Command-line]( - [Quickstart: Connect SQL Server machines to Azure Arc]( - [PASS Data Community Summit 2023 - Sessions are available to view!]( - [From the SQL Server Central Blogs - Quick Tip About Fonts in Deneb Visuals in Power BI]( - [From the SQL Server Central Blogs - How to check if powershell is running as administrator with elevated privileges]( - [Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition]( Question of the Day - [Creating an Old Symmetric Key]( The Voice of the DBA  Rogue Colleagues The economy might be good or bad for you right now. Some of that depends on where you live, what your employment situation is like, what your habits dictate about how you live life, and more. No matter what your situation, likely there are people around you that complain about the world and others who think things are fine. There are likely more of the former than the latter, but that's because humans tend to complain out loud more than they praise. When people think there is an economic downtown for themselves, they may be more likely to engage in malicious activities. While I don't think most data professionals will start to hack other systems, or even their own employer's systems, there is evidence to support the idea that some might be susceptible to recruitment by bad actors. [This piece]( references some research and warns security groups to be wary. There is no shortage of books, or television and movie scripts that might show creative ways to access information, but how can you tell if a colleague makes a simple mistake or they are a bad actor? Clicking on a phishing email could be either one. Not removing anonymous access to an S3 bucket could be either. Losing their credentials through social engineering is something that happens every day. Who's to say that this happened purposefully? I don't want to second guess the people I work with making mistakes, but I also think these possibilities are why we want to use our computer systems with strong auditing and multiple groups reviewing logs. We might not necessarily stop all activity, but we can often detect it quickly and mitigate the issues. It's also why DevOps and automated deployments with logging are a good idea. They can limit the problems from both accidents and malicious actors. My employer has started to do more education around security and how individuals can avoid accidentally causing issues. We use a lot of automation, and more all the time, that ensures once we know how we ought to patch and update systems, we can do it regularly and confidently. Repeatable, reliable deployments of changes are what we aim for. We know they'll be some mistakes, but we also know that we can quickly identify issues (MTTD) and fix them (MTTR). Even if we get a bad patch from a vendor, we can quickly deploy a "fix" if we get one, or even reinstall and re-patch to lower levels, if needed. DevOps, GitOps, and other xxOps aren't just about getting new features out quickly. They also include the ability to fix problems when the need arises. They don't prevent rogue actors from causing issues, but they should help you detect and recover quicker than you might expect. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Brief Introduction to PowerShellAI, an Intelligent Command-line]( Mikey Bronowski from SQLServerCentral Learn how you can interact with ChatGPT from PowerShell. [External Article]( [Quickstart: Connect SQL Server machines to Azure Arc]( Additional Articles from Microsoft MSDN Get started with Azure Arc-enabled SQL Server to manage and govern your Windows and Linux SQL Server instances hosted across on-premises, edge, and multicloud environments. [Technical Article]( [PASS Data Community Summit 2023 - Sessions are available to view!]( Additional Articles from PASS As hosts, Redgate are delighted to announce PASS Data Community Summit 2023 sessions are now live and available to view! [Blog Post]( From the SQL Server Central Blogs - [Quick Tip About Fonts in Deneb Visuals in Power BI]( Meagan Longoria from Data Savvy This week, I was working with a client who requested I use the Segoe UI font in their Power BI report. The report contained a mix of core visuals... [Blog Post]( From the SQL Server Central Blogs - [How to check if powershell is running as administrator with elevated privileges]( SQLPals from Mission: SQL Homeostasis How to check if powershell is running as administrator with elevated privileges In case it's not clear from the title, I am referring to... [Microsoft Power BI Quickstart Guide cover]( [Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition]( Steve Jones - SSC Editor from SQLServerCentral Bring your data to life with this accessible yet fast-paced introduction to Power BI, now in color.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Creating an Old Symmetric Key I have a database that I upgraded from SQL Server 2012 to SQL Server 2016. We set the compatibility mode to SQL Server 2016 and everything works just as it did on SQL Server 2012. This database had data that was already encrypted with the DES algorithm. We want to create a new symmetric key to do some testing on upgrading the encryption. I run this command, which matches what was used to create the original key, with a different object name: CREATE SYMMETRIC KEY PIIKey WITH ALGORITHM = DES, IDENTITY_VALUE='SomethingN3w', KEY_SOURCE = 'Someth!ngBl&e' ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword' GO What happens? 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 Isolation Level Across Databases I have a table in a couple databases with a fullname of "Steve Jones" and a firstname of "Steve". With one connection, I run this in the database, sandbox2: BEGIN TRAN UPDATE dbo.A SET firstname = 'Delaney' WHERE fullname = 'Steve Jones' This transaction is open. I now run this on a second connection in the sandbox database: set transaction isolation level read uncommitted; use sandbox SELECT * FROM dbo.A AS a1 INNER JOIN sandbox2.dbo.A AS a2 ON a2.fullname = a1.fullname For the row with fullname="Steve Jones" that is queried in sandbox2, what is returned for the firstname? Answer: Delaney Explanation: The isolation level is set for the connection, not the database. Even though the query goes across databases, the isolation level is applied and we get a dirty read from an uncommitted transaction in sandbox2. Ref: SET TRANSACTION ISOLATION LEVEL - [ [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](. --------------------------------------------------------------- Administration - SQL Server 2014 [Server memory question]( - I would like to know please how much memory I should add to my server here is what I have 51452 in MB and we be adding more data and databases,please recond how better to maintain SQL Server 2019 - Administration [Securing access to SQL Instance]( - Hi, I have a need to harden-down users access to a SQL Server instance. I have a number of SQL authenticated accounts that need to be able to create and curate database objects on a dedicated schema. At the moment they have membership of the ddl_admin role on the database, however this is proving insufficient […] [Can I set the publisher database into read-only mode]( - Hi - Can you set the db into read-only mode if it’s part of replication. As part of migration, I need to set the DBs into read-only mode before I take final backup. Can I do this if the replication is still active to another server? [ALTER or DROP TABLE could not clean up root row within 10 seconds]( - We keep getting these errors quite frequently. How do we troubleshoot and fix these errors ? Are they benign in nature or need to be fixed. Please advise. SQL Server 2019 - Development [SQLCMD output]( - I have a script that outputs a query to a text file. When using -o I am pointing to a shared directory on another server. Does sqlcmd use smb (445) or port 139 to connect when writing the file? [if condition select into temp table]( - Hi, I have a query where I search and provide the result. select * into #a from( Select ID, 1 as counter from TableA where ID=@id union all select ID, 2 as counter from TableB where ID=@id)a declare @tocheck integer set @tocheck =(select tocheck from #a) if @tocheck=1 DROP TABLE IF EXISTS #partA begin insert […] [TRY-CATCH blocks ignore converting errors in ORDER BY statement]( - BEGIN TRY SELECT * FROM ( SELECT 'int' AS [text] ) AS tmp ORDER BY CONVERT (INT, tmp.[text]) PRINT '1' END TRY BEGIN CATCH PRINT '2' END CATCH Just had an interesting case today where it seems as if when you unsuccessfully cast text as an INT, then this looks to be a severity level […] Amazon AWS and other cloud vendors [Is AWS the right choice for a dirt-simple API for myself only?]( - I’ve written a little Python script to post to multiple social medias at once, and I want somewhere to host it as an API so I don’t have to set up a static IP, keep a raspberry pi going etc. Essentially I want to be able to send a post request to this and have […] Connecting [MS OLE DB Driver for SQL Server vs MS OLE DB Driver 19 for SQL Server]( - I have two OLE DB drivers installed. Microsoft OLE DB Driver for SQL Server and Microsoft OLE DB Driver 19 for SQL Server Is it possible to uninstall the un-versioned driver (Microsoft OLE DB Driver for SQL Server) without breaking anything? Or must it be retained due to possible software dependency? Also, it looks like […] [Updates for multiple SQL Server ODBC versions]( - We have computers on our network with multiple ODBC versions installed on the same machine. For example, on one machine there is Microsoft ODBC Driver 17 for SQL Server 17.10.3.1 and Microsoft ODBC Driver 18 for SQL Server 18.1.2.1 Because of possible software dependency, it looks like you have to keep both versions and install […] Integration Services [Merging Data Sets in similar way as VLookup]( - I want to automate my manual Vlookup using Excel to SQL/SSIS. Table: is used with  The columns that are the same colour are used for Vlookup. Currently. I am having trouble loading data from tables with non primary/secondary keys to tables with primary and secondary keys (tables:). Error I am getting: SSIS package "C:\Users\Anthony.DESKTOP-ES5HL78\source\repos\WeatherDataETL_\WeatherDataETL\WeatherDataETL\Package3.dtsx" […] SQL Server 2022 - Administration [Just updated server to 2022 - CXSYNC_PORT waits - HELP!]( - Hi Updated from SQL2014 to SQL2022 today. Seeing a ton of CXSYNC_PORT waits. Its as if something is not configured correctly, What can I check? This in an on-premise server. SQL22 ENTERPRISE Microsoft SQL Server 2022 (RTM-CU1) (KB5022375) SQL Server 2022 - Development [Performance related issue in the report after adding new table in the sp .]( - I have an issue in the report. Have added new table in the SP. After adding new table report rendering gets hanged and never processes below is the stored procedure this is the table added in the query last  UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table this table contains 4 lakh records CREATE PROCEDURE [dbo].[RPT_IssuerRatingNew] /* Issuer Rating */ --Declare […] [how new window clause works in following case.]( - hi, can i get one example of following from this link "If the is contained in a that is part of which is a simple table query, then the scope of the new window_name also includes the , if any, of that ."  yours sincerley [Multicolumn indexes and fragmentation]( - Is there anything special you should think of concerning index fragmentation and multicolumn indexes (sorry for possible bad terminology)? I am not sure how to think here, if I need to think about it all. Currently I've got a index that looks like this: int, int, bit, datetime. I think it's easier to understand when […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -