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

Daily Coping Tip

Listen deeply to someone and really hear what they are saying

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.

Tool Limits

In many jobs, there are a variety of tools that people use on a regular basis. Chefs buy knives, mechanics buy tools, doctors buy stethoscopes, etc. While many of us in technology don't always have to buy tools, some of us certainly do. When there is value and it makes sense, many of us have purchased IDEs or add-ins that make our work easier.

Our tools are different than physical ones. We need a place to install them, but we often have employer-owned workstations, which complicates matters with licensing and rights to install. Bringing tools with you, or taking them with you, is often a strange situation, especially as our tools often need "privileges" to connect to other systems, which may be a problem for many employers.

Many employers do provide some tools, and we can certainly build others. We know how to write scripts, utilities, code generators, and more. We may get an IDE or some other commercial tool, which often ensures that all team members work in the same manner. My employer, Redgate Software, builds tools like this for database developers, and we strive to ensure that our tools make your job easier.

Forgetting the hassles of purchase and installs, today I'm wondering what you think about the tool you do use. What tools do you use, and what are the limitations or hassles you find? Are there issues with your tools that inhibit productivity?

These might be ones someone in your organization has written, or those they've purchased. Or maybe you've extended some tools to work in a different way and you're proud of your work. Let us know about how tools might dramatically help or inhibit your efficiency.

Steve Jones - SSC Editor

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

 
 Featured Contents

How to deploy and execute an SSIS package from the SSISDB catalog

Stan Kulp-439977 from SQLServerCentral.com

Beginning with SQL Server 2012, SQL Server Integration Services packages can be deployed and executed from a SQL Server database named SSISDB, which serves as a repository for SSIS packages.

Documenting a SQL Server Data Catalog in HTML and Git Markdown

Additional Articles from Redgate

Dave Poole explains the need for high quality database documentation and then demonstrates how to document the SQL Server database for a data catalog, in both HTML and Git Markdown, using SQL Doc, SQL Data Catalog, PowerShell, and a few helper scripts to ensure consistency and correctness.

Azure AD Authentication for Azure SQL Databases

Additional Articles from MSSQLTips.com

In this article we walk through how to connect to Azure SQL Database using Azure Active Directory authentication along with multi-factor authentication.

From the SQL Server Central Blogs - Rebuilding SQL Saturday–Picking a Board of Directors

Steve Jones - SSC Editor from The Voice of the DBA

With Redgate planning to donating the SQL Saturday brand, trademarks, and domain to a non-profit foundation, there is a need to build a group of individuals to voluntarily manage the...

From the SQL Server Central Blogs - My new Home Studio Setup

Klaus Aschenbrenner from Klaus Aschenbrenner

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance...

 

 Question of the Day

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

 

Checking out a remote branch

I have cloned a repo from someone else, but I don't see one of the branches that is shown on GitHub. I want to work on this branch. How do I check this out and get the code from the branch, mybranch?

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

 

 

 Yesterday's Question of the Day (by The Dixie Flatline)

How Many Rows Inserted?

Without running the code below, how many rows will be in the #table at the end of this batch?

IF OBJECT_ID(N'tempdb..#table') is not null DROP TABLE #table

declare @TC int

CREATE TABLE #table (SomeData varchar(20) primary key)

SET IMPLICIT_TRANSACTIONS ON

SELECT 'After Set Implicit, @@Trancount='+str(@@TranCount)

BEGIN TRAN

SELECT 'After Begin Tran, @@Trancount='+str(@@TranCount)

INSERT INTO #table
SELECT 'X'

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

set @TC = @@TRANCOUNT

SELECT 'After COMMIT @@Trancount='+str(@TC)
union all
SELECT 'After COMMIT #table has'+str(count(*))+' rows.'
FROM #table

if @@TranCount > 0
ROLLBACK

set @TC = @@TRANCOUNT

SELECT 'After ROLLBACK @@Trancount='+str(@TC)
union all
SELECT 'After ROLLBACK #table has'+str(count(*))+' rows.'
FROM #table

Answer: 0

Explanation: In this code, the SET IMPLICIT TRANSACTIONS ON sets a @@trancount of 1.  I am not sure why this occurs, but it means that after the COMMIT, there is still a transaction that is affected by the rollback. At the end, there are 0 rows in the table.  

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
Database list and the last access date - I've SQL like this, SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess) from ( select lastaccess = max(last_user_seek) where max(last_user_seek)is not null union all select lastaccess = max(last_user_scan) where max(last_user_scan)is not null union all select lastaccess = max(last_user_lookup) where max(last_user_lookup) is not null union all select lastaccess =max(last_user_update) where max(last_user_update) is not null) […]
cost licence calculated - Good morning all , How is the cost of the SQL CAL license calculated (Per number of Users) this is the SQL 2019 Standard Edition and according to you which is the cheapest CAL or Core license Thank you for your feedback
SQL Server 2016 - Administration
How to check if a column exists in SQL Server table? - I need to add a specific column if it doesn't exist. I have something like the following, but it always returns false: IF EXISTS(SELECT *   FROM INFORMATION_SCHEMA.COLUMNS   WHERE TABLE_NAME = 'myTableName'   AND COLUMN_NAME = 'myColumnName') How do I check if a column exists in the SQL Server database table?
SQL Server 2016 - Development and T-SQL
BCP - Export Query to Tab Delimited Text File - Dear Group: I am using SSMS and trying to export a query to a text file using T-SQL (I know there are options in the tool itself to do this, but I need this code for a stored procedure). I found examples but none of them seem to work and each gives a different error […]
Does SQL 2016 allow for column encryption? - I have a feeling I've asked this question before, but I can't find my having asked it previously. I'm sorry if I've already asked this question. We have a need to encrypt some data in the columns that they're stored in. However, we've no idea how that is done, both to store the data encrypted […]
SQL 2012 - General
How to compare development and production server database tables schema - Hello Everyone, We have converted a existing web application application from Asp.net Web forms to Asp.net MVC. The existing web-application hosted in dedicated server and the new one we are moving to Ms Azure cloud. On the development server we have Microsoft SQL Server 2012 - 11.0.2100.60 and in Azure cloud environment we have Microsoft […]
SQL Server 2019 - Administration
Connection issue from SSRS to SSAS data source - I have a data source defined on our SSRS server and it connects successfully when I am in IE but from Chrome or Edge, it fails. I ran Server Profiler on the SSAS server and see that when I Test Connection with IE, my user account logs in but when I do the same in […]
SSMS Encryption - Hello, Please forgive the elementary nature of this post -- I've been tasked with encrypting certain fields in our Azure SQL Server DB -- I have never done this and so far I have done the following: -Right-clicking on the column I want encrypted, and select 'Encrypt Column' -After following the prompts, SSMS created a […]
SQL Server 2019 - Development
String Manipulation Work? - Hello Geniuses, I have a string manipulation task that I am not sure is even possible...   Here is the data CREATE TABLE #TEMP ( Id INT IDENTITY(1,1), Sales Money, StateCode VARCHAR(2) ) INSERT INTO #TEMP (Sales, StateCode) Select '1200', 'MA' UNION ALL Select '200', 'MA' UNION ALL Select '120', 'GA' UNION ALL Select '190', […]
tune a table valued function that is using recursive CTE - We have a function that calculates student truancy records by counting their absence in a moving 30 days window. It uses a recursive CTE to count any 30 days passed. Then we use other table to join this table valued function, and it was extremely slow as records increased. I tuned it to select the […]
SchemaBinding and Temporal Tables - I have schema bound a view to a temporal (system-versioned) table and receive no errors in doing do. However, I am able to delete/modify columns from the temporal table, without issue. When I do a query against the view, it fails with a binding error. Has anyone else tried this? Should it work? BTW...on the […]
T-SQL SubQuery Help Please - Hi, I know this will be rather obvious to many, but I am struggling writing a sub-query at the moment. I am trying to return just the 1 row for each BadgeNo attendee based on the latest date updated joining it with a conf_code in table B. (Illustrative data below) Table A        […]
SSRS 2012
How to set up a zoom in the browser - Hi all, I have a problem with zoom option in SSRS. I published my report on the SSRS Report Server. After that I open it in GoogleChrome and when I go to the browser setting and change the zoom (for example 100 to 120) - my parameters are shifted. When I zoom in screen and […]
SSDT
Build SQL Server Data Project on Ubuntu - Hi everyone, we are currently designing CI/CD Pipelines and the build server we have available at the moment is a Ubuntu machine. I investigated a lot and it seems that this combination is just a bad idea and I really need a windows build server. Basically the CI pipeline is just a task to pull […]
Hardware
Block Size on MSSQL on Linux - Do I need to set my block size of drives on linux at 64k allocation size like I do on windowsOS?
 

 

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

 

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