SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Broken UDF

The Voice of the DBA

Daily Coping Tip

Let go of something small bothering you and focus on something going well

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.

The Azure Carrot

Microsoft wants more people to move their database workloads to Azure. They are constantly adding new features, capabilities, even tools to help people move databases to some part of Azure. It's working well, as the latest quarterly report shows tremendous growth in Azure revenue. We don't know precisely how SQL Server or Azure SQL databases impact this, but I certainly see more and more customers moving relational workloads to the cloud. Some to Azure, some to other providers.

In an effort to entice people, Microsoft has a promotion that they were running in March, which was asking SQL Server 2012 customers to move to Azure and save with free extended security updates. They reiterated the promotion on July 12, when SQL Server 2012 went out of support. They calculate savings and promote benefits, which might be tangible and desirable to some of you. After all, not having a supported platform isn't an option for some people.

I don't know how I feel about this. While we recently had a security update for SQL Server 2012, I know that it's an older platform and at this point, it's 10 years past RTM. It is probably time to upgrade to a newer version, which should be 2019 if you can't wait and SQL Server 2022 if you can wait until the end of the year. I can't imagine that your workload from 2012 won't run on 2019, but it's possible there are some issues.

I've run older versions of SQL Server that weren't supported and often didn't worry. I knew what worked and what didn't, and after a few years, it was unlikely that anything in the product would break. However, that was in an environment that was not regulated or certified, which wasn't always the case. I have worked in places where we would upgrade whenever we got to the end of mainstream support as we weren't interested in paying for extended support.

I also think that while SQL Server is very mature, and it's possible that you don't need new features, you do want a platform that is secure. That means you do want support and fast action if an exploit becomes published. There is also the case that we often want developers working on modern platforms, both because they can take advantage of better language constructs, but also because this helps retention. Try hiring for an all SQL Server 2012 environment v a SQL Server 2019 one. You might find people, but a lot of the talented ones would prefer to work on a modern platform, not a decade old one.

If you are running SQL Server 2012 (or 2008x) and considering the cloud, check out the promotion. It might help you make a decision on whether to upgrade locally or move to Azure.

Steve Jones - SSC Editor

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

Featured Contents

Stairway to MDX - Level 13: MDX Time/Date Series Functions: LastPeriods() and ParallelPeriod() Functions

Bill Pearson from SQLServerCentral.com

BI Architect Bill Pearson continues with the third of a subseries surrounding a group of MDX functions we can use to support analysis and reporting in a time / date context. In this Level, we will explore the LastPeriods() and ParallelPeriod() functions, and get hands-on practice with each in meeting sample business requirements.

Generate Random Dates in T-SQL

Additional Articles from SQLServerCentral

In this article we look at how to generate random dates in SQL Server to build a sample dataset along with code and examples

How to install SQL Server 2022 step by step

Additional Articles from Redgate

In this article, we cover how to install SQL Server 2022.

From the SQL Server Central Blogs - Transactional Replication: Where did my data go???

gbargsley from GarryBargsley

Good morning dear reader. Today I am writing this blog while sitting at a trampoline park. My six-year-old son loves this place, and we tend to find ourselves here...

From the SQL Server Central Blogs - You’re doing your POC wrong! : T-SQL Tuesday #152

Kenneth.Fisher from SQLStudies

It’s T-SQL Tuesday again! Deborah Melkin (blog|twitter) is our host this month and she’d like to hear our opinion. A ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Broken UDF
I have this code on SQL Server 2017: CREATE FUNCTION AddOne (@i INT) RETURNS INT AS BEGIN DECLARE @result INT; SELECT @result = @i + 1; RETURN @result; END; GO SELECT AddOne(3) When I execute this, I get the following error: Msg 195, Level 15, State 10, Line 11 'AddOne' is not a recognized built-in function name. What is wrong?
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)

Developer Permissions

I have created a new database on a development server and added the user, JoeUser, as a user in this database. This user has no rights other than to connect to the database.

I then execute this code:

CREATE SCHEMA Dev
GO
GRANT CONTROL ON SCHEMA::Dev TO JoeUser
GO
GRANT CREATE TABLE TO JoeUser

JoeUser then connects and runs this code:

CREATE TABLE Dev.MyTable (myid INT)

What can JoeUser run successfully on this table?

Answer: SELECT, INSERT, UPDATE, DELETE, and TRUNCATE TABLE

Explanation: With the CONTROL permission and the CREATE TABLE permissions, this user can create their own table and then run any CRUD commands. Ref:

GRANT Schema permissions - https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16
GRANT Database Permissions - https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver16

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

limit parsing a XML -datafield from one server to the CMS using Powershell - Guys, I do need some help on a limit parsing a XML -datafield from one server to the CMS using Powershell --------------------------------------------------------------- I registered some servers in a folder in the CMS (SSMS). On each server there is a DBA-database holding the XML_REPORT table and a Stored-Procedure (which generates a XML-report). On the CMS itself […]

SQL Server 2016 - Administration

TDE: Database Encryption key to be changed from 128 bit to 256 bit encryption - Hello, I was asked to apply TDE using 128-bit encryption initially. Later on, the new requirement governs to change that to 256-bit encryption. Without dropping the DB encryption key, can we change that from 128 bit to 256 bit encryption? Thanks Sourav

Query to remove all databases from AG - As part of DR we need to remove all the databases from different AG's , each AG is consists of 20+ databases , i need a query to generate script for removing all databases with respect to specific AG group , . Thanks,.

SQL Server 2016 - Development and T-SQL

pull data every 3 hrs - I'm trying to pull data every 3hrs between 2 dates. I need to just get one record for each machine every 3rd hour between two dates. I have this query which reports back midnight the day before thru today, but I'm looking for a single CREATE TABLE [dbo].[mach_data]( [dtstamp] [datetime] NOT NULL, [equipment_id] [varchar](6) NOT […]

Help Understanding A SQL Script - Hello Community, I am trying to implement a SQL Script from this link, and pasted below --================================================================================================= -- DESCRIPTION --================================================================================================= -- The purpose of this script is to create a set of database views in a database within your Synapse Analytics Serverless SQL Pool that will -- translate numeric codes for Option Sets, Status and […]

Administration - SQL Server 2014

Sudden rise in memory - Hi How i can check which process was run that Sql memory suddenly increased. Thanks

Development - SQL Server 2014

SSRS Rendering format Excel Change ? - I was wondering if anyone knew how I can change the Excel rendering format in the reportserverconfig file so the default cell format is text and not General. If not, is there any way to format my fields in the stored procedure so when I download to Excel it formats as text? Thanks

SQL 2012 - General

Urgent help needed. MS SQL Server 2012 - Hi, I've just got a call (6pm on a Friday!) from a customer who has had his line-of-business application provider sell him a solution which required SQL Server 2012 Standard, yet the supplier (joyfully) installed the evaluation version of SQL Server 2012 Enterprise. This has now ticked over and expired. My problem is that (a) […]

Merge Replication or Back up and Restore - Hello I have a database copied on two servers. Same name. I have tried a merge replication but it keeps failing on various objects. I have removed objects one by one and left with the bare minimum tables and still won't work. Users won't mind using one database as master and do all the updates […]

SQL Server 2019 - Administration

Extending High Availability Group into Azure - Hi I've been asked to extend a HAG which is currently on premise to include an azure vm sql instance. currently i have a 3 node cluster, two nodes in on city with synchronous connect and auto failover and one in another city with asynchronous connect and manual failover. I want to add an azure […]

SQL Server 2019 - Development

Parse SQL data - Below is the test data. What I am looking for is... I want to PARSE any value starting with PD- from the description column. Below is sample data. However, it could be possible the "Description" column could have n number PD id in the column, not restricted to any numbers. Any advice would be highly […]

General Cloud Computing Questions

Client doesn’t want to run discovery agent, what are the options ? - Trying to assess client on-Prem servers for a cloud migration. We usually run a discovery tool that runs an agent to capture high level server info (OS/ Softwares/CPU etc…). I’m trying to think out of the box solutions. Maybe ask him to run his on script to capture all data or deep dive session to […]

Integration Services

Replace function in SSIS Expression - hi, I have files that are coming in .txt files. Example MA.textfile1.txt ,MA.textfile2.TXT . I need to replace the file name after i process these files. I have a foreach loop to get the file names , and file system task to rename files. However i have set up variables within file system task, if […]

Suggestions

Add a resolution flag that can be set by the OP - Something like the following. Thanks!

Anything that is NOT about SQL!

Web site font colour/background colour and readability - Hi everyone I'm just interested in getting peoples about the readability of many web sites as far as the general words in sentences (not headings). Maybe it's my imagination, but it seems to me that there seems to be a tendency for there to be a lack of contrast these days between the font colour […]

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.