Playing with SQL Azure; part 1

For my own amusement and edification I've decided to learn about cloud computing by playing with SQL Azure. Within the last year, I've moved my personal information into the cloud (from Palm Pilot to a Nexus One Android phone with Google Docs, Remember the Milk, Google Contacts, etc) and am curious to see how that same switch could happen with databases.

Also, I just read the book The Big Switch: Rewiring the World, from Edison to Google. It's basically about how there's a very strong analogy between the move to cloud computing today and what happened about 100 years ago, when factories, instead of generating their own electricity, converted to buying their power from electrical plants. Interesting stuff. And I do believe that the movement to cloud computing is just as inevitable as the movement to buying power from dedicated electrical plants.

For my first step, I've set up a SQL Azure account (had to provide a credit card number, but they say they won't charge anything for three months). I've set up a database, and will be experimenting with it (downloading tools to access it, etc). My first task will probably be to go through the tutorials for SQL Azure.

Recursive common table expression 'TableName' does not contain a top-level UNION ALL operator.

I've started using CTEs (Common Table Expressions) a lot. I find they can make my code quite a bit easier to read. But just now, working on a SQL statement with a CTE, it took me a while to figure out why I was getting this error message:

Msg 252 
Recursive common table expression [TableName] does not contain a top-level UNION ALL operator.

It was a long and complex sql statement with multiple CTEs, which obscured the real problem. The little I found online wasn't helping either. I ended up going step by step, and stripping out everything from the SQL statement. Finally I ended up with something similar to this, which returns the error:

;With table1 AS
(
    Select field1 from table1 where field1 = 1
)
Select  * from table1

At this point it was obvious—you can't name your CTE with the same name as a table it's referencing. So the answer was to rename my CTE to something like this:

;With table1_filtered AS
(
    Select field1 from table1 where field1 = 1
)
Select  * from table1

There's other conditions that can cause this error to occur as well, but I didn't see this one described online anywhere. If you have this problem as well, feel free to comment.

Are you running out of space on your development SQL server?

On a couple of our development SQL Server machines, we've run out of space fairly often. Lots of databases are being created frequently by multiple different developers, sometimes production backups are restored, or sample data is loaded. Lots of databases are dropped, too. But I believe there may be some bug in the drop database command, such that sometimes, it drops the databases, but the mdf and ndf files are left there.

This is not something I've been able to reproduce, but I've run into it enough that I have a routine that goes like this:

1. Run out of space on the development server

2. Check on the drive that's low on space for files over a certain size - usually about 5o gigs. In our environment, these are almost always SQL Server database files.

3. Use the sp_msforeachdb stored procedure to check which, if any, of these files is associated with an existing database. If you have a lot of databases, this is LOTS easier than running sp_helpdb for each individual database

exec sp_msforeachdb

'

SELECT DatabaseName = ''?'', * FROM ?.dbo.sysfiles

where filename like ''%InsertFileNameHere%''

'

For those that are NOT associated with a current database, you can delete them and clear up some space. Be careful here, and do any double-checking you feel is necessary.

Easy Error Trapping When Using xp_cmdshell

Error handling can be tough when using xp_cmdshell. Before I learned the trick that I go over below, I could usually figure out in my code if an error had occured when I ran a command via xp_cmdshell. However, getting details about the error, or any output at all, was tricky and could involve parsing out files.

Before you start, please be aware that xp_cmdshell will be executed under the same security context as the SQL Server service, and can be a security problem in some environments.

This code uses the insert/execute syntax. If you've never used this before, it's a good idea to learn it. Basically, instead of inserting data into a table the normal way, you can insert the results of an execute statement - in this case the xp_cmdshell statement, like below:

Insert into XPCmdShellOutput 
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\temp\Employee.txt -c'

So, below is an easy, straightforward way to use xp_cmdshell to bcp out a table, and also see the output from the command. The same principles can be used for any other use of xp_cmdshell, and not only bcp.

set nocount on
use tempdb

if object_id('tempdb..Employee') is not null drop table Employee
if object_id('tempdb..XPCmdShellOutput') is not null drop table XPCmdShellOutput

-- Create the table that we need to extract from
create table Employee (EmployeeName varchar(20))
insert into Employee values ('John')

-- This table will be used to gather the output of xp_cmdshell
create table XPCmdShellOutput (OutputLine varchar(1000))

-- Show the output of xp_cmdshell when the directory does not exist
Insert into XPCmdShellOutput 
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\DirectoryDoesNotExist\Employee.txt -c'
select 'Error when directory does not exist' = OutputLine from XPCmdShellOutput
delete from XPCmdShellOutput

-- Show output of xp_cmdshell when the table to be exported does not exist
Insert into XPCmdShellOutput 
Execute master..xp_cmdshell 'bcp tempdb..Employee1 out c:\temp\Employee.txt -c'
select 'Error when table does not exist' = OutputLine from XPCmdShellOutput
delete from XPCmdShellOutput

-- Finally, successfully export the table!
Insert into XPCmdShellOutput 
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\temp\Employee.txt -c'
select 'Successfully export the table!' = OutputLine from XPCmdShellOutput

When you run the above code, this will be the output (note that you may need to modify the c:\temp directory in your environment, and you need create table permissions in the tempdb database):

Error when directory does not exist:

OutputLine
Password: 
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL


Error when table does not exist:

OutputLine
Password: 
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tempdb..Employee1'.
NULL


Successfully export the table!

OutputLine
Password: 
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1 Avg        1 (1000.00 rows per sec.)
NULL

The output of the xp_cmdshell was inserted into the XPCmdShellOutput table every time we ran it, because we used the insert/execute syntax. Then, we select from XPCmdShellOutput to show what the output actually was. The first section shows the error when the directory does not exist. The second shows the error message when table name to be exported is misspelled. And the third shows a successful export.

This is sample code, and simplified to make it easier to understand. In working code, you would check the XPCmdShellOutput table for the string "Error". If the error string exists, then obviously an error occurred, and the details would have been stored in the XPCmdShellOutput table. Note that when the export is successful, you can also extract other information from the XPCmdShellOutput table - for instance, how many rows were copied out, and how long the export took.

When using xp_cmdshell with bcp, keep in mind that it will NOT recognize any temporary tables that were created. If you need to use temporary tables, they must be global temporary tables, prefixed with ## instead of #.
 

Renaming a Column in a Temp Table in SQL Server 2005 - Yes, You Can!

For whatever weird reason, you may need to create a temp table in one step, and then rename one of the columns. Perhaps you're creating the temp table in one stored procedure, and modifying it in another. I'm not going to tell you that's a silly thing to do, or you should just create it with the right names in the first place - I saw those kind of responses online, and it's very unhelpful! Sometimes you just need to do this type of thing.

It's not a straightforward thing to do, though. When you just run the below script that calls sp_rename:

if object_id('tempdb..#Test123') is not null drop table #Test123
create table #Test123 (field1 int)
exec sp_rename '#Test123.Field1', 'Field2', 'COLUMN'

...you get this error: "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."

The key is to call sp_rename from the tempdb, like so:

if object_id('tempdb..#Test123') is not null drop table #Test123
create table #Test123 (field1 int)
exec tempdb..sp_rename '#Test123.Field1', 'Field2', 'COLUMN'
select * from #Test123

And - success! Note that you'll still have problems referencing the specific fieldname that was renamed. I solved this by doing a select into another temp table. Also, the procedure you're doing this in will probably have to do a recompile - that wasn't a problem for me, either. Overall, it was a better solution than the alternatives.

Easily delete database objects

Any developer working on larger, more complex systems, will eventually need to do some massive cleanup of tables, stored procedures, and other objects that are no longer used. Although you could just write a simple statement like this:

Drop table TestTable

...you should really have something more robust, that returns informative messagess and checks for errors. Something like this:

Declare @Error int
if exists (select * from sys.objects where name = 'TestTable' and type = 'u' and schema_id = schema_id('dbo') ) begin
-- The table exists, prepare to delete it
Drop table dbo.TestTable
Select @Error = @@Error
if @Error <> 0 begin
RAISERROR ('Error dropping table dbo.TestTable' ,16 ,1)
end
print 'Successfully dropped Table TestTable.'
end 
else begin
print 'Table TestTable does not exist or has already been deleted.'
end

But do you want to constantly be rewriting that piece of code as you need to drop different objects? Absolutely not! That's why I wrote the stored procedure above called sp_DropDatabaseObject that will delete many different types of database objects (tables, procedures, views, functions, and indexes). It incorporates all the functionality above (error trapping, good error messages), in a reusable procedure. It's created in the master database, so that it can be called from any database. Note that at the end, I call sp_MS_marksystemobject to mark it as a system object - this allows it to have the context of the calling database even though it's located in the master database.

Here are some examples of how to run sp_DropDatabaseObject

-- Drop table
exec sp_DropDatabaseObject 'dbo', 'TestTable', 'u'
-- Drop procedure
exec sp_DropDatabaseObject 'dbo', 'TestProcedure', 'p'
-- Drop index
exec sp_DropDatabaseObject 'dbo', 'TestTable.index1', 'i'
-- Drop View
exec sp_DropDatabaseObject 'dbo', 'TestView', 'v'
-- Drop function
exec sp_DropDatabaseObject 'dbo', 'TestFunction', 'fn'

And below is the code for the stored procedure. I haven't yet modified it to use SQL 2005 error trapping (try/catch), but that would be a definite improvement.

use master
go

Create procedure dbo.sp_DropDatabaseObject 
 @pSchemaName varchar(100) -- the schema the object belongs to, when applicable
 ,@pObjectName sysname -- name of the object to drop, including schema (i.e. dbo.TableName)
 ,@pObjectType char(2) -- type of object to be dropped. 
 -- Can be 'U', 'V', 'P', 'FN', 'I' (for table, view, procedure, function, and index)

as

----------------------------------------------------------------------------
-- Declarations
----------------------------------------------------------------------------
declare -- Standard declares
 @FALSE tinyint -- Boolean false.
 ,@TRUE tinyint -- Boolean true.
 ,@ExitCode int -- Return value of this procedure.
 ,@rc int -- Return code from a called SP.
 ,@Error int -- Store error codes returned by statements and procedures (@@error).
 ,@RaiseMessage varchar(1000) -- Creates helpful message to be raised when running.

declare -- sp specific declares
 @SingleQuote nchar(1)
 ,@SQL nvarchar(4000)
 ,@IndexTableName varchar(50)
 ,@IndexIndexName varchar(50)

----------------------------------------------------------------------------
-- Initializations
----------------------------------------------------------------------------
select -- Standard constants
 @FALSE = 0
 ,@TRUE = 1
 ,@ExitCode = 0
 ,@rc = 0
 ,@Error = 0

Select
 @SingleQuote = char(39)

----------------------------------------------------------------------------
-- Validate that all objects have an appropriate ObjectType
----------------------------------------------------------------------------
if @pObjectType not in ('U', 'V', 'P', 'FN', 'I') begin
 select @RaiseMessage = 'Invalid ObjectType value: ' + @pObjectType
 goto ErrorHandler
end

----------------------------------------------------------------------------
-- Put together the SQL to drop the database object
----------------------------------------------------------------------------
if @pObjectType = 'U' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The table exists, prepare to delete it
 Select @SQL = 'Drop table ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Table ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'V' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The view exists, prepare to delete it
 Select @SQL = 'Drop view ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'View ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'P' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The procedure exists, prepare to delete it
 Select @SQL = 'Drop procedure ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Procedure ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'FN' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The function exists, prepare to delete it
 Select @SQL = 'Drop function ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Function ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'I' begin
 -- Parse out the table/index names to be able to test for index existance easily
 Select @IndexTableName = substring(@pObjectName, 1, CHARINDEX('.', @pObjectName) - 1) 
 Select @IndexIndexName = substring(@pObjectName, CHARINDEX('.', @pObjectName) + 1, 50 )
 If IndexProperty(OBJECT_ID(@IndexTableName),@IndexIndexName,'IndexID') IS not NULL begin
 -- Check first whether it's a primary key
 if exists 
 (
 select * from sys.indexes where is_primary_key = @TRUE and object_name(object_id) = @IndexTableName and name = @IndexIndexName
 ) 
 begin
 Select @SQL = 'Alter table ' + @pSchemaName + '.' + @IndexTableName + ' drop constraint ' + @IndexIndexName
 end
 else begin
 Select @SQL = 'Drop Index ' + @pSchemaName + '.' + @pObjectName 
 end
 end
 else begin
 select @RaiseMessage = 'Index ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end 
end

----------------------------------------------------------------------------
-- Drop the database object
----------------------------------------------------------------------------
if @SQL is not null begin
 Exec @RC = sp_executesql @sql
 select @Error = @@Error
 if @Error <> 0 or @RC <> 0 begin
 select @RaiseMessage = 'Error dropping object : ' + @pObjectName + ' using sql statement: ' + @SQL
 goto ErrorHandler 
 end
 Select @RaiseMessage = 'Completed dropping object: ' + @pObjectName + ' using sql statement: ' + @SQL
 print @RaiseMessage
end

goto ExitProc

----------------------------------------------------------------------------
-- Error Handler
----------------------------------------------------------------------------
ErrorHandler:

 select @ExitCode = -100

 -- Print the Error Message now that will kill isql.
 RAISERROR (
 @RaiseMessage
 ,16 -- Severity.
 ,1 -- State.
 )

 goto ExitProc

----------------------------------------------------------------------------
-- Exit Procedure
----------------------------------------------------------------------------
ExitProc:

 return (@ExitCode)

go


-- Marks it as a system object. Otherwise, it may return object information from the master database instead of the calling database
EXEC sys.sp_MS_marksystemobject sp_DropDatabaseObject
GO

9 Things to Do When You Inherit a Database

So—Bob’s left the company to move back east, and you’re the new lead database developer on the database. Or, the third-party company to which the maintenance has been outsourced is no longer working on it, so it’s yours now. One way or another, you need to take over a database system that you had no part in developing. It's not in good shape, and there’s not many resources for you to tap.

What do you do?

I’ve been faced with this situation a few times now, and have developed a list of some of the things that have helped me the most, both in getting productive, and in bringing the database system up to par.

Backups
Make sure that backups are happening. I’m assuming here that you’re the database developer, and not the database administrator. However, just as minimum check, make sure that backups are occurring regularly. Ideally you should successfully restore the backup somewhere else.

Research
Look at the database. Go through and get an idea of the table structure, what the largest tables are by size, what the most commonly used stored procedures are, if there are jobs, and what documentation there is. Read through some the stored procedures. You may find it useful to create a quick and dirty database diagram if there isn’t one, using the built in diagramming tool in SQL Server. This can also be a good visual aid when you talk to other people.

Talk to the former developers
This may not be an option, but try hard to have a least a few friendly interviews with the former developers. This is not the time to make comments like, “I can’t believe you guys did [insert bad development practice here]”. You don’t know the history– maybe it was that way when they got the system. You’ll want to get as much information as they can give you on current issues, items on this list, etc. Keep things friendly – and maybe try to get their cell number in case of questions. A good relationship with former developers can go a long way.

A bug database
Is there a bug database – somewhere that bugs (and sometimes enhancement ideas) are tracked for this system? This is certainly one of the things that you want to set up, if it’s not there currently. I’ve always been lucky enough to work at companies where bug tracking was taken seriously, and there were systems already in place that I could just plug into. If there’s no bug database, time to do some research. I wouldn’t suggest reinventing the wheel here, since there’s a lot of good systems out there—just use what’s available.

Source code control
Is the code in some kind of source code control system, such as VSS or Perforce? If it is—is everything up to date? I’m going to hazard a guess that it’s either not in source code control, or it hasn’t been kept up to date. That’s been a big task for me when starting work on inherited systems. There’s a number of tools with which to tackle this. In the past I’ve used a custom written perl tool that used SQL DMO, but I won’t go into detail—that’s the topic of another article. If nothing else, you could use the built in tools that SQL Server provides to script out your database objects, and check them in. Once you have everything checked in, try running a database build from the checked in code, and compare it to production. Also—make sure you have a good system to keep all the code updated!

Talk to the users and/or business owners
Sit down and have some conversations with the users. This is a good opportunity to get to know their problems and concerns, the improvements they would most like to see, and where things are heading in the future. You want to make sure that this database is sticking around, that it’s not going to be replaced with a third party product or anything like that. If you’re going to put a lot of work into improving the system, you need to know that your efforts are going to pay off for the business. Also–you’ll probably be spending lots of time on issues that are important to a well-run database system (a bug database, source code control, etc), but that won’t give them any new features. Make sure they understand this.

Establish credibility with the users by fixing a few things or making some enhancements
Even though you’ll probably be needing to spend a lot of time on tasks like setting up source code control, bug tracking, etc, you don’t want to do this exclusively. From talks with users, hopefully you’ve identified enhancements or bug fixes that you could get out quickly. Do what you can here. This is a great way to establish credibility with them. Let them know, too, that once you have the systems in place, bug fixes and enhancements will be much easier to roll out.

Create a development environment
If you don’t have a development environment, but code still needs to be written, where are the developers going to write and test their code? I hate to tell you, but if they have access, they’ll write and test in the production environment. So you may have stored procedures called CampaignEmailExport_TEST hanging around (and never getting deleted). Or—oops—you may accidentally overwrite the production version with your new version, and then it runs and causes hundreds of thousands of emails to be sent where they weren’t supposed to. Not that I’ve ever heard of this happening. This kind of problem can go a long way towards convincing users that time and money needs to be spent on working on setting up a good foundation.
For the development environment–you may be able to just get a backup from production, and set it up on another server. If it’s too large, you might need to be creative. Whatever you do, don’t develop or test in the production environment.

Drop obsolete objects
In a system that hasn’t been maintained very well, it’s likely that there are a lot of database objects out there that aren’t being used. They may have suffixes like ‘temp’ or ‘bak’ on them. It can be hard to identify all of these, and you may be tempted to just leave them. However, they can cause a number of problems:

1. They make it difficult to figure out what the actual working codebase is. If you have a lot of duplicate, backup, “working” or “temp” objects, you don’t know what your codebase is like, and how complex it is.

2. Supposed you’d like to drop a tables because it’s huge, and looks like it hasn’t been updated in a long time, but it turns out that they’re being used by stored procedure X. If it turns out that stored procedure X is never used, but you’re keeping it around in the database anyway, then you’ve just lost this opportunity to enhance your code because of an obsolete stored procedure. This kind of issue, multiplied by all the obsolete objects that are in the database, can cause development to be very slow, or even grind to a halt.

Finally...
There’s potentially months and months of work if you start from scratch on all of the above. It’ll require good judgment on what to prioritize, where to start, and how much time to spend on all the tasks that need doing. And perhaps you’re not in a position to set all the priorities. But it can be worthwhile and fun to streamline and tune-up a database that just needs a little work to become a well-oiled machine, requiring much less development time.

Thanks for reading! I welcome feedback in the form of comments, and may post an update to this article with the best suggestions and comments.