SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

What's the age of your oldest, regularly used database?

I'm looking for an age in years. If the answer is less than one, I'm not sure I'd call that old at all. I am sure many of your systems are older, and might have changed, but let us know the year when the system went into production.

I can tell you the SQLServerCentral systems are old in some ways, not so old in others. We've grown from one database to three over the years. The oldest database is circa 2003. Some of the data from that one was migrated to other databases around 2007. We've got data in the system since 2001, but we've certainly changed structures and storage over the years.

I'd guess that most of you that are working in companies that are older than ten years will have a database that's at least that old. However let us know this week, and if you have any interesting notes, feel free to share them.

Steve Jones - SSC Editor

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

Featured Contents

Moving beyond principal components analysis

nick.dale.burns from SQLServerCentral.com

Principal components analysis is a standard, but usually terrible, technique for visualising complex data. We're using network diagrams to solve this problem.

Find SQL Server Installation Date for all Instances with PowerShell

Additional Articles from MSSQLTips.com

In this tip we look at a PowerShell script you can use to gather information about the installation date for all of your SQL Servers.

Free Solution Brief – Dedicated Database Environments for Dev, Test and CI

Additional Articles from Redgate

Developers and testers can be blocked from having a local, private database to work in because of disk space, refresh times, and data privacy. Download this free solution brief to learn how SQL Provision removes these blockers and enables production-like data to be delivered at speed.

From the SQL Server Central Blogs - Using 3rd party tools. T-SQL Tuesday #119

Kenneth.Fisher from SQLStudies

It’s that time again! Alex Yates (b/t) has asked us to discuss a time in tech when we’ve changed our ... Continue reading

From the SQL Server Central Blogs - Interview with Joe Sack

damian.widera from Coding Family

This is the seventh interview we have done. This time our guest is Joe Sack! Joe is Principal Program Manager for Azure SQL Database with a focus on query...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Just a few rows
I have a small table with a few rows of data in SQL Server 2017: CREATE TABLE [dbo].[Sales] ( [SalesWeek] [int] NULL, [SaleTotal] [money] NULL ) ON [PRIMARY] GO INSERT INTO Sales VALUES ( 1, 50.0000 ), ( 2, 70.0000 ), ( 3, 100.0000 ), ( 4, 60.0000 ), ( 5, 30.0000 ), ( 6, 120.0000 ), ( 7, 40.0000 ), ( 8, 80.0000 ), ( 9, 90.0000 ), ( 10, 92.0000 ), ( 11, 89.0000 ) I decide to run this query: SELECT * FROM dbo.Sales ORDER BY SalesWeek OFFSET 10 ROWS How many rows are returned?
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)

Securing the password

I am working in PowerShell on a script to run a few SQL commands. I need to give this script to another user to execute, but under a certain context. I don't want to hard code the password into the script, and I don't want it stored in a plain text file.

What cmdlet in PoSh will allow me to store a password securely for another user to pass to a script without them being able to read the value?

Answer: ConvertTo-SecureString

Explanation: The ConvertTo-SecureString cmdlet will let you change a string to a secure value that can be loaded and used in a script. Ref:

PowerShell and Secure Strings - https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-and-secure-strings/
ConvertTo-SecureString - https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/convertto-securestring?view=powershell-6

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

Availability Group creation on SQL2017 only has 2 options - Hi, I want to create an availability group in SQL2017 with "Windows Server Failover Cluster". However this option is missing in the "Specify Availability Group Options" section. I can only choose EXTERNAL or NONE. Anyone who can point me to the reason why this is like this? :/ I have already enabled Failover Cluster feature […]

SQL Server 2017 - Development

XML get result - Does anyone know how to get the value = "Avis" from this XML. DECLARE @myDoc XML DECLARE @ProdID VARCHAR(200) SET @myDoc = ' James McGovern aaaaaa ' SELECT @myDoc.value('/bookstore/book/span/@class', 'varchar(200)')

Data-Tier Application - Prevent Column being added in the middle of tbl (dacpac) - Question #1: Is there a setting that can prevent a column from being added in the middle of a table while developing in Visual Studio's Data-Tier Application (.dacpac) project tools? I understand that SSMS has a setting similar to the one I'm looking for which was discussed here: https://stackoverflow.com/questions/11802429/prevent-saving-changes-that-require-the-table-to-be-re-created-negative-effect Background: As a previous DBA turned […]

SQL Server 2016 - Administration

slower disk performance with VMWARE versus physical - Kind of open ended question but having issues with clearly degraded disk performance with our sql servers running on vmware versus physical. Anyone with past experience with this issue have any places to look?

Log shipping jobs losing flags - We have set up log shipping between two instances, and configured the log shipping job steps with @flags=12 (Log to table, Include step output in history). However, we have noticed that after a short period of time, the flags are reset to zero, and have to be manually configured again. We have made other amendments […]

Change Data Capture enabled on a replicated database - Hello SSC, My company is in the process of a migration from SQL 2012 to 2016. Our production environment is replicated. If CDC is enabled, what impact will it have? Will all of the CDC tables get replicated? Is it wise to have CDC on a replicated environment? Thank you all in advance for your […]

SQL Server 2016 - Development and T-SQL

Subqueries - My Question is what is the best way to learn subqueries? And why are they referred to as dynamic queries? Thanks

Add row number column based on id and stages - Hello Everyone I have a table with customer ids and different stages a customer can be in starting with A , B , C and D each starting at different dates Now a customer can be in multiple stages and I want to group them as below example (column rownum) Basically this determines the batch […]

Administration - SQL Server 2014

Transaction Log Shipping (No direct link) - A vendor provides a service whereby they keep a database up to date daily utilising transaction logs. They configure Transaction logs at there end to backup to a location to which we can obtain these files. How would I set up the secondary database given that the primary and secondary can not see each other. […]

SQL Server AlwaysOn and FailoverCluster - We have one project which has Failover Cluster with two nodes and one standalone server in AlwaysON. For example: Node1(ip:192.168.100.101) and Node2(ip:192.168.100.102) in Failover Cluster with clustered IP: 192.168.100.104. And Node3 (ip:192.168.100.103). Clustered IP:(192.168.100.104) and Node3 IP:(192.168.100.103) in AlwaysON with IP: 192.168.100.105. And now we have another project where we have Node1 (ip:192.168.200.101) and Node2(ip:192.168.200.102). […]

Development - SQL Server 2014

redistribute by total dollar value (not by count) - Table X: Consumer bigint (PK) CurrentGroup bigint DollarValue money I want to redistribute records in the above table to 4 different groups (currently not in table X) equally by the sum of the DollarValue (not a record count). Table X can have up to 5,000 records with a dollar value ranging from $10.00 to $10,000.00 […]

Extracting certain numbers from a string - Hi Guys, if object_id('tempdb..#Equipment') is not null drop table #equipment create table #equipment (Category varchar(20), ItemNumber varchar(30), ExpectedResult float) insert into #equipment (Category, ItemNumber, ExpectedResult) values ('Cable', 'CB1/03BAE300FT', 300), ('Cable', 'CB1/03BAE050FT', 50), ('Cable', 'CB1/04BAE100FT', 100), ('Cable', 'CB1/04BAE150FT', 150), ('Cable', 'CB1/04BAE025FT', 25), ('Cable', 'CB1/04BAE050FT', 50), ('Cable', 'CB1/04BAE075FT', 75), ('Cable', 'CB1/04CNN050FT', 50), ('Cable', 'CB10/5BAE1000FT', 1000), ('Cable', 'CB10/5BAE100FT', […]

SQL Server 2012 - T-SQL

Table Function becomes slow - Hi folks, I have a 2012 sql and it works very fast, one of the most used queries is a table function. This function over time becomes slow and heavy. If I add OPTION (HASH JOIN) the function is again fast (maybe faster), but only for a couple of days. I leave you an example […]

Reporting Services

SSRS Expression Help - I need assistance with the following requirements: I need the font on my text to equal "DimGray" when IsRunning = "Processing" else if the MostRecent.Value > today = Red else Green. With what I have below, its making them all DimGray. =Switch(Fields!IsRunning.Value ="Processing" , "DimGray", IIF(Fields!MostRecent.Value >= TODAY(), "GREEN", "RED"))

Integration Services

SSIS Excel Import Error - Hello, I'm an SSIS newcomer, but, far as I can tell, have done everything correctly. In a nutshell, I need to search a folder for Excel files and bring them into a table on the server. I also need to add a SheetName field as well as a FileName field. These 2 fields are ready […]

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.