SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Hidden Formatting Troubles with STR() (SQL Spackle)

Jeff Moden from SQLServerCentral.com

Fill in another bit of your T-SQL knowledge about STR(). It right justifies, rounds, and controls the output width of columns. Sounds perfect but here’s why you might not want to use it.

Reporting on the Status of Clones During Database Development

Additional Articles from Redgate

Phil Factor uses SQL Clone, PowerShell and Visio to build a live ‘clone network’ diagram showing when there was last activity on each clone, and the number of object changes made to each one, alongside useful metadata such as the clone and image sizes, who created them and when.

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

Additional Articles from Brent Ozar Unlimited Blog

This week, Brent is demoing SQL Server 2019 features that he’s really excited about, and they all center around a theme we all know and love: parameter sniffing.

From the SQL Server Central Blogs - Enable a Named Instance to Connect on 1433

Steve Jones - SSC Editor from SQLServerCentral

My default setup for a few years is to only use named instances, usually with a standard SQL2014 or SQL 2016 as the name of the instance. Recently, I…

From the SQL Server Central Blogs - Some Time Away

SQLFlipFlopsDBA from SQLServerCentral

Greetings everybody! It has been a while since I posted anything here. I figured I…

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Message Type Validation
Which of these is not a validation type for a Service Broker Message Type?
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)

Creating a DDL Trigger

What is the DDL For creating a DDL Trigger (a trigger that fires on CREATE TABLE or a similar DDL statement)?

Answer: CREATE TRIGGER

Explanation: The CREATE TRIGGER syntax is used, and the ON clause will specify whether this is on the server or database. Ref: CREATE TRIGGER - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

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.

Unable to Successfully Connect Using SQL Server Alias - I’ve tried on four different SQL instances on three different computers to configure a SQL Server alias and then connect to that SQL instance using SSMS on the machine that the alias was configured on. Each time I get the standard error message from SQL Server when it can’t find the SQL instance name being […]

Is there a way to walk "up" from current XML node -

AG Setup - I was thinking to configure Always on to 2 different secondary, one in DR Async and one in same site with Sync mode and then configure replication from secondary replica. I was thinking this would not be a best option when there is a fail-over the replication redirection would be question? However, checking to see […]

Using Cursor in DELETE statement - I have a need to use a SQL Cursor in order to delete data from a table, one row at a time (to prevent issues with a trigger associated with the table being deleted from). I wrote the following SQL, and while it is not producing any errors, it is running very long and eventually […]

Reports - Run time of reporting query is less when running directly against the SQL Server Vs Reporting tool. So running reports from third party reporting tool takes more time. Have you came across this problem. Any advise?

Connection to Integration Service Failed - I am using a trial of SQL Server 2017. SSIS worked. Untill i change Log On As to Local Service. Please help?

Parse In/Out Times into one or more Shift Codes - Once again, I come to rely on this helpful community. For a while now, I have been struggling with a problem that (even if it is more or less solved with application procedural code) would greatly benefit if it could be solved at the database level. Explaining the business case is a bit difficult but […]

Incrementally loading a very large Dimension table – Performance problems - Scenario: I have a very large type-1 dimension table (50+ million rows and 15+ GB with page compression) in a SQL Server 2016 SP1 64 bit Enterprise Edition database loaded from an SAP data source using Theobald Xtractor. I am required to perform both INSERTs and UPDATEs on this dimension daily which I do by […]

Status Update 24 Apr 2019 - We had a few days off for the Easter holiday and I got busy with other work yesterday, but a little progress. First, we still are short a developer as there is a delay in bringing on another resource and a couple internal resources got pulled for some higher internal priority stuff. The nature of […]

SQL VIEW works on its own but not when combined - Hi there, I have around 12 views and that works perfect if I execute them individually but when I combine them using UNION ALL it takes long time to execute and results in a time out error. Each view is bit complicated and brings back huge volume of data and they are grouped Please throw […]

Master Data Management (MDM) Tools by Microsoft - I have different data sources, applications having similar customer data. I want to sync them across them so that we do not have to find one customer in specific datasource/application and place all customers profile under one master database. How do Microsoft provide solution for this? any MDM DB tool y Microsoft available? Please share […]

AOAG endpoint won't connect - Hi, i’m not able to change the status of my hadr_endpoint do “connected”. It always show the status “disconnected”. I droped the endpoint, changed the endpoint owner, and changed the connect permission to the user, who starts the sql servives. But nothing change. Had someone any ideas, what to do? I won’t setup the AOAG […]

Splitting a multi-dimensional Array in SQL Server 2012 - Hello, I have data from an Orders table that has 4 columns, in which 2 contain data in a multi-dimensional array (ClassAttendees and ClassTickets). I am having trouble splitting the array in SQL server 2012. I would like to create a child table from the array that referenced the original OrderId, and held Class Attendee […]

Troubleshooting with. wait stats (last 2 weeks included) - I’m troubleshooting a slow SQL Server. Our server team is adamant it doesn’t need more memory or CPU. I’ve been working through the wait stats, but can’t figure out where to hunt next. Here is a link to an Excel sheet with stats, and below is a description of the tabs: https://www.dropbox.com/s/mg83ru9zv4rc9o6/waitstats_db.xlsx?dl=0 WaitStats – I’ve […]

grant user read only whole server - Hello — I should know this… but is there any way to grant a user global read-only access? We replicate our production server using log shipping, and we use the replicated copy for reporting purposes. We have a few users who we would like to give read-only access to this copy. However, the databases are […]

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.