Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Take a step towards one of your life goals, however small.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

 

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Remote Data Challenges

With many of us working remotely now, security is going to be a concern for many organizations. While some might have company provided hardware, many of us will use our personal equipment to connect to data and systems. This brings even more challenges to ensuring security than we've had in the past. This might be especially true for organizations that have (or are) scrambling to get everyone connected and ensure access.
 
There are a number of areas in which this might affect us as data professionals. First, we should ensure our devices are patched. Windows, OSX, Android, iOS, whatever platform you run, you ought to be properly patched. This might be especially disconcerting for some home users that run older versions of host platforms. This might be especially true for networking stacks, which have grown more secure over time. It is important for most of us, especially when using privileged accounts, to have fully patched devices.
 
There's also the issue of viruses, ransomware, and more transiting from home systems to work platforms. I know some companies are doing lots of work through remote access, but that's not practical for many of us. I do think the idea of separating out traffic to production systems is a good one, and requiring some sort of remote terminal to a jump box might be preferred. Having some sort of PAW is a good idea as well, though not always easy to set up, especially right now.
 
There are still the actual access rights and least privilege principles we want to maintain. Even as people struggle to get work done remotely, opening up all resources to users that want them can be a poor choice in many companies. We want to maintain adherence to good practices while working in an unfamiliar environment.
 
There is also the idea of good data protection. This piece talks about DR and data protection, something that many of us data professionals think about regularly. I don't know if organizations want to add home machines as remote backup targets, but we should certainly ensure that documents, code, and other information is protected. Workstations and laptops are going to fail, as we want to minimize data loss. Having version control for developers and default save locations that are remote are good practices for remote work. I know I keep most of my data in remote git repos, Dropbox or OneNote drives, or some other shared location that will persist if my hardware has issues.
 
While many of us try to keep moving forward at work, and our management certainly wants us to do so, we should keep in mind the security and data protection are still important parts of our jobs.

Steve Jones - SSC Editor

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

 
 Featured Contents
Stairway to SQL Server on Linux

Installing Ubuntu in Level 1 of the Stairway to SQL Server on Linux

Danilo Dominici from SQLServerCentral.com

In this first article of the Stairway to SQL Server on Linux, learn how to set up your host server.

Free eBook: SQL Server Backup and Restore

Press Release from Redgate

In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool.

From the SQL Server Central Blogs - Working from Home Effectively

John Morehouse from John Morehouse | Sqlrus.com

Some of us have been lucky enough to have already been working from home (WFH) prior to the world events.  This has allowed most of us the ability to...

From the SQL Server Central Blogs - Is there an easy way to tell what pricing tier my Azure SQL DB/DW is using T-SQL?

Kenneth.Fisher from SQLStudies

I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB ... Continue reading

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Getting Dataframe Metadata

I have loaded a dataframe in Python with this code:
>>> import numpy as np
>>> import pandas as pd
>>> sales = pd.read_csv('sales_data.csv', parse_dates=['Date'])
I want to get a list of the columns in this dataframe, their data types, and the number of non-null rows for each column. Which method should I call?

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)

CASTing Binary Values

I get a binary value from a co-worker that looks like this: 0x00006B61.

I am told this is a whole number that is encoded. How should I get the numerical value from this binary value?

Answer: SELECT CAST(0x00006B61 AS int)

Explanation: This binary value can be used directly in CAST() to change this to a whole number. Only the INT shown is a whole number. The answer is:

SELECT CAST(0x00006B61 AS int)

Ref: CAST - https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-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 - Development
Need to Display date wise sales summary. - Hi, I have created a stored procedure like follows select issuedate, SUM((CASE WHEN ISNUMERIC(netpayable)=1 THEN CONVERT(MONEY,netpayable) ELSE 0 END) ) AS TotAmt from tbktym_TicketPrimeDetails where LEFT(CONVERT(DATETIME,issuedate,103),12)>=LEFT(CONVERT(DATETIME,'01-02-2020',103),12) and LEFT(CONVERT(DATETIME,issuedate,103),12)<=LEFT(CONVERT(DATETIME,'03-02-2020',103),12) and cstatus='billed' and branchid=1 group by issuedate The result is showing like 2020-02-03 00:00:00.000 252397.86 2020-02-02 00:00:00.000 500.00 2020-02-01 00:00:00.000 401757.00 2019-02-03 00:00:00.000 119547.00 2019-02-02 00:00:00.000 395913.00 […]
Selecting data between just month and year - I know how to get data between just startdate and enddate, but I need to select it between just month and year, start month is previous July and current year,  so for example, July 2019(startdate) and April 2020(enddate). I am having issues with the between, not sure how to format it. can anyone provide assistance? […]
SSIS Export Help - Hi, Can someone show me (if its possible) how to create an ssis package to take the servername, tablename and todays date as parameters and export this table to a filename with todays date (formatted Filename_ddmmyyy.csv). I've managed to get the dynamic filename but its hard coded to the table and i don't understand how […]
SQL Server 2016 - Administration
How to enable instant file initialization on SQL Server 2016 after installation? - Hello, Is there any way to enable INI after SQL 2016 installation is done?  It seems we can’t add the service account to the "Perform Volume Maintenance Task" policy anymore. Can someone guide me if there's a way? Many many thanks!
AlwaysON cluster suggestion - Hi All, Following is the current setup 2 node OS cluster with AG on different location, primary node if physical and secondary is VM. I would like to move from physical to virtual and from 2 node to 3 node - On primary site 2 node as HA and third node as DR. Which will […]
Administration - SQL Server 2014
Extended Event To Capture Proc Execution - I configured an extended event to capture the time procedures are executed and below is what I have so far CREATE EVENT SESSION [PROC_EXEC] ON SERVER ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(0) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username) WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master, model, tempdb, msdb, UserDB1, UserDB2'))) ADD TARGET package0.event_file(SET filename=N'C:\PROC_EXEC.xel',metadatafile=N'C:\PROC_EXEC.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO I'm having a few issues […]
Development - SQL Server 2014
Number generation based on 3 columns - I have table1 Col1   Col2   update_date ABC   ab        04/04/2020 ABC   ab        05/04/2020 ABC   ab        08/04/2020 BCD   bc        09/04/2020 CDE   cd        11/04/2020 CDE   cd        12/04/2020 Also have extra 4  columns I need result Res     Col1   Col2   update_date […]
I want to find header row dynamically and create table schema with that header. - CREATE TABLE [dbo].[NewTable1]( [18/10/2020 date] [nvarchar](255) NULL ) ON [PRIMARY] GO INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'This is test file') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (NULL) INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'20-20-2020') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (NULL) INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'ID,name,sal') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'10,mahi,2000') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES […]
Convert VBA function to SQL function - I have VBA function need to convert to SQL function. Kindly help me change it. Here is a snippet of the code. Public Function VniToUni(str$) As String Dim VNI$, UNI$, i&, sUni$, arrUNI() As String VNI = "aù,aø,aû,aõ,aï,aâ,aê,aá,aà,aå,aã,aä,aé,aè,aú,aü,aë,AÙ,AØ,AÛ,AÕ,AÏ,AÂ,AÊ,AÁ,AÀ,AÅ,AÃ,AÄ,AÉ,AÈ,AÚ,AÜ,AË,eù,eø,eû,eõ,eï,eâ,eá,eà,eå,eã,eä,EÙ,EØ,EÛ,EÕ,EÏ,EÂ,EÁ,EÀ,EÅ,EÃ,EÄ,í ,ì ,æ ,ó ,ò ,Í ,Ì ,Æ ,Ó ,Ò ,où,oø,oû,oõ,oï,oâ,ô,oá,oà,oå,oã,oä,ôù,ôø,ôû,ôõ,ôï,OÙ,OØ,OÛ,OÕ,OÏ,OÂ,Ô ,OÁ,OÀ,OÅ,OÃ,OÄ,ÔÙ,ÔØ,ÔÛ,ÔÕ,ÔÏ,uù,uø,uû,uõ,uï,ö ,öù,öø,öû,öõ,öï,UÙ,UØ,UÛ,UÕ,UÏ,Ö ,ÖÙ,ÖØ,ÖÛ,ÖÕ,ÖÏ,yù,yø,yû,yõ,î ,YÙ,YØ,YÛ,YÕ,Î ,ñ ,Ñ " […]
SQL Server 2019 - Administration
SQL Server HA - Our network admin created a cluster  (vmware using shared disks seetings - 2 nodes) so plan to install SQL Server AlwaysOn.  After some trial and error, it now has passed the cluster validation and I can continue to install SQL. Are my next steps correct? [1] Install SQL Server 2019 on both nodes as a […]
SQL Server 2019 - Development
Object naming - I have a pretty "dumb" question, but I want to get the consensus on this   I have a table that is going to store Location data (address, geocoding, etc). Location is a reserved word in SQL Server and I have always lived by "do not pluralize a object name". Anyone have suggestions on naming […]
Analysis Services
Migration from Multidimensionnal Cube to Tabular Model - Hello I'm working on a migration from an on premise ssas multidimensionnal cube to an azure analysis services tabular model. Is there a way , a method or a tool to do it quickly and efficiently? It's a large cube and it will take time to develop it from scratch with tabular model. Thank you […]
Dim trying to process deleted attribute - Hi all   We have a Dim table with an attribute that's causing us problems.   We want to delete the attribute while we work on it but it won't disappear. To delete it, we've found the Dim, opened it and deleted the attribute using right-click/delete. We did uncover an issue where, if you check […]
Integration Services
SSIS Not writting all records to OLE DB Destination - Hi, I am importing data from csv flat file  with 481 278 records to  my  database table with  199 998 records already. This 199 998 already in the table are part of the 481 278 meaning i want to load the difference of (481 278 - 199 998 = 281 280).I am using Lookup in […]
COVID-19 Pandemic
Daily Coping 29 Apr 2020 - Today's tip is enjoy washing your hands. Remember all they do for you. My thoughts: http://voiceofthedba.com/2020/04/29/daily-coping-29-apr-2020/
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -