DBAs and the ITIL Framework, Part II: Asset Management (SQLServerCentral 2019-05-17)

 SQLServerCentral Newsletter for May 17, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [DBAs and the ITIL Framework, Part II: Asset Management]( - [The Silent Bug I Find in Most Triggers]( - [From the SQL Server Central Blogs - Launching ADS from SSMS]( - [From the SQL Server Central Blogs - Azure Cosmos DB – Real-time ETL using Change Feed and Azure Functions]( The Voice of the DBA  The Other Jobs There's a meme of sorts going around Twitter. It asks for your non-tech jobs, and I've seen some good ones from [Grant](, [Kendra](, [Erin Stellato](, and [more](. There are some interesting ones, and a few that I'm glad I never had to try. My list is [here](: - Ice bag packer/delivery - Waiter/bartender - Lawn service supervisor - Lay water/sewer pipe - Volleyball coach (current) In my youth, I had a few more (newspaper delivery, painter), but I've had paying computer jobs since I was about 16, off and on, and I had a lot of restaurant jobs. I've actually been a cook, busboy, delivery person, waiter, bartender, and manager. I always enjoyed restaurant work and even had some part time jobs even as I was starting my career in tech. These days I certainly have ranch jobs, usually assigned by my wife, and I do coach competitive youth volleyball over the winter. I know most of us have more in our lives than tech, and some of us might even have second jobs now, whether by necessity or for fun. This week I'm curious what other jobs you've held and which ones you enjoyed. Perhaps there were some you hated. Let us know how rounded your work history is. As I get older, I think about other jobs. Not that I want to leave Redgate or SQLServerCentral, but I'd like to retire at some point and still have something that occupies some time. Volunteering somewhere, with a commitment as a real job, is something I hope to do at some point. Volleyball is great, but one thing I'm looking to try is holding babies at the local hospital. Many of them need companionship when they are born premature or ill and parents need a break. Seems like a good gig to me. What interesting job would you try in retirement? BTW, I know some of you worry about these as a security question, but I've never seen this listed as one of my choices. If you have, then please don't answer. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [DBAs and the ITIL Framework, Part II: Asset Management]( SQLBlimp from SQLServerCentral.com See how the ITIL Framework's Asset Management standards make the DBA's job easier. Use the CMDB and DML to keep your data assets under tight control. [The Silent Bug I Find in Most Triggers]( Additional Articles from Brent Ozar Unlimited Blog I don’t have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you’re not allowed to change the application. As long as you keep the number of statements to a minimum (say, 2-3), and don’t try to do something really slow like fire up a cursor, triggers can be an efficient way to solve hard problems quickly. However, most triggers I run across have a really, really dangerous bug. From the SQL Server Central Blogs - [Launching ADS from SSMS]( Steve Jones - SSC Editor from SQLServerCentral If you haven’t heard, SSMS v18 went GA (Generally Availability) recently. You can download it from Microsoft, though if you have a preview version, you do need to uninstall... From the SQL Server Central Blogs - [Azure Cosmos DB – Real-time ETL using Change Feed and Azure Functions]( Arjun Sivadasan from SQLServerCentral Many people who read my Cosmos DB articles are looking for an effective way to export data to SQL, either on-demand or in real-time. After performing a search term...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Service Broker Sending Directions If I create a contract in Service Broken, what are my options for sending directions of my message types? Think you know the answer? [Click here]( and find out if you are right.  [Redgate SQL Provision](    Yesterday's Question of the Day (by Thom A) ISNULL vs COALESCE What values are returned from the SELECT in the following statements? CREATE TABLE Strings (String1 varchar(5), String2 varchar(10), string3 varchar(5), string4 varchar(10)); INSERT INTO dbo.Strings (String1,String2, String3, string4) VALUES('Hello',NULL, NULL,'Goodbye'); SELECT ISNULL(String1, String2) AS Expr1, COALESCE(String1, String2) AS Expr2, ISNULL(String3, String4) AS Expr3, COALESCE(String3, String4) AS Expr4 FROM Strings; Answer: Hello, Hello, Goodb, Goodbye Explanation: Although similar functions, ISNULL and COALESCE do not use the same logic to determine the return data type. ISNULL returns the data type of the first parameter, and will implicitly convert the value of the second parameter. COALESCE uses [data type precendence]( to determine the data type, as COALESCE is a shorthand CASE expression, and the return value will be which ever data type had the highest precendence. In the statement above, for the expression ISNULL(String3, String4) the value 'Goodbye' is truncated to a varchar(5), as that is the datatype of String3. This behaviour can be important to remember when using differing data types, not just lengths, precisions, or scales. For example, despite being very similar statements, only one of these below statements will successfully run: SELECT ISNULL('A',1); --Returns 'A' SELECT COALESCE('A',1); Returns an error as 'A' cannot be converted to an int. ISNULL (Transct-SQL) - COALESCE (Transact-SQL) - And for a break today: [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 [NOT IN with OpenRowSet]( - Hi, I am trying to use a text file to exclude a list of things. Thus I was hoping to use OpenRowSet to create the file. The query runs, it just doesn't exclude anything. DECLARE @Job_numbers VARCHAR(max) SELECT @Job_numbers=BulkColumn FROM OPENROWSET(BULK 'c:\temp\SampleUnicode.txt', SINGLE_nCLOB) x; SELECT ModelName0 FROM v_Manufac WHERE ModelName0 NOT IN ( SELECT @Job_numbers […] SQL Server 2016 - Administration [Availabiltiy datases on Always on]( - Hello! We have setup Always on Availability Group. All the databases are not added for availability, would it still work while building Tertiary AG?  Thanks.  [SQL Alert for stack dumps]( - I want to create a custom SQL Agent Alert for whenever a stack dump occurs. However I can't seem to determine what the stack dump error number is (for example Error 1205 Deadlock Detected) or what severity level (001 - 025) it would be. Does anyone know? [How to configure automated updates?]( - Hi, My company recently (a couple months ago) migrated a particular application to a new Windows Server 2016 box with SQL Server 2016.  As I review the event logs and SQL logs from last night, it appears that updates to SQL Server 2016 Database Engine Services and Full text Search tried to install, but failed. […] [home lab set up]( - Hi guys, I'm after advice on setting up a home lab. Basically where to get the OS & SQL server software. I'd like a mix of a DC, 2 x 16 with AAAG, a 12 and a 2008. So for the few win OS & SQL isos I require (for my test only) I guess […] SQL Server 2016 - Development and T-SQL [top 5 cpu consumers and collecting the result set in table]( - One of my clients has requested me to gather the information on top 5 cpu consuming queries. I can find several of them online that pulls it from cache and dump the result set into the table but my main concern is that I don't want to insert the same queries back again if it […] [Sum fields with a little twist]( - Hi,  I have got a unique requirement here but I’m struggling to get the right results.  I have table #temp, the data in this table can’t be updated permanently so the underlying table values can’t be changed permanently.  Below is all the code to create a sample table, the data and queries for […] [indexing view]( - Hello. I have 2 DBs (MS SQL Server 2016 Standard Edition). I want to create indexing view to implement some indexes. The view must be able to select data from other database on same server. How can do it ? Thanks in advance) Development - SQL Server 2014 [live update of data from a view into a table]( - Hi all, I have a pending upgrade to an application which changes a database table into a view. The issue I have is I have 4 copies of the same application on different servers in different regions and use the existing table to update a master table hosted locally using triggers. With this change I'm […] SQL Server 2008 - General [Monitor network traffic]( - Is there a way to monitor network bandwidth on a SQL Server? My network admin is telling me that we r using over 1 terabyte of bandwidth per hour and I am not sure how to capture that. Through Perfmon?DMV? SQL Server Newbies [Tasks of Senior SQL Server Experts/Data Scientists]( - Ahoi, i have started working with SQL for a year now. Ive managed to get along at work. My work includes: - SSIS ( ETL soley using TSQL) --SSAS --Backups (Ola Hallengren)  I am far from being an expert in what i am currently doing, but as i said i am getting along. Not […] SSRS 2016 [Change language across all reports in a single SSDT project]( - Hi, I have a number of SSDT SSRS project files where the language is configured EN-GB. These reports are to to be deployed in Australia. I know I can edit each report, reconfigure the language. My question is though. Is there a way of re-setting the language across all the reports in the project? Thank […] Integration Services [List all of the variables in a package]( - In multiple projects I always want to document the variable. Ideally the name, the value and (if applicable) the expression behind the variable. I can not install software so this would have to be done in code (script task) The packages are stored in file system so not available via SSMS. I have tried a […] Certification [Certification information collection]( - Hey guys, sorry if this is a dumb question. The MS website is confusing to me. What is a good path for a beginner looking to get certs for SQL Server? Most of the ones im finding are 2012 SQL Server. Isn't that a bit outdated at this point? What certs are available? Where can […] Resumes and Job Hunters [WPM Typing Requirement for a Systems DBA?]( - Has anyone else seen a job description for a systems DBA with a typing WPM requirement? Ordinarily I would think they want a lot of data entry but are naming the position "database administrator" erroneously but the rest of the job description doesn't make it sound that way. I type with all fingers, two fingers, […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -