Quick Reminder: How to Shrink Database Files
ALTER DATABASE soapnmteachtest SET RECOVERY SIMPLE GO CHECKPOINT GO DBCC SHRINKFILE(soapnmteach, 1) GO ALTER DATABASE soapnmteachtest SET RECOVERY FULL GO
Posts
SQL Query to Populate a Table with Random Data
My go to database for trying out new ideas is my CrittersAndThings database. Below is the layout of the first version from years ago. I use it even today when I need something simple to bang on. (The later versions get pretty crazy and sometimes it's a bit much for testing simple things.) The general idea is that the world (my test world, that is) has: critters -- living animal-like things such as people, orcs, fish, birds, bugs, sentient gelatinous masses, you name it things -- all the stuff that isn't a critter, such as plants, minerals, planets, buckets of mop water, clouds of gas, etc. Database Structure Table: Critters We store critters here. CritterID (PK, int, not null) Name (varchar(50), null) Height (int null) Weight (int null) Age (int null) CritterTypeID (FK, int, null) IsAlive (bit, null) Table: CritterTypes This table contains various types of critters. CritterTypeID (PK, int, not null) Name (varchar(50), null) Description (varchar(50)...
List of First Names and Last Names for Test Data, Plus a Query to Generate a Random Full Name
I found this page to be very helpful when I was generating random names for test data: http://www.quietaffiliate.com/free-first-name-and-last-name-databases-csv-and-sql/ Naturally, I didn't read everything and just grabbed the two CSV files (one for first names, a second for last names). Next, I created two tables (FirstNames, LastNames) and imported the CSV files into their respective tables. The following query will assemble a Full Name from a randomly selected First Name and randomly selected Last Name: -- Generate random LastNameID DECLARE @RandomLastNameID int; DECLARE @UpperLastNameID int; DECLARE @LowerLastNameID int; SET @UpperLastNameID = 88799; SET @LowerLastNameID = 1; SET @RandomLastNameID = RAND() * @UpperLastNameID + @LowerLastNameID; -- Generate random FirstNameID DECLARE @RandomFirstNameID int; DECLARE @UpperFirstNameID int; DECLARE @LowerFirstNameID int; SET @UpperFirstNameID = 5494; SET @LowerFirstNameID = 1; SET @RandomFi...
Handy Online Tool to Remove Line Breaks in Strings
I had to remove a bunch of line breaks in a SQL query (don't ask). I found this nifty tool that does just that. http://www.textfixer.com/tools/remove-line-breaks.php Copy and paste your string into the upper text area field, select "Remove line breaks only" or "Remove line breaks and paragraph breaks", and then click the "Remove Line Breaks" button. Your clean text will appear in the lower text area field. Put your mouse in there, click once, and the text is highlighted. Paste that into your clip board, and then paste that into whatever you want.
ASP.NET: Change page title based on database to which you're connecting
On occasion, you may find it useful to provide a visual cue that the user is not in an application's production environment. This is especially useful during a late night crunch when the developers, system administrators, and the Q.A. team are distracted and tired. Below is some code which allows you to change the page title and its style based on the database specified in a connection string. Note that we are parsing an Entity Framework connection string, not an ADO.NET connection string. Site.Master <div class="navbar navbar-inverse navbar-fixed-top"> <div class="container"> <div class="navbar-header"> <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse"> <span class="icon-bar"></span> <span class="...
List of Refresher Tutorials on Advanced Transact-SQL Techniques
It never hurts to go back and look at the fundamentals. For instance, I had forgotten about the APPLY operator and was rusty on the PIVOT operator. SQLServerCentral.com has a good list of tutorials, Stairway to Advanced T-SQL , that hits most of the advanced fundamentals. http://www.sqlservercentral.com/stairway/119892/ Enjoy!
Fixing the error: "The element 'entityFramework' has invalid child element 'providers'. List of possible elements expected: 'contexts'"
If you're getting the error message: The element 'entityFramework' has invalid child element 'providers'. List of possible elements expected: 'contexts'. and you're using Visual Studio 2012 or Visual Studio 2013, then you can download and install Entity Framework 6 Tools for Visual Studio 2012 & 2013 . These tools update the schema used to validate the configuration files for your projects. Entity Framework 6 Tools for Visual Studio 2012 & 2013 https://www.microsoft.com/en-us/download/details.aspx?id=40762