Updating a ‘Non-Existent’ Record

And by non-existent, I mean exists in one place, but not in another  (yes, I realize this article has a misleading title).  File this under “more fun with SQL.”

If you ever want to change a value of a record in one table because it does not have a matching record in another, you can use a simple EXISTS check in your WHERE clause. This works best when there’s a foreign-key relationship between the tables or there is data in both tables that can be used for an exact match.

In a recent case, I wanted to mark said record as inactive. To make it inactive, I simply needed to set an IsActive field to ‘false’.

Here’s a sample that very closely matches what was done:

UPDATE Table1
SET [IsActive] = 0
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.KeyField = Table1.KeyField)


See? Simple AND Fun!

Temporal Data in a Relational Database Pt. 2

The post that I wrote a few years ago regarding working with temporal data has gotten a few more reads than I’d expected, and since I’ve implemented the pattern successfully a few more times since then, I thought I’d update the article with the addition of another way to approach the SQL code.

The basics of the pattern remain the same, so I simply refer you to the article, Temporal Data in a Relational Database, for a description.

Start by retrieving the results that meet your effective date criteria and create a Common Table Expression (CTE) to represent a  ranked list that orders the results descending by effective date. Here, we partition by the employee id so ROW_NUMBER returns a ranking for each employee.


WITH ranked AS
(
SELECT
e.EmployeeId,
d.EmployeeDataId,
d.EffectiveDate,
d.EmployeeNumber,
d.EmploymentType,
d.HireDate,
etc...,
ROW_NUMBER() OVER (PARTITION BY e.EmployeeId
ORDER BY d.EffectiveDateTime DESC) AS RowNumber
FROM   Employee e
INNER JOIN EmployeeData d ON e.EmployeeId = d.EmployeeId
WHERE
e.EmployeeId = @EmployeeId
AND (d.EffectiveDate <= @EffectiveDate)
)


Next, we pull the result out of the previous query, grabbing only the top row number:

SELECT
EmployeeId,
EmployeeDataId,
EffectiveDate,
EmployeeNumber,
EmploymentType,
HireDate,
etc...
FROM ranked
WHERE
RowNumber = 1


This query is a lot easier to read and make sense of. However, it does add the additional overhead of figuring out how many rows are in a CTE before pulling out the ones that are needed. As such, you may not want to use this approach if the record count is particularly high.

Configuring the ESB Toolkit 2.2 for BizTalk 2013 – Exception Creating the ExceptionDb

One of the great things about BizTalk 2013 is the improvements made in the installation of the ESB Toolkit.  However, we’ve found lately, that there are still some issues that you should be watching for during configuration.

One issue, which I’ve seen twice now, has shown up while completing the very first configuration step in the ESB Toolkit 2.2’s configuration utility–creating the Exception Management database.  Our configuration failed with this exception: 

Exception Calling “create” with “0” argument(s) “Create failed for database esbExceptiondb’.”

Generally the first thing we do is figure out if there are security issues.  In particular, we need to make sure that the account we are logged in with has all the correct rights on the SQL Server.  In my opinion, it’s probably best to have SA privileges while completing the configuration, but I’m sure there are plenty of DBAs who would argue with me on that.  We were able to verify that, in fact, we were operating under such an account.

 

That led us into the configuration tool’s log file, which gave us a much more helpful hint: 

The primary file must be at least 100 MB to accommodate a copy of the model database.

The source of the problem seems to be a powershell script, embedded into an .exe, that sets the size of the DB.  Because the value is “hard-coded” into the uneditable script, the other option is to shrink the model db. Once you connect to the SQL server, you can find the model db under System Databases in SSMS.

 

First, before you go any further, BACK UP THE DATABASE. 

 

You can use SSMS to perform the shrink.  We found that running a shrink script didn’t work as well.  So in SSMS, you just right-click the database and select Tasks -> Shrink -> Database.  Then configure the options to get the DB down to a more manageable size.  You can find an MSDN article on using SSMS to shrink a database here.

 

This worked for us right away and configuration of the ESB Toolkit was able to be continued.

Deleting All Your SQL Server Stored Procedures at Once

This is a tip I picked up which you can categorize under the “fun with T-SQL” category…

Recently, I was looking to do some database cleanup and found that there’s no built-in way to delete every stored procedure in a database at once.  Most of my digging around found excellent articles and scripts on how to delete a single stored procedure, or to loop through a list using a cursor, but nothing really good for blowing them all away until I learned this little tidbit:  every proc in a database is stored in a system table called “information_schema.routines“.

Knowing this, it’s a pretty simple task to generate a list of DROP statements and execute them with a simple EXEC command.  Here’s the code:


DECLARE @dropSprocSql varchar(max)
SET @dropSprocSql =
(
SELECT ' DROP PROCEDURE ' + routine_schema + '.' + routine_name
FROM information_schema.routines
WHERE routine_schema = 'theSchema' AND routine_type = 'PROCEDURE'
FOR XML PATH('')
)
EXEC (@dropSprocSql)


Of course, you can only delete user-created procedures with this (which is nice considering the danger involved of deleting anything system-related). Also note the WHERE clause. In this example, I’m only looking for stored procedures for a particular schema, but you can open this up for all schemas just by removing that part of the where clause, and if you remove the “routine_type” portion as well, you would also remove your user-defined functions.

Deleting All Your SQL Server Data

Oftentimes during the development cycle we have the need to clean up our databases.  Recently, I’ve had the need to reset my SQL Server databases to a “no data” state to facilitate our development process. 

Because my databases contain a lot of foreign-key relationships, a simple truncate on all the tables won’t work.  I need to first, disable foreign key constraints, DELETE the data, and then re-enable the constraints.  Then, for good measure, I reset the identity keys so we start auto-numbering the key fields at 1 instead of whatever number they were at.  Here’s my script:

— disable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

— delete the data
EXEC sp_MSForEachTable “DELETE FROM ?”

— re-enable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

–reseed the identity columns
EXEC sp_MSforeachtable “DBCC CHECKIDENT (‘?’, RESEED, 0)”

Admittedly, this tip goes into the category of “No Big Deal” for the SQL Experts out there, but as I’m only a sometime SQL Developer, it’s nice to have these little tricks lying around in case I need them again.