AutomatedLab - A Quickstart for Deploying SQL Server Labs on Windows (2023-08-04)

SQLServerCentral Newsletter for August 4, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [AutomatedLab - A Quickstart for Deploying SQL Server Labs on Windows]( - [Modifying MySQL data from within Python]( - [How to List First or Last n% Records Using NTILE() in SQL Server]( - [From the SQL Server Central Blogs - Altering the Default Schema for a User]( - [From the SQL Server Central Blogs - Microsoft Fabric: Lakehouse vs Warehouse video]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [More Left Joins]( The Voice of the DBA  Flexible Work Policies are Preferred The great post-pandemic, post-Great-Resignation, hybrid/remote/in-the-office work debate continues. It seems almost every week I see more stories that report on, hype, attempt to prove, or otherwise stoke emotions about whether the future of work for many people is more likely in any of these three situations. One company mandates everyone back in the office, another goes completely remote, and it seems many can't decide and have some sort of hybrid approach. Your boss might have strong opinions on one of these situations, which may or may not align with your organization. I see CEOs wanting one thing and individual managers sometimes wanting another. As much as I like remote working, and I've been based out of my home for over two decades, I also like seeing people and I am regularly traveling to the various Redgate offices (along with [Grant and Ryan](. If you want to try and sway your boss to let you work more flexibly, which is likely remote or hybrid, perhaps [this article]( will help. There's research from at least one company showing that having a flexible policy has helped a number of companies hire faster in 2023. While this isn't in any particular geography, industry, or role, it does show that finding employees seems to be faster with flexible policies. That makes sense as the more remote you are, the more people in your pool of potential candidates. Of course, this doesn't say if these people stick around, or if they're qualified. Certainly many of us in the tech industry see plenty of candidates (or even coworkers) that don't seem qualified for many, or any, roles. Hiring faster isn't always good if there aren't good candidates, but having more choices of who to hire does give you an edge in finding qualified candidates. It also gives you more work to do in separating those you'd consider hiring from those you wouldn't. The flip side as well is that if you are flexible, then perhaps you can convince some already employed, talented workers to come to your organization. To be fair, many of us with these options are very lucky. There are lots of jobs where this isn't a debate and people must show up to work every day. Cooks, taxi drivers, retail workers, and many, many more. This is a luxury issue, but it is still an issue. My view is that lots of knowledge work can be done remotely. However, I also think that teams gain something when they bond and get to know each other in person. Day-to-day work is great remote, but brainstorming and creativity work better sometimes when people are in the same room. Not always, but there are times it is better. So let your boss know that they might be able to better fill their open positions if they're flexible. Maybe you'll convince them to be more flexible with you as well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [AutomatedLab - A Quickstart for Deploying SQL Server Labs on Windows]( Mikey Bronowski from SQLServerCentral Learn how you can use AutomatedLab to quickly create new environments. [SQLServerCentral Article]( [Modifying MySQL data from within Python]( Site Owners from SimpleTalk In the previous article in this series, I introduced you to how to access MySQL data from within a Python script. The article described how to use the MySQL Connector to establish a connection with a database and then retrieve data through that connection. In this article, I continue the discussion by demonstrating how to insert, update, and delete data in a MySQL database, again working with Python and the MySQL Connector. [External Article]( [How to List First or Last n% Records Using NTILE() in SQL Server]( Additional Articles from MSSQLTips.com Sometimes, we need to find the first or last n% records from a dataset based on a particular column. For instance, a teacher wants to know which students are in the bottom 25% based on marks. Hence, in such situations, the SQL NTILE() function helps. We will leverage this tip to get first or last n% records using NTILE() in SQL Server. [Blog Post]( From the SQL Server Central Blogs - [Altering the Default Schema for a User]( Steve Jones - SSC Editor from The Voice of the DBA I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote... [Blog Post]( From the SQL Server Central Blogs - [Microsoft Fabric: Lakehouse vs Warehouse video]( James Serra from James Serra's Blog Since Microsoft Fabric became available, I blogged about it and have an introduction video on Fabric that you can view here. I wanted to follow up with a short 30-minute video... [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):  More Left Joins I have a number of tables with this data: CREATE TABLE CustomerLeft (CustomerID INT, CustomerName VARCHAR(20)) GO CREATE TABLE CustomerContact (CustomerID INT, CustomerEmail VARCHAR(100)) GO CREATE TABLE EmailDomain (CustomerEmail VARCHAR(100), domain VARCHAR(20)) GO INSERT dbo.CustomerLeft (CustomerID, CustomerName) VALUES (1, 'Steve'), (2, 'Andy'), (3, 'Brian') GO INSERT dbo.CustomerContact (CustomerID, CustomerEmail) VALUES (1, 'steve.jones@red-gate.com'), (2, 'awarren@sqlservercentral.com') GO INSERT dbo.EmailDomain (CustomerEmail, domain) VALUES ('steve.jones@red-gate.com', 'red-gate.com') GO If I run this query, how many rows are returned? SELECT * FROM dbo.CustomerLeft AS cl LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID LEFT JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail 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) Changing the Default I have run this code in SQL Server 2019, which has no objects in the dbo schema: CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi GO ALTER ROLE db_datareader ADD MEMBER apiuser GO CREATE TABLE webapi.states (stateid INT NOT NULL CONSTRAINT statespk PRIMARY KEY, statecode VARCHAR(2), statename VARCHAR(20)) GO INSERT webapi.states (stateid, statecode, statename) VALUES (1, 'AK', 'Alaska') GO I log in to a second query window as apiuser and run this code: SELECT TOP 10 s.statename FROM states AS s; GO This works and I get Alaska returned. I now run this code in my first, administrative window, knowing there are no objects in the Sales schema: ALTER USER APIUser WITH DEFAULT_SCHEMA=Sales GO I return to my second window, which is still logged in and connected to the SQL Server. What happens when I run this code? SELECT TOP 10 s.statename FROM states AS s; GO Answer: I get an invalid object name for states Explanation: The permission change takes place, and when I requery on the same connection, the schema has changed. Sales is checked, and there is no Sales table. The dbo schema is checked and the object is not found, so an invalid object is returned. The user still has permissions to query the States table if the schema is included. Ref: No good reference for this, but if you switch the default schema for a user, you will see the error. [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 [tsql help]( - Hi All, Need some TSQL help. Need to extract a portion of the string of [tran_log_writes] column, convert that value to GB and display it as seperate column as "TLOG-gen-GB". Below is the sample data. CREATE TABLE [dbo].[test2]( [tran_log_writes] [varchar](8000) NULL ) GO INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)') GO INSERT [dbo].[test2] ([tran_log_writes]) […] SQL Server 2016 - Administration [WSFC - DR question.]( - I'm trying to write a DR doc with no test system for someone with no experience of SQL / clustering. I know! Don't ask. It's a weird old place where I work but they pay, I do. So, 2 nodes at site 1. 2 at site 2. 2016. AG. WSFC underlying on 2016. File share […] [SQL 2016 Patches and CUs marked "duplicate (do not use)]( - What's the story with a lot of SQL 2016 patches being marked "duplicate (do not use)"? It's not just one or two, it goes all the way back to CU1, and seems to be almost all of the patches, but there's no explanation or reference to what should be used or how to know if […] SQL Server 2019 - Administration [Odd Connect Issue With FQN]( - I have a 2019 SQL server that I can connect to with the NetBIOS name, but get "Access Denied" when I try to connect to it using the FQN. I have tried from multiple clients, including the console of the problem server. I have verified the normal things, access rights, group membership, that the domain […] [Need to enter the port to connect...]( - This is one of the mysterious things that clearly something changed, and n0body knows what. We have our monitoring software installed on a SQL 2019 server. Last week, it stopped connecting to two servers, SQL 2016 and SQL 2014 versions. Both are named instances. The connections were set up as ServerName\InstanceName, and this has been […] SQL Server 2019 - Development [Audit data]( - Hi Team , We have a requirement from the client , in which we have get the data about all users(ip address , machine etc) who have performed DML operation on any table in the database . If there were few tables I would have created a DML trigger . Is there any tools in […] Azure Data Factory [using ADF to extract Dynamics 365 data]( - Hello, we a migrating from on premises CRM to cloud D365 and need to ETL data into on-premises sql server/data warehouse. I have come across 2 options... KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 / SSIS Productivity Pack Azure Data Factory. We are a company that uses Microsoft products and services and already have […] Reporting Services [showing character "?'' as total of pages in the top toolbar navigation panel.]( - Hello fellow, I would like to post this issue which is driving me insane referred to the pagination control on the navigation top menu. On initial element listing paging shows up like image above. Whereas I'm moving forward thru next pages, it remains as such: Same issue continues till get the last page. The only time […] SSRS 2012 [Why would Microsoft Publisher come up?]( - I don't do much with SSRS these days, but I do have a SSRS report I support. The users were having a hard time getting to the report, but that has been resolved. (It was a change on the server.) However, now they're getting something which I don't understand at all. They access the report […] [How to config list spread the data on 2 column (Like side Z )]( - Hello, I can`t figure out how to config my list to spread on 2 column (Like Z) and only then continue to the next page. pictures attached  picture 2: example to my demand Powershell [Append MMYYYY to File to Copy]( - How can I adjust this script to add MMYYYY to the file name before the extension on the Copy? thanks Before: dys_ihhist.txt After Copy to Dest dys_ihhist_072023.txt  $source="c:\fileloading" #location of starting directory $destination="c:\filecopy"; #location where files will be copied to $files=@("*dys_ihhist*") New-Item -ItemType Directory -Force -Path ($destination); Get-ChildItem -recurse ($source) -include ($files) | Copy-Item […] Analysis Services [Can workspace database be recreated in tabular cube?]( - Hi All, I need to modify an old tabular model for which workspace database has been deleted accidentally. Is there a way to recreate the workspace database? I deployed the cube but it only creates main DB not the workspace database. Strategies and Ideas [What is the meaning actually for DWH]( - Hello.. Im working on a data facility almost since 8 years on a BI and DWH design … and there was always a question on my head about designing the DWH when we talk about storing data .. are we talking about every part , column , row , cell of data or are we […] Integration Services [Script task to get oauth 2.0 token error]( - I am trying to figure out a way to get oauth2.0 bearer token from the rest API. So from Postman I can do a POST method for " And in the body, I choose x-www-form-urlencoded and put grant_type as "password" and put the values for my username and password. This gives me a token which […] SQL Server 2022 - Administration [Find Version Mismatch]( - Hello, I'm looking for a way to generate a cross-sever report [using TSQL(without LinkedServers) or Powershell ] to show SQL Server AlwaysOn replicas that are not in the same version. Can someone please guide me? Thanks in advance   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -