thinking-out-loud

SQL Blob Storage

Working with a client, I came across some stored procedures that went through gyrations to selectively store XML in a varchar field or in a text field, depending on its size.  But, after doing a little research, I came across this article on SQL blob storage with the following key points

• blobs are text, ntext, or image fields
• blobs are stored in a separate heap of 8KB pages
• blobs can be stored “in row” if the feature is turned on and the size is below the configurable limit
• blob pointers take up 16 bytes when the blob is not stored in-row

SQL Server Database Publishing Wizard

In SQL 2000, the Enterprise Manager tool had the ability to script out an entire database including tables, store procs, etc.  Now in SQL 2005, the Management Studio doesn't include that.  It can only script out individual creation scripts for one object at a time.

But, Caleb turned me on to this add-in called the Microsoft SQL Server Database Publishing Wizard 1.1.  It adds right-click functionality to gen creation scripts including user definitions and data.  I've used it twice in the last two days.  Very handy!

PerformancePoint Server 2007

Just read this RedmondMag article on PerformancePoint Server 2007 - the forthcoming combination of MS Business Intelligence tools and SharePoint 2007 made possible by Microsoft's recent acquisition of ProClarity.  This is very exciting because almost every enterprise-class customer I've had in the last couple of years has been working on decision support dashboards and scorecards.  I look forward to seeing those customers grow more agile through real-time business feedback and better information summaries. 

The dinosaurs who don't get it will soon be extinct.

SQL Server Dev Install Error 193

I'm creating a VPC for my SharePoint 2007 Project and run into "Error 193" when installing SQL Server 2005 Developer Edition.  It seems that the dotnetfs.exe file is bad on the ISO for SQL 2005 Dev.  I found some guidance and came up with a simple work-around that I posted here.  Basically, once you have all your updates applied to your VPC, map a Shared Folder to a physical CD instead of capturing the Physical CD or ISO.  Then when you install, it skips the .Net 2.0 Framework install and procedes like you'd expect.

When in doubt... try isql

Last week I could connect to this client database without any problem.  Last night I finally put my new laptop on the IMPROVING domain.  This morning I couldn't connect to the same database.

So, I found a good source on debugging database connectivity, but it didn't help.  It led me to this MS article on firewalls and SQL.  Interesting, but still no help.

Then my client suggests using isql (usually found at: C:\Program Files\Microsoft SQL Server\80\Tools\Binn).  Vioala!  It connected, but how?   Well, that MS article gave the clue of using netstat, which has a '-b' option that shows the program making each connection.  So I used it while isql was connected to the db and saw a different IP address than ping reported.  That address works in SQL Manager. Hmmm.

I think the DNS gave me a different preferred IP address than last week since I'm in a different domain.  And I bet isql is smart enough to ask for multiple DNS records for a name and then try all the IP addresses before giving up.  I need to dig a little deeper to understand exactly what's going on.  But when in doubt, use isql.

SQL 2005 Wish List: Updated

[This is an extension of a previous post]

Spoke too soon -- I am realizing that there are a lot of things I really want from the SQL Server management tools. I want to capture these thoughts while my threshold is still high and I am not lulled into complacency. I am going to update this in place, so I apologize for violating blog ettiquette a bit. P.S. If the in place editing is annoying in some way, please let me know.

• Script dependent objects while viewing dependencies - there is an option from the dependencies screen, but it is disabled.
• Sort/Filter dependencies by type (i.e. only view stored procedures).
• Database refactorings.
  • Extract table - create data migration script, repair FKs as necessary, update dependent SPs, views, functions, etc.
• From the dependencies window, I ought to be able to navigate to a dependent or depended upon object. This one seems so obvious it hurts.
• Multiselect in the dependency view, right-click, generate script, etc.
• On the Generate Script wizard, when scripting to a file, create directories if they do not exist.
• On the Generate Script wizard, remember the options I last selected. I imagine that this is a systemic problem, although I don't know yet.
• This seems like a bug: Generate Script again. I want to select a single table and generate a script for the table and all of its dependencies of type SP, View, Function. If I select all of those types as the objects I want to script, I *must* choose one of each. If I only select tables as the type I want to script, then select my single table, only that table and dependent tables get scripted. No SPs. No functions. No roast beast.
• Generate Script: The depencies that get scripted should be the same dependencies that get displayed in the dependencies view. Argh.
• Generate Script: add an option to recurse dependencies.
• Restore: Keyboard navigation is lacking -- e.g. can't arrow between radio button options. In general, the user interface doesn't seem to support "standard" windows navigation, so it feels unnatural. Some of this I really like (I can maximize almost any window). But a lot, I don't.
• Restore: I can't restore from a backup file on a remote share (either unc or mapped network drive). I know, I know, databases are big and you probably don't want to do this, but gigabit ethernet is fast, and in fact, more often than not, I *do* want to do this in development mode, especially when joining a new project. It *is* the new millennium. Really.
• General: I wish it didn't do this:

SQL 2005 Wish List: Updated

[This is an extension of a previous post]

Spoke too soon -- I am realizing that there are a lot of things I really want from the SQL Server management tools. I want to capture these thoughts while my threshold is still high and I am not lulled into complacency. I am going to update this in place, so I apologize for violating blog ettiquette a bit. P.S. If the in place editing is annoying in some way, please let me know.

• Script dependent objects while viewing dependencies - there is an option from the dependencies screen, but it is disabled.
• Sort/Filter dependencies by type (i.e. only view stored procedures).
• Database refactorings.
  • Extract table - create data migration script, repair FKs as necessary, update dependent SPs, views, functions, etc.
• From the dependencies window, I ought to be able to navigate to a dependent or depended upon object. This one seems so obvious it hurts.
• Multiselect in the dependency view, right-click, generate script, etc.
• On the Generate Script wizard, when scripting to a file, create directories if they do not exist.
• On the Generate Script wizard, remember the options I last selected. I imagine that this is a systemic problem, although I don't know yet.
• This seems like a bug: Generate Script again. I want to select a single table and generate a script for the table and all of its dependencies of type SP, View, Function. If I select all of those types as the objects I want to script, I *must* choose one of each. If I only select tables as the type I want to script, then select my single table, only that table and dependent tables get scripted. No SPs. No functions. No roast beast.
• Generate Script: The depencies that get scripted should be the same dependencies that get displayed in the dependencies view. Argh.
• Generate Script: add an option to recurse dependencies.
• Restore: Keyboard navigation is lacking -- e.g. can't arrow between radio button options. In general, the user interface doesn't seem to support "standard" windows navigation, so it feels unnatural. Some of this I really like (I can maximize almost any window). But a lot, I don't.
• Restore: I can't restore from a backup file on a remote share (either unc or mapped network drive). I know, I know, databases are big and you probably don't want to do this, but gigabit ethernet is fast, and in fact, more often than not, I *do* want to do this in development mode, especially when joining a new project. It *is* the new millennium. Really.

Wish List

Spoke too soon -- I am realizing that there are a lot of things I really want from the SQL Server management tools. I want to capture these thoughts while my threshold is still high and I am not lulled into complacency. I am going to update this in place, so I apologize for violating blog ettiquette a bit. P.S. If the in place editing is annoying in some way, please let me know.

• Script dependent objects while viewing dependencies - there is an option from the dependencies screen, but it is disabled.
• Sort/Filter dependencies by type (i.e. only view stored procedures).
• Database refactorings.
  • Extract table - create data migration script, repair FKs as necessary, update dependent SPs, views, functions, etc.
• From the dependencies window, I ought to be able to navigate to a dependent or depended upon object. This one seems so obvious it hurts.
• Multiselect in the dependency view, right-click, generate script, etc.
• On the Generate Script wizard, when scripting to a file, create directories if they do not exist.
• On the Generate Script wizard, remember the options I last selected. I imagine that this is a systemic problem, although I don't know yet.
• This seems like a bug: Generate Script again. I want to select a single table and generate a script for the table and all of its dependencies of type SP, View, Function. If I select all of those types as the objects I want to script, I *must* choose one of each. If I only select tables as the type I want to script, then select my single table, only that table and dependent tables get scripted. No SPs. No functions. No roast beast.
• Generate Script: The depencies that get scripted should be the same dependencies that get displayed in the dependencies view. Argh.
• Generate Script: add an option to recurse dependencies.

Likes

This list will contain the things I like about SQL Server 2005 and/or the management tools.

• Recursive dependencies in the dependency view

SQL Server Management Studio Experience Report

I am working on a J2EE project currently, but we are using SQL Server 2000, so I have an opportunity to take the new 2005 tools for a lap. Here are some very [,very] initial experiences.

• +1 - Visual Studio Shell
• +1 - Backwards compatibility with SS2K
• +5 - All Dialogs I have encountered can be maximized to full screen.
• +1 - Query  Editor
• -1 - Can't search stored procedures - should have the same navigation experience as other code, e.g. C#
• -1 - "Generate Script" wizard makes you specify scripting options before selecting the types of objects you want to export
• -1 - "Generate Script" wizard won't drop and recreate (am I wrong here?)
• -1 -
• -10 - "Generate Script" wizard had problems with one stored procedure, causing the rest of the scripting to fail. It didn't even attempt the rest.
• +12 - "Generate Script" wizard has a "continue scripting on error" option. D'oh.
• -5 - It is set to false by default. D'oh D'oh.