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

A Good Reason to use a VCS

Imagine that you have some important year end procedures in your SQL Server database. Or maybe you have some important, but rarely run code. No matter what the circumstance, if this is a large amount of code, it might be difficult to remember how it's structured or even how it works on different sets of data.

Now, imagine that you have a malicious individual that thinks like Andy Mallon talks about in this post. They run an ALTER PROCEDURE and replace the entire procedure body with "RETURN 0". Now imagine that you don't find out about this until months later when the important code is run. That would be a bad day, and certainly one that might have you struggling to recover the code from backups. Do you have months of old backups? I used to, where we kept an end of month backup for a year.

In most places, we don't have that many backups, and we often assume we'd only go back a few full backups at most in the event of a disaster. I've also been a consultant for more than a few companies that found out that they had 2-10 full backups, all of which were corrupt and from the last few weeks. Sometimes there were older backups on dev/test machines, but in some cases, lots of data was lost.

It's important to run DBCC to catch corruption, but it's also important to keep track of your code. There might not be maliciousness, but simple errors in code that's changed without comprehensive testing. This can be especially problematic for rarely run code that we don't test as thoroughly and won't detect issues for a significant amount of time.

Save your code in a VCS. Git is free. File|Save is in every editor on the planet. There's no excuse not to use a VCS and save code. Redgate has tools to make this easy, but I worked in a file|save environment for years, and it worked. Learn about a VCS and build the habit to backup your code in a repository. It's simple and easy, and you won't know you need it until you do. At that point, it's too late.

Steve Jones - SSC Editor

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

Redgate SQL Source Control
 
 Featured Contents

Soft Skills: Controlling your career

David.Poole from SQLServerCentral

Technical skills come and go while soft skills will serve you throughout your life. They will have the greatest influence over your career, job and role.  For some people soft skills come naturally.  As an Aspergers person (Aspie) I have to practice my soft skills at every opportunity. Pure techies need not lose hope. Soft […]

How to Stop a SSIS Package Execution from Code

Additional Articles from MSSQLTips.com

In this tip we look at a way to stop the execution of a SSIS data flow task that is running by executing an outside process to stop the data flow immediately.

From the SQL Server Central Blogs - My fantasy SQL feature(s)? T-SQL Tuesday #118

Kenneth.Fisher from SQLStudies

It’s fantasy football time again? Oh, fantasy feature? Oh, yea, that makes a lot more sense for #tsql2sday. What’s #tsql2sday? ... Continue reading

From the SQL Server Central Blogs - Tracking Who Last Changed a Row

Bert Wagner from Bert Wagner

This post is a response to this month’s T-SQL Tuesday #118 prompt by Kevin Chant.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional...

 

 Question of the Day

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

 

Hadoop Connectivity

I want to enable my SQL Server 2017 instance to connect to Hadoop through the Polybase technology. When I go to configure the instance for connectivity, how many options do I have for the value to set in sp_configure?

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)

Modifying the Dataframe in R

I have this dataframe in R:

> speaking
  ID   Name Year Events
1  1  Steve 2015     30
2  2  Grant 2015     29
3  3 Kendra 2015     31
4  1  Steve 2016     30
5  2  Grant 2016     22
6  3 Kendra 2016     31
7  1  Steve 2017     24
8  2  Grant 2017     34
9  3 Kendra 2017     27

I want to modify row 3, and change the Events value for Kendra to "NA". How can I do this?

Answer: speaking[3,"Events"] <- NA

Explanation: We can address the data frame like an array, but specifying the row number and the column. This will allow us to change a value, as shown.

> speaking[3,"Events"] <- NA
> speaking
  ID   Name Year Events
1  1  Steve 2015     30
2  2  Grant 2015     29
3  3 Kendra 2015     NA
4  1  Steve 2016     30
5  2  Grant 2016     22
6  3 Kendra 2016     31
7  1  Steve 2017     24
8  2  Grant 2017     34
9  3 Kendra 2017     27

Ref: How to Modify a Data Frame in R - https://www.datamentor.io/r-programming/data-frame/

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
Collation change - Hi, We recently built a server using a prebuilt AWS AMI and the default collation isn't what the application vendor asked for. We ran the sqlservr -m -T4022 -T3659 -s"SQL2017" -q"somecollation" command which seems to have changed the DB collations but the default server collation seems to still be the same SELECT CONVERT (varchar, SERVERPROPERTY('collation')) […]
SQL Server 2016 - Administration
query execution plan - hello experts, i need help to understand query plan and what i can do to make it better . i am really confused about the missing index message .
Check if the statistics are off for table variables - Hi, How do I check if statistics are off for table variables? Thanks,
The EXECUTE permission was denied on the object for user in db_owner - Hello experts, A user who is in the db_owner role for a database reported that he is getting this error: Msg 229, Level 14, State 5, Procedure ReportProc, Line 1 [Batch Start Line 1] The EXECUTE permission was denied on the object... Does anyone know why EXECUTE would fail for an owner of the database? […]
Performance impact by running 2 scripts on the prod server. - I have 2 script which I want to run in our production environment since we don't have a monitoring tool in place and I have a feeling that there is a runaway query which is filling up the log file. The log file grows to be 300+ sometimes. We use DPM (Microsoft product) to take […]
SQL Server 2016 cannot show correct cpu cores in CPU affinity - Hi Guys, I wonder if SQL Server cannot support too many cpu cores? select @@version Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) Jul 21 2019 21:16:47 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) select * from sys.configurations where name = 'affinity […]
SQL Server 2016 - Development and T-SQL
wierd type conversion error - Hi I've been tracking through some code that has a type conversion error from varchar to uniqueidentifier I started commenting out parts of the code and found the offending line of code WHERE vaoq.Questionnaire_ID = '09C4C7B4-1275-460A-AE23-FFA9256B1ABE' I checked that this is a valid GUID (using convert) the only way I managed to get this fixed […]
Administration - SQL Server 2014
Table Rows deleted but not shrinking in size - Hi Folks, a general question here for better admin on a database. Have a number of tables, with approx 100 rows that clear down and re-populate every 5 minutes, however they're growing in size, up close to 10 gb even though the data is small. I deleted one of these tables, but the database is […]
SQL 2012 - General
Database Backup and corruption Jobs - Hi, Please advise of the below scenarios 1Q) I am trying to reduce the sql agent back up job running time. The job pickups a SSIS package which basically takes full backup of all user db's; which is taking 7+ hours to succeed  ( Due to the fact that 2 of the databases are of […]
SQL Server 2008 - General
Recycle SQL Agent Error Logs - hi all I want to keep 1 month worth of SQL agent Error logs and plan to cycle every week  by running : EXEC dbo.sp_cycle_agent_errorlog ( using SQL JOB) How do I do this ? Thanks All  
Reporting Services
permissions to connect to a report server 2016 - I have upgraded a vb.net web form application to be vb.net 2012. This application can currently call a report server that is 2008 or 2010 with no problem. I am trying to get this application to connect to a report server 2016. I am getting the error message, 'The permissions granted to user 'X\User' are […]
RC4 cipher disabled, Report Manager / Server no longer accessible - We not too long ago were required to remove / disable the RC4 cipher suite on our servers.  Shortly after that, I found I could no longer access the SSRS Report Manager and Report Server pages via IE.  At the time, this was not a pressing concern and I let it slide until just recently […]
SSRS 2014
trying to get everything to fit on 1 page - Hello, this might be very easy but not sure, i have an invoice, and after the invoice, i am trying to print the terms and conditions etc., however that needs to always be on a seperate own page... anyway to do that? example: invoice invoice info ... page 2 or page 3 depending on how […]
Powershell
Map a drive to a particular login and make it available all the time -   I am not sure if this is something possible, i would like to map a share to a drive letter using a particular login and if that login tries to hit that server remotely it should be able to see that mapped drive.
Analysis Services
MDX query: how to use the unequal operator between different attributes - Hi all, I am new to MDX and I have already tried Google to find a solution for my query but didn't get any answer by myself. For the use case, I have a role-play dimension (geography), a customer and store. Geography is used both in customer and store. I want to figure out, how […]
 

 

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

 

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