SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Unwired for Weeks

I took a few weeks off recently for a vacation with my family. We traveled from Denver to Yellowstone National Park in Wyoming and then on to Glacier National Park in Montana. We drove a horse trailer, camping along the way in the vast outdoors of America. It was a wonderful time and a beautiful part of the country. If you get the chance to visit, I'd highly recommend the trip.

One of the interesting things on this trip was that I was very unwired. In fact, I didn't even take a laptop, which is a rarity for me. Often I'll take one to jot notes, sketch an editorial, or just have around to check on things. This time, I didn't bother, and neither did my wife. One kid did, but only because he was enrolled in a couple classes (CS and Math) this summer and had assignments to do. 

The only device I had was my phone, which didn't have service for much of the trip. Both during long drives (I was glad Google Maps downloads directions), at many of our campsites, and in the national parks, there was little service. In fact, when I did have service, I was surprised. The camping locations did have wi-fi, but I only used it to upload pictures of the trip and didn't even bother checking social media as I posted. I mostly uploaded pictures to ensure I had a backup. 

I did check email once early on to be sure that I hadn't forgotten anything from work early, and forwarded on a couple items to people, but when I arrived home last week, I had hundreds of messages from Redgate and probably close to a thousand from SQLServerCentral. It was quite a chore handling and deleting a number of items, and took half of my Monday back in the office.

What was interesting to me is that I didn't really miss the social media much, certainly didn't miss the news, and nothing that important came up. I didn't worry about work, knowing that something would go wrong (such as a few buggy questions) and that someone would handle the situation (thanks to Grant for fixing things) or delay the item until I returned. I found numerous meetings canceled or rescheduled without me and quite a few hastily dashed emails or queries that were later retracted or amended. In fact, going through emails from newest to oldest allowed me to delete older, out of date messages without reading them.

I rarely take more than a few days off at a time, usually just a long weekend here and there, but this proved (again) something I've suspected for a long time. Much of the pressure we feel with deadlines at work is arbitrarily imposed. Whether we do it to ourselves or someone else picks a datetime, there isn't any rationale for the choice. Usually the goal is to complete work as fast as possible, but if there are delays, if something comes up, with life, other tasks, unforeseen failures (hardware/software) get in the way, the business will survive.

I learned a long time ago that despite my best efforts, things will go wrong and there will be delays. I've learned to expect them, accept them and try to not add any extra stress on myself when things are delayed. Go home at a reasonable hour most of the time, live the rest of your life, while doing the best professional job you can. There will always be more work, and the business will survive if most of your work ends up being delayed slightly.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 6.2MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
Database DevOps

Benchmark your Database DevOps maturity level

Get a better understanding of how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. Complete the Database DevOps Maturity Assessment

SQL Monitor

What’s the top challenge faced by SQL Server professionals in 2018?

Learn how 626 SQL Server professionals monitor their estates in our new report on the State of SQL Server Monitoring. Discover the challenges currently facing the industry, and what is coming next. Download your free copy of the report

Featured Contents

 

Using a CTE as a Tally Table

Adam Aspin from SQLServerCentral.com

Sometimes you need to output blank lines - in effect displaying Records for Missing Data. This article explains how to use a CTE to do this More »


 

The Top Five Things That DBAs Need to Monitor

Additional Articles from SimpleTalk

Being a database administrator is much more than knowing how to install SQL Server and set up a database. One of the most important responsibilities is being proactive by monitoring the instances in their care. But, what should be monitored? Here are the top five things to monitor when you are a SQL Server DBA More »


 

The march of DevOps from the backroom to the boardroom

By encouraging collaboration and teamwork, removing the barriers between development and operations, and introducing automation, DevOps speeds up software delivery and enables features to move from the fingers of developers into the hands of customers faster More »


 

From the SQLServerCentral Blogs - Creating SQL images in Azure with ACR Build – Part Two

Andrew Pruski from SQLServerCentral Blogs

In Part One I detailed how we can use ACR Build to push SQL Server docker images to the Azure... More »


 

From the SQLServerCentral Blogs - There’s Still a Place for SQL Server Profiler

Matthew McGiffen from SQLServerCentral Blogs

Follow a few of the SQL Family on Twitter and you’ll mostly see one view regards SQL Profiler, and it’s... More »

Question of the Day

Today's Question (by Steve Jones):

On SQL Server 2017, I have this data in a table:

 CREATE TABLE Scorers ( playername VARCHAR(200), points INT) GO INSERT dbo.Scorers VALUES ('Kareem Abdul-Jabbar', 38387), ('Karl Malone' , 36928), ('Kobe Bryant' , 33643), ('Michael Jordan' , 32292), ('Wilt Chamberlain' , 31419), ('DIRK NOWITZKI' , 31187), ('LeBRON JAMES' , 31038), ('Shaquille O''Neal' , 28596) GO 

I decided to run this query:

 SELECT 'Player' = s.playername , 'CurrentValue' = s.points , 'Behind' = LAST_VALUE(s.points) OVER (ORDER BY points DESC) - s.points FROM dbo.Scorers AS s ORDER BY s.points;

What results do I get for the Behind column?

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: LAST_VALUE.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Pro Power BI Desktop

This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I want to detect in my python source file whether this file is being run as a script or being imported into a REPL or other module. What variable allows me to detect if I've run the file as a script?

Answer: __name__

Explanation:

The __name__ variable will either contain the name of the module or "__main__", depending on context. If the file is run as a script, then the value is __main__.

Ref: __main__ - click here


» Discuss this question and answer on the forums

Featured Script

SQL table to HTML

Eduardo Pivaral from SQLServerCentral.com

Convert any table or select query to a html table format

FROM: www.sqlguatemala.com

SP: sp_TabletoHTML

Version: 1.1

AUTHOR: Eduardo Pivaral sqlguatemala.com

MIT License

This stored procedure converts a table or select query to a HTML table format, with some customization options.

I have taken as a base, a script Carlos Robles (dbamastery.com) provided me for a static table, so i modified it to accept any table and apply different or no styles, also you can output or not the column names to the table.

NOTES:


  • This SP works with dynamic queries, also data is not validated, so it is vulnerable to SQL injection attacks, so always validate your queries first.

  • Null values are not converted on this initial release, so before using it, remove null values from your data.

  • Some special datatypes like geography, timestamp, xml, image are not supported, if you try to use them, an error will raise, remove these columns before using it.

  • This tool is not designed to handle huge amounts of data, so, for massive information you can split them in various executions.


PARAMETERS:


  • @stTable: input table or SELECT query, a schema.object or SELECT query format
  • @RawTableStyle: OUTPUT variable, to use in another process or programatically
  • @includeColumnName: 0=does not include column names | 1=include column names (DEFAULT)
  • @TableStyle: 0=no style | 1=black borders (DEFAULT) | 2=grey style | 3=lightblue style | 4=zebra-striped table

SAMPLE EXECUTION:


Most basic usage, table name and all defaults to query window

 EXEC sp_TabletoHTML @stTable = 'sys.dm_os_windows_info'

Output:

table output

SELECT QUERY, all defaults

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @RawTableStyle = @st OUTPUT

Output:

table output

Remove column name

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @includeColumnName = 0, @RawTableStyle = @st OUTPUT

Output:

table output

Gray style with columns

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @TableStyle = 2, @RawTableStyle = @st OUTPUT

Output:

table output

Lightblue with columns

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @TableStyle = 3, @RawTableStyle = @st OUTPUT

Output:

table output

zebra-striped table

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @TableStyle = 4, @RawTableStyle = @st OUTPUT

Output:

table output

Remove style and columns

 SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @TableStyle = 0, @includeColumnName = 0, @RawTableStyle = @st OUTPUT

Output:

table output

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.

SQL Server 2017 : SQL Server 2017 - Development

parsing log data - hi everyone, i have case to parsing the log data like this on sql server { "Name": "David", "Gender": "Male", "Address":...

formatting T-SQL - Hi, I found one formatter online, but wanted to know if you use any tools to format the T-SQL code , before...

SQL help with date comaprisons - Hi,  I have a scenario where i need to calculate the number of days of hold on a loan . Example is...

Fact-and-Dimension vs Storing Character Values - Hey Everyone! I am new here, and have a question about "best practice" data warehouse design. I am facing a requirement to...


SQL Server 2016 : SQL Server 2016 - Administration

Separate DB, log and Temp drives ? - These days, is there actually any point ? Using VM's and SANS, is there any real point in separating data, logs...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Execute same SELECT with a different condition - I have this code. SELECT  CT.claim_type_name AS ClaimType,  COUNT(CM.claim_type) AS Cases,  ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal INTO #ChosenYear FROM ..dbo.t_claim_type CT  LEFT OUTER JOIN ..dbo.p_claim_main CM  ON CM.claim_type = C

Partioning Huge Table (650GB about) - Hi all, in my data warehouse (SQL Server 2016 Std) I need to maintain 36 (!!) versions of a table in order...

Stored Procedure Analysis and Design - I’m pretty new to Sql and I’ve been given a set of reports I need to develop. Specifically, I’ve got...

Question: performance loss on update statistics after upgrade from SQL Server 2012 - Good afternoon, I'm currently working for qn SAP consultacy firm, as a junior SAP technical consultant. But due to my preveious work...


SQL Server 2014 : Administration - SQL Server 2014

DB Autogrowth - Hi Experts, Which option is best Filegrowth by 10% or in Megabytes for a large database that grows with average 40GB...


SQL Server 2014 : Development - SQL Server 2014

Count the amount of times values appear in the database - Hello, I have a table of "jobs" which I need to export to an online accounting software. What I need to...


SQL Server 2012 : SQL 2012 - General

Move some tables onto a different database. - Hi Y'all I'd like some advice. My problem is there are a pair of audit log tables (AuditLog and AuditLogDetail) that...

BCP Error - Hi All: I'm trying to import data using bcp however, my I keep getting the below  message. I have checked the...


SQL Server 2008 : SQL Server 2008 - General

Question on query to show DB names, status, recover model & backup size. - I'm an Oracle DBA that used to work with MSSQL a few years ago, and now find myself taking over...

split a comma delimited string into columns - DECLARE @p NVARCHAR(MAX) SELECT @P = 'MONDAY,SUN,FUN,D' DECLARE @STRSQL NVARCHAR(MAX) SET @SQL = 'SELECT ''' + REPLACE(@P,',',''',''') + '''' EXEC( SQL) Above Code is splitting the comma delimited string into...


SQL Server 2008 : T-SQL (SS2K8)

please help !! Dynamic SQL - Hi experts Please I REALLY NEED YOUR HELP !! I want to create a dynamic sql in a cursor. I want to replace...

DATEPART WEEK start Monday - Hi, I'm doing this directly in a computed column: case when

Insert XML data with Namespace into SQL Table - I am in a situation where a system throws a xml file ( I don’t have any control to change this...


Programming : Testing

tSQLt AssertEqualsString - Is it possible to force a case sensitive comparison? ie. i want this to fail: EXEC tsqlt.AssertEqualsString 'Test','test';


Data Warehousing : Integration Services

Need help to extract unstructured excel data to sql server - I have 12 different Excel files which are having multiple tabs in every file and data in really unstructured. What's...

This email has been sent to newsletter@newslettercollector.com. 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.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com