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

Capturing the Application Time

I’ve always thought that any times we recorded were related to the actual data itself. Almost all of the time I’ve built software, we’ve assumed that the time a row is written or updated is the time we need to record as a timestamp. This works great for many applications and it certainly simplifies programming when the database can capture the time. System versioned tables are a great example of where we use time in the database as the time of record.
 
Over the years we’ve started to build more distributed applications that reach a widely dispersed audience. With the Internet today, it’s entirely possible that a row inserted by one user takes place in time zones far away from the next row inserted by a different user. What may be more confusing is that I may insert a row in Mountain Standard Time, but then update that row a day later in the GMT time zone. In fact, that’s something I’ve done.
 
The complexity of time in an application means that we can’t assume we know how best to capture or store time. While I think system versioned tables are a great addition to SQL Server, they are an incomplete one. We need the ability to use these tables with some sort of application time marker, not just the database server time.
 
This week I’m wondering how many of you might feel the same way. How many of you need to capture application time separately from the system time? Do you do that now, with some bespoke system? Perhaps you wish you could and this is a feature your system is missing.
 
There is a feedback request for Microsoft that asks if application time can be captured as a part of the system versioned timestamps. Regardless of whether or not you have the need, this is an important enhancement that would greatly improve the system versioned table feature. Cast a vote today, and let us know if you would use application time in addition to the time in your database server.
 

Steve Jones - SSC Editor

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

 
Redgate University
  Featured Contents

Create Table DDL via TSQL

Lowell from SQLServerCentral.com

A stored procedure from longtime community member Lowell Izaguirre, returns the CREATE TABLE definition for any table, including PK, UQ, Identity, FK, Defaults, and more.

GDPR in the USA

Grant Fritchey from SimpleTalk

GDPR enforcement began in May of 2018, but if you are doing business in the US, you may not think it applies to you. Grant Fritchey explains why you might be wrong about that and why you need to act now.

Scripting Custom SQL Server Clones for Database Development and Testing

Additional Articles from Redgate

Phil Factor shows how to automatically apply T-SQL modification scripts during image and clone creation. Using this technique you can, for example, apply data masking to all clones, or customize an individual clone to work on a special variant or branch version, or set up instance level objects like Agent jobs or linked servers.

SQL in the City Summits – UK, US & Down Under

Press Release from Redgate

Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. Register now

From the SQL Server Central Blogs - Validation activity in Azure Data Factory – Traffic light of your operational workflow

Rayis Imayev from SQLServerCentral

(2019-Apr-02) Last week Microsoft introduced a new Validation activity task to the Azure Data Factory (ADF) – https://docs.microsoft.com/en-us/azure/data-factory/control-flow-validation-activity  Initially, I was confused by its name, thinking that it would help me…

From the SQL Server Central Blogs - Testing with Temporary Stored Procedures

Bert Wagner from SQLServerCentral

A while back I learned that it’s possible to create temporary stored procedures in SQL Server. I never put that knowledge into practice however because I struggled to think of…

 

  Question of the Day

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

 

Renaming a login

I am concerned about security on my SQL Server 2017 instance. One of the things I've heard  is that I should not use the "sa" login, and should both disable and rename it. How can I rename my sa login?

Think you know the answer? Click here, and find out if you are right.

 

Redgate SQL Provision
 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Executing Child Packages

When using the Execute Package Task in SSIS, when items can I not configure for the child package execution?

Answer: LoggingLevel

Explanation: The logging level is not set in the Execute Package task. While you can configure logging in a package, it's not something the task changes. The out of process state, reference type, and password are settings. Ref:

Discuss this question and answer on the forums

 

Featured Script

CTE Example with Pivot operator

KeithDB from SQLServerCentral

A short demo of a CTE used to perform aggregations.

CREATE TABLE orders
( order_id int identity(1,1) primary key
, amount numeric(10,2)
, order_dt datetime
) ;

INSERT orders
( amount, order_dt ) VALUES
( 10.01, '4-01-2019' )
,( 9.99, '3-16-2019' )
,( 7.01, '1-15-2019' )
,( 23.46, '4-15-2019' )
,( 3.16, '2-14-2019' )
,( 4.13, '2-11-2019' )
,( 12.14, '7-04-2019' )
,( 13.16, '5-20-2019' )
,( 6.10, '8-23-2019' )
,( 1.53, '1-01-2020' )
,( 40.66, '1-18-2020' )
;

;with cte_amt as
( select [amount], year(order_dt) as [yr], yr_amount, month(order_dt) as [mo]
from [orders] o
JOIN ( select year([order_dt]) as [yr], sum([amount]) as [yr_amount] from [orders] group by year([order_dt]) ) as y
ON year([order_dt]) = y.[yr]
)
select yr as [Year], [yr_amount] as [Annual Total]
, coalesce([1] ,0) as [Jan], coalesce([2] ,0) as [Feb], coalesce([3] ,0) as [Mar]
, coalesce([4] ,0) as [Apr], coalesce([5] ,0) as [May], coalesce([6] ,0) as [Jun]
, coalesce([7] ,0) as [Jul], coalesce([8] ,0) as [Aug], coalesce([9] ,0) as [Sep]
, coalesce([10],0) as [Oct], coalesce([11] ,0) as [Nov], coalesce([12],0) as [Dec]
from cte_amt
pivot(sum(amount) for mo in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as pvt
;

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


Named Link BBCode bug - I think I figured out what is causing the bug to incorrectly reformat certain links such that they are no longer a link and end with “/[url]” being visible.   URL-only links work just fine: [url]some_URL[/url]   But, I just noticed that if I save a named link: [url=some_URL]text instead of URL[/url]   it gets […]
Login issue for one Instance on failover node - Hello, We have two fail-over node 01 and 02 and three instances (Ins1, Ins2 and Ins3) on these two nodes. I can able to login to all three instances from node 02 using my windows authentication but i can’t login to only Ins3 on node 01. I am receiving error as below while trying to […]
Having issues with Excel source in SSIS - I don’t have a lot of expertise with SSIS but when I create a simple package (source “Excel” and dest “SQL Server”), I run into some issue and wondering if I can get some help. I have an excel sheet (source) with handful of columns but when I preview it, it shows some values as […]
Status Update 12 April 2019 - My apologies for missing the 11th. I got busy with other work unrelated to the site and had to focus on that. It started out as a rough week, especially for me, but things have improved. Some big changes here that I think smoother out the migration quite a bit. Highlights for now: Search has […]
Stored Procedure for three different date ranges - Hi Guys, I have to create a montly SQL agent job for Stored Procedure where it can pick dates automatically.  I have a stored procedure with two parameters @begindate and @enddate. As this is April, I want this Stored procedure to run for 3 months. Now I am hard coding this, but I want this […]
Creating and Joining CTEs in SQL - I am new fairly new to SQL coding.  I am trying to write and join two CTEs to return one data set so that I can create a master CTE that will return the data desired by the end user.  I am running in circles chasing the error messages. 
Two bugs in forums migrated from old site - Links to the old forum topic URLs that were to pages other than the first / initial page of the topic are redirecting only to the first / initial page of the topic. For example: https://www.sqlservercentral.com/Forums/2001850/HTTP-Requests-Using-SQLCLR?PageIndex=2 That URL takes you to: instead of: (it’s missing the “/page/2” part at the end). Some formatting is mangled. […]
Derived colum expression with strings removing 0 - Hi, I am setting up a derived column to combine a variable that is entered to a previous derived column with a dataflow value. (DT_I4)@[User::Actual_FileName] in this example = 1000 and becomes the derive column called TMC_Ident Branch Key = ‘072’ In turn, (DT_STR,4,1252)TMC_Ident + (DT_STR,6,1252)Branch_key should therefore = 1000072 but is coming out from […]
Update trigger and concurrency issue - I built a table – and the DBA added a mandated UPDATE trigger to automatically update standard audit dates.  I update the table from a number of sources with several statements.  Every time, some of them produce the following error: A Concurrency problem exists. [my table] was just changed by another user. the code in […]
Need to extract a query result to CSV from a list of servers - I have been trying the following PowerShell script to extract a query result from a list of SQL instances to a single CSV output file. It seems to be a common task, but I have been struggling with it. I got a file generated, but empty. So either I am not looping through the list […]
Auto-scroll throughpages - Hi all I’ve got a 2-page SSRS report (SSRS 2016) which auto refreshes every 60 seconds. I want to alternate between page 1 and page 2 at each refresh. I can’t find any sort of setting which will allow me to do that. Has anyone got any ideas on how it can be done? TIA
SSAS Tabular Model Row Level Security with Excel, PowerBI and SSRS - I am working on a solution where the data is coming from a SSAS tabular model and I am looking to implement dynamic row level security. I have got this working by filtering the security role within the model for both the sales person and then the sales manager. When I test this through SSDT […]
Making report run for different users in a single subscription. - I have created a report that has a filter on the dataset that filters the information on the report based on the user that is viewing the report.   What I am wanting to achieve is to use one single subscription to email a PDF of the report to all users however when I try […]
SQL Merge Replication – 2x Identical Schema DB with different data – HELP! - Morning All, I’m trying to Merge together Two Identical Schema Databases into a single DB. If we think of each Database as a separate shop that is setup identical and sells the same products. I need to merge two or more together into a single Database. The first answer that’s going to fly in is […]
Is it a good practice to mix surrogate and natural keys? - When designing a database, some tables do not have natural keys, but many of them  have natural candidate keys. So, I want to know which is considered a good practice, if I am sure going to use some surrogate keys, it it better to keep natural keys for valid tables or just created surrogate keys […]
 

 

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

 

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