SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Stairway to SSAS Tabular Level 6: Creating Meaningful Measures

Thomas LeBlanc from SQLServerCentral

In this sixth level of the SSAS Tabular stairway, learn how to create meaningful measures.

SQL Server Trigger on View Example

Additional Articles from MSSQLTips.com

In this tip we look at how to create a SQL Server trigger on a SQL Server view.

SQL in the City Summits – 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 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.

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 - ASF 021 video invitation (Star Wars Theme)

KamilN78 from SQLServerCentral

Since now, we will not keep our guests in secret. We want you to be prepared and wait if you’re interested in. First time 2 vs 2 in #AskSQLFamily…

From the SQL Server Central Blogs - Drop Database Objects Practically Risk-Free!

Chad Crawford from SQLServerCentral

FIRST OF A TWO PART SERIES Some months ago, a fellow DBA came to me and expressed her concern over the normal housecleaning process that occurred at her company. …

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Getting Dataframes in Python
Which Python command is used to import the data structure and data analysis tools such as the DataFrame() function?
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)

Ignoring Divide by Zero

Which setting do I turn on to allow an error message from a divide by zero to be ignored?

Answer: SET ARITHIGNORE

Explanation: There are various settings that need to be changed in order to experience this, but to ignore the divide by zero error to not be returned to the client, you need to set the SET ARITHIGNORE setting.

SET ARITHIGNORE On
SET ARITHABORT off
SET ANSI_WARNINGS OFF
SELECT 1/0 AS test
GO

Ref: SET ARITHIGNORE - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithignore-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

Featured Script

Add working days but avoid holidays

steve 14359 from SQLServerCentral

A function to add or subtract working days taking into account weekends and using a table of non-working days.

CREATE FUNCTION [dbo].[fns_AddWorkingDays]
(
@StartDate datetime,
@N INT
)
RETURNS datetime
AS
BEGIN
-- This ensures that however the server is configured for dates
-- the function will know the DATEPART(DW values for Saturday
-- and Sunday
declare @SaturdayDW int
declare @SundayDW int
set @SaturdayDW = DATEPART(DW,CONVERT(datetime,'2019 January 5')) -- A Saturday
set @SundayDW = DATEPART(DW,CONVERT(datetime,'2019 January 6')) -- A Sunday
-----------------------------------------------------------------
-- If @N is zero then reduce the date by 1
-- and try adding one day
if @N=0
begin
set @N=1
set @StartDate=DATEADD(DAY,-1,@StartDate)
end
----------------------------------------------------------------
-- If @N GTE 0 then increment dates while counting
-- If @N LT 0 then decrement dates while counting
declare @increment int
if @n>=0 set @increment = 1 else set @increment = -1
-- Work out what to do if the loop encounters
-- a Saturday or Sunday - it depends on the
-- direction of travel.
declare @saturdayadjustment int
declare @sundayadjustment int
if @n>=0 set @saturdayAdjustment = 2 else set @saturdayAdjustment = -1
if @n>=0 set @sundayAdjustment = 1 else set @sundayAdjustment = -2
----------------------------------------------------------------
declare @CountDays int
set @CountDays=0
declare @LoopDate datetime
set @LoopDate = @StartDate
while @CountDays<ABS(@N)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
if DATEPART(DW,@LoopDate)= @SaturdayDW
begin
set @LoopDate=DATEADD(DAY,@saturdayAdjustment,@LoopDate)
end
if DATEPART(DW,@LoopDate)= @SundayDW
begin
set @LoopDate=DATEADD(DAY,@SundayAdjustment,@LoopDate)
end
if exists(select HolidayID from tblHoliday where HolidayDate=@LoopDate)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
end
set @CountDays=@CountDays+1
end
return @LoopDate
END

Database Pros Who Need Your Help

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

Windows Server 2016 or Windows 10 for SQL server!!! - Every MS SQL server I have worked with in the past has been run on Windows desktop because I worked in radio and it was cheaper for our clients (who had very limited funding). I’m now in a position where it’s my decision about going Windows server or desktop on production MS SQL machines that are our […]

Beginner help with SQL Server Management Studio 2016!! - OK, so am still new to the world of SQL. Am teaching myself on the job and have a question I need help with. I’m noticing that it isn’t just as easy as open a query that I’ve saved and running it. The Query windows needs to be connected to a specific database. I’ve […]

Post from the future - I haven’t seen anything to say otherwise, and I post I made earlier suggest the same, but all post times are currently displayed in UTC (I suspect this post will show it was made at around 08:40 on 28 April 2019). This post, however, appears to have come from the future, as it’s going to […]

Get Records from UDT - I’m trying to get data from User-Defined Table type which has the values needs to be inserted into Parent & Child table. In the below-stored procedure, I’m using separate SELECT statement for getting values from User Defined Table and inserting into Parent & Child table. Can you please help me with the following questions? 1) […]

Export results of a Stored Procedure to a PC Folder - Hi Forum, I have a Stored Procedure that selects one record from a table. I want to select one row at a time (maybe using a cursor?) and paste into a Folder on my PC’s ‘C’ Drive. I have below the code to create the Stored Proc & then the script to run it. This […]

Status Update 26 Apr 2019 - Another slow day. New developer trying things, some not working. Old developer out with back issues. A few other things moved to test and got kicked back and fixed, but no real changes. Filed 3 new issues. 1 frontend high priority, 2 frontend low backlog. Hopefully things will pick up speed next week.

SSIS connection manager to a MySQL database - Hello, Looking for some help with creating an SSIS connection manager to a MySQL database. I have tried the following with no luck: https://smallbusiness.chron.com/connect-mysql-ssis-60851.html The error message is: Test connection failed because of an error in initializing provider. Authentication to host ” for user ” using method ‘caching_sha2_password’ failed with message: Access denied for user […]

ADFS database [dbo].[IdentityServerNotificationCleanup] - There are a couple of adfs servers (pri and sec) with backend adfs database. This was installed by an ex-employee and that user is the database owner (account does not exist in AD anymore). However, the ADFS service runs on a service account and that service account also owns the schema for IdentityServerPolicy in database […]

SQLCMD Mode - Hi, I’m just curious. I know when we as database administrators and developers install SSMS on a new machine, we either import or set our preferences. I know there’s 10 or so that I set. One thing I’m curious about is whether you enable SQLCMD Mode to be on by default. Having it on should […]

ssrs display bullet in report - In an ssrs 2012 report, I need to place bullet dot marks on several lines. The dot looks like the following: • . Thus can you tell me on to setup this representation in the SSRS report? There is probably some kind of a character representation that I need to use.

Alias server names - Hello. This should be simple, maybe. I have to work with over a dozen servers all named such as VSKDFJLMM99003. I don’t want to (well, I can’t) rename the servers. But it would be nice that when looking at Object Explorer, they were labeled as something more readable, like “Mortgage_dev” or something like that. Is […]

is_rpc_out_enabled - Can someone tell me if this setting should be set to true or false? I have a list of linked servers and I have noticed that for some, it is set to true and for some, it is set to false.

web.config security best practice - So a dev has created a web app connecting into one of my dbs with a connectionString hardcoded into the web.config. We’re using a sql login. He has left. I want to harden it if possible. Can I create a dsn & reference this instead? Or is there something else I could do to secure […]

Text file import - Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column. For example the format of the txt file is as below but there are hundreds of lines. […]

limitations for RDS - Hello everyone What limitations can I have if I migrate to RDS SQL Server? thank you for your feedback

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.