SQL Formatter

Our DBA writes some mean SQL code, I can write some SQL that works :) .  It’s not my strongest suit but certainly not my weakest so I handle most of my sql statements myself.  However, when I need his help I cringe at sending him my sql code because he freaks when he sees it.  To me SQL is a string variable, I use to not care how pretty the formatting is, what tab setting to use, when to carriage return a FROM and so on.  Well, he cares and makes sure I know about it.  I totally understand why but for me it was too time consuming to make sure I format something that in the end is a string variable…. Until I found SQLinForm.  I am now a religious sql formatter, because not only does SQLinForm does it for me but it also creates c#, vb.net, php, and whole slew of other string variables.  My SQL has never been prettier and I can finally ship sql to my dba.

 

image

Read More

Stored Procedures vs Ad Hoc SQL

Stored procedures debate from my point of view:

Pros of using Stored Procedures

  • Cleaner code.  Thousand lines of logic and sql statements can be traded in for hundreds.
  • Push more work to someone else besides developer.  If you have a dedicated DB guy, this can really help in your timing. 
  • Put much more logic into Stored Procedure then Ad Hoc SQL
  • Faster. Ok, maybe a little bit, but with processing power and RAM does this really matter anymore?
  • Permissions are centralized and encompassed.
  • Trips to the database server can be reduced.

Cons of using Stored Procedures

I’m not entirely sure which side of the fence I lie.  Right now I’m more comfortable with not using stored procedures because of the maintenance factor.  I really think LINQ is going to make stored procedures deprecated.

.NET specific – Here is a nice line of code to keep around when working with stored procedures that I end up using when getting the the dreaded “Parameters do not match” error.  You can put this on your datasource Inserting Event.

For x As Integer = 0 To e.Command.Parameters.Count – 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(e.Command.Parameters(x).Value)

Next

Read More

Navicat Review

I do a good amount of work in MySQL so that I need a good database admin tool.  I come from a Microsoft Enterprise Manager/Management Studio background so much of this review compares to that as a benchmark.

The Good

  • Import/Export Data feature rocks.  Much easier to use then DTS for simple imports & exports & supports multiple formats (db, dbf, txt, html, xls, wk1, wq1, xml, mdb, ODBC)
  • Dump as SQL.  Will write out your table to .sql file with the full inserts.  Microsoft so needs this.
  • Maintain Tables.  Easily able to
  • Backups.  Straight forward step by step.
  • Saved Queries.  Navicat saves then within the application at the database level not a sql file, this makes finding queries much faster.
  • Scheduled Job.  Again straight forward step by step
  • Manage Users.  They make it as simple as MySQL can be (which isn’t that simple sense you have to create a user multiple times to have access from localhost and from anywhere)
  • Data Transfers.  Their version of DTS, not as robust but gets the job done.

The Bad

  • Query Windows.  This is my biggest complaint of Navicat and almost a show stopper for me. Tabbed windows are great BUT having multiple tabs within tabs causes my eye to lose focus of which tab I need.  Not good for quick tabbing, I have to think before clicking which slows me down. I much prefer how Management Studio has tabs for queries but shows results at the bottom of that window.

 

  • Query Builder.  I do most of my SQL by hand so this doesn’t effect me to much but Navicat’s query builder uses the dreaded Grave `instead of the Quote ‘.  So if you use their query builder to get a sql statement started, maintaining it could be a nightmare.
  • Designer. Seems clunky and implemented as a second thought.  Big improvements here.

Overall Navicat is the most feature rich MySQL tool on the market (I’ve tried ~4 so far). Pricing is at $139, well within reason. Besides a reworking of the query window, Navicat is a finely tuned.  2 TechToolBlog thumbs up.

Read More

Scripting a MS SQL Database

One thing I love about Navicat is the ability to give me the raw SQL in a backup, something that Microsoft Management Studio lacks big time. I found this great Code Project article/application that does this perfectly.

http://www.codeproject.com/dotnet/ScriptDatabase.asp

Read More