A LEFT OUTER JOIN in SQL: What Is It? (2023-08-18)

SQLServerCentral Newsletter for August 18, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A LEFT OUTER JOIN in SQL: What Is It?]( - [Import Text and CSV Files into SQL Server Database with SSIS Script Task]( - [Managing Test Data for Database Development]( - [From the SQL Server Central Blogs - Getting a Summary of Changes in SQL Compare 15]( - [From the SQL Server Central Blogs - Updated training list for a new DBA]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [The Variable Priority]( The Voice of the DBA  Taking Advantage of Conferences I've been very lucky in life to go to many conferences throughout my career. I've gone as both an attendee and a speaker, and I have found them to be valuable in helping me continue to grow and thrive in my career. This is across many employers, some of which I don't think had particularly great benefits. Plenty of people I worked with did not get to attend any conferences, and some of them felt resentful. I understand that, but I think that many employers are willing to invest in training and conferences when there is a return for them. I started going to [ComNet]( in DC, as a young professional wanting to learn. It was close enough to drive up and go for the free day and then return home. A few colleagues and I would make the trip once a year and talk with vendors, chat with attendees, and learn more about computer networking. Across a couple of years, we had moved from [thinnet]( and [thicknet]( to ethernet and IPX to IP, and this was a place where those technologies, and many more, were being discussed. During (and after) the event, I worked hard and improved my knowledge. I got things done at work. I got extra things done at work. I got my normal work done better. I solved more problems than in the past. Part of this was growing and learning, part was improving my troubleshooting, research, and testing skills. Some of it was meeting a few people at a conference I could email later and ask a question. As I changed jobs, I moved more into development and data work. I got the chance to go to [TechEd]( the [PDC](. I became involved in PASS and started attending[the annual summit](. I learned a lot in different sessions, and I made a lot of networking contacts with others. Many became friends, and many of them have helped me answer questions since then. I learned to not just attend a conference, but bring something back. Usually knowledge and always some swag to give out to co-workers. A gift for the boss that approved the trip was also a good idea. I continue to do this today. I often attend a [SQL Saturday]( and sit in a few sessions. I still learn something that I can write about or help a client with. I might take a moment to ask a speaker a question that likewise gives me knowledge that I can use in my day job. There's a payback for my employer when they've spent money for me to attend. There's also a payback for my career as I'm always learning something new. This fall is the annual [PASS Data Community Summit](. It's in-person, which I know limits who can go, but if you're looking to be a high performer. If you get things done at work and your company values you, perhaps you can make a case that you are the person that will take advantage of a conference. Convincing others that you're worth investing in, and proving you are, is a good skill to have. Take your best shot at it, and maybe you'll get the chance to [register]( and come. If not, the worst case is someone says no but learns that you want to drive your career forward. PS - Don't delay. [Prices]( rise on Sep 21. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A LEFT OUTER JOIN in SQL: What Is It?]( Parthprajapati from SQLServerCentral Learn about the basics of left outer joins in this article. [External Article]( [Import Text and CSV Files into SQL Server Database with SSIS Script Task]( Additional Articles from MSSQLTips.com There are several techniques available to import and export data for SQL Server. In addition, there are free SQL tools and utilities available that might be helpful for specific use cases. In SQL Server Integration Services (SSIS), we can use a Flat File Source to load Text/CSV files. Despite this, I would like to demonstrate how to import Text/CSV files using the Script Task. [External Article]( [Managing Test Data for Database Development]( Additional Articles from Redgate In coming up with a strategy for managing test data, first you need to understand what the many test requirements are likely to be for the particular database you are developing. Next, you need to create all the different categories of test data required to suite test requirements and, finally, work out how to manage it in a way that allows a test-driven approach to development. Read more in this latest article from Phil Factor [Blog Post]( From the SQL Server Central Blogs - [Getting a Summary of Changes in SQL Compare 15]( Steve Jones - SSC Editor from The Voice of the DBA A client asked for a summary of changes, so I wrote this quick post to show where to find this in SQL Compare 15. If you use SQL Compare,... [Blog Post]( From the SQL Server Central Blogs - [Updated training list for a new DBA]( Will Assaf from SQL Tact Met an eager young volunteer at SQLSatBR over the weekend who inspired me to update a blog post that hadn't been refreshed in a few years. See the [UPDATED] Training... [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):  The Variable Priority I run these commands in an administrative command prompt in Windows: setx myvar SQLSaturdayToronto setx myvar SQLSaturdayBoston /m If I now run this in a new command prompt that was just opened, what is returned? echo %myvar% 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) Restoring With Standby I have a SQL Server 2017 database that I backed up. On a SQL Server 2019 instance, I want to restore this backup. All paths for files are the same on both machines and exist. Which statement or statements below will fail? Answer: RESTORE DATABASE [PoC_test2] FROM DISK = N'D:\SQLBackup\Poc_2017.bak' WITH STANDBY = N'D:\SQLBackup\PoC_RollbackUndo_2023-08-16_00-00-18.bak' Explanation: The restore with recovery and the restore with norecovery work find. The restore with standby will fail as the database needs to be upgraded and this cannot be done with the standby command. Ref: Restore: [ [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 2016 - Development and T-SQL [How to identify changed values from a history table?]( - We have several tables with system_versioning set to on. There is a requirement now to produce a solution (function / stored procedure) to return a list of changed columns of a given table within a range of days. Below is an example of one of the tables - customer_sites and a bit of data: /**** […] [Run Stored Procedure on the first Monday if each month of Tues if Mon Holiday]( - Hello, I need to setup a Stored Procedure to run on the first Monday of each month that is not a holiday. If the first Monday of the month is a holiday, than I need the PROC to run on Tuesday. Via Scheduled Jobs, how is this possible? Can someone help with a query on […] SQL Server 2019 - Administration [repeated login failures.]( - Do repeated login failures into SQL affect performance. Surely some resources are used with each attempted ack & nack? Certainly log writes. I have careless app owners who repeatedly fall foul of my attempts to stop this happening as their apps try access every 10 seconds or so. They failures fill my logs & I […] [SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format]( - Hi Team, SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format we can see the audit logs using view logs rather than is there any way we can set the audit to run in frequency for every one hour and schedule it in a job where we can get […] [Deadlock - implicit transaction]( - Hi, We are experiencing deadlocks. The process not being the victim is doing an update by stored procedure which has begin tran, commit tran. However the transactionname in the deadlock graph is implicit_transactions. Could this be potentially causing the deadlock issue and from the application implicit_transaction is on?  [File does not close: VerifyShareWriteAccess.txt]( - Hi Folks. We have two SQL Server 2019 Enterprise in an High Availability Group (always on). The OS is Windows server 2019. For the witness of the failover cluster we use a file share. The cluster itself runs perfect. But we have some troubles with the file VerifyShareWriteAccess.txt. The cluster service opens the file (read, […] [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]( - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. see error below: I'm trying to login into a sql server on another domain (DEV). I run a command to launch SSMS and then login to a DEV server as follows: %windir%\system32\RUNAS.exe /smartcard /user:ABC@dev.com /netonly "C:\Program Files (x86)\Microsoft […] [Why would user account authentication issues arise in Azure?]( - Through a series of events too long to go into here, last year I was involved in helping to move a test SQL Server 2019 database from on-premises, to a VM in Azure. However, after it was migrated, they no longer needed me and that was the last I heard of or knew about it, […] SQL Server 2019 - Development [Using Dynamic SQL to execute Select from Openrowset with JSON]( - I have some code which will load in and process a JSON file using Select..from Openrowset as follows: Declare @JSON varchar(max) SELECT @JSON = BulkColumn FROM OPENROWSET (BULK 'C:\Users\User1\DR\staging\returns\RET02022203.JSON', SINGLE_CLOB) as j select @JSON i tried to turn this into Dynamic SQL so that I can pass in the path to the JSON file as […] [xp_cmdshell problem]( - Hi, i'm trying to run this command from within the SQL but i get this error 'C:\Program' is not recognized as an internal or external command, operable program or batch file. here is my code DECLARE @CONVERT_HTML_TO_PDF VARCHAR(4000) SET @CONVERT_HTML_TO_PDF = '"C:\Program Files\Microsoft SQL Server\MSSQL15.PRI\MSSQL\monitor_binaries\wkhtmltopdf.exe" "c:\new folder\1.html" "c:\new folder\1.pdf"' EXEC xp_cmdshell @CONVERT_HTML_TO_PDF what is wrong […] [Best way to handle incremental duplicate data in SQL Server?]( - I am processing .csv files daily which has persons data. Every person has a unique ID. My table has FileID and PersonUniqueID columns; it is working fine and saved to parsed table, i.e. with duplicate rows. Now would like to copy the unique data to new tables which is used by other applications. I can […] CosmosDB [To change the maximum throughput of an Azure Cosmos DB for NoSQL container]( - Hello,  I have a question, and please advise. When I wish to change the maximum throughput of an Azure Cosmos DB for NoSQL container from 4000 RU/s to 5000 RU/s, would that be sufficient if I use command: az cosmosdb sql container throughput update command --max-throughput ‘5000’ ???  Tanks in advanced  Reporting Services [Custom Authentication SSRS 2019]( - When trying to setup custom authentication on ssrs 2019 getting "ERR_CONNECTION_REFUSED " and this in the logs  ERROR: Error loading configuration file: The configuration file contains an element that is not valid. The #text element is not a configuration file element. library!DefaultDomain!13d0!08/02/2023-09:54:52:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration […] [SSRS Integration with Power BI]( - I am trying to integrate SSRS 2022 with Power BI. One of the steps is to go into Report Server Configuration Manager and go to the Power BI integration tab; however, I am not seeing a Power BI integration tab. Is there something special I need to do or install to see that tab?  […] Analysis Services [Date Difference of current record and its relative record using MDX]( - Dear All, I have a some rows where it points to another row in same table based on RelativeId column value. I need to find the difference of these two records date values using MDX. for ex. sample table structure with some values ID - HasRelative - Date - RelativeId 1 - False - 2023-01-01 […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -