SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Setting a default schema

The Voice of the DBA

The 2019 Techie Gifts

It's Black Friday in the US, and I assume, most of the world. With the advent of so many online options, I'm not sure how big a day this is for many people, but there certainly are many deals from local retailers. While I do like to buy local, I don't plan on doing any shopping today. It's just too crowded and busy for me. Likely a day of chores around the ranch for me.

There have been a number of Black Friday deals already going on for days. Brent Ozar has had a sale on training all month. Pluralsight had one last year, and I assume another coming. I expect there are other deals as well, but this week I wanted to talk about a few items that I use already or need to purchase. It's the season for giving, so perhaps this will give you some ideas for the tech friends in your life.

I have a couple wireless charging pads I use regularly: one on my desk and one by the bed. It's fantastic to be able to pick up or put down my phone without wires. I also have quite a few long Anker cords for my phone (here's a USB-C and a lightning one). The more I use a 6ft cable in different places, the more I appreciate the length. Especially when charging in a public place. To help, I also have a travel adapter with USB plugs. I've used this often in the US when I need to share an outlet with someone.

I love music, and while I get by with cheap headphones often, I am tempted by a better set of noise cancelling ones. Maybe my wife will read this and get me something. She does carry a JBL speaker with us on trips, which we often use in our hotel room for some nice ambiance. I also carry around a wireless HDMI adapter, which comes in handy on the road, for work and pleasure.

I'm about ready to get a new laptop, and I'll likely go with an HP Spectre again, though the Lenovo X1 and the Dell XPS both look really nice. I'm still debating, and if there's a good sale this weekend, I might grab one. These days I think most laptops will do the job for me, but I like the small, lightweight ones for traveling. I've had no shortage of adapters, but I am thinking to get a small USB-C hub that might work well for a variety of tasks.

I've tended to stay away from any of the voice powered devices, mostly for privacy reasons. I have an older tablet, but most of the time my phone or laptop are what I use, so I don't have a need there, but the new iPads look really nice, as do the Galaxy's. I have a smart watch, and I love it. I certainly see the appeal of the Apple Watch or the Galaxy Watch. It's on my list to pull down my data and start playing, something every data pro might enjoy.

There's a lot more to the holiday season than shopping, but since it's a big part of today, I wanted to list a few items that I've enjoyed, and encourage you to add more in the discussion. Perhaps you'll give someone a good idea for a gift, or an item to put on their list.

Disclosure: These links are affiliate links for me, and I get a small commission from any purchases.

Steve Jones - SSC Editor

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

Featured Contents

View R Plots from within SQL Server Management Studio

Bill Palace from SQLServerCentral.com

Describes how to view R plots in SSMS and gives a brief introduction to R plotting

Power BI Filter Pane

Additional Articles from MSSQLTips.com

Data Filtering | Configurations - In this tip we look at a new feature in Power BI that allows users to filter data in Power BI reports using the new Filter Pane.

From the SQL Server Central Blogs - T-SQL Tuesday #120 – Recap

WayneS from A Discussion of SQL Server-Related Topics

The wrap up post for T-SQL Tuesday #120. 9 bloggers contributed this month.
The post T-SQL Tuesday #120 – Recap appeared first on Wayne Sheffield.

From the SQL Server Central Blogs - DBA Myths: Is an identity column always unique, in order, and without gaps?

Kenneth.Fisher from SQLStudies

Identity columns are all over the place. Probably 80-90% of the tables I see have them and they are almost ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Setting a default schema
I need to create a new user, SSISProcess, and set a default schema for this user. What code should I use?
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)

Happy Thanksgiving 2019

It's Thanksgiving in the US today, and I got called into work to fix a potential issue with our site. The main Redgate site has a game designed to entertain customers by letting them put a series of actions in order to get their turkey dinner ready.

There is a table with this data for the game:

CREATE TABLE Thanksgiving
( StepKey INT IDENTITY(1,1) constraint ThanksgivingPK PRIMARY KEY
, PreparationStep VARCHAR(100)
)
GO
INSERT dbo.Thanksgiving (PreparationStep) 
VALUES ('Buy Turkey'),
 ('Defrost Turkey'),
 ('Remove innards'),
 ('Preheat oven'),
('Butter outside'),
('Fill with stuffing'),
('Bake for 1 hour per pound'),
('Serve and enjoy')
GO

The site is supposed to randomly order these rows every time the page is loaded. This is the query that is being called:

WITH pkCTE (n)
AS
(SELECT ROUND(RAND(StepKey),0)
 FROM Thanksgiving
 )
 SELECT *
  FROM Thanksgiving t
  INNER JOIN pkCTE c
  ON t.Stepkey = c.n
  ORDER BY n

However, this isn't working. The results come back as:

Buy Turkey
Buy Turkey
Buy Turkey
Buy Turkey
Buy Turkey
Buy Turkey
Buy Turkey
Buy Turkey

What can I do to fix this?

Answer: Remove the CTE and just change the ORDER BY to use NEWID() only

Explanation: The problem is that the RAND() function is only called once for the query. This will return a value between 0 and 1, and if this rounds to 1, only the first row is shown. The CTE is unnecessary, and ordering by NEWID() will randomly return the rows. The CTE with NEWID() will return a syntax error when a parameter is used, and a conversion error between a uniqueidentifier and int when no parameter is used. Ref: NEWID() - https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-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

Unable to Join Databases to AG - I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16 I run into a scenario where one or more databases will join […]

Using Storage DeDupe for AlwaysOn Availability Groups - I have a Sr. Sysadmin here who wants to turn on storage dedupe for the three nodes that will be host AG's because he really wants space savings. The setup is that I actually have 3 sets of 3 nodes. Each set of three nodes will be hosting databases from what is now a single […]

SQL Server 2017 - Development

sqlcmd - Unable to pass in exact file path as an input variable to :out - Hi there I have a routine which outputs an ID generated from a stored procedure into a text file as follows: :!!if exist \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt del \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt --:connect localhost\SQL2008R2 :out \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt select Ltrim(Rtrim(@StoredProcedureRunTrackerID)) Now i wanted to change this , so that the path for :out is not hardcoded and passed in as a variable, ie […]

SQL Server 2016 - Administration

Package error - when i ran the package through sql agent job it got failed but i did not find any information in job history Executed as user: . Started: 11:59:55 AM Finished: 12:00:43 PM Elapsed: 47.281 seconds. The package execution failed. The step failed. How can we trouble shoot

Extended events - MY EXTENDED EVENT SCRIPT: CREATE EVENT SESSION [CLogins] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) […]

SQL Server 2016 - Development and T-SQL

The data types varchar and datetime2 are incompatible in the add operator. - In the procedure i have date column as datetime2 declare @date datetime2 = null, @CaptureError varchar(100) when i am logging this variable like below SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL') this is getting error as data types varchar and datetime2 are incompatible in the add operator. what is the workaround for […]

SSIS For each Loop not finding files when deployed - Hi, I have an issue with a SSIS package deployed to the catalog. This package includes a For Each container that scans a folder for CSV files. I can run the package from Visual Studio on my development box using my credentials and it completes successfully finds the files targeted by the for each loop […]

Administration - SQL Server 2014

Problem with calculating an approximate space required for online index rebuild - I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below: total size […]

Development - SQL Server 2014

Determining if more CPU is needed - We have a data import & processing process that often longer than expected, often delaying the time at which users can begin working. I've determined that the CPU runs around 80% for the 1-2 hours the process runs (90 minutes is the SLA), but rarely hits 100 percent. I've found some queries that look at […]

SQL 2012 - General

Join Query, two tables, two databases? - Is that possible? To have two databases with one query? If so the query below should populate some data! If not, how can I accomplish the task? SELECT JobSheet.dbo.JobHrs.JobNo, JobSheet.dbo.JobHrs.ActualHrs, JobSheet.dbo.JobHrs.ReworkHrs, TimeReporting.dbo.TimeData.Time, TimeReporting.dbo.TimeData.Rework FROM JobSheet.dbo.JobHrs INNER JOIN TimeReporting.dbo.TimeData ON JobSheet.dbo.JobHrs.JobNo = TimeReporting.dbo.TimeData.JobNo WHERE JobSheet.dbo.JobHrs.JobNo = '19-0228B'

General Cloud Computing Questions

Archival Options due to Regulation - Hi Guys, I have an ongoing need to provide a archival solution for an ex-production system for Regulatory reasons. In a nutshell: active database is around 30GB, but there will be others pending of varying sizes there may be the need for a couple of queries a year initially, tapering off potentially need to […]

SSDT

Using variables in multiple packages - I have a SSIS Solution built in Visual Studio Community 2019 using SSDT. The solution contains about 15 packages. I am using 5 variables (created in the first package) that I would like to use in all of the other packages. Is there a way to make a variable usable in other packages within the […]

Expression won't evaluate for property - I am attempting to parameterize the three properties for checkpoints in a SSDT 2017 SSIS project and keep receiving this error for the CheckpointUsage property: Error loading: The result of the expression "@[$Project::parCheckpointUsage]" on property cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have the […]

Analysis Services

Row Level Security based on multiple factors - Hey there, I recently got a request from some people here to implement another level of RLS for a couple of tabular models that I manage. Background: I work for a commercial real estate company, so the security that I have implemented is based on region. Someone in Toronto can only view data from Toronto. […]

Events

PASS Local Group/Virtual Group/SQL Saturday organizers corner - A dedicated place for PASS Local Group/Virtual Group/SQL Saturday organizers to share marketing and promotional content as well as discuss what is working and what is not (credit for the idea goes to Cecilia Brusatori)

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.