List ALL Stored Procedures in an SQL Server Database

Today I ran into a unique problem. I needed to reassign the permissions on a bunch of Stored Procedures in an SQL Server database. What a joyless task.

Now, I could do this by hand. Just open up Microsoft SQL Server Management Studio, right click on the Stored Procedure, and assign the permissions. In my case, however, I had hundreds of Stored Procedures to change. Doing this one by one would make me want to claw my eyes out.

The solution is to use some hidden system tables to get the list for me.

SELECT [name]
FROM sys.sysobjects
WHERE (type = 'P')

This gives me the list I want, but I can do a bit better:

SELECT 'GRANT EXECUTE ON OBJECT::[dbo].[' + name + '] TO [MyUserID];' AS Expr1
FROM sys.sysobjects
WHERE (type = 'P')
AND (LEFT([name],3) = 'DNN')

With this SQL, I generate the actual command needed to grand the new permissions to the Stored Procedure. I also use a WHERE condition to limit the effect to just Stored Procedures beginning with DNN, so I don't do this to everything, just the ones I want.

Once I run this, I just copy the results, paste it back into the SQL section of Microsoft SQL Server Management Studio, and execute. Bingo! My permissions have been altered.

Enjoy.

Generating Google Base XML files in VB.NET

This may be a bit obscure, but since I struggled with this, I'm sure someone else in the future will struggle with it too. Hopefully this little post will help.

Goal: Create an XML file in RSS 2.0 format using VB.NET for use with Google Base

Challenge: Getting the .NET XmlSerializer object to generate the XML for you with the correct "g:" prefixes on the Google specific tags.

Making XmlSerializer generate basic XML is rather trivial. A quick search of the Interwebs will show you numerous examples. Essentially you just generate a class that matches the basic layout of the XML you want, populate the class, and send XmlSerializer off to covert it to XML.

Getting it to generate the Google specific XML needed for Google Base is a much more difficult problem, however. It becomes a bit tricky when you need some of the xml tags to be generic (for example, <title>), and others to have the Google prefix (ala <g:price>)

Solution: The key lies in a few hints you include in the Class definition to tell XmlSerializer what to do. Here's an example of the class I used:

RSS.vb

Imports System.Xml.Serialization

<XmlRoot(ElementName:="rss", Namespace:="")> _
Public Class rss
<XmlElementAttribute("channel")> _
Public channel As New rssChannel()
<XmlAttributeAttribute("version")> _
Public version As String = "2.0"
End Class

Public Class rssChannel
Public title As String
Public link As String
Public description As String
Public language As String = "en-us"
<XmlElementAttribute("item")> _
Public item As New rssChannelItems()
End Class

Public Class rssChannelItems
Inherits CollectionBase

Public Sub Add(ByVal Item As rssChannelItem)
Dim I As Integer = List.Add(Item)
End Sub

Default Public ReadOnly Property Item(ByVal Index As Integer) As rssChannelItem
Get
Return CType(List.Item(Index), rssChannelItem)
End Get
End Property
End Class

Public Class rssChannelItem
' Required
Public id As String
Public title As String
Public description As String
Public link As String
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public price As Double

' Recommended
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public provider_class As String
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public year As Integer

' Optional
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public agent As String
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public area As String ' Sq Ft
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public lot_size As String
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public property_taxes As Double
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public school_district As String
<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _
Public zoning As String
End Class

Notice this part of the code:

<XmlElement(Namespace:="http://base.google.com/ns/1.0")> _

It is telling the XmlSerializer that I want the element to have the g: prefix. That's the revelation. Once you have that in place, you just create an instance of the class, populate it, and save the XML to disk.

Export.vb

Sub GenerateGoogleBase()
Dim rssFeed As rss = Nothing
Dim rssItem As rssChannelItem

Try
rssFeed = New rss

With rssFeed.channel
.title = "one"
.description = "two"
.link = "three"
End With

rssItem = new rssChannelItem
rssItem.id = "1234"
rssFeed.channel.item.Add(rssItem)

Dim xml As New XmlSerializer(GetType(rss), "http://
base.google.com/ns/1.0")
Dim strFile As New FileStream("C:\Test.xml", FileMode.Create)

Dim xmlns As New XmlSerializerNamespaces()
xmlns.Add("g", "http://base.google.com/ns/1.0")

xml.Serialize(strFile, rssFeed, xmlns)

Catch ex As Exception
End Try
End Sub

Wrap Up: As I said up front, yeah, this is a bit random and obscure. I'm sure I won't be the only person who struggles with this though. My hope is that the next person down the line will find this post and be helped by my struggle.

Big Thanks to Phil Weber for the basic RSS code, and Martin Honnen for helping me with making it work with Google Base.

Earth to HBO, Get With The Times!

Why isn't HBO content available for sale on iTunes?

Sure, I know the standard answer. "HBO is a subscription service. If you want to watch HBO, you must sign up for it. If they sold their shows on iTunes, why would anyone pay their cable company for HBO?"

Blah, blah, blah.

Let's face facts, folks. There are some people who will always subscribe to HBO. It's convenient, it's practically forced down your throat when you sign up for cable, and it's ideal for channel surfing. Those people aren't going to ditch the monthly subscription for iTunes.

There are also a group of people that will never subscribe to HBO. They're not going to pay $15 per month -- every month -- for the few times that a movie comes on that they haven't already rented at NetFlix or BlockBuster. It's just not worth it to them to pay that much for an occasional movie. They might, however, pay $1.99 for an episode of Entourage.

Unfortunately, HBO does not allow that. They have the All or Nothing mentality. Subscribe, or you get nothing until the box set comes out a year later.

Here's a hint, HBO Execs. What you're really telling people is "Steal our content on Bit Torrent". If you don't offer a legal alternative where consumers can purchase your shows ala-cart, it will be stolen instead. That's the reality of the 21st century. It's your responsibility to provide the content when and where people want it. You no longer get to dictate how your shows are watched.

Maybe you should take a page out of Showtime playbook. They sell every one of their original series on iTunes. Weeds, The L Word, Californication, Penn & Teller BS -- everything. They don't seem to be suffering. In fact, what they are doing is making more money with their existing content, and by exposing people to their shows, they're probably getting a few extra subscribers as well.

So, get with the program HBO. It's 2008. Time to listen to your customers and embrace new technology.

Better Exchange Support Coming to Apple Mail?

On Thursday, Apple announced its Roadmap for the future of the iPhone. Great fanfare was made of the forthcoming SDK and the Enterprise features being introduced in the 2.0 release of the iPhone software. There was much rejoicing.

One interesting tidbit mentioned during the press event was that Apple has licensed the Microsoft ActiveSync technology to tightly integrate the iPhone with Micrsoft's Exchange server. This nifty bit of software will allow things like push email, calendar and contact updates from the server to the iPhone. In a nutshell, Mail on the iPhone will now have the same tight integration with Exchange that Outlook does on the Windows OS.

This raised an interesting question in my mind....

Will Mail.app on OS X also benefit from this licensing deal?

Mr. Jobs and the Apple leadership went to great lengths to show that the iPhone is really just a small Mac. It runs the same underlying operating system code. It runs the same Objective-C based applications. It's a Mac in every sense, with a few minor enhancements.

If that's true, why couldn't the desktop versions on Mail.app, iCal and Address Book benefit from this new ActiveSync integration like the iPhone versions will? It should be fairly trivial to make the changes in both places, right?

Many corporate users have bemoaned the lack of true Exchange support in Mail.app. For that matter, even Entourage 2008 doesn't have full Exchange support. Both use IMAP4 to interact with the Exchange server. It works, but it's not the most elegant (or reliable) solution.

I may be reading the tea leaves incorrectly, but I'm guessing we're about to see a much more robust version of Apple Mail coming to a Mac desktop near you very soon. Here's to hoping...

Microsoft Access 2007 - Pretty New Face, Same Old Annoyances

I use Microsoft Access.

I'm not particularly proud of it, but it's a fact of life. If you're going to work in Windows, Access is the easiest tool for some jobs. More importantly, many of my clients use it, so I must as well.

I've been using Access 2007 for a few days now, and I'm sadly disappointed. I had high hopes that after four years of development, many of the lingering bugs and missing features would be addressed. Alas, my hopes have been dashed. It may look nicer, but under the covers, it's the same old Access.

Does Microsoft actually improve anything from release to release, or just change the colors so they can charge you another $500???

It's now 2008, and this is version 12 of Access. Why do these problems still exist:

1. Importing external data still requires an ODBC connection. Really? In 2008 I still can't just give you an ADO connection string? I still must go setup a bleeping ODBC configuration in the Control Panel before I'm allowed to import from SQL Server? That's just silly.
   
2. No Multiple Selections. This is just ridiculous. Now that I've setup my ODBC connection, and entered in the password -- again - I'm given a list of tables I want to import. For my particular SQL Server database, that list is about 100 tables long. Why can't I select more than one at a time? AAARRRGGHHH!!! I have to click on every flargin table name one by one?!? Really? REALLY!?!?
   
   I could just click the "SELECT ALL" button....if Access didn't mix System Tables in with User Tables. I don't want to import all the arcane System tables, just the data I've created.
   
   At least Access 2007 allows me to save the group of tables I've selected -- a step in the right direction. That is, until next week when a new table gets added to the SQL Server. Then I have to start all over again to ensure this new table gets included.
   
3. No, Really, No Multiple Selections. One nice thing that Access does is allow you to copy and paste objects in the database. For example, you may want several tables with similar structures. You can just create the first one, copy it, then paste to create a second. Very handy..... Unless you want to copy more that one item at a time.
   
4. Status? What Status? Access 2007 changes the way items are show in the database. Instead of defaulting to icon or list views -- similar to how you see things in Windows Explorer -- Access 2007 gives you a list in a column. Of course, it omits basic nicities, like the ability to sort your items, or even give you a simple count of how many there are. Would it be so hard to tell me that I have 89 queries in the Status Bar? That is what it's for, right? Status...
   
   
5. Script this! I'm sure anyone who uses Access on a regular basis -- and deals with customers -- runs in to the same problem. Version control. The folks in Spokane have the latest version of the home-grown reporting database, but the folks in Butterfield aren't sure when the last time their database was updated. Worse yet, the folks in Utica are still running on Access 2000. They must be at least 3 upgrades behind but, they did have a few special reports created for them two years ago.
   
   Wouldn't it be nice if there was a way to compare two copies of the database and find the difference?
   
   It might not be ideal, but if you could at least script out all the tables, queries, forms and reports, you could use your favorite Diff tools to merge the variations and create one master. You then run the merged script and you've got the latest, greatest version of the database with all the changes included.

Honestly, I don't think the folks at Microsoft use Access themselves. If they did, these short-comings would be painfully obvious. They would certainly fix them just to make their own jobs a bit less cumbersome.

Well, there's always Office 2010 to look forward to. Maybe that one will have a green color scheme...

iPhone SDK -- Are we there yet?

Tick tock, tick tock. It's February, Apple, can we get the iPhone Developer's Toolkit?

OK, so it's only the 5th of February, and maybe I'm a bit impatient. Still, many iPhone customers have been longing for this release since the phone was announced. With the SDK comes the great possibility that the iPhone reaches its true potential as a mobile computer. Games? A Flash Plugin? Remote Desktop? A Skype Client? It's easy to imagine the possibilities.

Waiting is the hardest part. Speculating, however, is very, very exciting...

iPod Shuffle Contest

The folks over at EasyImage Software are running a contest right now where you could win a new iPod Shuffle.

You must actually purchase a copy of software to enter, but lots of the people I know use it, so it may fit for you too...

Check it out!