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

The Value of Doing Something

I live on a working horse ranch. My wife boards, trains, and trims horses and has employees. Occasionally I have to help out with chores, or more often, fixing things. I was talking with a coworker recently and showing some pictures. He was asking why I do things like tap a bolt instead of hiring someone to do it.

I caught this except from a book on cooking, which got me thinking. I don't love the flow here, as it wanders between cooking, economics, politics, social justice, and more. However, the core ideas of specialization and taking on a task was what I got out of the piece.

I work for a software company, and certainly, I make much more doing that than I save by cooking a meal or fixing a bolt. I could hire someone at much less than my hourly rate to do the work. Except, I don't get paid more for working more. Maybe if I do more sales and get a quota, but for now an extra hour of work hasn't helped me.

Then there's the satisfaction of getting something done. I talk and write and move bits around all day; nothing that has a tangible result. It can be satisfying but in a very different way than cooking or physically finishing a task. I actually like getting my hands dirty and seeing the results of my work. Seeing a new fence go up, a tree come down, or a repaired bolt brings about pride. I feel good when my family appreciates a tasty recipe and everyone cleans their plates. It's a nice challenge.

At the same time, routine maintenance, like changing oil, sometimes feels like doing an expense report. Needs to be done, it helps me, but there's nothing very exciting about it.

In the era of specialization, or even the era of automation and AI tools, it might seem that we don't need to learn or work on anything outside of our area of expertise. I think it's important to practice, learn, and use a variety of different skills. Even if I don't need to write PoSh often, it is helpful to sometimes tackle work that needs doing. Each bit of new knowledge brings new understanding and appreciation for the work others do.

It also helps me gain confidence in my ability to learn and use new technologies should the requirement arise. This has also served me in past jobs where I can step in and help others or even tackle a job when they aren't available. This is something that I find far too many people struggling with. Either because of a lack of confidence or not having enough general skills that help them troubleshoot, build, or even support the vastly changing environments in which we work.

Doing something old, new, challenging, or easy is good for all of us. Try something new, while you also appreciate and use the skills you have. Believe in your ability to learn and prove it to yourself on a regular basis.

Steve Jones - SSC Editor

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

 
  Featured Contents
SQLServerCentral Article

Let Your Data Do the Talking with PowerPoint Plus Power BI

MagdaBro from SQLServerCentral

What is this about This article will be about Power BI and PowerPoint collaborating. Both tools are well-known to anyone, particularly to those who are presenting data, charts and visualizations. Without a doubt, these are powerful tools for communicating and visually transmitting information. We can use them to hand over complex information in a simple, […]

Technical Article

PASS Data Community Summit

Additional Articles from PASS

Keynote speakers for Summit 2023 have been announced! Who will you go see on the main stage? If you’re not already on the mailing list, sign up now and be the first to know all Summit 2023 updates.

External Article

Using Virtual Network Data Gateways to Secure Access to Azure SQL in Power BI

Additional Articles from SimpleTalk

Power BI and Azure are two related services, but with a considerable independence between them. Power BI share the Azure Tenant for security management, but in relation to networking, they are in completely different environments.

Blog Post

From the SQL Server Central Blogs - Restore SQL Server Backup to Docker Container

hellosqlkitty from SQLKitty

Restoring a SQL Server backup in a Docker container is quite easy. To begin with, you will need persistent storage on your Docker container to follow this blog post....

Blog Post

From the SQL Server Central Blogs - Heraflux and Rubrik Webinar – July 25

kleegeek from Technobabble by Klee from @kleegeek

Heraflux is returning to deliver a new webinar with Rubrik and ActualTech Media titled “How to Secure Your Azure SQL Data.”  With Azure SQL Server offerings continuing to grow...

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):

 

Roles Across Schemas

I ran this code in a new database:
CREATE TABLE dbo.mytable (myid INT) 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 CREATE LOGIN apiuser WITH PASSWORD = 'Demo1234' GO CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi GO ALTER ROLE db_datareader ADD MEMBER apiuser GO 
Next, I log in as the apiuser and change to this new database. I run this:
SELECT TOP 10 s.stateid, s.statecode, s.statename FROM webapi.states AS s; GO SELECT myid FROM mytable; 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)

Resuming Indexes on Old Databases

I have a SQL Server 2019 developer edition instance with a database that is set to compatibility mode 130. I run this code:

CREATE NONCLUSTERED INDEX [NC] ON [dbo].[RandomTestData] ( [ID] ASC, [DateOfBirth] ASC )WITH (ONLINE = ON,RESUMABLE = ON) GO

What happens?

Answer: The index creation starts and can be paused

Explanation: In testing, this appears to work with SQL Server 2019, regardless of the compatibility level. Even with 100 set, there is no syntax error and I can pause and resume the index. The image below shows me checking the compat level and then resuming the index. resuming index operation Ref: CREATE INDEX - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-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 2017 - Administration
Log Shipping Jobs stop working automatically - Dear Friends, On my Log Shipping configuration , sometimes , more frequently actually, the Backup,Copy and Restore Jobs stop working automatically.. I have to check on them and run manually...and some times the restore has errors which force me to reconfigure it again..Any comments / advises on this will be highly helpful..Thank you.
Trace Flag 3444 - I just discovered trace flag 3444 set on a SQL 2017 srver, and I can't find reference to it. Has anyone come across this trace flag and what is it used for? Thanks MC
SQL Server 2016 - Administration
Need to read ErrorLog to get the END TIME of last restore? - is there a stored proc somewhere for sql server 2016 that can return latest restore database duration in seconds and in minutes for each database on the server? I cannot find a ready code, and the GPT cannot help. it is hard to believe though that there is no working code somewhere. I looked and […]
SQL Server 2016 - Development and T-SQL
WITH RECURSIVE gives error - WITH RECURSIVE factorial(F,n) AS ( SELECT 1 F, 3 n UNION ALL SELECT F*n F, n-1 n from factorial where n>1 ) SELECT F from factorial where n=1   Error Msg 102, Level 15, State 1, Line 26 Incorrect syntax near 'factorial'. Completion time: 2023-07-07T14:16:59.5379702-04:00 If i remove RECURSIVE option it works fine.
SQL Server 2019 - Administration
[SSIS Server Maintenance Job] failed - Executed as user: ##MS_SSISServerCleanupJobLogin##. The SELECT permission was denied on the object 'availability_databases_cluster', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229). any help with this will be appreciated!
SQL Server 2019 - Development
List Month End Dates Between Two Dates - Hi everyone I am working on a query where I need to list all business month end dates between two dates.  The two dates would define the start and end periods but these two dates are dynamically created: DECLARE @START_DATE DATE = DATEADD(YEAR,-1,GETDATE()) DECLARE @END_DATE DATE = GETDATE() I am not sure how to generate […]
sql Convert datype int to string - hallo all, let me ask i have a query convert like this : INSERT INTO STG_RECOVERY (MODEL, VALID_ON, NOREK, KOL_AWAL, KOL_AKHIR, BD_AWAL, BD_AKHIR, BD_KL_AWAL, BD_KL_AKHIR, AMOUNT, RECOVERY, RECOVERY_PCT) SELECT 3, A.validon VALID_ON, A.Acct, A.BIKOLE KOL_AWAL, B.BIKOLE KOL_AKHIR, A.BAKI_DEBET BD_AWAL, B.BAKI_DEBET BD_AKHIR, NULL BD_KL_AWAL, NULL BD_KL_AKHIR, C.BD_KL AMOUNT, A.BAKI_DEBET - B.BAKI_DEBET RECOVERY, ((A.BAKI_DEBET - B.BAKI_DEBET) / […]
getting next date - Is there a way to show the next date that is greater but not have them duplicate like the highlighted below? I am using sql server and doing a select from the columns below where the service date is greater than da discharge date.     This is what I want it to show. It […]
Remove duplicate data - Hi Below is the create table and insert script. I am attaching current output and expected output. USE master GO CREATE TABLE [dbo].[test_07052023]( [EMAILS SENT] [float] NULL, [mmClientID] [float] NULL, [Report Run Date] [nvarchar](4000) NULL, [Issue Name] [varchar](4) NOT NULL, [Issue Year] [int] NOT NULL, [Product] [varchar](17) NOT NULL, [Rate Card Name] [varchar](9) NOT NULL, […]
Integration Services
deploy ssis package to MSDB db in SSIS server using azure pipeline - Has anyone tried deploying ssis to MSDB db in SSIS server using Azure pipeline?  I am used to do the file system deployment using Azure pipeline but not to MSDB db. Din't find any good article on this on the internet.
Anything that is NOT about SQL!
Need recommendations on replacing a NAS device for home use. - In my home office (SQL Server DBA, now a 13-year retiree) I have two NAS devices, a D-Link DNS-343 (4x2TB) and a WD EX4100 (4x10TB). I had to take the WD NAS off my surge protectors due to repeated 'power supply failure' notices, and it has worked since with no problem. My Win 10 machine […]
Administration
Transaction log BU fails in Maintenance Plan fails when there is no Full BU - I have 3 maintenance plans for full backups, differential and transaction log. The job for log backups fails in when there is no full backup. Which is logical of course. But is there a way to prevent this error from happening? I would like an option that it will not try to backup the log […]
SQL Server 2022 - Administration
RAISERROR Serverity 16 Showing in SQL Agent Log - We are evaluating SQL Server 2022 and have upgraded an SQL Server Fail Over Cluster and AG with with CU 4. In order not to run the jobs created by Reporting services on the passive node, we have added a step to check to all SQL Agent Jobs that does a RAISERROR to exit out […]
SQL Server 2022 - Development
SQL Server AOAG Add 3 IP's to LIstener with Powershell Script Not working - Team, Why does the below not work? Using parameters for listener name and ip addresses. When i hard code the IP addresses in there it works even with the listener as a parameter . I assume it has to do with the quotes. Any assistance is greatly appreciated. See full section of code not including […]
Issue with sql server stored procedure--``i need to update multiple columns from - use dB DROP PROCEDURE [dbo].[pre_update] GO SET QUOTED_IDENTIFIER ON CREATE TYPE GetTrackingNo AS TABLE (TrackingNo Int ); go CREATE PROCEDURE pre_update @TVP GetTrackingNO READONLY AS Begin SET NOCOUNT ON; DECLARE @trackingTVP AS GetTrackingtNO ,@col1 VARCHAR (MAX) ,@col2 VARCHAR(100) ,@col3 VARCHAR(MAX) your text insert into @TrackingTVP (TrackingNO) (select distinct TrackingNO from sub where date between '2023-01-01' […]
 

 

RSS FeedTwitter

This email has been sent to [email protected]. To be removed from this list, please click here. If you have any problems leaving the list, please contact the [email protected]. 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.
[email protected]

 

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