Tuesday, December 13, 2011
Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.
Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.
One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data:
-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))
INSERT dbo.JustTryIt VALUES(1)
I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that.
However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all:
CREATE TABLE NoSA(a int not null,
CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))
CREATE TABLE NoSysAdmin(a int not null,
CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))
CREATE TABLE NoAdHoc(a int not null,
CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))
CREATE TABLE NoAdHoc2(a int not null,
CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))
CREATE TABLE NoCursors(a int not null,
CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))
CREATE TABLE ANSI_PADDING_ON(a int not null,
CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))
CREATE TABLE TimeOfDay(a int not null,
CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO
-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login
-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO
-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO
EXEC NotAdHoc 2,2
-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2
-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)
SELECT * FROM NoCursors
I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?)
The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.
All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules!
(Hmmmm, what can I do with rules?)
#TSQL2sDay
Tuesday, October 18, 2011
Did you know that if you run a query in SQL Server, and it processes it as a hash match, and there's not enough memory to fully process it, the memory will spill to disk? You can find out all about it here.
Note: I know this makes even less sense than my usual blog posts do, so for more information, check out Jen's Awesomesauce blog entry about it. (Please don't sue me Jen!)
#sqlsue
Tuesday, September 13, 2011
(Yeah yeah, technically it's in Alpharetta, but it's close enough.)
Saturday…Saturday….Saturday…. September 17th. TWO THOUSAND ELEVEN!
OK, it's not a tractor pull, but it's even better: FREE SQL SERVER TRAINING! They have a bunch of great speakers lined up, and for some reason, me. (Protip: be good friends with the program committee, have sufficient bribe funds, and if all else fails, lots of alcohol, drugs and a camera. Ba-ZING! You too can speak at SQL Saturday!)
I will be presenting Revenge: The SQL! in a new and improved SQL Saturday themed presentation. Actually, it's the same ol' presentation, I just updated the slide theme to match the new SQL Saturday website design. (Yeah guys, thanks for changing that a month ago. So much for coasting on the old format.) 
Of course, you have your choice of three other SQL Saturdays in other cities that day, but come on, you really want to go to this one.
#sqlsat89 #sqlsaturday #sqlkilt #sqlpass
Wednesday, September 07, 2011
24 Hours of Pass (or 24HOP) is a great program offered by PASS to provide free, online training for anyone who wants to learn more about SQL Server. They routinely have the best SQL Server presenters available for these sessions, and attract hundreds, perhaps even a thousand attendees from around the world. This is definitely one of the best things they've started doing in the past few years, and every session I've attended has been excellent.
So why am I so grumpy about it?
I'm not really, pretty much everything here is a minor annoyance that I can deal with. However since they're so minor they seem to be things that can be easily corrected and would make the process much better. 
First off, this is my biggest gripe, the registration page:
https://www323.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=lj6378f4fhf5hpdm
What grinds my gears about this? I have to scroll alllllllllllllllllllllllllllll the way to bottom to actually register for the sessions. This wouldn't be so bad except all the details of the session, including the presenter, is in a separate list at the top. Both lists contain info the other does not, and scrolling between them to determine "Should I make time to listen to this? Who is speaking at this time anyway?" is really unnecessary.
My preference would be to keep the top list and add the checkboxes and schedule info in separate columns. This is a full-width design, so there's plenty of space for this data, which is pretty small anyway. The other huge benefit is halving the size of the page, which improves performance and lowers bandwidth usage considerably. And if you know HTML/ASP.Net, and you view the page source, you can find PLENTY of other things that can be reduced even further. (not just viewstate)
One nice thing that PASS does is send iCal reminders to your email address so you can accept them to your calendar. Again, they leave off the presenter in the appointment details, while still duplicating the meeting title in the body. Sometimes I make decisions based on speaker rather than content (Natalie Portman is reading the Yellow Pages??? I'M THERE!) and having the speaker in the iCal is helpful.
Next minor annoyances are the necessity for providing a company name, and the survey questions. I know PASS needs to market themselves effectively, and they need information to do that, and since this is a free event it's really not worth complaining about, but why ask the survey question twice? (once at registration, once again when joining the LiveMeeting) Same thing for the company name. All of this should be tied to email address, so that's all I should need to enter when joining the LiveMeeting.
The last one is also minor, but it irks me in this day and age of multiple browsers and the decline of Internet Explorer as a dominant platform. The registration page was originally created in Visual Studio 2003, and has a lot of IE-specific crud representative of the browser situation of 2003. (IE5 references? really? and is the aforementioned viewstate big enough?) This causes some grief with other browsers like Firefox, Chrome, and sometimes IE8 or 9. And don't get me started on using the page on a Mac or in Safari.
My main point is that PASS is an international organization, welcoming everyone from all levels of SQL Server proficiency, and in that spirit I think it would help to accommodate a wider range of browser software, especially since the registration page is extremely simple. I recognize that this page is not hosted on the PASS website and may be maintained by some division of Microsoft, but to me that's even worse if MS can't update their own pages. They've deprecated IE6, so they don't need to maintain support on their own websites anymore.
OK, I'll shut up now.
#sqlpass #24HOP
Thursday, September 01, 2011
As a follow-up to my earlier post, I found yet another great free resource that the "professor" and the poor students taking that class should look at. I found this via the excellent material Stanford provides for their open course on Databases.
You'll notice how the example ER diagrams look nothing like the one the "professor" created. They're clear, readable, have descriptive text, and use standard UML notation. They also have accompanying SQL to show how the two languages relate and translate to one another.
And if anyone is wondering (because I've been asked):
- I don't know if I'll name the "professor".
- I really, really, really want to. It pisses me off that this person is allowed to teach, especially a paid course.
- I'm constrained by the wishes of others who want to avoid trouble with the "professor" and the school.
- Sorry, I can't provide hints, at least not privately. If I name anyone or anything it will be on this blog.
- Sorry for the unnecessary intrigue and mystery, I hate it as much as you do.
- Yeah, you really should play with that Powerpoint. Don't just glance at it, move things around. It's hilarious.
At the risk of starting something (else) that I won't finish, I may write about the university in question, especially to compare their material to what Stanford is providing. Not sure how to do it yet, without it sounding like a cheap shot, useless comparison, or angst-ridden/raging/vituperative babble. (I know, why stop now?)
But, you know what? I think it's fair.
Tuesday, August 30, 2011
I received an "ER diagram" from someone enrolled in a "database course" offered by a "professor" at a "university". This person would like to remain anonymous for the time being, as they are in an important position and don't want certain people to know what information they're providing. Let's call this person Hal Holbrook.
You can find the ERD here. Go ahead and open it, take a few minutes, really check it out. I'll wait.
Seriously, it's worth your time.
There will be a quiz later, and you'd better not fail it.
OK, done laughing are you? I wish I could stop.
Disclaimer: all the identifying info has been removed. I changed the name of the company and the date in the lower right corner. Everything else is untouched.
So, where to begin a critique of this marvelous construct? The following is a rant, in F Major:
- Powerpoint. Someone did an ERD in fucking POWERPOINT. Powerpoint 2003 (maybe even '97)
- The general layout screams "grid". Not that it HAS that layout, but that it is desperately clamoring for it.
- Bubbles. You'll never see as many bubbles unless you hook a jet engine up to this thing and let it run for a couple of weeks.
- The lines. There are even more lines than there are bubbles. And I really like the lines that don't connect to anything. This must be some new-fangled relational database, it joins to nothing.
- And how about those connectors? The three-pronged ones (Crow's feet). Notice how those were done?
- Speaking of lines, did you find it yet? You know, the funky line? The one that looks a little off? The one that's not a line but a picture of a line?
- Good, I'm glad you found it, because there's two of them. I told you there was a quiz.
- All those little annotations like "based on", "used for", "involves", "includes", "part of", "belongs to" (those last two are separate concepts I'm sure). I can see how they became part of the SQL Language spec. They're so….specific.
- JESUS TAPDANCING CHRIST ON A POGO STICK IN RUSH HOUR TRAFFIC, THEY DID IT IN POWERPOINT. Were they high? Retarded? Both? Is Visio really that expensive? (hint: no)
I wish I had this to submit for T-SQL Tuesday #21, even though it's not mine. Maybe I can submit it to TheDailyWTF, despite the stiff competition.
So anyway, Hal's assignment is to create an MS Access database based on this ERD. And only this ERD. There are other "validations" that should be included, but none of them are described. Just bubbles and lines. And the "professor" is adamant that this is sufficient, the design is perfect, and they're the most knowledgeable person on the subject (according to Hal).
There's been some debate between me and my local homies about the ethics of "naming and shaming" people or companies in the database industry when they do something really dumb. It comes up every now and then on certain SQL blogs. There's no consensus, naturally, and there's definitely good and bad things about it. While I won't name the "professor" or their school in this post, I feel completely justified in ridiculing this ERD, and by extension their course and teaching methods, because Hal:
- Is paying for this class (see below)
- Already knows basic database theory, and even some advanced
- Will learn nothing from this "professor", especially "professional" knowledge
- Has no recourse to address problems with the school
And the "professor":
- Clearly isn't trying to teach or explain; this diagram is meant to obscure
- Can't or won't use the proper tools to update the teaching material, or even make it usable
- Doesn't accept feedback, criticism, or advice on how to improve
- Doesn't know or care how bad or unprofessional they're behaving. They've got their tenure, so fuck it.
If you had to hire a database person, and you required a college degree, would you hire anyone who took this class? Even if they passed it? Would you hire anyone else who attended this university? Did I mention this ERD is for a 400-level course?
This is especially galling to me as there are so many freely available and vastly better sources for learning database design and theory. ON THE INTERNET, no less. And if you've spent any time in the past month on the nerdy parts of the web, you've heard about Stanford University's open courses in Artificial Intelligence, Machine Learning, and whaddya know, Databases. And if you haven't, turn in your nerd badge now, and enroll in them!
Holy crap, you can even find free database modeling tools. And even free database models, even for real estate.
So with all the ranting and cursing I've done here, my opinion is that Hal would be far better served by surfing the web, posting questions and answering them on the forums, and enrolling in free online courses, rather than continue to struggle in this bullshit class, with this bullshit "professor", just for the sake of a (bullshit?) degree, just to get a (likely bullshit) job, which is the only reason Hal is enrolled. And that, to me, is total bullshit.
And if you think this ERD is bad, you've haven't seen the "professor"s website yet. I won't post the URL (not yet anyway), but the background color is #daa520. Go ahead, check it out for yourself.
Bring a bucket.
Wednesday, August 10, 2011
Adam Machanic's (blog | twitter) ever popular T-SQL Tuesday series is being held on Wednesday this time, and the topic is…
SHIT CRAP.
No, not fecal material. But crap code. Crap SQL. Crap ideas that you thought were good at the time, or were forced to do due (doo-doo?) to lack of time.
The challenge for me is to look back on my SQL Server career and find something that WASN'T crap. Well, there's a lot that wasn't, but for some reason I don't remember those that well. So the additional challenge is to pick one particular turd that I really wish I hadn't squeezed out. Let's see if this outline fits the bill:
- An ETL process on text files;
- That had to interface between SQL Server and an AS/400 system;
- That didn't use SSIS (should have) or BizTalk (ummm, no) but command-line scripting, using Unix utilities(!) via:
- xp_cmdshell;
- That had to email reports and financial data, some of it sensitive
Yep, the stench smell is coming back to me now, as if it was yesterday…
As to why SSIS and BizTalk were not options, basically I didn't know either of them well enough to get the job done (and I still don't). I also had a strict deadline of 3 days, in addition to all the other responsibilities I had, so no time to learn them. And seeing how screwed up the rest of the process was:
- Payment files from multiple vendors in multiple formats;
- Sent via FTP, PGP encrypted email, or some other wizardry;
- Manually opened/downloaded and saved to a particular set of folders (couldn't change this);
- Once processed, had to be placed BACK in the same folders with the original archived;
- x2 divisions that had to run separately;
- Plus an additional vendor file in another format on a completely different schedule;
- So that they could be MANUALLY uploaded into the AS/400 system (couldn't change this either, even if it was technically possible)
I didn't feel so bad about the solution I came up with, which was naturally:
- Copy the payment files to the local SQL Server drives, using xp_cmdshell
- Run batch files (via xp_cmdshell) to parse the different formats using sed, a Unix utility (this was before Powershell)
- Use other Unix utilities (join, split, grep, wc) to process parsed files and generate metadata (size, date, checksum, line count)
- Run sqlcmd to execute a stored procedure that passed the parsed file names so it would bulk load the data to do a comparison
- bcp the compared data out to ANOTHER text file so that I could grep that data out of the original file
- Run another stored procedure to import the matched data into SQL Server so it could process the payments, including file metadata
- Process payment batches and log which division and vendor they belong to
- Email the payment details to the finance group (since it was too hard for them to run a web report with the same data…which they ran anyway to compare the emailed file against…which always matched, surprisingly)
- Email another report showing unmatched payments so they could manually void them…about 3 months afterward
- All in "Excel" format, using xp_sendmail (SQL 2000 system)
- Copy the unmatched data back to the original folder locations, making sure to match the file format exactly (if you've ever worked with ACH files, you'll understand why this sucked)
If you're one of the 10 people who have read my blog before, you know that I love the DOS "for" command. Like passionately. Like fairy-tale love. So my batch files were riddled with for loops, nested within other for loops, that called other batch files containing for loops. I think there was one section that had 4 or 5 nested for commands. It was wrong, disturbed, and completely un-maintainable by anyone, even myself. Months, even a year, after I left the company I got calls from someone who had to make a minor change to it, and they called me to talk them out of spraying the office with an AK-47 after looking at this code. (for you Star Trek TOS fans)
The funniest part of this, well, one of the funniest, is that I made the deadline…sort of, I was only a day late…and the DAMN THING WORKED practically unchanged for 3 years. Most of the problems came from the manual parts of the overall process, like forgetting to decrypt the files, or missing/late files, or saved to the wrong folders. I'm definitely not trying to toot my own horn here, because this was truly one of the dumbest, crappiest solutions I ever came up with. Fortunately as far as I know it's no longer in use and someone has written a proper replacement. Today I would knuckle down and do it in SSIS or Powershell, even if it took me weeks to get it right.
The real lesson from this crap code is to make things MAINTAINABLE and UNDERSTANDABLE. sed scripting regular expressions doesn't fit that criteria in any way. If you ever find yourself under pressure to do something fast at all costs, DON'T DO IT. Stop and consider long-term maintainability, not just for yourself but for others on your team. If you can't explain the basic approach in under 5 minutes, it ultimately won't succeed.
And while you may love to leave all that crap behind, it may follow you anyway, and you'll step in it again.
P.S. - if you're wondering about all the manual stuff that couldn't be changed, it was because the entire process had gone through Six Sigma, and was deemed the best possible way. Phew! Talk about stink!
Friday, June 17, 2011
Yeah, it was 2 weeks ago, but I'm finally blogging about something!
I presented Revenge: The SQL! at SQL Saturday #77 in Pensacola on June 4. The session abstract is here, and you can download the slides from that page too. You can see how I look in the speaker's shirt here.
Overall it went pretty well. I discovered a new bit of evil just that morning and in a carefully considered, agonizing decision-making process that was full documented, tested, and approved…nah, I just went ahead and added it at the last minute. Which worked out even better than (not) planned, since it screwed me up a bit and made my point perfectly. I had a few fans in the audience, and one of them recorded it for blackmail material posterity.
I'd like to thank Karla Landrum (blog | twitter) and all the volunteers for putting together such a great event, and for being kind enough to let me present. (Note to Karla: I'll get the next $100 to you as soon as I can. Might need a few extra days on the next $100.)
Thanks to Audrey (blog | twitter), Peg, and Dorothy for attending and keeping the heckling down. Thanks also to Aaron (blog | twitter) for providing room and board and also not heckling. Thanks to Julie (blog | twitter) for coming up with the title for the presentation. (boo to Julie for getting sick and bailing out on us) And thanks to all of them for listening to a preview and offering their suggestions and advice!
Cross your fingers that I get accepted at SQL Saturday 81 in Birmingham, SQL Saturday 85 in Orlando, or SQL Saturday 89 in Atlanta, or just attend them anyway!
Monday, October 04, 2010
For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot. It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution. All is not lost though, this mistake turned out to be informative for me, and hopefully for you.
Referring back to the "Version" view in the prelude I started with the following query to extract the version number:
SELECT DISTINCT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, 12) VerNum
FROM VERSION
I used PATINDEX() to find the first hyphen "-" character in the string, since the version number appears 2 positions after it, and got these results:
SQLVersion VerNum
----------- ------------
2000 8.00.2055 (I
2005 9.00.3080.00
2005 9.00.4053.00
2008 10.50.1600.1
As you can see it was good enough for most of the values, but not for the SQL 2000 @@VERSION. You'll notice it has only 3 version sections/octets where the others have 4, and the SUBSTRING() grabbed the non-numeric characters after. To properly parse the version number will require a non-fixed value for the 3rd parameter of SUBSTRING(), which is the number of characters to extract.
The best value is the position of the first space to occur after the version number (VN), the trick is to figure out how to find it. Here's where my confusion about PATINDEX() came about. The CHARINDEX() function has a handy optional 3rd parameter:
CHARINDEX (expression1 ,expression2 [ ,start_location ] )
While PATINDEX():
PATINDEX ('%pattern%',expression )
Does not. I had expected to use PATINDEX() to start searching for a space AFTER the position of the VN, but it doesn't work that way. Since there are plenty of spaces before the VN, I thought I'd try PATINDEX() on another character that doesn't appear before, and tried "(":
SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString))
FROM VERSION
Unfortunately this messes up the length calculation and yields:
SQLVersion VerNum
----------- ---------------------------
2000 8.00.2055 (Intel X86)
Dec 16 2008 19:4
2005 9.00.3080.00 (Intel X86)
Sep 6 2009 01:
2005 9.00.4053.00 (Intel X86)
May 26 2009 14:
2008 10.50.1600.1 (Intel X86)
Apr
2008 10.50.1600.1 (X64)
Apr 2 20
Yuck. The problem is that PATINDEX() returns position, and SUBSTRING() needs length, so I have to subtract the VN starting position:
SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString)-PATINDEX('%-%',VersionString)) VerNum
FROM VERSION
And the results are:
SQLVersion VerNum
----------- --------------------------------------------------------
2000 8.00.2055 (I
2005 9.00.4053.00 (I
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Ummmm, whoops. Turns out SQL Server 2008 R2 includes "(RTM)" before the VN, and that causes the length to turn negative.
So now that that blew up, I started to think about matching digit and dot (.) patterns. Sadly, a quick look at the first set of results will quickly scuttle that idea, since different versions have different digit patterns and lengths.
At this point (which took far longer than I wanted) I decided to cut my losses and redo the query using CHARINDEX(), which I'll cover in Part 2.2.
So to do a little post-mortem on this technique:
- PATINDEX() doesn't have the flexibility to match the digit pattern of the version number;
- PATINDEX() doesn't have a "start" parameter like CHARINDEX(), that allows us to skip over parts of the string;
- The SUBSTRING() expression is getting pretty complicated for this relatively simple task!
This doesn't mean that PATINDEX() isn't useful, it's just not a good fit for this particular problem. I'll include a version in the next post that extracts the version number properly.
UPDATE: Sorry if you saw the unformatted version of this earlier, I'm on a quest to find blog software that ACTUALLY WORKS.
Just a quick post I should've done yesterday but I was recovering from SQL Saturday #48 in Columbia, SC, where I went to some really excellent sessions by some very smart experts. If you have not yet attended a SQL Saturday, or its been more than 1 month since you last did, SIGN UP NOW!
While searching the OBJECT_DEFINITION() of SQL Server system procedures I stumbled across the DEFAULT_DOMAIN() function in xp_grantlogin and xp_revokelogin. I couldn't find any information on it in Books Online, and it's a very simple, self-explanatory function, but it could be useful if you work in a multi-domain environment. It's also the kind of neat thing you can find by using this query:
SELECT OBJECT_SCHEMA_NAME([object_id]) object_schema, name
FROM sys.all_objects
WHERE OBJECT_DEFINITION([object_id]) LIKE '%()%'
ORDER BY 1,2
I'll post some elaborations and enhancements to this query in a later post, but it will get you started exploring the functional SQL Server sea.
UPDATE: I goofed earlier and said SQL Saturday #46 was in Columbia. It's actually SQL Saturday #48, and SQL Saturday #46 was in Raleigh, NC.