SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Output Parameters

The Voice of the DBA

Daily Coping Tip

Get natural light early in the day. Dim the lights in the evening

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.

Always Retry

When multiple people connect to a SQL Server database and attempt to query or update rows, blocking occurs. This is normal, and we expect a certain amount of this, usually very short lived. If it's not, then having tools that monitor your database and can quickly let you know which connection to kill is important.

There are also issues like network hiccups and deadlocks, which can cause a transaction to fail and roll back. In these cases, the application should retry a query, often quickly, without bothering the user. This isn't something that most developers code into their applications, though they should. However, is this something that lots and lots of developers ought to learn and re-implement over and over?

Microsoft has released a preview of a configurable retry logic in the SqlClinet driver for .NET (others coming soon). With this enhancement, developers can tell the driver how to react with some types of connectivity errors and perhaps resubmit the query. There are various options you can read about, and you ought to carefully test and you decide to use before you deploy them to production.

To me, this is long overdue. Software ought to work for us and make our work easier, including easier for software developers. Often I've heard many vendors point to the configuration options and flexibility of their software, tool, or framework, while placing the burden on developers to write a lot of the code to actually take advantage of the tool. Whenever possible, we ought to make the preferred choice, the best practice, the most common code an easy choice by making it easy to add to a system. Give people flexibility when they need it, but make it easy for them to see the benefits of your software quickly.

Steve Jones - SSC Editor

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

Featured Contents

Load Multiple Excel Files and Worksheets with SSIS

pramodh kumar tammana from SQLServerCentral

I was asked to load multiple Excel files, each with multiple Excel worksheets. I found a very easy way to complete this in SSIS using a Script Task. This article will explain how I did this. For this example, I have two Excel files, File1.xlsx and File2.xlsx, as shown here: Each Excel File has three […]

SQL Monitor v11.1 now supports Azure SQL Managed Instances

Additional Articles from Redgate

Migration to the cloud and PaaS solutions has accelerated in the past years, and many organizations now manage hybrid estates. To reflect this, the latest release of SQL Monitor adds Azure SQL Managed Instances to its list of supported cloud platforms. This article explains how to monitor performance and activity for Azure SQL Managed Instances using SQL Monitor, with advice on monitoring hybrid estates and migrating from on-prem to Azure SQL Managed Instance.

Power Apps Canvas Apps Examples

Additional Articles from MSSQLTips.com

In this article we will focus on Power Apps canvas apps which are natively optimized for mobile devices and we will look at potential use cases for canvas apps.

From the SQL Server Central Blogs - T-SQL Tuesday Retrospective #013: What the business wants

Randolph West from Born SQL with Randolph West

Click here to read previous retrospective entries. From Steve Jones (blog | Twitter) in December 2010 comes the question “What issues have you had in interacting with the business to get your...

From the SQL Server Central Blogs - Converting a SQL Server Docker image to a WSL2 Distribution

dbafromthecold@gmail.com from The DBA Who Came In From The Cold

Windows Subsystem for Linux is probably my favourite feature of Windows 10. It gives us the ability to run full blown linux distributions on our Windows 10 desktop. This...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Output Parameters
I have this simple stored procedure: CREATE OR ALTER PROCEDURE [dbo].[GetAsciiValue] ( @ASCII CHAR(1) output, @input INT ) AS BEGIN IF @input > 32 AND @input < 127 BEGIN SET @ASCII = CHAR (@input); RETURN; END; END; GO I want to call this and get the ASCII value back. I have this test script: DECLARE @val CHAR(1) = ' '; EXEC dbo.GetAsciiValue @val, 42; SELECT @val; This doesn't seem to work to get the ASCII value of the integer passed in. What is the problem?
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)

Sorting in R

There is a sort() method in R that you can use on various objects. What type of sort should I use with a vector of large integer values for the best performance?

Answer: Use the default sort algorithm

Explanation: The radix sort is the default sorting algorithm. This is a hash sort and is generally the fastest, Ref: Sort() - https://www.rdocumentation.org/packages/base/versions/3.5.1/topics/sort

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

SQL Server rename - Instead of SQL Server name I am thinking to use dns cname for connection string etc. Would this be possible to setup with named instances? If yes would there be any issues? My understanding is it can cause issues for connecting with SSIS and linked server?

SQL Server 2017 - Development

T-SQL code help - Hello! I am designing one query where I have to convert the value from one currency to another. The problem is for I have to calculate a value with following conditions: In that transaction row, if the current row currency (A) is not equal to the posting currency (B) then I will have to go […]

read value return from URL on page - Hi folks, I have a peculiar problem I am trying to find a solution to. We have developed a process of importing data which requires validating against a web server URL, which returns either a 0 or a specific string on a page. There is nothing else on the page, no graphics, HTML, or CSS, […]

SQL Server 2016 - Administration

fn_dump_dblog - Hi All I have been trying to figure out what has caused a spike in the size of a particular log backup. ' I am seeing entries for LOP_MODIFY_COLUMNS & LOP_MODIFY_ROW I found this in attempt to get an understanding... LOP_MODIFY_COLUMNS: Designates that a row was modified as the result of an Update command. LOP_MODIFY_ROW: […]

AG db Synchronized but not accessible - Hello experts, We have a report of a failed login for an application. The error is like this one: Login failed for user 'sql_login'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: ] This database is in an Always On Availability Group. The db is accessible on the secondaries, but not on the […]

Using Cluster Aware Update - Applying Windows Update to SQL Server part of AG - Is there any official Microsoft documentation around why Cluster aware update cannot be used to patch sql servers that are part of Availability group? I am completely aware of steps needed to patch sql server in AG using different methods.

SQL Server 2016 - Development and T-SQL

Problem using While to loop from record - Hello, I have build this While Statment to loop for record based on primary key DOSTAMP for later create an insert statment for each dostamp(In practice, for each document that have this primary key). I do something wrong because some dostamp are repeated on the print test This is my SQL While loop : DECLARE […]

Administration - SQL Server 2014

Database patching on SQL server 2014 reporting services - We have SQL server installed and it is up to date with respect to patching. Recently, we have installed reporting service on the same default instance. Please let me know how we can patch reporting services only. When I tried to apply SP3, I can see the whole instance along with database engine is getting […]

Camunda & Database - Hi Experts, We are planning to implement camunda BPNM and the SQL server is going to be our production one running all application database. Can anyone enlighten me whether Camunda will be a memory hog and will cause performance issues ? TIA

SQL Server 2019 - Administration

BMC Remedy Service Desk Integration with Sailpoint - I am working on a new project and need some information. We are going to integrate sailpoint with BMC Remedy Service desk. we would like to give access to user through Remedy, Where we provide access request and then sailpoint provision them to the defined (groups or entitlements). I would like to get more knowledge […]

SQL server 2019 installation CU 10 where to upload and install on my server - Hello where can I find to apply latest CU thank you

sql server 2019 standard edition intallation question - for installation sql server standard edition for instance id should I put server name? lets say I have as 12345inv server name would that work? because by default it goes mssqlserver when i installed wasnt able to log in to the sql serverer so I un installed and not sure how what to put for […]

SQL Server 2019 - Development

Help with a query - Hi all! I want to create a stored procedure that returns a result based on the value of some DateTime parameters. Table format is: CREATE TABLE [dbo].[Contracte]( [id] [int] IDENTITY(1,1) NOT NULL, [NrContract] [int] NOT NULL, [DataContract] [datetime] NOT NULL, [idOrganizator] [int] NOT NULL, [idExcursie] [int] NOT NULL, [idUser] [int] NOT NULL, CONSTRAINT [PK_Contracte] PRIMARY […]

Reporting Services

SSRS not delivering all reports at the same time. - I have several data driven subscriptions running throughout the day delivering anywhere from 1 to 50+ reports to a file share. On those subscriptions that deliver 30+ reports, randomly it will deliver all but a handful and then deliver the rest when the next subscription runs. I have looked at the Execution logs and see […]

Connecting

connect to mdf file of localDB - Potentially dumb question. There is 1 x ldf & 1 x mdf for DB1. LocalDB. How do I connect to these? I am unfamiliar with localDB. If I run [sqllocaldb.exe i] - there are 2 other instances but not those using the log & data files I want to look at. Is there a way […]

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.