Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Do something constructive to improve a difficult situation

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.

Remote Remote Work

A few years ago, one of my co-workers wanted to work remotely for the winter. They received permission and spent 3 or 4 months in France, waking up early to work for an hour or two, skiing in the late morning and early afternoon, and then coming back to work more at night.

I was quite jealous. Mostly because with family, this wasn't an option for me. Don't feel too bad for me as I get the chance to slide down mountains plenty during the winter.

During the pandemic, we've seen various locales try to entice workers to come live there, spend money, and work remotely. It's not simple or necessarily cheap, as many countries require workers to provide their own health coverage. However, it's nice that there is flexibility from some locales, and from some companies. There are tax implications, so moving residences isn't always possible.

I love the flexibility of working remotely, which I've been doing for 20 years. While I get to Redgate offices 4-6 times a year and work with others, most of the time I'm at home. Occasionally at a coffee shop or restaurant around town, but not too often.

Recently, I took advantage of this to work remotely away from home in another state. I scheduled a week trip to see my daughter play volleyball in college. Extended a weekend trip to reduce flight costs, I ended up working near her campus for a few days while she was in class. Remote from a remote spot away from the home office.

Great for finding some balance in life, but not without challenges. I found a few instances where I didn't have all the data I expected with me. The ergonomics were different, with hotel and coffee shop chairs and desks not being as comfortable as usual. One small screen instead of 3 large ones, no wrist rest, searching out power outlets, and other small items remind me of how much I appreciate the space I've set up at home.

Still, I'm glad I was able to go and visit, without taking holiday. I'd have been on my own most of the time, so spending the day working wasn't distracting. I hope to do this more in the future, and I hope to see more of you doing this as well to better enjoy life while continuing to earn a living.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

How to Use PowerShell to Track a Network Latency Issue in an Always On Availability Group

Vincent92 from SQLServerCentral

This article tracks down a problem with Availability Groups due to network bandwidth.

External Article

Sign up to PASS Data Community Summit 2022

Additional Articles from Redgate

There's still time to register for Summit and unlock access to over 300 sessions. Join peers and industry leaders in the data community homecoming. Get $200 off on the 3-Day Conference pass (in-person) with code REDGATEVIP or 50% off on the 3-Day online pass with code RGVIPONLINE

External Article

SQL Server Data Masking with DbDefence

Additional Articles from MSSQLTips.com

 

Blog Post

From the SQL Server Central Blogs - T-SQL Tuesday #155–Using Dynamic SQL for SQLCMD

Steve Jones - SSC Editor from The Voice of the DBA

It’s that time of month, and I’m the host this month. I wrote the invitation last week and now its’ time to answer. I’m actually using an example from...

Blog Post

From the SQL Server Central Blogs - PGSQL Phriday #001: Two Truths and a Lie

Grant Fritchey from The Scary DBA

As a part of my own journey of learning within PostgreSQL, I’ve decided that I’m going to take part in PGSQL Phriday as often as I can, just as...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Necessary Insert Columns

I have built this table in a database:
CREATE TABLE Car
( CarID            INT         NOT NULL IDENTITY(1, 1)
, CarMake          VARCHAR(50) NOT NULL
, CarModel         VARCHAR(50)
, AvailableForSale BIT         NOT NULL DEFAULT 1
, Options          VARCHAR(20) NOT NULL
, Color            VARCHAR(20));
GO
I want to insert a row into this table. How many columns must I specify in the insert statement?

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)

Committing the Work

I need to reverse two values in two different rows, and I want to ensure both changes complete. I run this code as the first code executed after connecting to the instance:

BEGIN TRANSACTION 
UPDATE dbo.ArticleSeries
 SET BannerImageFileID = 234
 WHERE ContentItemID = 10
UPDATE dbo.ArticleSeries
 SET BannerImageFileID = 235
 WHERE ContentItemID = 11
COMMIT WORK

What happens?

Answer: If both statements complete without error, the changes are committed.

Explanation: This actually is valid ANSI SQL. The COMMIT command has an optional "WORK" keyword that can be used. This cannot be used with named transactions, and COMMIT TRANSACTION should be used with named transactions. Ref: COMMIT - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/commit-work-transact-sql?view=sql-server-ver16

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 - Administration
Multi-Site Cluster with cloud File Share Witness - What happens? - Could someone advise on the following design? We have a 2-node cluster, Windows 2012, sitting in different Data Centres and different Subnets. The 3rd vote is a File Share Witness sitting on an IAAS VM in Azure....The question recently came on the following scenario In the scenario where Node 1 was hosting a SQL AG […]
How to find an actual table for a given wait resource? - Hi all, I am in a process of creation of Extended Events for blockings and deadlocks and reading information from respective .xel files in XML format. One of returned tag values is waitresource. In my cases it comes in variety of formats: KEY: 12:72057594052870144 (ce35eeb1d081) RID: 8:1:16771856:0 OBJECT: 8L1918629878 I need to find out the […]
SQL Server 2016 - Development and T-SQL
Weird behaviour with row level security policy - Hi guys, I am trying to figure out what is wrong with this implementation. I have a table called "POSTS" with 32 thousands rows inserted. This table has a column called "OWNER" which indicates the company of the user who inserted that post. I've created a security row policy which evaluates the rows and returns […]
Some questions about improving performance of Update query in SQL - Hi all, I have a following SQL query: UPDATE Individual_TEMP SET Individual_Id = CONCAT (Record_ID,'-',Individual_Id), Ind_PK_Value_Updated =1 where Ind_PK_Value_Updated is null; Objects involved in query: Table name: Individual_TEMP Column names: Individual_Id, Record_ID, Ind_PK_Value_Updated What I am trying to do with above Update query is that I am updating value of Individual_Id of the those records […]
SQL Server 2019 - Administration
Hiding SSRS Jobs does not work - Ahoy, i have a weird/annoying problem which i cant get my head around. We have implented the following solution to hide SSRS from the Jobhistory in the Agent. But somehow this does not work for my user/login, which means i still see them in the jobhistory. When my colleagues browse it, the SSRS stuff is […]
Alwayson availblity group on dev edition vs enterprise edition - We would like to set up two test instances for test alwaysOn availability group for disaster recovery and HA. They will both in our network, but in different physical location of data center. Since it needs Enterprise edition of SQL server, my question is for this testing purpose, in order not to buy license , […]
SQL Server Transaction Log Backup Fails Intermittently backing up to Azure - Just recently we have been receiving: BackupIORequest:ReportIOError: write failure on the backup device "url" Operating System Error 12175 (failed to retieve text for this error: Reason 15105) for transaction log backups. We backup the transaction log every 5 minutes and haven't had an issue with this until now. Just to be clear we are backing […]
Linked Server With SQL Authenticated Users - I'm facing an issue that has me stumped. Server A is linked to Server B via "be made using this security context" where the ID it uses is a SQL authenticated user on Server B which we'll refer to as LinkedUser. When I set up a data connection to Server A, and pull data from […]
SQL Server 2019 - Development
SSIS Pkg Load fails w/ column value containing multiple single quotes in data - Multiple single quotes in my column's data value is causing my SSIS pkg load to fail. I extract from a SQL 2016 table and load to a flat file. These 3 column nvarchar(80) values load successfully into my flat file: TE'SHAY and: ADD'L MODEM and: * HENRY'S MARKET This 1 column  nvarchar(80) value fails loading […]
Need to generate IDs from the MAX(ID) of a CTE - WITH MAXPRedID as ( SELECT MAX(ID) + 1 AS MaxID, SystemID FROM dbo.TableA GROUP BY SystemID) SELECT a.MaxID + COUNT(B.*), b.* FROM dbo.TableB b INNER JOIN MaxPredID a ON b.SystemID = a.SystemID If my MaxID is 15001 and I have 25 rows in tableB, I need to generate the new 25 IDs for starting at […]
Invalid because it is not contained either in aggregate function - Hi In below code it is giving error - column,status is invalid in the Select Statement because it is not contained either in aggregate function or Group By clause. Create View [dbo].[View_Summary] AS SELECT Max(S.[Name]), (Select count (ID) from Receipt where Id = D.Id and Status = "Receipt") as 'Receipt', (Select count (ID) from Issued […]
Amazon AWS and other cloud vendors
How to create glue scripts using Lambda function?? - Hi, Basically, working on a project which requires dynamic generation of ETL scripts using Lambda functions. These scripts are generated based on objects which define the operations required.   Does anyone know of any way that a Lambda function could generate scripts for ETL jobs, that would be stored in an S3, ready for invocation […]
General Cloud Computing Questions
Cloud interview questions - I have an interview coming up for a Cloud engineer/support Internship program. The email said they will be asking Linux and Networking questions just to get an idea of where I am at. I am currently a third year University student and I am majoring in networking and cyber security, however I wanna smash the […]
Reporting Services
Filtering on Stored Procedure Results - Currently, I have a stored procedure that produces a dataset.  A parameter value is passed in, and this causes the stored procedure to execute for each parameter value.  What I need is for the procedure to execute once without the parameter (I already modified the SQL code to process all values in one swoop), BUT […]
Integration Services
Converting word document to PDF via SSIS? - Hi all, In my SSIS project I have a variable which consists some string data. My goal is to convert this lines of data to a pdf file. What would be the best way to achieve this? I have created a File System Task which will create a word document with name like "Log_File_2022_10_18.docx". Now […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -