Buck Woody : SSMShttp://sqlblog.com/blogs/buck_woody/archive/tags/SSMS/default.aspxTags: SSMSenCommunityServer 2.1 SP2 (Build: 61129.1)Multi-Monitor Support in SQl Server Management Studio 2008http://sqlblog.com/blogs/buck_woody/archive/2010/02/04/multi-monitor-support-in-sql-server-management-studio-2008.aspxThu, 04 Feb 2010 14:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21831BuckWoody2http://sqlblog.com/blogs/buck_woody/comments/21831.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=21831<P>Did you notice that there are a lot of "little" things that were improved in SQL Server 2008? One of those was done by one of my favorite developers and deals with multi-monitor support. In previous versions of SQL Server Management Studio&nbsp;you could open&nbsp;the tool in multi-monitor and arrange the panes onto mulitple monitors. But when you disconnected the second monitory (as is often done with a laptop) you would "lose" the panes that were moved - SSMS just wasn't aware of those multiple monitors. Vlad spent a lot of time making that not true.SSMS is much better about being aware of the other monitors.</P>
<P>Sure, there's more to do in this area. We'll keep improving that feature, but I wanted you to know that sometimes little things take a lot of work - and aren't always noticed.</P>
<P>&nbsp;Thanks, Vlad!</P><img src="http://sqlblog.com/aggbug.aspx?PostID=21831" width="1" height="1">SSMSSQL Server Management StudioSQL Server 2008Hovering Over the Titles in Activity Monitorhttp://sqlblog.com/blogs/buck_woody/archive/2010/01/21/hovering-over-the-titles-in-activity-monitor.aspxThu, 21 Jan 2010 14:16:11 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21318BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/21318.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=21318<p>I show this little-known feature in just about every perf tuning class I give, and it’s often a surprise to most of the folks there. It’s not earth-shattering or anything, but I do find it helpful.</p> <p>In SQL Server Management Studio 2008, start the new Activity Monitor. Open one of the “Bands”, such as “Processes”. Move your mouse cursor over one of the header titles and leave it there. If that column is getting information from a particular Dynamic Management View (DMV), we’ll tell you where we are getting it from. </p> <p>Just thought I would share that.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=21318" width="1" height="1">SSMSTipsSQL Server Management StudioSQL Server 2008Performance TuningTools and Processes for “Fitting it all in”http://sqlblog.com/blogs/buck_woody/archive/2010/01/18/tools-and-processes-for-fitting-it-all-in.aspxMon, 18 Jan 2010 14:42:05 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21147BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/21147.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=21147<p>Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.</p> <p>So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.</p> <p>I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town. </p> <p>For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines. </p> <p>Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.</p> <p>So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?</p><img src="http://sqlblog.com/aggbug.aspx?PostID=21147" width="1" height="1">DevelopmentSSMSDeveloperTipsMicrosoftDBADesignSQL ServerSQL Server Management StudioScriptsWebPlanningPowerShellProcessAgentManagementWizards are evil – wait, no they aren’t!http://sqlblog.com/blogs/buck_woody/archive/2009/12/14/wizards-are-evil-wait-no-they-aren-t.aspxMon, 14 Dec 2009 15:55:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:19942BuckWoody4http://sqlblog.com/blogs/buck_woody/comments/19942.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=19942<P>SQL Server contains many “Wizards. Wizards are simply programs that collect information based on user choices. The Wizard’s screens explain each step and the choices on that screen. Based on those answers collected from the user, the Wizard performs some task. What could possibly be wrong with helping a user this way? Well, plenty. </P>
<P>Wizards hide complexity from the user, which can prevent them from learning the process behind the Wizard. Wizards can also enable the user to choose the wrong options too easily – especially if the user doesn’t read the screens. </P>
<P>For instance – in the example that’s always mentioned, the Maintenance Wizard, the screens present not only “Shrink Database” (which you should never do) but it presents both REBUILD and REORGANIZE options for the Indexes. Of course, Microsoft isn’t sure which you need to do, but some users select both – which isn’t necessary. </P>
<P>And then from time to time there’s a bug in the code and the Maintenance Wizard doesn’t work properly. For this reason, some experts have recommended that we remove all of the Wizards. </P>
<P>But I don’t agree. I think Wizards have their place – given two conditions. Number one, they always need to work. And number two, the users need to read the options carefully so that they make the right choices. The Wizards do a good job of making sure the process can be scripted, and many of them drop you off (like the Maintenance Wizard) with a package that can be changed later. So don’t give up on the Wizards. Let’s fix them. In fact, tomorrow I’ll talk about an idea brewing in my head, and it involves actually increases the number of Wizards! </P><img src="http://sqlblog.com/aggbug.aspx?PostID=19942" width="1" height="1">SSMSDeveloperDBASQL ServerSQL Server Management StudioAdministrationProcessCode that Writes Codehttp://sqlblog.com/blogs/buck_woody/archive/2009/11/25/code-that-writes-code.aspxWed, 25 Nov 2009 14:01:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:19171BuckWoody5http://sqlblog.com/blogs/buck_woody/comments/19171.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=19171<p>I have scripts that re-create my databases for testing and development purposes. But sometimes I want to take the data from a set of tables and move it as well – I could use SSIS, or a SELECT INTO statement, but what if I want to “re-set” the data to a point in time? In other words, load it with some “base data”? </p> <p>I thought this might be a good place to demonstrate “code that writes code”. No, it isn’t that big of a deal – most DBAs know how to do this, but in the interest of those who don’t, I thought I would share.</p> <p>So the end result I want in this case is to have a bunch of INSERT statements that contain my base data from a table where that data already exists. I could script out the table from SSMS and use the replacement parameter feature to fill out each record individually:</p> <p>I have a table in one of my databases that I right-clicked to show the INSERT statement:</p> <font color="#008000" size="2"><font color="#008000" size="2"> <p>-- Person</p> </font></font><font color="#0000ff" size="2"><font color="#0000ff" size="2"> <p>INSERT</p> </font><font color="#000000" size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">INTO</font></font><font color="#000000" size="2"> [WAVS]</font><font color="#808080" size="2"><font color="#808080" size="2">.</font></font><font color="#000000" size="2">[dbo]</font><font color="#808080" size="2"><font color="#808080" size="2">.</font></font><font size="2"><font color="#000000">[Person]</font></font></font><font size="2"><font size="2"> <p></p> </font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2"><font color="#000000">[PersonName]</font></font> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2">[Street]</font> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2">[CityStateZip]</font> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2">[PersonType]</font><font color="#808080" size="2"><font color="#808080" size="2">)</font></font></font><font size="2"> <p></p> <font color="#0000ff" size="2"><font color="#0000ff" size="2">VALUES</font> <p></p> </font><font color="#808080" size="2"><font color="#808080" size="2">(&lt;</font></font><font color="#000000" size="2">PersonName</font><font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font color="#000000" size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">varchar</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font color="#000000" size="2">150</font><font color="#808080" size="2"><font color="#808080" size="2">),&gt;</font></font></font><font size="2"> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,&lt;</font></font><font size="2">PersonStreet</font><font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">varchar</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">100</font><font color="#808080" size="2"><font color="#808080" size="2">),&gt;</font></font></font><font size="2"> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,&lt;</font></font><font size="2">PersonCityStateZip</font><font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">varchar</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">255</font><font color="#808080" size="2"><font color="#808080" size="2">),&gt;</font></font></font><font size="2"> <p></p> <font color="#808080" size="2"><font color="#808080" size="2">,&lt;</font></font><font size="2">PersonPersonType</font><font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">varchar</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">100</font><font color="#808080" size="2"><font color="#808080" size="2">),&gt;)</font></font></font> <p><font color="#808080"><font color="#808080"><font color="#333333" size="2">I can use the CTRL-SHIFT-M keys to replace the VALUES part with the values I want.&#160; </font></font></font></p> <p><font color="#808080"><font color="#808080"><font color="#333333" size="2">But I already had a copy of that “base” data before I turned the database over to the testers. Before I give it to them, I run code like this on each table – this one is for the <em>Person</em> table above (note that there should only be two lines here):</font></font></font></p> <font color="#808080"><font color="#808080"><font color="#333333"><font color="#0000ff"><font color="#0000ff"> <p><font size="2">SELECT</font></p> </font><font size="2"><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">'INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES ('</font></font><font color="#000000"> </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> </font><font color="#ff00ff"><font color="#ff00ff">CAST</font></font><font color="#808080"><font color="#808080">(</font></font><font color="#000000">[PersonID] </font><font color="#0000ff"><font color="#0000ff">AS</font></font><font color="#000000"> </font><font color="#0000ff"><font color="#0000ff">VARCHAR</font></font><font color="#808080"><font color="#808080">(</font></font><font color="#000000">5</font><font color="#808080"><font color="#808080">))+</font></font><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">', '''</font></font><font color="#000000"> </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> [PersonName]</font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">''', '''</font></font><font color="#000000"> </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> [Street]</font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">''', '''</font></font><font color="#000000"> </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> [CityStateZip] </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">''', '''</font></font><font color="#000000"> </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> [PersonType] </font><font color="#808080"><font color="#808080">+</font></font><font color="#000000"> </font><font color="#ff0000"><font color="#ff0000">''')'</font></font></font></font><font color="#0000ff"><font color="#0000ff"> <p><font size="2">FROM</font></p> </font><font size="2"><font color="#000000"> [WAVS]</font><font color="#808080"><font color="#808080">.</font></font><font color="#000000">[dbo]</font><font color="#808080"><font color="#808080">.</font></font><font color="#000000">[Person]</font></font></font> <p><font color="#000000" size="2">When I run this code, I get these statements:</font></p> <p><font color="#0080c0" size="2">INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (1, 'Buck Woody', '123 Here Street', 'Covington, WA 98042', 'Vet') <br />INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (2, 'Jane Doe', '231 Function Ave', 'Redmond, WA 98052', 'Vet') <br />INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (3, 'Diane Wilson', '34251 Appt 3 7th Street', 'Seattle, WA 98061', 'Vet') <br />INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (4, 'John Kelso', '89734 Country Lane', 'Covington, WA 98042', 'Farmer') <br />INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (5, 'Marjorie Christian', '9893452 Changein Lane', 'Maple Valley, WA 98072', 'Farmer') <br />INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (6, 'Joanne Lister', '98904 Mapelwood Drive', 'Spokanne, WA 98045', 'Zoo Worker')</font></p> <p><font size="2">All I have to do is copy those statements into my “initialization” scripts just after the table creations, and then I can run them at will to create my new database (I know I can take a backup – I have reasons for not doing that).</font></p> <p><font size="2">There are two keys for writing code that writes code: The first is to simply use the single tick (') to write out what the end code should say (INSERT INTO, in my case) and then to use multiple ticks (''') when you want the code to contain the ticks themselves. This “escapes” the characters so that you can embed them. If you use SSMS to write your queries, the color coding will look as mine does above. You’ll also need to remember to CAST or CONVERT any data types that don’t concatenate (+) together well. In my case I had to CAST an integer value – but that’s OK, since it is only printing to the screen and I don’t include the ticks, the INSERT works fine.</font></p> <p><font size="2">You can use this method not only with INSERTS, but with lots of operations. And you can also even fire off the code once you create it – but that’s another post :)</font></p> <p><font size="2">&#160;</font></p> </font></font></font><img src="http://sqlblog.com/aggbug.aspx?PostID=19171" width="1" height="1">DevelopmentSSMSDeveloperTipsDBADesignSQL ServerSQL Server Management StudioScriptsTutorialsWebT-SQLVirtualizationChanging the Primary Key After You Have Datahttp://sqlblog.com/blogs/buck_woody/archive/2009/11/24/changing-the-primary-key-after-you-have-data.aspxTue, 24 Nov 2009 13:25:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:19147BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/19147.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=19147<p><a href="http://blogs.msdn.com/buckwoody/archive/2009/11/23/changing-the-primary-key-before-you-have-data.aspx" target="_blank">Yesterday I blogged about changing a Primary Key (PK)</a> during the design phase, and before you have data in the database. Even then, it’s not trivial to change the data type or column(s) that make(s) up the PK. When you have data in that Primary Key and/or you have Foreign Keys (FK) that point to a PK field, this becomes a much more involved process.</p> <p>First, you MUST take a complete backup of the system, and you MUST do this work on a development system. You’re going to be manipulating base data, and in the worst kind of way. Why do I say that?</p> <p>There are three kinds of data problems. One is physical data corruption, and once you restore it (I don’t trust repair with data loss) then you know where the “good” data stops and starts. You know that the FKs are pointing to the right PKs, and that the data is readable. </p> <p>The second kind of data problem is “incorrect” entry, meaning that you put in “Buck Woodie” when you meant “Buck Woody”. This is also often correctable, as your Declarative Referential Integrity (DRI) will still keep that record together. It’s a simple matter to locate and correct.</p> <p>But the third kind of data problem is the worst. In this kind, the FKs point to the <em>wrong</em> PK, or <em>not at all</em>. Or perhaps some of the FK data is missing. The reason this is the worst is that the data cannot be trusted – did Buck Woody really pay for that purchase or was that payment from a different record? If the links are incorrect, everything will look fine, but it won’t be correct.</p> <p>So in the situation where you want to change the PKs when you already have data, one of the steps is to re-point the FKs to the proper PKs. Get this wrong and you’ll have “dirty data”. The only recourse there is to restore the backup and start over.</p> <p>I’m assuming that you have a REALLY GOOD reason to make this change, and that you’ve taken that backup and you’re on a test system. As Hannibal Lector would say: “Okeedokee – here we go.”</p> <p>I’ll describe two possible choices. In the first, understand and document the PK and FK relationships for each table. Then create queries that tie out the INSERTS for that data in the correct order, and include all fields for both tables, including the PKs in the parent table and the FKs in the child tables.</p> <p>With those INSERT statements made, drop the PK and FK constraints on all of the tables using the script I mentioned yesterday. Make your change to the data type (or field) in the tables. Then edit the INSERT statements to have the new value types, ensuring that the “sets” or records are kept together by keeping the FK’s pointing to the right PKs. Then re-apply the PK and FK constraints, watching for errors. With backups and scripts, you can make corrections along the way.</p> <p>The second method works much as the first, with the exception of using the INSERT statements. You can use SSIS or a third-party transfer process to actually move the data and change the data type or values “in flight”, although this requires more thought and planning in my experience.</p> <p>In any case, you should test the results thoroughly and ensure that you’re getting the data you expect. Communicate what you’re doing, and be ready to fall back to your backups at the first sign of trouble.</p> <p>Oh – since SQL Server 2005, you’ve been able to “relax” constraints to insert data. That won’t help you here, since you’re changing the entire key for whatever reason. That’s only meant to allow you to get data in when the key doesn’t change.</p> <p>OK – feel free to comment on this post, since there are other ways I didn’t cover. But if you’re reading my material or any of the comments, make sure you test it yourself – I might have missed something, no one is perfect, your situation may vary. Once again, you can see why I value a good design and ERD so highly – that way you never have to get here to begin with!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=19147" width="1" height="1">DevelopmentSSMSDeveloperTipsMicrosoftDBADesignSQL Server