SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

RANKX Ties

The Voice of the DBA

Daily Coping Tip

Focus on what you can do rather than what you can’t.

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.

Are You Ready To Change Your Work Environment?

It's Friday, it's been a long few months, and I'm wondering what you think of work now that many of us are telecommuting. This week, I'm wondering if you want to answer one of these questions?

If you are telecommuting full time for the first time, do you want to continue doing so?
If you are telecommuting full time for the first time, are you ready to go back to an office?
If you've been working from home for some time, are you ready to go back to an office with people around?
Do you just need a variety in your working environment?

These are questions I've asked myself many times across the last few decades. My thoughts and answers have changed a bit over the years, and even now they're different than they used to be a few months ago. Right now, I want to go back to an office more often than I used to go. I usually go to the Redgate Cambridge office four times a year, and the US offices rarely. I think after this, I'd like to get to each office in the US a couple times a year and four times in Cambridge. I miss talking to live co-workers. I might feel differently when this shut in ends, but I miss having some people around.

For almost ten years, I drove to an office every day while my wife worked at home. I was jealous, and wanted to telecommute full time, but none of my management would allow it. Then for almost another ten years I worked at home with my wife. She traveled quite often, and I struggled with the loneliness of being by myself. I often went to lunches with friends or sat in a Starbucks and worked, just to have some other people around. The third phase of my work environment has me at home alone often, but also on the road regularly, so I haven't struggled with the times I've been alone in my office for weeks.

I don't know how things will move forward from here. I'm sure something will change, and I look forward to societies finding ways to open things up over time. I know we'll adapt and cope, but I also know that even the most introverted of us is a social creature and we do need some contact with others. Today, I wonder if your views of how we want to work have changed.

Steve Jones - SSC Editor

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

Featured Contents

Export SQL Server Tables to Excel

Additional Articles from SQLServerCentral

Sometimes there is a need to export data for a lot of different SQL Server tables into an Excel file. In this tip we look at how we can quickly export several tables into one Excel workbook where each table is stored in a different worksheet.

An Introduction to PowerShell for a DBA

Alex Chamchourine from SQLServerCentral.com

An introduction to PowerShell that covers basics alone with some more advanced features. It will walk you through from the very beginning to the writing of a few useful scripts.

From the SQL Server Central Blogs - Coding Standards Gone Bad in SQL Server

SQLEspresso from SQLEspresso

Knowing your data is very important when it comes to writing code. Now I’ll admit that I am very far from being a developer, however as a DBA, I...

From the SQL Server Central Blogs - Be warned: The way I’m feeling right now I might actually give you sysadmin when you ask for it.

Kenneth.Fisher from SQLStudies

You want sysadmin permissions in production? And your manager has approved it so I shouldn’t have any problems giving it ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	RANKX Ties
I have a data set that looks like this: User Sales --------- -------- USA 1000 Japan 800 UK 800 Ghana 400 Australia 700 I want to create a SalesRank column using the RANKX() function. I enter the table and column for sales, but I want to ensure that I handle ties appropriately. Which value do I choose for the ties parameter if I want the ranking to look like this? User Sales SalesRank --------- -------- --------- USA 1000 1 Japan 800 2 UK 800 2 Ghana 400 4 Australia 700 3
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)

Bad Error Handling

I have this code I'm using to test some error handling.

BEGIN TRY
    BEGIN TRAN
    SELECT 'Did this run?', 1/0 AS AnException
    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    THROW
END CATCH

What results should I see when this executes?

Answer: Only the output from ERROR_MESSAGE()

Explanation: This is a similar example to a select alias statement. Here, the SELECT ERROR_MESSAGE() sees the THROW as an alias, so only the output from ERROR_MESSAGE is returned. Ref: SELECT clause - https://docs.microsoft.com/en-us/sql/t-sql/queries/select-clause-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 - Administration

Reporting Services Question - Someone recently told me that from SQL Server 2017 you are only allowed to install one instance of SQL Server reporting services on an individual server. I am no reporting services expert but I believed in the Reporting config manager you were able to select different instances you wished to connect to on a single […]

SQL Server 2017 - Development

CASE Statement - Hi All, I have a simple case statement that won't work and I can't see where the error is, missing a bracket or something not sure. The syntax error is appearing at the, END as 'Measure' Thanks for any help. DECLARE @1startdate DATE = '2014-01-01' , @2Endate DATE = '2019-09-30' ,@3Policy VARCHAR(50) = 'Policy_View' ,@4Channel […]

SQL Server 2016 - Administration

How to skip duplicate record and keep on running until the end? - I used code below to insert data into a table from another table. INSERT INTO tUser (Name,Email) SELECT Name,Email FROM tUSER_NEW T WHERE T.EMAIL NOT IN (SELECT EMAIL FROM tUser) But, this code will stop running once there is a duplicate record. How to skip duplicate record and keep on running until the end?

Scripting Query Store - I could not find such script but I am sure it should exist. If I want to ask a DBA of a particular database of a particular server to send me ALTER DATABASE etc.. script that would allow me run it and recreate his precise Query Store settings in my database. I want to just […]

Restore Encrypted Database on another instance - In order to restore an encrypted database on another instance, do I need the entire chain on the new instance, ie: Service Master Key, Database Master, and the DEK key, the last 2 from the old server? I created the master key on the new server, as well as the service master, (then set up […]

Development - SQL Server 2014

How to create table with 3rd row data as columns with separate cama(,) column - As above image is my table data, i have to create a table from 3rd row separate cama as a column and create new table and the same after 5th row should take data and load into creted new table.. can you please please help anyone .. Please help me on this topic

SQL 2012 - General

Howto retain few backups and delete old backup files - Hi, I have created a dynamic backup script to take backup for 2 databases. something like as follows: use master go declare @dbname varchar(100) set @dbname = 'db1' declare @cmd varchar(1000) set @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''C:\backup\'+@dbname+'_FULL'+'_'+convert(char(8),getdate(),112)+'_'+REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')+'.BAK'' WITH INIT, STATS =5, BUFFERCOUNT = 100;' print @cmd EXEC (@cmd) go declare @dbname […]

DBCC Integrity Check Error - insufficient system memory in resource pool - We have Sql server STD 2012 SP2 and from last 3 days, we are getting error while running the DBCC Integrity Check for both User and system Databases, we are using Ola Maintenance solution script. We have Total Physical Memory 8 GB, Total Virtual Memory 9.25 GB (showing in system Information Available Physical Memory 925 […]

SQL Server 2019 - Administration

SQL server upgradation - Hi Experts , We are using SQl server 2012 database for our application . Management is planning to migrate our application to Azure cloud. So we are planning upgrade server to SQl server 2019 or Azure database. So i want to know which one is better on the basis of scalability, which instance has more […]

SQL Server 2019 - Development

Collapse record and get the number of days - here is my data. I am trying to get the dates in single row like below. Any help appreciated. result I need

using OleDbConnection can we connect to both sqlserver and mdb - Hi All, In administrative Tools ODBC Data Sources (64-bit) configuration ,Please find the attached screenshot where i have a datasource configured. Example: Standarad Part Library 32/64 -bit Server Standarad Part Library0904 32/64 -bit Microsoft Access Driver (*.mdb) How to establish connection for both SQL Server and Microsoft Access Driver in VB .net […]

Integration Services

Package migration from version 6 to version 3 failed with error 0xC001700A - Forum, Im trying to execute and migrate a package which was developed in SQL 2012 on to a SQL 2008 server , i see the version mismatches from the error is there an alternative way to make it working ? . Im planning on having the BIDS installed and rewite the complete SSIS package. This […]

Cannot extract Filename from Filepath using GetFileName in C# - Hi there I have a SSIS package with a connection manager as follows: Package.Connections[Output_ExistingFile].Properties[ConnectionString]" ValueType= \\acslonitstaging\Segmentation\PushPortfolio\Results\Existing_SVPP_XXXX_00001_00021_20200424_231245.csv I want to extract the filename [Existing_SVPP_XXXX_00001_00021_20200424_231245.csv] in a C# Component Ive tried the following string fileName; string path = Dts.Connections["Output_ExistingFile"].ConnectionString; string result; result = path.GetFileName(fileName); MessageBox.Show(Message1); However this is not working Is there an alternative way to extract […]

COVID-19 Pandemic

Daily Coping 27 Apr 2020 - Today's tip is: connect with nature. Breathe and notice life continuing. My thoughts: http://voiceofthedba.com/2020/04/27/daily-coping-27-apr-2020/

Daily Coping 24 Apr 2020 - Today's tip is: Thank three people you’re grateful to and tell them why. My thoughts: http://voiceofthedba.com/2020/04/24/daily-coping-24-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.