Search Bug in SQL Server Management Studio!

I just got bitten by this one, and it's beyond me why this doesn't work!

I have several .sql files open in SQL Server Management Studio (SSMS). I'm using SQL Server 2005. I wanted to find all occurrences of a particular table anywhere in my open scripts. Sounds simple, right? Just use the find feature (Edit --> Find or Ctrl-F), enter the table name and choose All Open Documents in the Look In dropdown.
Here's the catch - I'd run some of these scripts, and their Results tabs were active. The search function failed to search the content of those Editor tabs, searching the Results tabs instead!

Come on Microsoft, this is a rookie mistake. I'd understand searching the Results tabs in addition, but certainly not to the exclusion of the Editor tabs!

Is "Beta" Just an Excuse?

Just get it to Market

Anyone else frustrated with the proliferation of the word Beta?

Why not just call it half-baked code we want to build a buzz around and tell the truth?

I'm not sure Google started this trend, but they certainly popularized it. But hey, they popularized the web, so I'll give them credit. And when they release a Beta, it seems to be pretty good and solid.

But they never take the Beta tag out.

Breaking the Brand

Now it seems that every software shop or garage-coder has started putting junk on the market. I'm convinced they do this to stake their marketing flag rather than to build their customer base.

The problem is that when you show every potential customer your wart-covered code, some of them go elsewhere and don't come back.

If you're upgrading or adding new features it's fine to have a beta, but don't make it your customers' only option. Keep the functioning site up and put a "Care to try our Beta?" link on it. Microsoft does this with their pop-up "Care to try our new Download Center® Beta?"

Don't get me started on pop-ups, but at least Redmond lets me stay with the Byzantine download pattern I'm used to.

The Final Straw

So now you're asking yourself "Why's that mild-mannered WillSQL4Food all worked up today? What pushed him over the edge?"

Dilbert.com, an important part of my morning routine, was broken (beta-ed) this morning. They taunt me with the promise of full-color Dilbert comics every day, but dash me against the Rocks of Beta with a cryptic error.

Come on, Scott! Come to think of it, Mr. Adams probably did this to create material for some strips.

I just hope they're funny.

Thanks for stopping by,

Will

Windows Automatic Updates: Delay that Reboot!

War Story:

In my previous company, not only was I prohibited from changing this behavior, but the IT police would roll updates mid-morning and force a reboot sometime thereafter. I worked in two different facilities for this company and in a fit of Standards Adherence they behaved differently at different facilities. At corporate HQ, I'd usually receive a notice about 10:00 in the morning that my machine would unceremonially reboot in four hours. The dialog could not be minimized and continued to taunt me with a countdown timer. I could suck it up and reboot manually to clear the requirement, but if I didn't I was in for a rude interruption in the middle of my work day.

The second facility, for the same multi-national conglomerate, would give the same warning dialog, usually about the same time of day, but with a 90 minute countdown. This meant that if I didn't take action shortly, my machine would reboot during lunch. Fortunately, I don't trust these things enough to walk away without saving my work, but I can't count how many delicate spreadsheets were lost by others while out for a quick sandwich.

Application:

Suffice it to say, then, I get rather annoyed on my personal machine having to tell Windows to wait until I'm ready to reboot after Automatic Updates are installed.

I don't mind the first message. I don't even mind being reminded - on a reasonable interval.

But every ten minutes? Come on, Redmond!

Solution:

So here's how to tell your machine to leave you alone for a while.

From command line, run gpedit.msc

Navigate to:

> Computer Configuration

> Administrative Templates

> Windows Components

> Windows Update

Modify Configure Automatic Updates to Enabled and choose your settings therein

Modify No auto-restart for scheduled Automatic Updates Installations to Enabled

Modify Re-prompt for restart with scheduled installations to Enabled and specify your time limit (mine's 480 minutes)

Then enjoy clicking Later only once every eight hours rather than the default 10 minutes.

Disclaimers:

On some corporate machines, you may not be able to edit this policy.

Editing group policy is up there with registry editing - you could break something, so be careful! If you're like me, you'll wander from the path above and fiddle with things. It will start with "Hey, what's this do?" and could end with "Where's my Windows XP Professional Edition CD?"

Thanks for stopping by!

Will

Registered Servers

Registered servers make a great shortcut if you manage more than a few SQL Server instances. I’ve got a quick little tip for you…

First - thanks for stopping by!

Here’s another tool for you...

In SQL Server Management Studio (SSMS) you’ll find several embedded tools. I talked about templates the other day, and today I’m reviewing the Registered Server pane. Ctrl+Alt+G (View – Registered Servers in the menu) will bring this pane into view, and you can dock it as you like. You can set it to auto-hide to save space as well.

Right-click in the pane and one of the options is New, which has two choices:

· Server Group… allows you to create a folder for like servers (more in a minute)

· Server Registration… brings up a modified connection dialog which allows you to specify the server, choose the name, and add descriptive text (this is displayed when you mouse over the server registration – I find it helpful to include some database names here.)

Some highlights:

1 – Types: Depending on the services you have installed, you can register various types – Database Engine, Analysis Services, Reporting Services, SQL Server Compact Edition, and so forth.

2 – Groups: You can group servers with a folder structure, similar to templates.

3 – Connections: Once registered, connecting with the Object Explorer or a new query window is quick and easy.

4 – Portability: Other right-click options allow you to export or import the server list to an XML file (more on this in a minute).

Server Groups:

Now that my company’s got a foot print on four continents (we recently added Europe and Africa), I have several more instances to manage. Most locations have at least two servers with different purposes, and then there are development and QA servers, and other miscellaneous servers (think back office). My registered servers are grouped into Production, Reporting, Development, QA, and Other. I haven’t needed to sub-group yet, but these four simple groups have helped a lot!

Portability:

The ability to export and import server registrations is a nice touch – kudos, Redmond! Right-click, choose export, and specify a file name and all your registrations are stored in an XML file. The extension is regsrvr, but a simple text editor allows you to edit at need. You can also choose to export only a group, or even a single server.

I set up my environment on one machine, exported the entire list (allowing it to store passwords – relax, they’re encrypted!). Then, I copied the file to my other machine and imported. Voila! Now all my servers are at my fingertips whichever keyboard I use. After this, I destroyed the file, exported again without passwords and put a copy in source control. This way I can hand that to other a new DBA one day and simplify that initial setup process.

If you have a new developer starting, and he needs access to only a few instances, simply export only the Development group (or single server) needed. Pass the file to the developer and she’s all set!

And here at willsql4food HQ, I can have personal stuff in other groups for research, home use, and any consulting work I might do.

The more servers you manage, the more you’ll come to appreciate this simple, yet powerful tool in SSMS. Hope this helped you!

Thanks again for visiting,

Will

Templates!

Templates are a great addition to any DBA’s toolkit.  I’ll walk you through an overview and even throw in some free code – you’ll be more productive by Friday even if today is Friday.

 

First, thanks for stopping by!

 

Repetition again?  Ugh…

I find that as a DBA, there are several things that are just repetitious.  I’ve found a few tricks and tools to speed up my work and remove some of the drudgery.  Today, I’ll talk about templates, and cover some of the others soon.

 

Templates aren’t new in SQL Server 2005 – they’re also available in 2000, and I won’t pretend to know or care about SQL Server 7.0 and previous versions – that’s someone else’s blog!  I’m not knocking them, but I’m not using them either.

 

The strength of templates is that they shorten repetitive but slightly different tasks.  One I use frequently, and display at the end of this post, helps me check index usage on a given table.  I like to snoop around my indexes occasionally, checking on different tables when mood or suspicion strikes me.  Today I may be concerned about dbo.customers, tomorrow it might be sales.orders.  Furthermore, I might be investigating problems in the Demo database one minute, Beta the next, and Production when usage peaks after lunch.

 

I might even find <gasp!> that indexes are different in different databases.  (And that might be okay…)

 

Sure, I could type out most of the code below, but why when templates will for me?  Plus, I  get quick, consistent answers when management is snooping around me.

 

So how do you get to these, you ask?  I illustrate with SQL Server 2005 Express Management Studio (SSMS) since that’s what’s on my laptop and mention any obvious differences on my work PC.

 

What and where?

There’s no special file extension for templates – they’re straight SQL text.  Templates located in the expected directory behave like templates in Excel, Word and many other tools – opening one, changing and saving triggers a Save As… dialog.  This protects the original template file by prompting you to save somewhere else.  Where is this expected directory, you ask?  Yours will vary on OS, version, install drive, and username, but on my laptop (Vista, SQL Express):

 

C:\Users\Will\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\Templates\Sql 

 

My work desktop (WinXP, SQL2005 Standard):

 

C:\Documents and Settings\wcarter\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql

 

Up one directory and you’ll find templates for other installed tools, such as SQL Mobile, Analysis Services, etc.  Play with those, blog on them, comment below, or whatever…

 

To display a list of available templates, the short cut is Ctrl+Alt+T.  (View / Template Explorer if you prefer menus).  You get a file explorer style dockable window with templates grouped in folders.  Right click any one for options like New, Edit, Search, Rename, and Delete (Edit is the way to change the template rather than a single instance of it).  You can even add your own folders to stay organized.

 

Filling in the <Blanks, , >

The power is in the parameters!  They take the form of

 

<Parameter name, Type, Default value>

 

Put one wherever you have dynamic code.  You don’t even have to supply all the values – <P1, , > will be sufficient.

 

To replace them with specific values, Ctrl+Shift+M (Query / Specify Values for Template Parameters) brings up the Specify Values for Template Parameters window, listing each parameter’s:

·          name

·         type

·         a textbox where you can enter a value.  (If you specified a default, it’s already there.)

 

Especially for templates I don’t frequently use or those I share, I often “name” my parameters with a short phrase or question, like this:

 

<Table name?, sysname, customers>

<Audit date from:, datetime, dateadd(day, -1, getdate())>

<Audit date to:, datetime, getdate()>

 

Anything else?

Templates are also great for those occasional, moderately complex operations.  Build some templates to handle those customer account credits that can’t be done in your app’s UI; quarterly data archival and purge processes; sales force hierarchy changes; and so on.  You won’t have to remember where the file is or if you changed all instances of customer id, sales person name, date, etc; it’s just Ctrl+Alt+T, Ctrl+Shift+M away.

 

Of course, if you spend much time creating and customizing, find that directory above and back it up.  I even keep copies on my thumb drive for when I’m visiting someone else’s cube…

 

The Point?

Templates are readily accessible, customizable, and time-saving.  They make a great addition to any DBA’s toolkit.  Check them out – you’ll be more productive by Friday.

 

They’ve saved me a lot of keystrokes (so I had some left for this blog!)

 

Again, thanks for stopping by!

 

The Promised Example – Enjoy!

 

/**********************************************************************************************************************

TEMPLATE: Snoop Indexes.sql

      Finds usage stats for all indexes for a given table

      Author: WillSQL4Food

**********************************************************************************************************************/

use <db, sysname, WillSQL4Food>

 

-- Index usage (seeks, scans, updates, etc.)

select            object_name(i.object_id) table_name, i.[name] index_name

            ,     c.[name] column_name, ic.key_ordinal, ic.is_included_column

            ,     i.type_desc index_type, i.is_primary_key, i.is_unique, i.is_unique_constraint

            ,     s.user_seeks, s.user_scans, s.user_lookups, s.user_updates

            ,     s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update

  from            sys.indexes i

  join            sys.index_columns ic

      on          i.object_id = ic.object_id

      and         i.index_id = ic.index_id

  join            sys.columns c

      on          c.object_id = ic.object_id

      and         c.column_id = ic.column_id

  left join sys.dm_db_index_usage_stats s

      on          i.object_id = s.object_id

      and         i.index_id = s.index_id

      and         s.database_id = db_id('<db, sysname, WillSQL4Food>')

  where           object_name(i.object_id) = '<tablename, sysname, >'

  order by  object_name(i.object_id), i.is_primary_key desc, index_name, ic.is_included_column, ic.key_ordinal, c.[name]

 

-- Fragmentation

select * from sys.dm_db_index_physical_stats(db_id('<db, sysname, WillSQL4Food>'), object_id('<tablename, sysname, >'), null, null, null)

 

-- Statistics generation

select * from sys.stats s where s.[object_id] = object_id('<tablename, sysname, >')

 

 

The Multiple-Record Set Problem

I've had several gotcha! moments in my career, and this week another one hit me.

Multiple-record sets. I'm not talking about MARS; rather queries which are expected to return a single record, but instead occasionally return two or more.

Now say your application has a subsystem for user access control, third party integration, or content management. Your user may be an individual, a group of individuals, or a client's SOAP architecture consuming a web service. Different users have access to different things in your application. These things might be technical analysis articles on NYSE traded stocks; stock transactions; items in a virtual shopping cart; billing invoices or other account activities for your services; or even comments on a blog.

When your controlling subsystem validates the user's right to a particular thing, it likely issues a SQL query against the application's database. It may look something like this:

select u.user_id, ur.user_rights
from users u
join user_rights ur on u.user_id = ur.user_id
where u.user_id = 3245674 and ur.thing_to_access = 'blog';

What your application expects is a single record, getting it in some, even most cases. It may look something like this:

user_id user_rights
------- ------------------------------------------------
3245674 articles=read;comments=read,write;post=none

However, what it actually gets, in a few cases, is this:

user_id user_rights
------- ------------------------------------------------
3245675 articles=read,write;comments=read;post=false
3245675 articles=read,edit;comments=approve;post=true

And worse, sometimes it receives this:

user_id user_rights
------- ------------------------------------------------
3245675 articles=read,edit;comments=approve;post=true
3245675 articles=read,write;comments=read;post=false

Most trying, as I've said, this doesn't even happen in most cases. This can be the cause for a bug with the classic traits - infrequent, inconsistent, and hard to track down. The bug can be on the data side requiring a DBA to resolve, on the application side requiring a developer to solve, or in the business layer requiring an entire committee, budget, and project plan to solve.

Now you may be scratching your head and wondering how this even leads to a bug. Let me illustrate!

Assume your application expects exactly one record defining user rights, looking up users by id. Now if the user's id is 3245674, there's only only one record to handle making the user rights certain. However, when looking up the user with id 3245675, two records are returned and the rights granted to the user are variable.

This can be bad. Sometimes very bad.

How so? Let's look at the cases:

1 - The code interpreting the recordset may assume a single record, interpreting the first in the set and ignoring the rest. In this case, the user's granted rights depend on the order of the records returned. Without an ORDER BY clause, the first record in subsequent executions of the same query can vary for a number of reasons - page splits, index reorganizations, and statistics recalculations to name a few. The user in this case would sometimes have rights to write articles but not post and at other times rights to edit and post but not write.

2 - It may loop through the records, discarding all but the last record. It should be obvious that this is the same problem as #1.

3 - It may even loop through the set and attempt to synthesize the results into a single set of permissions. In the above case, the user would be able to write and edit; read and approve comments; both of which may break business logic or internal controls. Then comes the ability to post - how do true and false combine? Either way, the end result is the user likely has the incorrect rights.

Now why does this happen, and what do you do about it? If you already have some strategies, add a comment for my other reader.

If not, check back with WillSQL4food soon!