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

Daily Coping Tip

Remember we all struggle at times as humans

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.

Do as I say, not as I do

A common saying from parents, teachers, and many managers, is that you should follow their instructions and not necessarily their behavior. This is a very human thing to do, with many of us struggling to follow the behavior that we ourselves want. Instead, we follow the vagaries of our moods and desires. We do this even as we tell others to do things that we don't bother to do.

It's not just human behavior, but it applies to how many companies deal with their customers. Microsoft talks often about taking advantage of new features in code and using the platform to solve problems. They dislike adding simple "syntactic sugar" (like a numbers table), and instead prefer you build the code to handle some of these simple tasks.

However, they don't really follow this advice, as Andy Mallon showed with a recent post on why not to use a couple of their "recommended" stored procedures. They're not well written for modern code, they have limitations (or bugs), and could be considered a security risk.

To be fair, I know that changing code in something that works is always dicey, but at the very least, moving from varchar() to nvarchar() shouldn't break anything. If there are edge cases, then write some tests and rebuild the code to work better. Maybe, more importantly, these procedures ought to model good code, as Microsoft would recommend to their customers.

There are a lot of places where different products at Microsoft might not use SQL Server well, and I understand. These might be software developers that don't know a lot about how to perform good data modeling or even how to take advantage of SQL Server code. However, at a company with the resources Microsoft has, I'd expect some teams formed to handle these tasks that then review and suggest changes to their software, like Dynamics, Sharepoint, etc. Even if they can't use the latest features in the SQL Server codebase, they ought to model good practices for all versions.

For many of us, we might act similarly inside a company. Often we write code out of habit, and perhaps, to expeditiously get work completed, even when we know better. Using SELECT *, leaving out error handling, and more are habits that far too many of us embrace, far too often.

Start making some changes today. If you know there are better practices you should follow, then take the few extra moments to implement them. If you don't know of good practices, start compiling a list, asking questions, even post an idea or question in the discussion for this editorial. We all could write better code, and that starts with us actually making an effort to model the behavior we might preach to others.

Steve Jones - SSC Editor

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

 
  Featured Contents

Merge Multiple Files in Azure Data Factory

arindamxs from SQLServerCentral

A short tutorial on how to combine multiple files together in an Azure Data Factory Copy Data activity.

Using Data Scanning to Identify and Classify Sensitive Columns

Additional Articles from Redgate

SQL Data Catalog's new data scanning feature uses regular expressions and data dictionaries to identify where sensitive and personal data is stored in your databases.

Typical uses of Oracle sequences

Additional Articles from SimpleTalk

Oracle sequences supply unique numbers that can be used when populating tables. In this article, Jonathan Lewis explains the typical uses of Oracle sequences.

From the SQL Server Central Blogs - Using GitHub Actions to build packer AMI on AWS

Adetokunbo Ige from Adetokunbo Ige

GitHub Actions is a very cool tool for automating CI/CD pipeline workflows or any routine task. Once the code resides in Github, automating tasks from using Github actions is...

From the SQL Server Central Blogs - Quickly Identify Configuration Drift In Your Environment

gbargsley from GarryBargsley

As you sit and wonder about when the next Star Wars movie is going to come out, do you ever get the thought of “I wonder if all my...

 

  Question of the Day

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

 

Multiple Lambda Parameters in Python

I want to create a python lambda expression that will concatenate two parameters into a string with proper casing. I want the parameters to be givenname and surname. The formula I will use is:
f'{sname.title()}, {gname.title()}'
How do I define a lambda with two parameters?

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)

The Double Table

I have this code:

CREATE TABLE dbo.DoubleTable ( myid INT NOT NULL CONSTRAINT DoubleTable PRIMARY KEY , Val VARCHAR(20)); GO 

When I run this, I get this error:

Msg 2714, Level 16, State 5, Line 19 There is already an object named 'DoubleTable' in the database.

Msg 1750, Level 16, State 1, Line 19 Could not create constraint or index. See previous errors.

Why?

Answer: There is an object name conflict

Explanation: There isn't another table already, but there is a table created by the CREATE TABLE statement, and then when the constraint tries to be created, there is an object name collision. The constraint and the table cannot have the same name. Ref:  Table Constraints - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql?view=sql-server-ver15

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
Replace hexidecimal value wih '§' or blank in a TEXT field - I have a DB with a field defined as: dag_text text allow nulls I have an hexadecimal value (0x15) placed some hundred places in the table. It should be replaced with '§' . In comes REPLACE, but it does not work on text fields. the offending tekst look like(Its the that should be replaces with […]
Conditional Update of Column - I can't remember ever trying this before, but I have need to do it now. I need to update a specific column in a table based on data in another table. The column to be updated varies. Some code will explain better: DROP TABLE IF EXISTS #sample; CREATE TABLE #sample ( PK CHAR(3) NOT NULL […]
SQL Server 2016 - Administration
How to limit SQL Job Success events - Hi, We have a monitoring tool that reads Windows Event Log entries for Failure and Success of SQL Job executions. So, we have setup MSSQL jobs to write to the Windows Event Log when they fail or complete successfully. This works okay, however for jobs that run at a very high frequency I find that […]
CPU and Memory issues - I am a DBA  trying to get to the bottom of some instances when we have hit high CPU for a sustained period impacting performance. On our spotlight monitoring tool we use, when we hit 100 CPU for a sustained time I see that the procedure cache has dropped significantly from 5GB down to MBs, […]
SQL Server 2019 - Administration
Batchmode on rowstore: deadlock - Are there known issues with Batchmode on rowstore in CU11? One specific complex query is getting "Transaction (Process ID 65) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction." The deadlock sofar triggers when the optimizer decides to go for BatchModeOnRowStoreUsed="true" OPTION(USE HINT('DISALLOW_BATCH_MODE')) […]
Error shrinking database - While trying to shrink a database using the command "dbcc shrinkdatabase ([db_name])", I get the following message "DBCC SHRINKDATABASE: File ID 1 of database ID 13 was skipped because the file does not have enough free space to reclaim." That seems to be case with few other DBs too. I was web searching and came […]
Are Powerplans still relevant for VM servers? - Hello, I am rolling out a new monitoring environment.  While in testing, it is alerting on high performance plan not enabled. I did some research and found a lot of articles on improving SQL Server performance changing the powerplan from balanced to high performance. When I asked our platform team about the power plans, they […]
Login failed: Password did not match that for the login provided. - I just installed a new instance of SQL Developer Edition - All my apps run on SQL Express and I am testing them on the new Edition. My Web Service connects to the new instance, my integration tests can access the new instance... However For a Windows Service - I get this error every time […]
SQL Server 2019 - Development
Having sum(amount) = 0 returns negatives as well. - Hello all, I'm trying to get a list of ID's that have a total sum of their amount column = to zero... But I'm getting a lot of negative numbers, but no positives. So, for example, I might get an ID that has two rows in total which if you add up their amounts it […]
Monitor data and update it conditionally - Hi, Is there a way for monitoring data in a table and update it when it exists in the table longer than a period of time? For example, a table has a column called "measure", if the column contained value > 0 longer than 15 minutes then update it to be 0. Any help would […]
Azure Data Factory
Extract bearer token from a string ADF and APIs - Hi all I just wondered if there was a way to extract only the bearer token of the below string into a variable. I have a variable @activity('GetToken').output.accessToken When I run the task I get the following value into my variable. { "name": "token", "value": "eyJhbGciOiJSUzI1NiJ9.eyJqdGkiOiI0MDAiLCJpYXQiOjE2Mjc1NTM1MTcsImlzcyI6ImF" } What I want to do now though is […]
Reporting Services
Aging report with days parameter - I created an aging report for a case management system with a single parameter @days with values 30,60,90 etc. and the following where clause WHERE DateOpened <= Getdate() - @days This worked fine but the user rather than return rows for the last 60 or 90 days wants cases between 30-60 days or 60-90 days. […]
General
SELECT records from table 1 that does not exist in table 2 via UNION - Sorry if this seems too beginner; I have been stuck at this for days and for some reason couldn't figure out the answer. Suppose I have tbl Customers and tbl Orders.  The relationship is that 1 customer can have many or no orders. I have to use UNION, to retrieve customer records that does not […]
Powershell
SQL Server Agent producing unreproducible error - I've built a Powershell script, which I want to run through SQL Server Agent. The script works fine in multiple environments: My own local environment using Powershell 7.1 My own local environment using Powershell 5 SQL Server host using Powershell 4 As soon as, however, I ask the script to be run through agent it […]
BulkCopy error - I'm trying to load a csv to SQL using the BULKCOPY, but running into a issue load the string value into a Float within SQL I build a Data Table then load from there. The data from the csv has values like .247888 Thanks.
 

 

RSS FeedTwitter

This email has been sent to newsletter@newslettercollector.com. 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

 

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