SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

SQL Server Build Lists

Steve Jones - SSC Editor from SQLServerCentral.com

An index to the build list articles for various versions of SQL Server.

Synchronously Refreshing a Power BI Dataset using Azure Logic Apps

Additional Articles from MSSQLTips.com

In this tip we will implement a polling mechanism to verify if the Power BI dataset has refreshed successfully.

Free eBook: Inside the SQL Server Query Optimizer

Press Release from SQLServerCentral.com

This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Merry Christmas 2019
I want to calculate the total number of gifts from the song, The Twelve Days of Christmas. I have this code: WITH myTally(n) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n) ) SELECT SUM(x) FROM myTally WHERE n <= 12 With which piece of code should I replace SUM(x)?
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)

Knocking off early

It's the day before Christmas, and everyone in the Redgate office in the UK is knocking off at noon. I want to know when I can stop working in the US, since everyone else in the UK will go home.

I have this code set up:

DECLARE @t DATETIMEOFFSET = '2019-12-24 12:00+00:00'

What should I add to my SELECT below to determine the time in Colorado (7 hours behind) that I can safely stop working?

SELECT

Answer: @t AT TIME ZONE 'US Mountain Standard Time'

Explanation: The AT TIME ZONE function allows me to convert a time from another time zone to my own. In this case, I don't even have to go to work. They'll knock off at 5:00am my time. Merry Christmas and Happy Holidays. I'll see you Thursday. Ref: AT TIME ZONE - https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-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

Composite non-clustered index: will it still work if I use only one index col) ? - If I have an index called idxT1_C1C2C3 on table T1 that is non-clustered non-unique non covering, has 3 colums C1, C2, C3 in same order, and I run a query SELECT FROM T1 WHERE C1 = 'value' can such query be expected at all to still use the idxT1_C1C2C3 index? Or […]

SQL Server 2016 - Administration

Why columnstore indexes are not very popular? - I noticed that many SQL Server shops do not even consider or discuss introducing columnstore indexes or in-memory columnstore indexes. While trying to solve performance issues with many queries in many other painful ways, this option is not even on the plate of DBAs and Developers and product owners agenda in many today's IT shops […]

SQL Server Agent replacement - Friends, I have a server, inherited, SQL Server with more than 150 jobs using ETLs, direct invokations for SPs, etc. This server, only job is executing those jobs, because there are no user databases associated. I am just thinking, if could be a better way to program those tasks or my only option is to […]

SQL Server Pivot Table on two columns renaming one column - I've the following source table: --------------------------------------------------------------- | Id | GroupName | RuleName | RuleText | RuleValue | +-------------------------------------------------------------+ | 1 | Group1 | Exclude1 | Excluded (Reason1) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude2 | Excluded (Reason2) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude3 | Excluded (Reason3) | 1 […]

SSRS Email Log review - Hi gang, I need some guidance: I have a SQL server 2016 standard instance set up with SSRS on a remote server environment (running NetForum) I have been tasked with helping the report team allow scheduled reports to get sent via email. Seems simple, right? We are using Office 365. I set up an email […]

can I use Stretch database to migrate the database to azure. - HI Guys, can I use Stretch database to migrate the database to azure.? how ?

Performance difference - I have a user sql from 3rd party started running terrible on prod server. has 128 gig ram, 4 procs plenty of disk space. It basically runs forever and buries tempdb (yes it is a cte and it is ugly). take a backup of prod drop on uat server, 2 procs 16 gig of ram, […]

List of INDEX with create and DROP script should not include any constrains P/F - Hello Sir.. Please help me the script.. From entire database i want List of INDEX with CREATE and DROP INDEX script, But in this should not include any constrains like primary keys and Foreign keys. Please help any one have the script like this? Thank you..

SQL Server 2016 - Development and T-SQL

Pivot Query - /* I'm trying to write a query that uses a list of dates as a header and lists, lets say, employees and if they were in training on that day. I've made a start, but I can't get a pivot working Note: This, by necessity, is a made up scenario similar to what I'm trying […]

SQL 2012 - General

Uninstall SQL without uninstalling SSRS - We have a non-prod server that (long ago) we installed SQL Server on and we were only supposed to install the SSRS components. Long story short, corporate wants us to remove components we are not using due to security concerns. Nevermind that we've disabled the services, there are concerns. I'm not sure there's a good […]

SQL Server 2008 - General

weird sql problem. - Hi all, I´m not sure what I´m doing bad with this query: Situation: I need to select all the purchase order lines(poitem_all) that not exists in the table of requisition lines (preqlines). select po_num from poitem_all results with all the purchase orders (po_num) for the table poitem_all. Ok. Then for select only that not exists […]

T-SQL (SS2K8)

plantation shutter exterior or interior grade - Here is a recent inquiry we received about an oval window, and our response with the customer: Customer Request– Hi there. I have an oval bathroom window for which I’m looking for Plantation shutters the hieght is 30? and the width is 40-1/2? I’m not sure if I prefer the fan -like, or horizontal louvre […]

is there any better way to export SQL server data into csv file? - I have questions of exporting SQL server data, the details as below, thanks! how to use BCP export data from SQL server (table or view or stored procedures) into a csv file (supposing the file path is d:\temp) ? is there any better way to fast export SQL server data into csv file ?

Reporting Services

SSRS log error - We have a SQL server reporting server installed both database engine and SSRS service 2017 on it. The database only hosts the reporting database. I found a lot of repeating errors in the file: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles: 2019-12-10 15:00:04.0060|WARN|5|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file 'D:\Program Files\Microsoft SQL […]

ssrs 2012 column headers repeat on each page while the user is running the rpt - My issue I want to have column headers repeat on each page while the user is running (accessing) the ssrs report. I just started to work with ssrs 2012 reports. Recently I have workd with ssrs 2010 and ssrs 2008 reports. In the past when working with ssrs 2010 reports, I would select the 'advanced […]

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.