Voice of the DBA

Tag: syndicated

WAITFOR isn’t a function–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I needed to delay the execution of some code the other day. This was a test that was trying to simulate a few things happening, and one batch needed a random delay. I started typing and got something I didn’t expect.

Where’s the TIME or DELAY? SQL Prompt didn’t like this, and since I expect SQL Prompt to save me from writing bad code, I knew I’d done something wrong. I backed up and removed the parenthesis and ‘R’ and then typed again, this time adding a space.

That works. Now I can change this test to real code.

WAITFOR is designed to delay execution, but it’s not a function. No parenthesis. Instead, it’s a control of flow statement, like CASE, so it just takes other expressions after.

SQLNewBlogger

This was one of those commands I haven’t used in a long time, but is a handy one. Hopefully this 5 minute writeup will help me remember this in the future.

October 11, 2017
Using xp_delete_file
First, this is an undocumented proc, and there’s nothing in BOL on this. Second, it’s a holdover from previous versions, so you’d be better served by using Powershell or some other type of scripting mechanism. This procedure is not supposed to be supported in the future, so I’d remove this from your code. In fact, if you want to just remove these, use Remove-DbaBackup from dbatools.

If you use this, or want to know what to refactor, Patrick Keisler has a nice post on the proc as does Andy Leonard, and there’s a parameter list on StackOverflow. I dug in recently as a customer was having issues, and I needed to refresh my memory.

Essentially, there are a few parameters that you use with this procedure, but bear in mind this only deletes SQL Server backup files or report files. You choose this with the first parameter, a 0 for backup files, and a 1 for report files. The rest of the items are fairly self-explanatory, but keep a few things in mind.

First, the date is a datetime value. Meaning if you just include a date, this is the beginning of the day (midnight). You can see this here. I’ve got some backup files.

I then run this code:
```
EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901'
```
Now, I see this:

The file from Sept 1 still exists because it’s at 9:56am, and the parameter is midnight (2017-09-01 00:00:00). Keep that in mind, and use the appropriate values. If I’d run this:
```
EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901 10:00:00am'
```
The file from Sept 1 is removed.

Next, you need to use the extension to decide which files to remove. That means you might need to have separate calls for .bak and .trn (and .dff), or just remove all old files. The choice depends on what your requirements may be.

Lastly, make sure that if you use separate folders for each database, you set the last parameter.

Again, I wouldn’t use this command, especially not in a modern system, but if you do use this, make sure it’s working.
October 9, 2017
Simplified Networking with a Wsky USB NIC

I used to share an office with my wife. We both needed Internet connections, with both desktops needing Ethernet cables. That was fine when my DSL router was in the office, but at some point we got a new provider and the connection to another room, where we put the new router.

That was an interesting time as I needed to get a range extender when range extenders were rare. Many people used OpenWRT to flash one of a few devices and build a range extender. I almost did that, but discovered a Securefi Almond router that would to the same thing, give me Ethernet locally and extend my wi-fi. That worked for years, but seems to have lost some capability recently. Despite this being connected to a UPS and surge protector, it can’t find some networks and loses its range extender capability about once a week. The speed has also been flaky, seeming to lose some bandwidth at times.

I’m alone in the office, but still need Ethernet, or so I was thinking. As I priced range extenders, I realized that I really only need one port, and I don’t need more range anymore. So I looked at wireless NICs instead. I found the Wsky Wireless adapter, with lots of capabilities, good reviews, and low cost. I ordered one and when it arrived, I started the install.

It comes with a mini-CD, but I don’t have a CD drive anymore. I mean, why? So I found the driver on their site and downloaded it. I ran install, rebooted, then plugged in the adapter. It found my network, I entered the password, and inside of 5 minutes of opening the box, I was connected.

My wife has tried 3 or 4 different USB adapters, with various success. All seem to die in 9-12 months, which isn’t what she wants, but most of them are < US$20, so it’s hard to be too upset. Both of us remember paying $100 for NICs in our careers.

I’ve had this a week, and it appears to work better than my Almond was working. We’ll see how things work over time, but for now, this cleans up my spectrum of wireless devices (2 other routers) and I have one less thing to plug in.

October 6, 2017
Compound FKs with SQL Data Generator
This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

A person was wondering about how data is generated with SQL Data Generator and foreign keys. In this case, the person was having an issue with a compound foreign key.

Here’s a quick look at how this works. I’ve got two tables
```
CREATE TABLE Product
(   ProductCode VARCHAR(30) PRIMARY KEY
   , ProductDesc VARCHAR(100))
;
CREATE TABLE SubProduct
(   SubProductCode VARCHAR(30)
   , ProductCode    VARCHAR(30)
   , SubProductDesc VARCHAR(100)
   , CONSTRAINT SubProductCodePK PRIMARY KEY (ProductCode, SubProductCode)
);
ALTER TABLE dbo.SubProduct
ADD
     CONSTRAINT fk_SubProduct_ProductCode FOREIGN KEY (ProductCode)
                      REFERENCES dbo.Product (ProductCode)
;
GO
```
These two tables are related with a FK. In this case, SubProduct relates to Product, but there is a constraint that says I can’t have duplicate product/subproduct combinations. That’s fine, and my data looks like this:

Now, I have a new table, this one contains a FK back to the other tables. In fact, I have two FKs, though I could deal with one.
```
ALTER TABLE dbo.SubProduct
ADD
     CONSTRAINT fk_OrderItems_ProductCode FOREIGN KEY (ProductCode) REFERENCES dbo.Product
                                          (   ProductCode)
;
ALTER TABLE OrderItems
ADD
     CONSTRAINT fk_OrderItems_SubProductCode_within_ProductCode FOREIGN KEY
                                                             (
                                                                 ProductCode
                                                               , SubProductCode) REFERENCES dbo.SubProduct
                                                             (
                                                                 ProductCode
                                                               , SubProductCode)
;
```
This FK is compound, consistenting of both ProductCode and SubProductCode. What does this mean? It means I can’t have an entry in OrderItems for a SubProductCode unless that SubProductCode has a matching ProductCode in the SubProduct table.

Or, in better English, If I have a Product Code of “RXZP”, of which I have 2 above, I can’t have a SubProductCode of “BA”, which isn’t in the table. I’ve constrained my SubProductCodes to those values that are available for a particular ProductCode. In this case, my only SubProductCode choices are “TJ” and “TW”.

A good data consistency model.

In Data Generator, this DRI is picked up. If I look at the generation for the OrderItems table, I see that both ProductCode and SubProductCode are listed as FK values.

That’s good, but I have two FKs on OrderItems. Which one is important? In this case, I can click the column and see. For ProductCode, it’s using the compound FK.

The same thing appears for the SubProductCode

If I generate data, I then get something like this:

As you can see, my product “RXZP” will all have “TJ” or “TW”. I know this because the FK will prevent anything else. This is handled and enforced by SQL Server, but Data Generator doesn’t create any errors when it generates the data since it’s using the source tables as the domain of possible values.

The User’s Problem

The user in this case didn’t have any DRI declared in this way. Their schema actually had the primary key in Subproduct as only the SubProductCode, not as a compound primary key. Their FK from OrderItems had the same issue, with a FK only to SubProductCode. As a result, Data Generator, and really any script, would assume that all values in SubProductCode are valid, regardless of ProductCode.

The lesson here is that DRI matters, and if you have business rules such as these, use a FK to enforce them.
October 5, 2017