SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

@@CURSOR_ROWS -m Flag

The Voice of the DBA

Daily Coping Tip

Share what you are feeling and be willing to ask for help.

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.

Looking Back

Things always go wrong. Those of us that build or operate software know that we will have failures at times. These could be while applying a patch, deploying a new system, during a spike in traffic, or perhaps just a mechanical failure somewhere. Most of us fix things and move on, as there's always plenty of other work.

In a few organizations in which I've worked, whenever we had a large issue, we had a retrospective. I think we defined large issue as a VP or CTO become involved, but in any case, we sometimes had to have a retrospective meeting on why things went wrong. Usually these weren't blameless, or psychologically safe. They didn't end up being good meetings, nor did they serve to prevent future problems.

In today's software world where we want to adopt DevOps and build better software, we need retrospectives, both when problems occur, but also periodically as we finish major portions of work. We want to learn and become better at building software, so it pays to spend some time actually assessing how we work as a team. I ran across a post from Thoughtworks that talks a bit about how to make these better.

I don't completely agree with the post, and I certainly think that a retrospective won't solve all the issues we've had. Nor should it. I really see the growth and adaptation of a software team as taking time. When we find lots of problems, the key to making the software development process better is to decide on something to change and try it.

I'm all for an evolutionary approach for a couple reasons. The first is that change is hard and disruptive. If we can change just one or two things, then most of our work continues forward. I don't want a new set of coding techniques and a new build process at once. The second thing is that any change we make might have other consequences. We see this constantly in the world. We try to alter one thing and other parts of our process change. If we change too much at once, we might introduce lots of other problems.

Let's grow and change in small ways, but do so on a regular basis. This is both for the way we work as a team and get our software to customers, as well as the techniques we use to write code. Let's code better, refactor older code when we can, and learn to improve our skills and techniques each month. We'll have a healthier code base over the long term and a healthier team as well.

Steve Jones - SSC Editor

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

Featured Contents

Collections: Computed gaps for continuous numbers

Bogdan SAHLEAN from SQLServerCentral

Problem Finding the hole for continuous sequences of numbers could be a discouraging task even for advanced programmers. This article presents a new solution based on usage of geometry data type and on a unknown function (if not, maybe less used function) specific designed for geometry objects. Sample: Given the following sequence of [dis]continuous numbers […]

Tagging SQL Server Changes in SQL Monitor

Additional Articles from SQLServerCentral

How to use RAISERROR() in T-SQL to send annotations to SQL Monitor, so you can observe the direct impact of application tasks, or server changes, on the SQL Server metrics.

Streamline and Acclerate SQL Server Data Warehousing

Additional Articles from SQLServerCentral

In this presentation we’ll discuss the aspects of data automation and focus on the central goal of extracting the data that is locked in several operational systems, normalizing it to the enterprise's standards and sharing it with the business.

From the SQL Server Central Blogs - It’s Not April Fools – Redgate Streamed is Coming

Steve Jones - SSC Editor from The Voice of the DBA

Next week is April 1, typically April Fools Day, where we have jokes, pranks, and false stories. I’ve done my share of them, but this isn’t one. With the...

From the SQL Server Central Blogs - Why you should never use default string lengths.

Kenneth.Fisher from SQLStudies

TL;DR; You probably don’t know what you’re going to get. Even if you do know the person maintaining your code ... Continue reading

Question of the Day

Today's question (by MilesC):
	@@CURSOR_ROWS -m Flag
What does the –m flag do on the @@CURSOR_ROWS function?
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)

SQLCMD Output

I am working on a SQLCMD script. I have this written:

:setvar DirectoryRootPath "C:\Users\Documents\Log.txt"

:connect PlatoSQL2017

XXX

select * FROM dbo.Logger

What do I put in place of the XXX to get the data from the table into the file specified in my variable?

Answer: :out $(DirectoryRootPath)

Explanation: The OUT command will do this. The command needs to be preceded by a colon (:). Ref: Edit SQLCMD Scripts with Query Editor - https://docs.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?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 - Administration

Attempt to retrieve data for object failed - On Local Instance of SQL Server - I am getting this error in SQL Server Express 2017 when trying to script a table out to a new query. I can open it in design mode, run queries and so on but not generate scripts for tables... Attempt to retrieve data for object failed for Server '(LocalDB)\MSSQLLocaDB'. (Microsoft.SqlServer.Smo) Additional Information Invalid Urn filter […]

SQL Server 2017 - Development

Create Excel (XLSX) file from Stored Procedure - Hi, I've been searching for current code that will allow me to create an Excel file from the contents of a SQL Server table. I'm just looking for the simplest approach. Does anyone have working code they would be willing to share with me? We are on SQL Server 2017. Also, I want to be […]

Study Resources - Hi - I am originally a DBA who has been doing SQL DEV work for the last few years as a contractor - primarily SSIS / ETL/ reports projects. I'm wrapping up a contract imminently and am expecting to have some time on my hands while the market re-adjusts to the current pandemic situation, so […]

SQL Server 2016 - Administration

Using listener_name in 4 part naming convention gives sys.servers error - Hi, app uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name, but when I run a simple query like: select * from Listener_Name.DatabaseName.[schemaname].[objectname] go I get: Could not find server 'Listener_Name' in sys.servers. Verify […]

OPENROWSET question - Hello, Simple question, can the OPENROWSET if enabled, grant all access to databases only? or can i specify to only for a particular database?

SQL Server 2016 - Development and T-SQL

Database Deployment Scripts - I am wondering what people are using for automated deployment of database objects. We are agile, but only because we have sprints. We perform large about every 3 months and assign the stories to a release in TFS, so it is easy to figure out what scripts are being released. We also do smaller releases […]

Filling dates between the status changes - Hi , I have a table with a sample data like this. CREATE TABLE EMployeeStatus( EMployeeID Int, TranDate datetime, OldStatus varchar(50), NewStatus varchar(50) ) INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '07/11/2018', 'SUSPENDED', 'ACTIVE') INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '7/18/2018', […]

Importing csv file into SQL table - Looking for something like BULK insert CSV file to SQL table. But BULK insert assumes there is table already exist. Is there any other option to directly import csv file into SQL server (may be into temp table)...kinnda like SELECT * into . Problem is csv contains about 200 columns and sometimes they send less […]

SQL Server 2019 - Administration

SA account locked out/Single user turned on/SQL Server Agent stopped - Hey there all! So we have recently been having an issue at work. Started with our sqladmin account being locked out randomly. Searched the logs and we cannot find out where this is happening at. Now...We came into work our SQL server was changed to Single User mode. Next issue......SQL Server Agent decided to stop. […]

SQL Server 2019 - Development

Pivot Data based on the date range - Hi All, I have a dataset which has information about students and their registered courses. following is a temp table with a sample structure and data. DROP TABLE IF EXISTS #StudentData CREATE TABLE #StudentData (StudentID INT,CourseType VARCHAR(50),CourseID INT,SubscriptionStartDate DATE, SubscriptioneEndDate DATE) INSERT INTO #StudentData VALUES (1,'Basic',50,'10-15-2016','10-15-2017') INSERT INTO #StudentData VALUES (1,'Advanced',72,'10-15-2016','2018-12-31') INSERT INTO #StudentData VALUES […]

SQL Server 2008 - General

SSIS - Package failing after first run - Hello, I have created an SSIS package which calls a SQL PROC and exports the data to an Excel file. We use an Excel template within the ConnectionString name, then within the ExcelFilePath we change the name of the file and add a date. However, we are getting the error message at the bottom of […]

SQL Azure - Administration

Blocked Processes in Azure - Hey all, What's the appropriate way to setup a blocked processes report in Azure SQL? I see blocked process threshold is set to 20, this can't be changed right? There's no profiler, but data factory has a plugin. Do you have to use extended events out to a storage account? Basically I just want to setup […]

My Azure SQL Database Tips from real life - My Azure SQL Database Tips from real life ?? On premium subscription Restore 1.5 TB database to point in time takes ~ 10 hours Truncate 100 rows ,100K or 958M rows from table located in azure sql database takes same time ~2 seconds ?? DBCC SHRINKDATABASE will bring DTU to 100% for all execution period […]

General

How to calculate a "DUTCHING" formula with an odds on runner < 2.00 price? - I would provide a table creation script but I am getting the following error which I posted earlier > https://www.sqlservercentral.com/forums/topic/attempt-to-retrieve-data-for-object-failed-on-local-instance-of-sql-server Attempt to retrieve data for object failed for Server '(LocalDB)\MSSQLLocaDB'. (Microsoft.SqlServer.Smo) Additional Information Invalid Urn filter on server level: filter must be empty, or the server attribute must be equal with the true server name. […]

Integration Services

Refresh Power BI dataset using script task - Hi , I am trying to refresh the power bi dataset using the API in the Script task using C#, Can some one guide me with the right approach Thanks in Advance

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.