How to Decipher @@Options (2021-01-08)

 SQLServerCentral Newsletter for January 8, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Decipher @@Options]( - [The Joy of Realistic Generated Fake Database Data]( - [SQL Server Error Log]( - [From the SQL Server Central Blogs - Saving PASS Pro Videos]( - [From the SQL Server Central Blogs - Powerful Monitoring for Your Linked Servers with XEvents]( Question of the Day - [Sending Your Local Branch Up]( The Voice of the DBA  Daily Coping Tip Make time to do something kind for yourself. 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. Improving AGs [Availability Groups]( (AG)were introduced in SQL Server 2012, with the idea that we could dramatically improve (and ease) the burden of dealing with high availability in SQL Server. At the time the (code named) HADRON technology seemed full of possibilities. Since then, there have been some enhancements, but it seems that setting up and managing an AG, especially across subnets, isn't as simple as Microsoft would have us believe. One of the problems with AGs is that there are non database resources (logins, jobs, etc.) that create dependencies. Working around the issues is a headache for many administrators, and it shouldn't be. While there are [some enhancements potentially coming](, I don't know what shape these will take or if they will make things easier. Some of my previous work as a DBA relied heavily on SQL Agent and jobs, neither of which are handled by AGs, or by plenty of other HA/DR technologies. Instead, administrators cobble things together, save scripts, and manually repair issues after failover. To me, this is one area that I'd hope Microsoft enhanced for AGs. Another area is the listener, which seems to be brittle. It works great, or it's a nightmare to get working, without always an easy way to debug. I'd certainly welcome improvements here, including the ability for tooling to support multiple listeners easily. Many of you work with AGs now, and many more of you may in the future as the need for HA grows all the time for databases. Are there improvements you'd like to see in AGs, or any other SQL Server HA/DR technology? Feel free to leave a comment or [submit something to Microsoft](. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [How to Decipher @@Options]( jonfrisbee from SQLServerCentral This article will show how you can read the @@OPTIONS variable and determine your settings. [The Joy of Realistic Generated Fake Database Data]( Additional Articles from Redgate Have you ever had to demonstrate a database-driven application, and longed to have the real data to do so? To do what, precisely? Well, so you can then scroll through the customers who have used the system, demonstrate the accounting and audit, browse through the products, maybe even demo the customer tracking system with comments, complaints and so on. All this is possible, using realistic, fake data. [SQL Server Error Log]( Additional Articles from SimpleTalk The SQL Server error log is a great source of information for DBAs for monitoring errors and the operation of the server. In this article, Greg Larsen explains the details and how to configure the SQL Server error log. From the SQL Server Central Blogs - [Saving PASS Pro Videos]( Steve Jones - SSC Editor from The Voice of the DBA PASS will close down their websites on 15 Jan 2021. That’s just a couple weeks away, and if you’re someone that paid for a year of PASS Pro access,... From the SQL Server Central Blogs - [Powerful Monitoring for Your Linked Servers with XEvents]( SQLRNNR from SQL RNNR Linked Servers can certainly provide an enigma to your finely tuned Database environment. In this article, I share how to decrypt that enigma via XEvents. The post Powerful Monitoring...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Sending Your Local Branch Up I clone a Git repository and am on the main branch. I have created a new branch in Git with this code: git branch featureSteve I now add some code to the repo and commit it. However, I've made a mistake. What is it?  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) Masking with BCP I have a normal user that wants to export some product data. When this user queries the table in SSMS, they see masked data, such as this: 10000023 BaskeXXXXXX 460 0.8027 10000024 InnerXXXXXX 199 0.3597 10000025 XXXXXX 390 0.8232 10000026 TableXXXXXX 2238 0.4413 This user wants to bcp out using this code: bcp "ProductDetails" out productexport.txt -S SQL01 -c When they get the export, what will the file look like? Answer: It will look the same as the query in SSMS Explanation: The masking is handled by the query processor, and this includes bcp. Ref: Moving Dynamic Data Masked Data to New Locations - Step 5 of the Stairway to Dynamic Data Masking -  [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 - Administration [Exec SSIS package from TSQL Agent Job Step]( - Hello, I have a SSIS Package deployed to the SSISDB Catalog. I have a stored procedure setup to create an execution, pass in a few variables/parameters and then start the execution. I created a tsql jobstep to call the stored procedure. This works as long as the agent job is owned by an SA user. […] SQL Server 2017 - Development [BCP to Export Query to Pipe Delimited File]( - Dear Group: I have a requirement to create a SQL Job that exports a query to a Pipe / Vertical bar delimited file (|) and save it on a network drive in either *.txt or *.csv format. Right now, I am just trying to get this to work inside SSMS, calling the BCP command and […] SQL Server 2016 - Administration [How to upgrade to 2016 AlwaysON from SQL Server 2012 ?.]( - Hello, I'm looking for the best way to go from existing 2012 Enterprise AlwaysON Cluster to SQL 2016 Enterprise AlwaysON . Existing setup: 2 nodes with OS 2012R2 and SQL Server 2012 Enterprise on latest service pack. Goal for new setup: Purchased 2 new servers that I want to be the final 2 new servers. […] SQL Server 2016 - Development and T-SQL [Query Code returning incorrect results]( - Hello Community, I have the following logic: Find customers who didn't place orders from 2019-02-01 to 2019-03-01. Output customer's first name. I am working with the following dataset: CREATE TABLE #tmpTable ( id int, first_name varchar(50), last_name varchar(50), city varchar(50), address varchar(50), phone_number varchar(50), id int, cust_id int, order_date date, order_quantity int, order_details varchar(50), order_cost […] [ORDER BY using successful thousand comma separator]( - This query requires ActualTotal to be ordered with largest value top however as you can see from the screengrab, this hasn't worked completely. Is there a way to achieve this? Here is my current code: SELECT m.name, FORMAT(x.ComTotal,'N0') AS Total_2020, FORMAT(y.Totals,'N0') AS Some_2020, FORMAT(x.ComToTal + y.Totals,'N0') AS ActualTotal FROM Mees m LEFT JOIN View_2020_Listx_Jan2020 x […] [Odd query question i did some basic digging but didn't find anything relevant.]( - SQL Server 2016 cursor and query: select * from table1 where col1 = '1' and col2 = '00001' and col3='I' and col4 = 'JULY--2015' this is a dynamically created query and for the most part the col1, col2 and col3 stay the same only the col4 changes in this test case. while all the other […] [SQL query for First swipe in and Last Swipe Out for each day for each pay period]( - Hi, "EMPLOYEE" table has EmpName,EMPID . "SWIPE" table has SwipeTime_UTC,Swipe_location,EmpID . Note:Swipein and swipeout locations are different. SwipeTime_UTC is UTC time format. Looking for SQL query to generate the first swipe in and last swipe out for each employee on each day for given pay period.(Display time should be in EST). Any suggestions pls. Thanks. Development - SQL Server 2014 [Need to insert differences between Staging and Working tables into the Working T]( - Hi, Still kind of a newbie to SQL Server. Anyway I have two tables: tblStaging and tblWorking. The staging table is updated every 15 minutes and I need to look at what's different in the Staging table and insert the differences into the Working table. I have a combination of two fields that guarantee uniqueness. […] SQL 2012 - General [At wits end with Service Master Key, please help.]( - Recently our linked servers are failing due to the following "Generic failure...Service Master Key..." So knowing what I know I need to regenerate the Service Master Key using: ALTER SERVICE MASTER KEY FORCE REGENERATE Which I get the following: The current master key cannot be decrypted. The error was ignored because the FORCE option was […] [How to get duplicate on PartId and Company Id both and have different Status ?]( - How to get duplicate on Part Id and Company Id both and have different Status ? I work on SQL server 2012 I have issue I can't get duplicate on company id and part id and on same time have different status as partid 1211 and companyid 3030 repeated two times meaning two rows but […] [There is insufficient system memory in resource pool]( - Not a DBA here. Now that that is out of the way... I am troubleshooting an issue where our SQL service is stopping. We are on SQL 2012 Express x64 11.0.2100.60 running on Windows Server 2008 R2 Standard x64. The Windows application event logs show: -Event ID 701: There is insufficient system memory in resource […] SQL Server 2012 - T-SQL [Last Column value at the maximum value of Cond Column]( - Good morning,  I'm trying to get the Temp value at the maximum value of the Cond column. The red line in the first query is what I'm expecting, the second one its the result I'm getting at the moment.  Current query: SELECT TOP (1) DateTime, CASE WHEN TagName = 'WFI_LP01_Cond.PV' THEN value END […] SQL Server 2019 - Development [CONCAT and CASE WHEN combined]( - Hi everyone, I'm trying to create a query that concatenates the directions field that were involved in each transaction_id. I've tried to do it by creating a flag table with multiple case whens, and then querying on it, but as you can see in the image below, I'm having trouble to get a multiple 1's […] Reporting Services [Execute Stored Procedure (with no Fields)]( - Hi Guys, I'm searching for a while now, but can't find a solution. We have a ssrs project where we are using Reports for Dashboarding. To do Right Management, we have a function for each table, these functions has a so called "Token" wich represents a specific user. As these Tokens have a timeout, there […] General [How often have you needed to start a sequence of numbers at other than 1 or 0?]( - Itzik Ben-Gan (definitely one very smart guy in the world of SQL Server and T-SQL) published an interesting challenge back on the 9th of December, 2020. You can find that challenge at the following link... To summarize... Itzik posted the basis of his world-famous "GetNums" function, which generates a sequence of numbers. The version […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -