SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Partial Dataframe

The Voice of the DBA

Staying Safe when Traveling

Today we have a guest editorial from Grant Fritchey as Steve is away on his sabbatical.

If you travel for business like I do, a big part of your travel involves dealing with the fact that the world is a hostile place. No, I don’t mean the airlines. I mean all the possible security risks that you must deal with. Here’s just my latest example of protection.

That is what is commonly referred to as a USB prophylactic. Basically, it allows you to connect your phone, or whatever, to a public USB port without having to worry about getting hacked. Yes, that’s right, you should be worried about getting hacked when you’re charging your phone.

Speaking of phones, you’re using a VPN on your phone, right? No? Are you connecting to public WIFI? I know a few people who say they never connect to public WIFI, hotel WIFI, airport WIFI, or any other kind of WIFI that is not under their direct control (most of them don’t travel much). The rest of us are going to take advantage of that free WIFI so we can get work done, check our email, or just watch the darned football game for a few minutes. However, unless you’re encrypting your signal, you’re exposed on that public WIFI.

You’ve got a password on laptop, right? You also have encrypted the storage haven’t you. Wait? You haven’t? Yeah, you need to sweat that your laptop may end up in the hands of someone who does not have your best interests at heart. So, encrypt that puppy in addition to securing it through traditional means.

Security is a never-ending battle, and even more so when you become a road warrior. A few more tips. Turn off Bluetooth and Wi-Fi when you’re not using them. Make darned sure you’ve got the latest updates on your mobile devices. Put your credit cards in a security sleeve or wallet. Don’t use those shared computers to log in to anything, ever (dire emergencies excepted, but plan to be hacked). Have a firewall on your laptop. Disable file and printer sharing. All this will help.

Hitting the road may sometimes feel like hitting the Oregon Trail. Just remember, getting hacked is not quite as bad as dying of dysentery. However, unlike death on the Oregon Trail, there’s a lot you can do to mitigate problems on the road.

Grant Fritchey

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

Featured Contents

Stairway to SSAS Tabular Level 2: Installing the Components

Thomas LeBlanc from SQLServerCentral.com

In this level of the Stairway to SSAS Tabular you will learn how to install the platform and tools.

Load Time Series Data with SQL Server

Additional Articles from MSSQLTips.com

In this article we look at how to load time series data into SQL Server both for an initial load as well as adding new data.

Custom Deployments from Source Control using SQL Compare Command line

Additional Articles from Redgate

Giorgi Abashidze explains how his team use a 2-phase deployment process with SQL Compare Command line, and some SQL Synonyms, to automate custom deployments for each of their customers, while only needing to maintain one branch per release in source control.

Free eBook: Understanding SQL Server Concurrency

Press Release from Redgate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do.

From the SQL Server Central Blogs - Parameterizing a REST API Linked Service in Data Factory

Meagan Longoria from Data Savvy

We can now pass dynamic values to linked services at run time in Data Factory. This enables us to do things like connecting to different databases on the same...

From the SQL Server Central Blogs - CREATE TABLE MyTable (Col1 CHARACTER VARYING (50))

Kenneth.Fisher from SQLStudies

If you read the title to this post and your first thought was That’s not SQL is it? then you ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Partial Dataframe
I have this dataframe in R: > fantasy.playoffs Team Score Opp OppScore 1 Way0utwest Cowboys 135.40 NA 2 SSC Ravens 66.26 Green Machine 66.26 3 Ditka 85.20 NA 4 Orange Engineers 111.52 Mexicanos 111.52 When I run this, what is returned? > fantasy.playoffs[2:3,]
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Grant Fritchey)

Query Optimization Level

Where within the execution plan can you find what level of optimization was performed on the query in question?

Answer: In the properties of the first operator.

Explanation: The optimization level is always stored with the execution plan. Whether you get the plan from cache, Query Store, extended events, or capture it through Management Studio, the optimization level is stored with the plan. Whether we're talking about an Execution Plan, or an Execution Plus Runtime Metrics, the optimization level is always there. The properties that make up the first operator are stored within the XML of the plan, but they are visible from more than one location, not only, ever, in the XML.

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

FTS - Does Crawl Completed??? And wait untill next query can get the results! - Hi, Actually with Full Text Search FTS we have a table on which we just insert thousand of rows; and then in very next statement try to fetch data with contains query. The index is not built within this span of time (even Auto) so what would be the solution to this problem; or even […]

Uncommitted implicit transactions - I've searched on this topic, but haven't quite been able to get the answer I'm looking for. I have a situation where our ERP system database ends up with uncommitted implicit transactions that sit there indefinitely and end up causing excessive tlog growth that eventually fills the disk. I am working with the vendor to […]

Securely query Active Directory - Hello SSC, I need to query Active Directory from a stored procedure, and have been successful with getting it set up - however, the only way I know how to get it working is to set up a Linked Server (OLE DB Provider for Microsoft Directory Services) with having an active directory user name & […]

SQL Server 2017 - Development

Optimizing view and Table - We have a table (tblASAP_Detail_v3) which we fill from different sources and which was derived from an Excel with around 100 columns. This table will then be used in a view we use for an Excel Pivot. The table has grown and grown and needs around 3.7 GB for 2.2 Million records. (Every year it […]

SQL Server 2016 - Administration

Memory grant - SQL can't estimate OpenJson querying and is Granting excessive memory approx close to 1 GB by looking in the execution plan warning. It also uses a lot of memory to query out the Hash Joins. Has anyone came across this issue?

Sql server 2016 SERVICE PACK 2 - SQL server service pack installation just closes. Would anyone know what is this? Also I tried with SP1, the GUI just closes and no errors. Even tried a hot fix exe same thing. only SQL SERVER installed successfully(manually installed) Thanks The message I see in log is : Saved .Net security policy file 02/03/2020 17:45:04.241 […]

SQL Server 2016 - Development and T-SQL

get year/month min/max from dates - I have 50 tables that have all the columns as varchar. The columns have different data in each one such as dates, integers, currency, etc... Does anyone have a dynamic way to get the year/month/min/max of all columns that contain date information?

SQL 2012 - General

How to run dynamic pivot table without using exec(sql statment) - I working on sql server 2012 I need to run query below without using exec(sql statment) I try that but i face problem on single quotes on this line '+@column+' my statment i need to run as below : DECLARE @Columns as VARCHAR(MAX) SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName) FROM --distinct FT.FeatureName […]

How to add feature value without prevent group data based on itemid pivot table? - How to add feature value without prevent group data based on itemid pivot table? I work on SQL server 2012 I make pivot table based on itemId it work good but after add featurevalue data repeated and not grouping How to add Featurevalue without prevent repeated data on pivot table ? desired result ItemCode IPN […]

SQL Server 2019 - Development

Outstanding balances for invoice table - I have a table with cust references payment type pi or pa, amounts and transaction dates The table design is actually not great because the if the incoming and outgoings transactions were always linked by reference then I could use that but unfortunately they're not and I have to work with what I have. so […]

SQL 2019 Bug : "SELECT @local_variable" - Hi, It seems that the statement "SELECT @local_variable (Transact-SQL)" no longer works as it is documented by SQL Docs an now returns incorrect data. According to SQL Docs : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15 SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If […]

Deploying an SSIS Solution - Target Server Version 2016 vs 2019 - We have a development environment set up with SQL Server 2019, and SSIS projects that are currently built using SSDT 2015 with a target server version of SQL Server 2016. As we're looking to begin testing on the SS2019 box, I noticed that SSDT 2015 doesn't have a "TargetServerVersion" of 2019 - we'd have to […]

Reporting Services 2008 Development

Need to display the query result in colums - Query a table gives this result ContractId ContractStartDate ContactEndDate ContractValue ContractManager 1 2019-11-01 2020-06-30 45000 Stephen Smith 2 […]

SSRS 2016

Calling SSRS report from ASP.Net - I have a web page in Web Forms with a Script Manager, a Button and a Reportviewer. I want to click the button and run my report. The report is not populating. My code is as follows: Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Try ReportViewer1.ProcessingMode = ProcessingMode.Local Dim myreportviewer As ServerReport […]

Integration Services

SSIS 2012 connection to Azure BYOD - we do have SQL server 2012, ssis 2012 on-premise DW environment. Now we need to bring data from AZURE BYOD vendor database server. Please let me know if any one have suggestions. Currently we are not ready to upgrade the server.

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.