Playing with SQL Azure; part 3

Well, I think I'm done with SQL Azure. Not because I'm finding any kind of major problems with it, just because—there's not enough that's different between regular SQL Server and SQL Azure!

The last thing I did here was create some additional tables and stored procedures in my SQL Azure database. I inserted to these tables via BCP, that was also fairly straightforward once I figured out a few gotchas (naming for the user and server).

I thought there would be so much to learn, but honestly, for a database developer (as opposed to a DBA) it appears to be 99% the same as SQL Server. Now, if I were doing a full fledged pilot project, with stress tests, investigating privacy issues, size issues etc, it would be different. But for just playing around for my own interest, I'm all done, and it's been fun!

Playing with SQL Azure; part 2

I'm working my way through the SQL Azure tutorial (the one I found most useful was here).

Ran into some surprises - you can't use the "Use DatabaseName" syntax to switch from one database to another (you have to open a connection in another database).

Also, it appears that you can't reference another database on the same server. For instance, when running the following:

select * from TestSylvia..test1

I got this error:

Msg 40515, Level 16, State 1, Line 1  

Reference to database and/or server name in 'TestSylvia..test1' is not supported in this version of SQL Server.

From some searching online, it looks like that is being worked on.

So, there's some thing that are not yet possible. But what really struck me is the fact that overall, working on SQL Azure is so similar to working with an on-premises MS SQL Server database. I look forward to exploring further. And writing up a micro blog post on SQL Azure really motivates me to explore further.

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.