Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Listen wholeheartedly to others without judging them

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.

Your User Group Goals

Many of you reading this piece have likely been to a user group at some point in your career. Some may go every month, some may go a once or twice annually , and some may not have been for years. I also know some of you don't have a group in your area, or perhaps not enough people for a SQL Server group, but I would bet if you are working in technology, there are enough people to have some sort of technology group, perhaps even an online book club.

Recently I was speaking with Andy Warren about his group in Orlando. He's preparing to turn over leadership of the group to others, and he's actually looking for comments on the docs he's put together. I'd urge you to take a moment and think about your group and how you want it structured and check out what Andy has done. He'd appreciate comments on his effort, whether you are in leadership or just show up at meetings.

Our discussion, however, was on goals. What goals are appropriate for the group, and what guidelines should he leave for others. He's tended towards goals of "more", meaning more people touched, more attendance at meetings, more events. I've tended to aim for more ensuring you keep people interesting, inspired, and with some engagement, but not worried about size.

Whether you are in the leadership of a group, just an attendees, or wish you had a more interesting group of technical people to meet with regularly, what do you wish your group would accomplish next year? Are there things that matter to you for an organization outside of work that might help your career? Take a minute to think and leave me a comment today.

Steve Jones - SSC Editor

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

 
 Featured Contents
Stairway to Biml

Stairway to Biml Level 8 – Using the Relational Database Metadata to Build Packages

Andy Leonard from SQLServerCentral.com

In this next level of the Stairway to Biml, we will examine how you can use the information stored in your RDBMS to build packages.

Deploy Database Objects to Multiple SQL Server Instances Using PowerShell

Additional Articles from MSSQLTips.com

In this article we look at a PowerShell script that can help you deploy database objects to multiple SQL Server instances.

Effective Data Governance: Being Grown Up About Data

Additional Articles from Redgate

William Brewer explains how to make data governance a continuous organizational activity, based on well-established standards and practices, rather than a knee-jerk response, and which skills and tools will help you achieve compliance, including SQL Data Catalog for discovery and classification of data held in SQL Server.

Free eBook: Fundamentals of SQL Server 2012 Replication

Press Release from Redgate

Fundamentals of SQL Server 2012 Replication provides a hands-on introduction to SQL Server replication. The book introduces you to the technologies that make up replication, and then walks you through setting up different replication scenarios. When you've finished reading, you should be able to implement your own multi-server replication setup while following the principle of least privilege.

From the SQL Server Central Blogs - Migrating from SQL Server to Amazon AWS Aurora

Daniel Janik from Confessions of a Microsoft Addict

Is Microsoft’s licensing scheme getting you down? It’s 2020 and there are now plenty of data platforms that are good for running your enterprise data workloads. Amazon’s Aurora PaaS...

From the SQL Server Central Blogs - Provisioning storage for Azure SQL Edge running on a Raspberry Pi Kubernetes cluster

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

In a previous post we went through how to setup a Kubernetes cluster on Raspberry Pis and then deploy Azure SQL Edge to it. In this post I want...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The Classifier

What does the classifier() function do in R?

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)

A Minor Problem

Don't run this code, but there is a problem in it. There is a dbo.Day2 table with a datavalue column and no other tables in the database. What is the problem?

WITH cteData (lowerbound, upperbound, letter, pwd)
AS (   SELECT
            SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) as lowerbound
          , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) as upperbound
          , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1)
          , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50)
       FROM dbo.Day2 AS d)
   , cteSolution (occ, valid)
AS (   SELECT
            LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ
          , CASE
                WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound
                     AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN
                    1
                ELSE
                    0
            END AS valid
       FROM dbo.cteData AS d)
SELECT COUNT(*) FROM cteSolution WHERE valid = 1;
GO

Answer: The second CTE has a syntax error

Explanation: The problem with this code is in the second CTE. The FROM clause lists the cte as dbo.cteData. There is no schema qualification on the CTE. Ref: CTE - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-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
Chance data Capture with AG - Since CDC uses log reader to read the changes from the Log. If the SQL server is configured for AOAG group, I was thinking to enable the CDC on secondary replica instead of primary replica. Would you see any issues with this setup? I am thinking Change data capture are not supported on secondary databases that […]
TDE- Transparent Data Encryption related question - Hi Experts, Looking for suggestions on TDE encryption. We have a 3TB database in UAT env. Its in 2-node(node-A, node-B) Alwayson AG. SQL Server version is SQL 2017 EE CU22. We turned on TDE encryption node-A. It took more than 12 hours and completed of about 40% encryption. Meanwhile, unexpected failover occured. we brought it […]
SQL Server 2017 - Development
Extract data with variable column names and order - Hi guys,  Just looking for any good suggestions on how to tackle this problem. I have a table of attributes which are held in very wide sparsely populated table (at least it's not EAV!).  For display purposes in the application there is a separate table that defines which columns are being used, their display order […]
SQL Server 2016 - Development and T-SQL
Help Transforming TSQL to MSSQL - Hello, I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can you please restate it correctly or put me on the right track? It is the With statement that is killing me! I think it needs […]
Enabling FileStream in AG participated Database - Hi team, I was asked to enable FileStream for one of the existing varbinary(max) column in existing table. It is already participating in Avalability Group where we have 1 primary & 1 secondary server. Now as part of the change, i am doing the below Enable FileStream at instance level by enabling it via SQL […]
SQL Server 2019 - Administration
Perf hit for Increasing tran log frequency - we are currently doing a 1 hour tran log backup for a system, I'd like to push that up to 1 minute. Is there a performance hit to be concerned about with doing this? I've gotten some pushback regarding performance and doing tran log backups this frequent. My view is that each hit will be […]
tlog back up and normal restore back migration - Hi Expert,   why  there is less down time in tail log backup as compared to the normal backup and restore in migration   Shree
SQL Server Agent Failing After Windows Shutdown - Hi All~ Where I work, we have Azure SQL Server that we are connecting to through SSMS.  I have created several SQL Server Agent Jobs, using my own login as a Proxy.  These jobs run a VBS Script (using a command line) that uploads data into our db, creates reports, and moves and manipulates files […]
Database Mail - Times out - Hello All, I have a lovely program called "TreeSize" on my SQL Server that can send an email out via SMTP just fine. I decided to try setting up Database Mail so I can get email alerts when Backups complete, etc. I am following these steps: https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/ Again, my TreeSize Application can send mail no […]
SQL Server 2019 - Development
SQL Spatial - find all locations within X distance of a point. - I have a really stupid question, and I'm betting the answer is "Well, what happened when you actually ran the query?", but I'll ask anyway. (Maybe I just need a bigger spatial dataset). Say I have a center point P that's my location. I can do all the "find the distance" stuff just fine using […]
nested while - CREATE Table TABLEA(N varchar(50),Q int,R int) ALTER PROCEDURE [dbo].[SP](@N varchar(100),@Q int=0,@R int=0) AS BEGIN DECLARE @I int = 1 BEGIN WHILE (@I <= @R) BEGIN WHILE (@I <= @Q) BEGIN INSERT INTO [dbo].[TABLEA] SELECT @N,@Q,@R SET @Q = @Q - 1 END SET @R = @R -1 END END END exec [dbo].[SP] 'NameA',10,2 Current TableA […]
SSRS 2016
Execution Log Clean Up - The task that cleans up out of date execution log entries runs at 0200 every morning.  This is not an ideal time for us because we're a 24 hour business and the most important times for are actually overnight.  There have recently been complaints raised because SSRS is unavailable for a short period at 0200 […]
Integration Services
SIS gets stuck on data flow task loading screen upon opening project - I'd like to start by mentioning that I have searched for this topic, although there are similar solutions out there, I haven't found one specific to my case and others I've tried haven't worked, so I am seeking any help relating to my issue. In SSIS, Anytime I open a project, whether I import it […]
Anything that is NOT about SQL!
Searching for PII Data in Teams and Outlook - I've been performing a a Proof-of-Concept within our business, showing how Redgate's various tools can help the company to keep a track of PII data and let us use databases that have PII data as the basis for QA and Dev environments. One of the conversations with our InfoSec people raised a question that they […]
PostgreSQL
OLE DB provider "PGNP" for linked server "MyLinkedServer" reported an error - Hi, I am trying to migrate SQL Server databases from 2008 to 2019.  My old server has a linked server to PostgreSQL (version 8) I've installed the driver and created the linked server: I've installed the driver and created the linked server: EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'PGNP', @datasrc=N'N'MyLinkedServer', @provstr=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;', @catalog=N'MyDB' ... The linked […]
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -