SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

The Voice of the DBA

Rebuilding SQLServerCentral

It’s been quite a journey, but we finally released version 3 of SQLServerCentral recently. After months of development work, the site moved to the WordPress platform, backed by SQL Server under Project Nami. I’m hoping that most of you appreciate the change and like the new look. I’m sure some don’t, and I’m somewhat torn myself, but I know that this was a change we needed to make and the platform will give us some flexibility and resources in a way that we haven’t had in years.

Version 1 of the site was an original ASP based site that Brian Knight mostly coded, with some help from the other founders. We ran that site for seven or eight years, on SQL Server 2000, and it worked well. Version 2 came after Redgate Software purchased the site, with a custom site based on the nHibernate framework and built around SQL Server 2008 that ran the site for the next decade.

Last year we knew that the changing world necessitated another revision. Mobile is critical, as are some other requirements from Google and other search engines, not to mention the need to ensure we kept up with security requirements. While a custom site was possible, we aren’t really in the business of writing website software at Redgate. I’ve struggled to get development resources as most of our .NET experts are busy building the products we sell. With Redgate.com and Simple Talk moving to WordPress across the last few years, we decided to migrate SQLServerCentral to the same environment.

We examined a number of RFPs, and eventually decided that Project Nami was the way to go, allowing us to continue to use SQL Server as the backend, while a number of custom modules could be used to expand the WordPress platform to handle our daily question quiz, the forums, the Stairway series, and more. Across the last months, we’ve built that functionality to ensure the site continues to provide a home for this community.

As with many businesses, we discovered that data migration was a very complex process, one that we invested a lot of resources in to ensure we minimized any data issues. Across the years, we have tried to be careful with our data. Most of it is public, but we have taken data security very seriously from the beginning. Like many of your systems, we also have some junk data and some inconsistently stored data, often because of bugs in the way our software was written and patched across time. As a result, we certainly haven’t accounted for every edge case, but please let the webmaster know if there is something that needs to be fixed.

It seems there is never enough time to really properly model and design a database. Time is the one resource in development that is often in very short supply, and SQLServerCentral is no different from other systems. I think we’ve improved some aspects of how the site works, but I also know that we’ve taken some shortcuts because we have developers getting software working first and foremost. Not the ideal process, but one grounded in the realities of the world.

I’m looking to do some analysis and recommendations, and hopefully some refactoring over time of our database. We should have more resources available since quite a few people understand the WordPress platform. I hope to share more of the journey and decisions made over time, as well as get some critique of our choices and implementation. I’m hopeful this will be an interesting and educational set of content for all of us.

For now, I hope you still enjoy using the site, find it useful, and continue to give us feedback on how things work well, are broken, or need improvement.

Steve Jones - SSC Editor

Featured Contents

Creating a comma-separated list (SQL Spackle)

WayneS from SQLServerCentral.com

comma separated list

Learn how to create a comma separated list of values in this short SQL Spackle article by Wayne Sheffield.

Crosstab queries using PIVOT in SQL Server

Additional Articles from MSSQLTips.com

Crosstab

In this tip we look at how to construct a SQL Server PIVOT query with an example and explanation.

How to Monitor IDENTITY columns to prevent unplanned downtime

Additional Articles from Redgate

IDENTITY

If a table runs out of IDENTITY values then it, and any dependent services and applications, will be "read-only" until the problem is fixed. Steve Jones explains how to set up a custom monitor to detect and prevent such problems.

From the SQL Server Central Blogs - dbatools for the accidental DBA

carlos10robles from SQLServerCentral Blogs

dbatools is a very well known tool within the SQL Server community, and probably most of the production DBA’s are…

From the SQL Server Central Blogs - Disk Space and SQL Server

SQLRNNR from SQLServerCentral Blogs

Disk Space in SQL Server
One of the frequently required job functions of the database administrator is to track disk space…

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Changing the default FTS language
Right now the default language for my full text search is US English on a SQL Server 2017 instance. How do I change this to British English?
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)

Getting the Primes

I have written these functions in Python:

import math

def is_prime(number):
    if number > 1:
        if number == 2:
            return True
        if number % 2 == 0:
            return False
        for current in range(3, int(math.sqrt(number) + 1), 2):
            if number % current == 0: 
                return False
        return True
    return False


def get_primes(number):
    while True:
        if is_prime(number):
            yield number
        number += 1

I now do this:

y = get_primes(25)

What is y?

Answer: y is a generator function

Explanation: Y is a generator function that can be called with next(). If I were to execute this:

  next(y)

I would get 29 as the return value. This:

  type(y)

returns something similar to this:

<class 'generator'>

Ref: Python Generators - https://wiki.python.org/moin/Generators

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.

MAJOR DATA LOSS ISSUE TO FIX ASAP: code in code block treated as standard HTML - If you place any HTML and/or BBCodes in a code block, it will not be respected as being code. It will be handled as if not being in a code block and translated. Meaning, if I add the following to a code block (which I am escaping so that it will show correctly here):

Code Sample block is missing languages (at least "HTML" and "Plain") - When selecting a language in the code sample editor, there are options for C#, XML, Python, R, PowerShell, and not just one, but two options for SQL. But, it is missing the following languages (and possibly others): HTML Plain (normal / regular / none / whatever translates to the prettyprint language of “lang-none”)

SQL 2017 - Looking to find if there are any benefits of SQL 2017 over SQL 2016. Looking at natively compiled stored procedure improvements in SQL 2016, whereas case statements were included in SQL 2017. Is there any benefits you would like to add,I am looking more towards overall benefits on development and administration side.

Latest Topic not sorted from newest to oldest based on last post. - Topic says it all. This is worthless when not sorting the newest first.

"Cancel" button, at least for replies - Not sure this applies to new posts (though it might), but at the very least I think there should be a “Cancel” button when adding a reply. Currently there is only a “Submit” button, but if you find that you don’t want to create the reply, then unless I am missing something, you need to […]

Ability to delete your own replies and posts that have no replies - Is there a practical reason to not having the ability to delete your own content, as long as it doesn’t affect anyone else’s? This would apply to replies as well as posts that do not have any replies yet. I can’t remember if the old forum allowed this or not. If no reason to not […]

Look at ROWCOUNT and ERROR just after update? - I added error handling to a stored procedure I inherited to check for errors after every update – department policy (and good proctice). Unfortunately, it breaks a piece of SQL which looks at ROWCOUNT. Any advice? insert table (bla bla select bla bla bla IF (@@ERROR <> 0) BEGIN SET @ErrMsg = OBJECT_NAME(@@procid) GOTO ERROR […]

The requested security protocol is not supported. - Hi Experts, I am working on SSRS server and i have a report that is dependant to a cube.The report isn’t working and when i test all data sources, the data source to the cube is giving me an error “The requested security protocol is not supported.” This is on Production but on DEV this […]

The requested security protocol is not supported. - Hi Experts, I am working on SSRS server and i have a report that is dependant to a cube.The report isn’t working and when check the data source it’s giving me an error “The requested security protocol is not supported.” This is on Production but on DEV this works very well with same settings.What could […]

How to handle dynamic XMLNAMESPACE - I have a situation where an XMLNAMESPACE has a URL that needs to be dynamic. Its being used with an insert into a table from an XML parameter passed in my stored procedure as the source. Setting a variable and placing it in the XMLNAMESPACE generates an error ;WITH XMLNAMESPACES(DEFAULT ‘http://www.microsoft.org/2005/Atom’, @Base_Namespace, ‘http://schemas.microsoft.com/ado/2007/08/dataservices’ AS d, […]

Unescaped single quote in data - Hello, I need some help with some code that I borrowed and am trying to make work. The issue is my data contains many names with apostrophes and I know I need to deal with it with by escaping the single quote (turn it into two single quotes)but I’m having difficulty doing that in the […]

Underlining, via Ctrl+U, not working - When highlighting text, and pressing Ctrl+U to underline it, the editor shows that the text is underlined, however, on submission the text is wrapped in tags. Example. Using BBCode, however, (.

Future Versions - With the sterling work that has gone to transform this site would this also be the opportunity to merge the Future Versions Topics and postings into the appropriate forums? I am referring to this:

Prevent package from failing when there is no file for File System Task to move - Hello, I have a SSIS package that has a File System Task inside a Foreach Loop Container that’s used to move files. The files are generated every 5 minutes, and the package is run by SQL job agent every 5 minutes but last night they got out of sync and the package failed, because there […]

MASTER database unrecoverable – Script Upgrade Mode - Fun! IT was applying a Windows upgrade to clustered SQL 2008 R2 environment when script error occurred (Event Viewer) apparently during changes to MASTER database. Now SQL is down, and when I bring it up in single user mode, I am unable to connect and get message about system being in “script upgrade mode” and […]

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.