SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Adding More Columns

Featured Script

Output multiple datasets from one INVOKE-SQLCMD command

The Voice of the DBA

Daily Coping Tip

Thank three people you’re grateful to and tell them why

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.

A Remote Work Guide

It seems that the vast majority of people I know in the data professional world are working from home these days. Actually, I think more people are working remotely than ever before. I don't know how much will carry over into the future, but I certainly think more companies will think about having people work from home more often. This might be the thing that finally gets many managers accepting remote work.

Across the last month I've seen lots of advice and thoughts about how to adjust to remote work. I've been full time at home for seventeen years at this point and my wife did it for nearly twenty. We've had a lot of experience, and learned a lot in that time. While thinking about the advice I've give to others, I noticed this guide from Gitlab for their employees on adjusting to working at home.

I think it's good advice, though certainly finding space and finding separation between work and home life can be tough. I think it took me 2-3 months to really adjust to the new routine when I started, and even now, I can struggle at times. Usually I have the gym, coaching, travel, and other activities to break up life. That's not the case now, and this tweet from Scott Hanselman sums up things for me. This isn't the same as my routine has been for over a decade. This adjustment is hard, and we should allow for fits and starts from both ourselves and others.

Many of us technical people are somewhat introverted. I know I am, despite all the public speaking I do. It's easy to retreat a bit into our own world right now, which isn't necessarily the best thing. As the post notes, experiment with some changes and new things. I have to say that I never thought a virtual coffee break or happy hour would be something I'd try, but I look forward to my 9am happy hour with Redgate staff every Friday. Don't stop engaging, and as you find structure, experiment a little here and there. Loneliness can creep up on you.

If you're a forum person, the SQLServerCentral forums have some great discussions. Feel free to join in to something fun, like coping, baseball, or anything not about SQL. If you prefer video, I'd urge you to reach out to a friend or family member for a quick chat. I've made it a point to reach out to a few people every couple days, usually text or messaging, just to check in and say hi. It's been helpful to me.

Remote work is a little challenge, but this isn't the same as normal telecommuting. Try some different things, build some routine, and remember to take care of yourself and your family in this challenging time. If you have tips or tricks that have worked for you, especially those of you in small spaces, share them with others in a comment today.

Steve Jones - SSC Editor

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

Featured Contents

Representing XML data in SQL Server

diogosouzac from SQLServerCentral.com

In this article, we will see how to use the FOR XML statement in SQL Server to represent the result of queries in XML format.

Creating or Altering an Objects with a Single Statement

Additional Articles from SQLServerCentral

Do you get tired of having your CREATE PROCEDURE statement failing if the stored procedure already exists? If you are like me then you probably hate this as much as I do.

From the SQL Server Central Blogs - 3D Printing for Covid19

Tim Radney from Tim Radney - Database Professional

A friend tagged me in a Facebook post about a surgical mask strap that had been remixed by a Boy Scout for faster printing and less filament usage. My...

From the SQL Server Central Blogs - Database classifications have changed

Daniel Janik from Confessions of a Microsoft Addict

Some time back I wrote about the new data classification features in Azure and SQL Server Management Studio. If you’ve done quite a bit of work classifying your data...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Adding More Columns
I need to add an additional column to a data set that's in Power BI. I'm using DAX, and I need to substring some of the data that already exists in an existing column. What technique does this?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by BTylerWhite)

Finding Lowercase Characters

My database is currently using the Latin1_General_100_CI_AS_CS collation. I determined this by executing the following statement:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');

I create a table variable and insert some rows using the following code:

DECLARE @X TABLE
(
    FirstName varchar (32) NOT NULL
);

INSERT INTO @X (FirstName)
VALUES ('Adam'),
       ('benjamin'),
       ('charles'),
       ('David');

I need to write a query referencing this table variable to identify the rows where the first letter of the FirstName column is lowercase. The names "benjamin" and "charles" should be returned.

I begin my query by typing:

SELECT FirstName
FROM @X

Which of the following search conditions should I use in my WHERE clause?

Answer: WHERE FirstName COLLATE Latin1_General_BIN LIKE '[a-z]%';

Explanation: The correct solution is:

SELECT FirstName
FROM @X
WHERE FirstName COLLATE Latin1_General_BIN LIKE '[a-z]%';

Collation can be forced in a character expression for a given query. The reason Latin1_General_BIN was used in this scenario is due to the fact that binary collations are case-sensitive and enforce a binary sort order. Even though Latin1_General_100_CS_AS_SC is a case-sensitive collation, it's sort order is dictionary-based. The other options are not viable as the column in the table variable was created using the default database collation. It's important to note that changing the collation creates a non-sargable expression. The following collations would also be viable: Latin1_General_BIN2, Latin1_General_100_BIN, Latin1_General_100_BIN2, and Latin1_General_100_BIN2_UTF8. Reference:

Discuss this question and answer on the forums

Featured Script

Output multiple datasets from one INVOKE-SQLCMD command

Adam A from SQLServerCentral

A common question that I've seen online is how do I return multiple sql datasets from a single query using INVOKE-SQLCMD. Most of the answers out there say it is not possible, but it is in fact possible by using the OutputAs dataset parameter. This parameter outputs a dataset that has all of the information […]

$ServerInstance = sname
$sql = "SELECT
SERVERPROPERTY('MachineName') MachineName
, SERVERPROPERTY('ProductVersion') ProductVersion
, SERVERPROPERTY( 'InstanceName') InstanceName
, SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath
, SERVERPROPERTY( 'InstanceDefaultLogPath') InstanceDefaultLogPath
, DB_NAME() DatabaseName
;
SELECT name FROM msdb.sys.databases;"
$ds = invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql -OutputAs dataset
$ds.tables.table[0]
$ds.tables.table[1]

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

query CTE - Hi, I have a table with 2 fields DocumentID and ArrivedDate. I am able to elaborate only 2 documents in a day. How can I add a field (Final Date) with the correct days (every day with a max of 2 doc)? Thank you

SQL Server 2016 - Administration

Creating Columnstore indexes on Compressed table? - If I have a 500 million rows PAGE compressed table, should I/Can I be creating a Columnstore index on such compressed table? Will it even work? Will it be beneficial.. ? Can't find much on web on correlation between regular data compression and Columnstore compression. thanks.

SQL Server 2016 - Development and T-SQL

How much data can you get in a VARBINAY (MAX)? - BOL says it's "2 ^ 31 - 1" which is good old familiar 2,147,483,647. That looks pretty clear and unequivocable. So when we recently had this code Send On Conversation @ConversationHandle Message Type @MessageType (@Message) throw this error message Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes where @Message is a VARBINARY […]

Nested Views and General ETL Design Advie - (Edit: Should this be in the SSDT or Integration Services forum?) Hi, This post may be a bit like "how long is a piece of string", but any advice you can give is most welcome. Background: My source data is about 30 tables. I don't own or control this data. IMO the data modelling is […]

Trouble formatting data from table. - Hello, I am trying to format data in a specific way from one table. The columns look like this currently: CustomerID Data Entry Entry Number ReportDate A00AA033 Entry 1 06-Mar-2019 12:24:49 A00AA033 Task 1 06-Mar-2019 13:47:53 A00AA033 Task 2 13-Mar-2019 18:21:41 A00AA033 Entry 2 19-Mar-2019 10:24:45 A00AA033 Entry 3 02-Apr-2019 11:14:00 A00AA033 Entry 4 […]

Administration - SQL Server 2014

What is the correct size of my database? - Hi everyone We're in the process of moving our database over to a new supplier, and they've asked for the size of our current db. Using the stored procedure EXEC sp_spaceused I find that the database_size is 11918.81bm (so almost 12gb). However, I also get sizes of data = 6144880kb and index_size = 1559784kb (so […]

SQL Server 2012 - T-SQL

Query Performance - Hi folks, Just wondering if anyone can give me some pointers as to some other options to try to improve performance on a query I'm running. Its a flat select off one table with 23 fields to display along with a where clause for an ID column, which will have multiple values so using an […]

Reporting Services

Stored Procedure Returns 800 Rows. Report Only Shows 23 - I have a stored procedure I am using in my SSRS report. The SP returns about 800 records. However, my SSRS report only displays 23 records and I cannot figure out why. Things I have tried: Refreshed the report Cleared the data cache (*.rdl.data file) Went into Dataset Properties then Query Designer and ran the […]

Integration Services

How can you change the shape of a Precedence Constraint flow? - I have an SSIS package with quite a lot of data flows and precedence constraints coming out of them. Is there a way I can alter the shape of these precedence constraints as I would like to tidy up the layout of the package. Similar to Visio, where you can change the format or direction […]

"Execute SQL"-tasks sent to MS SQL Server are not processed in parallel - Hi all, I am using SQL Server 2016 with SSIS (latest patches installed as of January 2020). It has been decided to use SSIS only as process and batch-management tool, i.e. no data streaming is used, but INSERT INTO statements are sent to the database. As I can define a workflow to allow several tasks […]

Refresh Power Bi Dataset using API - Hi , Can some one suggest me with the best approach to refresh power Bi using API in SSIS solution . I am looking for a solution like installing Nuget package manager in the Visual Studio for Business Intelligence or any Rest API calls solutions using the Script task Thanks in Advance

SSIS Script task to write data in single xlsx file , excel sheet seperetaly - DO we have any script task , has to dynamically generate xlsx file. i have 3 different tables. each table data has to go in each tab (3 tabs - sheet1, shee2 and sheet3) in one xlsx file. test folder - c:\test in this folder i have to create xlsx dynamically

Reading 'BLOB' data out of AS400 to SQL - I have success in moving data from our AS400 to a new SQL instance, however there are a few tables with RichText or other Binary (image) files. The datatype on the AS400 is Alpha 500. I just tried to do a 1-for-1 integration to SQL using varchar(500), but I get errors [SSIS.Pipeline] Error: SSIS Error […]

COVID-19 Pandemic

Daily Coping 21 Apr 2020 - Today’s Tip is: Connect with nature. Breathe and notice life continuing. My thoughts: http://voiceofthedba.com/2020/04/21/daily-coping-21-apr-2020/

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.