Voice of the DBA

Tag: SQLNewBlogger

Getting all Yesterday’s Sales, or Finding Midnight Yesterday
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I see questions like this regularly. How do I get all the sales from yesterday? I tried using DATEADD(day, –1, getdate()), but I only get some of the sales.

Many people working with T-SQL know this is an issue. They know that getdate() returns the date and time of this instant (roughly). At the time of this writing, that’s 3:11 pm.

However if I want sales from yesterday, I really want all timestamps from midnight on. So I probably want code that looks like this:
```
SELECT SUM(ordertotal)
 FROM sales
 WHERE SalesDate > '20151124 00:00:00'
 AND SalesDate < '20151125 00:00:00'
```
How do I get the time to be midnight?

The easy answer is one I’ve been using quite a bit lately to answer questions, and I’ve refreshed my knowledge of the datetime trick. I use a combination of DATEADD and DATEDIFF to get to a 0 based datetime.
```
SELECT DateAdd(Day, Datediff(Day,0, GetDate()), 0)
```
In this case, I’ll get midnight yesterday, or 2015-11-24 00:00:00. This is because I’m using 0 as my base date and looking for the days (in DATEDIFF) since that 0 based date. When I add those days with DATEADD to the same zero based date, I get the correct date, but with a 0 based time.

This same technique works to find the first of this month.
```
SELECT DateAdd(Month, Datediff(Month,0, GetDate()), 0)
```
You can also use other datetime values to normalize those times.

SQLNewBlogger

This was a quick post. I had answered the question and spent less than ten minutes putting this together.
December 23, 2015
Tracking Logins with Extended Events
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was building a question the other day on Extended Events and needed to build a new session. One of the easier sessions to build is with the logins, so I decided to use that, and then wrote this post on how to build the session.

The first thing is to to to the Management tab in SSMS and then the Extended Events folder. I right click the Sessions folder and select New Session.

This gives you a dialog. Like many in SSMS, we start with a name. You could select run at server startup, which I’d do in the case of this being a production system.

When I click next, I get to the "Events" tab, which lists all events. I’ll scroll down to login and select that. I need to click the arrow to the right.

Once I do that, my event is in the session.

After I pick the events, I choose the fields I’m going to capture. There is a "Configure" button in the upper right that you click. This scrolls the dialog over.

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

For auditing, I might want a file. In this case, for testing, I’ll use the ring buffer, in memory storage.

That’s it for getting the session set up. However it’s not started. To do that, I need to right click the session and select Start.

This will allow the server to start collecting data. Is it working? Let’s see. We’ll watch the data. Right click the session again and select Watch Live Data

This pops open a window. I usually make this a separate vertical tab group. Once that’s open, I’ll click "New Query" in SSMS, which will log me in again

As you can see, a few events popped up here. I am capturing data. Don’t forget to stop the session after this if you don’t need it.

SQLNewBlogger

This post came out of work I was doing, and which I’d likely do as a DBA. However as soon as I got things working and tested, I knew this was a good post. In fact, I got a couple posts from the process. The setup and testing took about 20 minutes, including a little research. However the writing for this was about 10 minutes.

References

A few things I used.
- Using Extended Events to review SQL Server Failed Logins – http://www.sqlshack.com/using-extended-events-review-sql-server-failed-logins/
- Mapping Extended Events to SQL Trace – https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-20-of-31-mapping-extended-events-to-sql-trace/
December 16, 2015
Running Powershell with Task Scheduler
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

One of the things I needed to do with my SQL Saturday download process was run this automatically. I don’t want to remember to do this, so I decided to set this up on a schedule.

I ran a quick search and it seemed to be a simple process. Essentially I run the Task Scheduler application and then call my script as a parameter to the PowerShell exe. The first step is to run Task Scheduler.

This has a list of jobs, which I found some to be interesting. I disabled Adobe and a few others. Those are really annoying. I clicked "Create a basic task".

The basic task is a wizard to walkthrough. First enter a name and description. Use something that will help you in 5 months when this breaks.

I decided to run this daily. It’s not a big deal to me, so I’ll let it just go.

The time doesn’t matter, and I let it run in the middle of the day. This way I might notice it running sometimes and that will motivate me to keep working on this process.

I want to run a program, so I choose that in the next step.

The dialog wants to know which program. In my case, I’ll have to browse to my PoSh code.

I pick my code and it puts the path in here. However that won’t run as is. If you’ve used PoSh, you know this doesn’t work.

What I need to do is call powershell.exe. I found this in the reference item below and what I do is add "powershell" to the front of my file, and then use the "-file" parameter. I also enclose my PoSh script in quotes.

When I click next, I get the warning, which I accept.

There’s a summary after this, but I say OK to that, and then run the script from the Task Scheduler. It works perfectly, so I stop there.

Now we’ll see how it works over time. And if I get the next step working.

SQLNewBlogger

This was a quick process for me. A google search, a 2 minute read, and then 2 minutes to build the task.

It took maybe 10 minutes to write this post, most of which was taking the screen shots and resizing them.

You can do this.

References

I just used one link, which was from the Scripting Guy blog.
- Weekend Scripter: Use the Windows Task Scheduler to Run a Windows PowerShell Script – http://blogs.technet.com/b/heyscriptingguy/archive/2012/08/11/weekend-scripter-use-the-windows-task-scheduler-to-run-a-windows-powershell-script.aspx
December 9, 2015
Ending My Loop in PowerShell Early
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was modifying my PowerShell (PoSh) script to download SQL Saturday files recently to not re-download files. However when I did this, I also realized that I didn’t necessarily want the script to run too long.

One of the challenges of downloading the data is that I don’t know how many events exist. We don’t keep that number handy, and it changes regularly. One of the things I decided to do was run my process in a loop.
```
While ($i -lt 9999) {
```
That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I decided to use 12 as my number of missed. No good reason, but that’s what I picked. I started by putting a variable outside of the loop.
```
$missedXML = 0
```
```
While ($i -lt 9999) {
```
Then I increment this variable in the CATCH section of my error handler.
```
Catch 
{ 
  # if we can't load the file, assume we're done for now. 
  $missedXML++ 
  Write-Host "error with  #" $i 
}
```
Finally, I set up an IF loop at the bottom of the loop. If I’ve missed 12 times, I break the loop by setting the counter to the last value.
```
$i = $i + 1
```
```
if ($missedXML -ge 12) { 
 $i = 9999 
}
```
I tested this with some debugging information and what I found was that when I got to 494, I started missing files. As soon as I hit 12, the loop ended.

Enhancement complete.

SQLNewBlogger

This post came about when I started working on the script. I made the modifications from the previous post and decided to also fix the extra loops with this technique.

This post took about 10 minutes to write.

References

No resources needed here. I’ve got enough PoSh knowledge to handle this task myself.
December 2, 2015