tag:blogger.com,1999:blog-67783986966601839742024-03-08T01:31:24.131-08:00SQL Server and .NET from the TrenchesThis blog is designed to share tips and tricks learned "in the trenches" of software development projects, including Microsoft SQL Server 2008, C# .NET, Visual Studio 2008 (including the database edition, VSDB), and any other related technologies that I might be inclined to post about such as Excel VBA, source control, etc.Unknownnoreply@blogger.comBlogger18125tag:blogger.com,1999:blog-6778398696660183974.post-42742018306402765982011-10-11T14:09:00.001-07:002011-10-11T14:28:05.791-07:00Why Windows Mobile has a legitimate shot at market share<p>Windows Mobile has been in the news recently as Microsoft gears to launch a new revision of the OS and new devices. (<a href="http://www.bloomberg.com/news/2011-10-10/microsoft-windows-phones-will-get-more-marketing-dollars-sales-incentives.html">http://www.bloomberg.com/news/2011-10-10/microsoft-windows-phones-will-get-more-marketing-dollars-sales-incentives.html</a>, others). I used a Windows Phone 7 for a while and loved it except that it was missing many key apps I like to use. It’s going to be tough for Microsoft to grab market share – they will need slick devices with great features, but most importantly they are going to need very good apps, the apps that consumers want, such as Pandora, Meebo, Spotify, and Angry Birds.</p> <p>This is going to be a long uphill battle. Even if an app is available on the Windows Phone, will it be as good as the versions on other platforms? The Geocaching app on the Windows phone does not have as many features as the one on the iPhone. But will consumers know that there are differences among the same apps on different platforms? For Microsoft’s sake, let’s hope that consumers <strong>do</strong> notice!</p> <p>It is this very issue that may be to Microsoft’s long term advantage. From what I have seen, development on the Windows Phone is easy. Easy development is the key. I just met with a friend at small hip company that has an iPhone app and an Android app for their platform. He is the sole iPhone app developer, but they have a team of 4 Android developers that can hardly keep up with the iPhone app’s features while constantly testing and configuring for a plethora of Android platforms and versions. <strong>That’s 4 Android developers to 1 iPhone developer – and the Android team cannot not even keep up on features.</strong> That’s a big cost – and in the long run, a cost that companies will need to find a way to reduce. The Android versions of apps will not be as good as their iPhone counterparts – but Windows Phone versions should be less expensive to develop and maintain than the Android versions. This is a promising thing for Microsoft if they can get everything else right. I don’t think Windows Mobile will be the market leader next year, but I wouldn’t be surprised if they had 25%+ in three years – and the ease of writing and supporting apps is paramount.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-57459286213860681642010-11-06T17:13:00.001-07:002010-11-06T17:13:14.963-07:00Migrating to Hyper-V from Virtual Server or Virtual PC<p>These instructions seem to work well for migrating guest machines from Virtual PC or Virtual Server 2005 to Hyper-V. As noted some steps may not be necessary, but this is comprehensive should you run into issues – e.g. migrating from Virtual PC to Hyper-V is not supported and machines that originated from Windows XP or Virtual PC might not work without the undocumented/unsupported steps to replace the HAL with an ACPI HAL.</p> <p>1. BEFORE migrating to HYPER-V (optional but helpful)</p> <p>a. Log into the machine as an Administrator (e.g. a domain admin) so that the password is cached to log in later when there is no network access available to validate against the domain. If you are using the local admin account you can skip this. If you did skip this step and you get a message when you log into the guest machine about not being able to contact the domain admin, then you <b>will need</b> a local (non-domain) admin account password to migrate the machine.</p> <p>b. Make a note of network settings, such as TCP/IP settings for the existing network adapter(s) in the VM guest – these adapters will be deleted (or at least hidden) during the migration.</p> <p>c. Uninstall VM Additions from the VM and shut it down properly.</p> <p>2. Install HYPER-V</p> <p>3. Copy guest VHD to the HYPER-V server – or backup the VHD to another location in case you need to get back to the original VHD if something goes wrong.</p> <p>4. Then run HYPER-V Manager (hit Start and being typing HYPER): <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/TNXu56SYzQI/AAAAAAAAAKs/nC3B6UjUk_Q/s1600-h/clip_image001%5B3%5D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" src="http://lh5.ggpht.com/_81s8BMvVcug/TNXu6Uv-7fI/AAAAAAAAAKw/jjXqEdad3Ug/clip_image001_thumb.png?imgmax=800" width="244" height="191" /></a></p> <p>a. If you don’t have Hyper-V Manager, then download it or get the Remote Server Admin Tools for Windows 7: <a href="http://go.microsoft.com/fwlink/?LinkId=131280">http://go.microsoft.com/fwlink/?LinkId=131280</a></p> <p>5. Connect to SFWVIRUTAL01 (Click HYPER-V Manager in the MMC and then Action or Right-Click … Connect to Server and enter the name of the server with Hyper-V.</p> <p>6. Actions à New à Virtual Machine</p> <p>7. Give it the same name as the old machine and then Next: <br /><a href="http://lh4.ggpht.com/_81s8BMvVcug/TNXu7dI9ngI/AAAAAAAAAK0/F8zhSlIwEK8/s1600-h/clip_image002%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image002" border="0" alt="clip_image002" src="http://lh6.ggpht.com/_81s8BMvVcug/TNXu8Oh0e5I/AAAAAAAAAK4/XHhAHjtOh9Q/clip_image002_thumb%5B1%5D.png?imgmax=800" width="422" height="311" /></a></p> <p>8. Set the network connect in the next window: <br /><a href="http://lh4.ggpht.com/_81s8BMvVcug/TNXu8STZNqI/AAAAAAAAAK8/1ME9B0vF-68/s1600-h/clip_image003%5B4%5D.jpg"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image003" border="0" alt="clip_image003" src="http://lh3.ggpht.com/_81s8BMvVcug/TNXu81siSnI/AAAAAAAAALA/LmvLuIuWWFc/clip_image003_thumb%5B1%5D.jpg?imgmax=800" width="357" height="72" /></a></p> <p>9. Then find the EXISTING VHD – do NOT create a new one: <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/TNXu9QeOyFI/AAAAAAAAALE/QQlhUyGVm7I/s1600-h/clip_image004%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image004" border="0" alt="clip_image004" src="http://lh5.ggpht.com/_81s8BMvVcug/TNXu9_IUKNI/AAAAAAAAALI/QHyKjrvDYlk/clip_image004_thumb%5B1%5D.png?imgmax=800" width="356" height="193" /></a></p> <p>10. Click Finish – HYPER-V will do some configuring and give you status…</p> <p>11. Now select the new machine, click START then after it is running click CONNECT: <br /><a href="http://lh3.ggpht.com/_81s8BMvVcug/TNXu-h5fn4I/AAAAAAAAALM/c0DPe4HpVjA/s1600-h/image%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_81s8BMvVcug/TNXu_ZMVmLI/AAAAAAAAALQ/nKxRrIVm43o/image_thumb%5B2%5D.png?imgmax=800" width="460" height="192" /></a></p> <p>12. I had one unfortunate experience where I could not use the mouse in the new machine until I uninstalled the old Virtual Machine Additions. In this particular case, mastery of the keyboard commands in the Virtual Machine Connection window is essential. The following list of keyboard shortcuts will come in handy: <a href="http://blogs.msdn.com/b/virtual_pc_guy/archive/2008/01/14/virtual-machine-connection-key-combinations-with-hyper-v.aspx">http://blogs.msdn.com/b/virtual_pc_guy/archive/2008/01/14/virtual-machine-connection-key-combinations-with-hyper-v.aspx</a></p> <p>13. Next steps are to log in several times to uninstall VM Additions (from Virtual Server 2005 or before) and then install the new HAL and then install Integration services / VM additions (<b>Action -> Insert Integration Services Setup Disk</b>; you may need to then manually run the CD from the guest machine after that) from HYPER-V which installs new hardware… several reboots…</p> <p>a. Log in</p> <p>b. Under some circumstances, you may get a message that Windows needs to be reactivated do to hardware changes. Do NOT do this YET – wait until after the process is complete (after the new HYPER-V integration has been installed): <br /><a href="http://lh4.ggpht.com/_81s8BMvVcug/TNXu_sSkpgI/AAAAAAAAALU/WZA_8KvIDzM/s1600-h/clip_image007%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image007" border="0" alt="clip_image007" src="http://lh3.ggpht.com/_81s8BMvVcug/TNXvAEVtxtI/AAAAAAAAALY/RoRLLc4kPY4/clip_image007_thumb%5B1%5D.png?imgmax=800" width="442" height="132" /></a> <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/TNXvAZG6LqI/AAAAAAAAALc/r4UmAK1avV4/s1600-h/clip_image008%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image008" border="0" alt="clip_image008" src="http://lh6.ggpht.com/_81s8BMvVcug/TNXvBcWjy4I/AAAAAAAAALg/j7mgBM1Hb2s/clip_image008_thumb%5B1%5D.png?imgmax=800" width="440" height="106" /></a></p> <p>c. Uninstall previous Virtual Machine Additions, if applicable (Control Panel, Add/Remove Programs, etc.): <br /><a href="http://lh6.ggpht.com/_81s8BMvVcug/TNXvB3LNuDI/AAAAAAAAALk/SpflLOpi4Wk/s1600-h/clip_image009%5B5%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image009" border="0" alt="clip_image009" src="http://lh4.ggpht.com/_81s8BMvVcug/TNXvCVjpSSI/AAAAAAAAALo/mt9kydwJe_g/clip_image009_thumb%5B2%5D.png?imgmax=800" width="442" height="141" /></a></p> <p>d. Once the machine reboots, log in again and “insert the integration “</p> <p>e. If you get this dreaded message, “<strong>Setup cannot upgrade the HAL in this virtual machine. Hyper-V integration services can be installed only on virtual machines with an ACPI-compatible HAL.</strong>”, then you need to perform extra unsupported steps. <br /><a href="http://lh3.ggpht.com/_81s8BMvVcug/TNXvCkYX8iI/AAAAAAAAALs/WrJSGoObIok/s1600-h/clip_image010%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image010" border="0" alt="clip_image010" src="http://lh4.ggpht.com/_81s8BMvVcug/TNXvDBc6VdI/AAAAAAAAALw/bzH1gaqAAQU/clip_image010_thumb%5B1%5D.png?imgmax=800" width="405" height="139" /></a></p> <p>f. Open Device Manager (e.g. Start, right-click Computer, Manager) and open Computer, then right-click Standard PC. If Uninstall shows up in the drop down menu, click it and then reboot and try installing integration services again. You may not have an Uninstall option, in which case you need to continue with an unsupported workaround: <br /><a href="http://lh6.ggpht.com/_81s8BMvVcug/TNXvDf2bitI/AAAAAAAAAL0/4tCORgaKphE/s1600-h/clip_image011%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image011" border="0" alt="clip_image011" src="http://lh4.ggpht.com/_81s8BMvVcug/TNXvD0ynRhI/AAAAAAAAAL4/kEyokvWZhec/clip_image011_thumb%5B1%5D.png?imgmax=800" width="366" height="158" /></a></p> <p>g. Shutdown and then Turn off the new virtual machine.</p> <p><b>h. </b><b><u>MAKE SURE YOU HAVE A BACKUP OF THE VHD BEFORE CONTINUING!</u></b></p> <p>i. Get access to a Windows XP installation CD e.g. with i386. E.g. mount the ISO with something like VirtualCloneDrive.</p> <p>j. In the host server, mount the VHD as a drive. (To mount a VHD, right click Disk Management and Attach the VHD, do NOT select Read-Only): <a href="http://thelazyadmin.com/blogs/thelazyadmin/archive/2009/01/15/mount-a-vhd-within-windows-7-server-2008-r2.aspx">http://thelazyadmin.com/blogs/thelazyadmin/archive/2009/01/15/mount-a-vhd-within-windows-7-server-2008-r2.aspx</a>)</p> <p>k. Basically, you are going to replace the HAL.DLL on the Guest A’s VHD with the ACPI version of the HAL.DLL on the Windows XP image:</p> <p>i. Open a Command Prompt as Administrator.</p> <p>ii. The following assumes the VHD is mounted as I: and a Windows XP CD is mounted as G: <br />C:\><b>I:</b> <br />I:\><b>cd windows\system32</b> <br />I:\WINDOWS\system32><b>rename HAL.DLL HAL.DLL.BACKUP</b> <br />I:\WINDOWS\system32><b>expand G:\i386\HALACPI.DL_ .\HAL.DLL</b> <br />Microsoft (R) File Expansion Utility Version 5.1.2600.0 <br />Copyright (C) Microsoft Corp 1990-1999. All rights reserved. <br />Expanding g:\i386\halacpi.dl_ to .\hal.dll. <br />g:\i386\halacpi.dl_: 40176 bytes expanded to 81280 bytes, 102% increase. <br />I:\WINDOWS\system32></p> <p>l. Now Detach the VHD (right-click on the DISK (not the drive letter) in Disk Management and select Detach VHD, do <b><u>NOT</u></b> select the option to delete the VHD afterwards).</p> <p>m. Unmount the Windows XP image (optional).</p> <p>n. Start the virtual machine again</p> <p>o. Log in, and skip through any screens asking to activate windows; click yes to attempt to install any new hardware but ignore any messages about not being able to install it. If asked to reboot, say NO: <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/TNXvETaC4xI/AAAAAAAAAL8/roejrv_CK3E/s1600-h/clip_image012%5B5%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image012" border="0" alt="clip_image012" src="http://lh3.ggpht.com/_81s8BMvVcug/TNXvEv2LrHI/AAAAAAAAAMA/Y_mLDe-nGr4/clip_image012_thumb%5B2%5D.png?imgmax=800" width="439" height="158" /></a></p> <p>p. Open Device Manager again and Uninstall the Standard PC. <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/TNXvE0f8fqI/AAAAAAAAAME/Jgm_AotEZA8/s1600-h/clip_image013%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image013" border="0" alt="clip_image013" src="http://lh3.ggpht.com/_81s8BMvVcug/TNXvFUxMzUI/AAAAAAAAAMI/pGHuGmwn_ts/clip_image013_thumb%5B1%5D.png?imgmax=800" width="341" height="158" /></a> <br />If prompted, confirm that you want to remove device Standard PC. Reboot if prompted.</p> <p>14. Let the integration services / VM Additions for HYPER-V install, reboot, log in again, and reboot again as necessary.</p> <p>a. You should receive a message that the HAL needs to be upgraded: <br /><a href="http://lh6.ggpht.com/_81s8BMvVcug/TNXvF0VdBfI/AAAAAAAAAMM/mqkidIBb8fY/s1600-h/clip_image014%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image014" border="0" alt="clip_image014" src="http://lh3.ggpht.com/_81s8BMvVcug/TNXvGbIQpiI/AAAAAAAAAMQ/-tdE5AxH-UY/clip_image014_thumb%5B1%5D.png?imgmax=800" width="352" height="119" /></a></p> <p>b. Click OK to continue. HYPER-V Installation will upgrade the HAL an d then prompt you that a Restart is required. Click to restart.</p> <p>c. Log back in; and if prompted, do not activate windows yet.</p> <p>d. HYPER-V Integration Services installation will automatically continue (if not, insert the setup disk again) and begin installing the Windows Driver Framework, Guest Components, etc. You will need to Restart again to complete the installation.</p> <p>e. Log in again. If Windows wants to re-activate, this is the time to do it.</p> <p>15. The original network adapter(s) have probably been deleted (or at least hidden because the device(s) are not there). You may need to manually configure the new network adapter(s) for any static IP settings etc. Note also that the MAC address is probably different than before in case you are using DHCP reservations. You may want to reset or check firewall settings as well.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-49609161188838221272010-09-23T22:12:00.001-07:002010-09-24T11:15:57.292-07:00Don’t Repeat the FETCH Statement with SQL Server Cursors<p>If you are anything like me, then you are always trying to avoid repeating the same code whenever possible. One thing that has always bugged me is how all of the T-SQL code examples for using cursors in SQL Server use the FETCH statement right after opening the cursor and then repeat the exact same FETCH statement again inside the WHILE loop itself. Using those examples, if you ever need to update the list of columns you are fetching then you have to edit the code in two places. Here I provide you with an alternative -- there is another way!</p> <p>In order to do this, I take a slightly different approach. I create a WHILE loop where the test condition is always true, FETCH, and then I BREAK out of the WHILE loop if the @@FETCH_STATUS was not zero. In the code sample below, see the highlighted sections of the before and after code:</p> <blockquote> <pre class="code"><span style="color: green">-- Two FETCH statements, straight from MSDN...<br /></span><span style="color: blue">DECLARE </span>Employee_Cursor <span style="color: blue">CURSOR FOR<br />SELECT </span>BusinessEntityID<span style="color: gray">, </span>JobTitle<br /><span style="color: blue">FROM </span>AdventureWorks2008R2<span style="color: gray">.</span>HumanResources<span style="color: gray">.</span>Employee<span style="color: gray">;<br /></span><span style="color: blue">OPEN </span>Employee_Cursor<span style="color: gray">;<br /></span><strike><span style="background-color: yellow"><span style="color: blue">FETCH NEXT FROM </span>Employee_Cursor</strike></span><span style="color: gray"><strike>;<br /></strike></span><span style="color: blue">WHILE </span><strike><span style="background-color: yellow"><span style="color: magenta">@@FETCH_STATUS </span><span style="color: gray">= </span>0<br /></span></strike> <span style="color: blue">BEGIN</span><br /> <span style="color: green">-- do something here</span><br /> <span style="color: blue">FETCH NEXT FROM </span>Employee_Cursor<span style="color: gray">;</span><br /> <span style="color: blue">END</span><span style="color: gray">;<br /></span><span style="color: blue">CLOSE </span>Employee_Cursor<span style="color: gray">;<br /></span><span style="color: blue">DEALLOCATE </span>Employee_Cursor<span style="color: gray">;<br /><br /></span><span style="color: green">-- Only one FETCH statement!<br /></span><span style="color: blue">DECLARE </span>Employee_Cursor <span style="color: blue">CURSOR FOR<br />SELECT </span>BusinessEntityID<span style="color: gray">, </span>JobTitle<br /><span style="color: blue">FROM </span>AdventureWorks2008R2<span style="color: gray">.</span>HumanResources<span style="color: gray">.</span>Employee<span style="color: gray">;<br /></span><span style="color: blue">OPEN </span>Employee_Cursor<span style="color: gray">;<br /></span><span style="color: blue">WHILE </span><strong><span style="background-color: yellow"><u>1<span style="color: gray">=</span>1</u></span></strong><br /> <span style="color: blue">BEGIN<br /> FETCH NEXT FROM </span>Employee_Cursor<span style="color: gray">;<br /> </span><strong><span style="background-color: yellow"><u><span style="color: blue">IF </span><span style="color: magenta">@@FETCH_STATUS </span><span style="color: gray">!= </span>0 </u></strong><span style="color: blue"><strong><u>BREAK</u></strong></span></span><br /> <span style="color: green">-- do something here<br /> </span><span style="color: blue">END</span><span style="color: gray">;<br /></span><span style="color: blue">CLOSE </span>Employee_Cursor<span style="color: gray">;<br /></span><span style="color: blue">DEALLOCATE </span>Employee_Cursor<span style="color: gray">;<br /></span></pre><br /></blockquote><br /><br /><p> </p><br /><br /><p>Note that the WHILE condition is “1=1”; this is simply an expression that always evaluates to true. One is always equal to one -- T-SQL does not have a “true” value like C#. You could use another expression, as long it always evaluates to true.</p><br /><br /><p>Just don’t forget to check the FETCH_STATUS and BREAK out of the loop! This code is more succinct than the examples that use the FETCH statement twice (especially if it is a large FETCH statement) and I use it many production systems without any issues. Now you only have to have one FETCH statement when you need to use cursors in your T-SQL code. Enjoy!</p> Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-6778398696660183974.post-35221996492738935332010-03-08T17:07:00.001-08:002010-03-09T06:35:42.179-08:00Missing the Hidden Boot Process in Windows Virtual PC? SHIFT-ESC!<p>Windows Virtual PC sure has some nifty improvements over Virtual PC 2007. Take USB support, for example. Without it, I would have had to get a new scanner at home, since the only drivers available for my scanner are for XP. Now, instead of getting a new scanner, I just have to explain to my spouse the complex process of why when she scans something and saves it to the desktop it’s not really on a desktop she can see, but it’s on another desktop hidden behind the scenes… hmm. Maybe I should have just bought a new scanner. I digress.</p> <p>Now let’s get to the meat of this post. If you are a power user of Virtual PC like I am, you are going to love and hate Windows Virtual PC. First off, there is no longer a Virtual PC “application” per say where you can list your virtual PCs and their current running states and configure them. Instead, this is all integrated into Explorer. Unless it’s broken like it was on my machine… if that is the case then see here: <a title="http://blogs.msdn.com/virtual_pc_guy/archive/2009/07/06/fixing-a-missing-create-virtual-machine-option-windows-virtual-pc.aspx" href="http://blogs.msdn.com/virtual_pc_guy/archive/2009/07/06/fixing-a-missing-create-virtual-machine-option-windows-virtual-pc.aspx">Fixing a missing “Create Virtual Machine” option</a>.</p> <p>Well almost right away I ran into some issues because I was trying to boot a virtual machine from a CD (using an ISO) and I needed to see the boot menu, before Windows started up. I had some fancy new dialog box telling me that the virtual machine was starting, even though clearly it was not:</p> <p><a href="http://lh6.ggpht.com/_81s8BMvVcug/S5WfNch7OoI/AAAAAAAAAIE/kWAj_zJgsh0/s1600-h/image%5B9%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/S5WfOHHXsyI/AAAAAAAAAII/kxETTVHc0Dk/image_thumb%5B5%5D.png?imgmax=800" width="404" height="102" /></a> </p> <p>So I did the logical thing – hit F1 for Help – but I couldn’t find anything useful. I tried to search for documentation for Windows Virtual PC on Microsoft’s web site, but all I could really find was high level marketing mumbo jumbo about great Windows Virtual PC is why I am going to simply LOVE XP Mode. This wasn’t very helpful. I even scoured through the Virtual PC Guy’s blogs and MSDN forum postings to no avail. Then finally I found an obscure post somewhere with the magic bullet!</p> <p><strong>Press SHIFT+ESC to get from the “Starting the virtual machine…” dialog to the REAL virtual machine display behind the scenes.</strong> Now fast forward from a few months ago to this week, when I was tackling a similar problem, and I couldn’t remember the key combination! I rolled up my sleeves and searched again for a complete list of keyboard shortcuts, etc. I even went Googling (er, Binging?) and STILL couldn’t find it. I sat on it for a couple days, hoping it would come to me in a dream, and I scoured the Internet one last time. And finally, once again, I found a hint somewhere in a comment buried deep in an arbitrary blog posting. Woo hoo!</p> <p>This time I decided to dedicate an ENTIRE blog posting to Windows Virtual PC SHIFT-ESC just so I would never forget it again, or least if I did, I would know where to look ;-)</p> <p>This can help you with all kinds of trouble. For example, let’s say you accidentally had a bootable CD in your CD drive and you went to startup XP Mode for the first time, and XP mode failed to setup after a half hour or so and you didn’t know why. You could use SHIFT-ESC to see the boot screen (and maybe even get into the BIOS of the virtual machine) and find out that the new XP Mode virtual machine was booting from your CD and not from Windows!</p> <p>Another use might be that you want to run some Linux utilities on your virtual machine – perhaps CloneZilla, ntfsresize, GPartEd, or any other number of useful goodies out there. Well if Windows thinks your virtual machine is a Windows machine, its going to hang on that dialog box thinking that Windows should be starting any minute now, while the whole time there is some command prompt from Linux awaiting your every input. Or maybe Windows keeps starting and you need to adjust the BIOS on the virtual machine to let you boot from your fancy Linux tools ISO. SHIFT-ESC to the rescue! Just be sure to hit it really fast after the dialog pops up. Then have a finger ready to hit DEL to get into the virtual BIOS.</p> <p>Just for fun I hit SHIFT-ESC (should I use a dash or a plus between those two?) while booting a Windows XP virtual machine:</p> <p><a href="http://lh3.ggpht.com/_81s8BMvVcug/S5WfO5OiRYI/AAAAAAAAAIM/jeTFiRg79cA/s1600-h/image%5B10%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/S5WfPqp6V1I/AAAAAAAAAIQ/yJ_iDgw6vPY/image_thumb%5B6%5D.png?imgmax=800" width="404" height="334" /></a> </p> <p>… and while booting a Windows 7 virtual machine:</p> <p><a href="http://lh3.ggpht.com/_81s8BMvVcug/S5WfQPRpBgI/AAAAAAAAAIU/b-mRcBUmjKw/s1600-h/image%5B14%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/S5WfQ5QogyI/AAAAAAAAAIY/ZyEplXJX05I/image_thumb%5B8%5D.png?imgmax=800" width="404" height="324" /></a>  </p> <p></p> <p></p> <p>Interestingly, although you can also hit SHIFT-ESC when you are shutting down, I just seem to get a black screen on the virtual machine instead of the “Windows is shutting down” message. Also you have to be pretty quick the with the SHIFT-ESC if you want to get into the BIOS.</p> <p>Now why isn’t this documented anywhere? And better yet, what other goodies are buried away in Windows Virtual PC on Windows 7? If anyone from the product team is reading this, PLEASE improve the documentation, and keep us old Virtual PC 2007 power users in mind, not just the new Windows 7 XP Mode target market.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-36589318219108295532010-03-01T16:48:00.001-08:002010-03-01T16:48:08.530-08:00A Poor Man’s Object Search in SQL Server<p>Have you ever wanted to find all of the stored procedures, functions, maybe even views in SQL Server where you reference a particular column? Or maybe you are planning to change a table and you want find all of the T-SQL code in your database that references that table directly? Or maybe you just want to find all of the objects where you placed a comment like “-- TODO” into you code. Over the years this is something has come up so often for me that I have memorized the SQL query to make it happen:</p> <blockquote> <p>select <br />    distinct object_schema_name(id), object_name(id) <br />  from sys.syscomments c <br />  where text like '%SomeText%'</p> </blockquote> <p>Note that there are some pretty important limitations, so if these results are life and death, then you may very well want to use a robust solution instead. You see, the syscomments table breaks up the text of your objects into 8000 character chunks – so if you happen to be so unlucky that your text is broken across that 8000 character boundary and ends up in the two different syscomments records, then you will miss some results. But for a quick and dirty search, this works much of the time.</p> <p>If you need to be 100% sure you found everything, though, there are some alternatives to consider. You could reverse engineer your database into a Visual Studio Database Edition (VSDB) Database Project – that will bring in the full text of every single object into Visual Studio, where not only can you search, but you can search using options like “whole word” or "match case”. Even better, you can use the Schema Explorer and refactor with built in tools that are smart to parse all of your SQL and really just rename that column you want to rename and not any other columns with a similar name – much more precise than search and replace.</p> <p>Another alternative would be to use SQL Server Management Studio’s Generate Scripts command to script out all of the objects in your database and then search the output for your text. Depending on how big your object are, though, you might find yourself doing a lot of scrolling around to try and make a list of the objects that reference your search text.</p> <p>If you are simply looking for dependencies, you could always run some queries on sysdepends. Of course the problem there is that in any real database that wasn’t just created from a script in the perfect order, there may be many missing entries. For example, let’s say that View B depends on Table A. Then you drop and re-create Table A for some odd reason. Unless you drop and re-create View B, View B still works but there is no longer an entry in sysdepends saying that View B depends on View A.</p> <p>What’s your favorite way to search for some text across objects in a database? Leave a comment and let me know!</p> Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6778398696660183974.post-54257793207611881762010-02-27T10:52:00.001-08:002010-02-27T11:02:09.432-08:00SQL Azure Pros and Cons<p>A friend of mine is working at a start-up and they are using Azure to host their website and database. I’ve been discussing ways that they can manage the database development cycle specifically as it relates to SQL Azure.</p> <p>For traditional SQL Server development, my preferred development process leverages Visual Studio Database Edition. We generally only use RTM products for client development, so I have been using Visual Studio 2008 Database Edition with SQL Server 2008 Database Projects for some time now. I am looking forward to seeing what new features are in Visual Studio 2010.</p> <p>Generally, members of my development team have their own development databases where they use whatever tools they prefer – SQL Server Management Studio, Visual Studio, etc., to make schema changes and add or update stored procedures, functions, views, etc. Then they use the Schema Compare functionality to sync those changes back to our project and then to source control, where other members of the team can get to them and we can keep version history, validate the builds before deploying, and either deploy new instances or use schema compare to help create migration scripts for existing databases. These tools are fantastic, and although there is definitely a learning curve for the team, I highly recommend using them.</p> <p>So my friend was asking me what I recommend for their development process for the database against SQL Azure. They are in dev/QA right now and are expecting to start getting real data into the SQL Azure database that they are going want to persist, which means moving from a simple “drop the entire database and then run the big create script” to a managed approach where copies of the deployed database can be migrated to the latest version.</p> <p>In this scenario, here is generally what I would want to do in an ideal world:</p> <ol> <li>Take a backup/snapshot of the SQL Azure database that is deployed. </li> <li>Restore that backup/snapshot to another location – it could be in the cloud or could be local. </li> <li>Run a Schema Compare from the Database Project to the restored snapshot and create a migration script. </li> <li>Test the migration script against the restored snapshot and verify that everything went as expected. </li> <li>Take the web site offline (so there are no more changes to the database) and take another snapshot of the SQL Azure DB just in case. </li> <li>Update the deployed SQL Azure database with the migration script, and take another snapshot post-migration. </li> <li>Bring up the new website (without getting into detail, assume you deploy the new code to Staging, test it, then swap it into Production). </li> </ol> <p>This would all be great, but there are several gaps in the toolset for SQL Azure:</p> <ol> <li>VSDB does not support database projects for SQL Azure (see this <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/e4ffe20f-21b8-4687-b966-e5fdc30599e3/">thread</a>). Apparently, neither does VS 2010 (yet – I hope they are planning this). The workaround here would be to create you DB project for SQL 2008. Then you have to run any scripts you generate through tools / update them to be SQL Azure compliant. The <a href="http://sqlazuremw.codeplex.com/">SQL Azure Migration Wizard</a> can help you do this, but I still consider this all a big hack that is going to take time away from raw development tasks. </li> <li>SQL Azure does not have a mechanism to take a snapshot or a backup. This is a HUGE shortcoming. I wouldn’t consider putting a production database up in SQL Azure without the ability to take snapshots or backups. I wouldn’t be comfortable running a migration script against it if I didn’t have a backup and if I haven’t been able to test that script against the real database. Fortunately, this feature is in the works. You won’t be able to restore a backup to a local machine, though, at least that doesn’t appear to be planned. See this <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/b2a3c161-96b1-4201-ac46-a747e1806be1/">thread</a> on MSDN. Also, the SQL Migration Wizard can help you copy schema and data, but that’s no replacement for a real backup mechanism. </li> </ol> <p>Sure there are ways to get around all this. You can use “Generate Scripts” in SSMS for SQL 2008 R2 from a SQL Azure database, and you can use BCP. But none of that replaces a real backup. You have make sure nothing is changing the database – e.g. what if you get inconsistent data which causes problems on the other end do to changing data, foreign keys, etc.? I’d rather have a real backup. Also, once I have the scripts and data I need to restore them to a local database somehow through this manual process and then sync that back to my project / source control system. Some folks out there have written some great tools to help do this, and even RedGate has some SQL Azure tools in beta – but here is what I would like to see:</p> <ol> <li>Backup/restore support in SQL Azure with the ability to restore a backup to another SQL Azure instance </li> <li>Staging/Production support in SQL Azure, similar to how you do it with Azure web sites. </li> <li>Ability to take a backup (snapshot) from SQL Azure and restore it to a local development environment. </li> <li>A “SQL Azure” mode in a local SQL Server environment that has all of the same rules/restrictions as SQL Azure to simulate SQL Azure locally </li> <li>Full support for a SQL Azure Database Project in Visual Studio 2008 Database Edition as well as the equivalents in VS 2010 (Database Projects AND Data Tier Projects). </li> </ol> <p>I’m sure we’ll see all or most of this eventually – until then we will have lower productivity and we will have to rely on hacks and community / 3rd party tools.</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6778398696660183974.post-59621077976489328922010-02-27T10:16:00.001-08:002010-02-27T10:16:05.915-08:00Big SQL Server Log File and Recovery Mode<p>Occasionally I will be working on a project where we have some database operations that delete/update/insert large amounts of data by design. Every now and then we get caught by surprise when one of our development or QA servers runs low on disk space, and then we discovery that the log files for the databases are HUGE.</p> <p>In most cases it turns out that we had the databases set to FULL RECOVERY MODE. You can find many articles about this on the Internet and on MSDN, but the bottom line is that for our purposes SIMPLE recovery mode is more than adequate and it will greatly reduce the size of these log files. Full recovery would allow us to roll the database back to point in time – generally we don’t need this, especially on our development and QA databases.</p> <p>I have some cool backup/restore scripts and we simply added an ALTER DATABASE statement to end of the restore script to make sure that the database is in SIMPLE recovery mode. Then I got to thinking, why not just create a one-liner that will set all of the user databases on a given server to SIMPLE recovery mode? Well – here it is. Enjoy!</p> <blockquote> <p>EXECUTE sp_msforeachdb 'IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'') exec(''ALTER DATABASE [?] SET RECOVERY SIMPLE'')'</p> </blockquote> <p>In case you are wondering why the ALTER above is in an EXEC, apparently SQL server will parser the ALTER statement and stop on tempdb without even executing the script, so even though tempdb would be excluded in the IF statement, SQL still sees the ALTER code and doesn’t like it. Turning this into a dynamic query avoids the problem.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-6666747186103769052009-11-18T09:55:00.001-08:002009-11-18T09:55:46.617-08:00PDC09 Day 1 - SketchFlow<p>Today I attended a session on a product called SketchFlow, part of Expression Blend 3, which for us Microsoft Gold Partners is part of Expression Studio 3 on MSDN. SketchFlow is a tool that uses WPF and helps you quickly prototype, gather feedback, and document for projects that will use WPF or Silverlight as the UI. Prototyping, in general, helps you do design up front leading to more accurate estimations, share ideas quickly, get feedback quickly, and fail faster when you are going down the wrong path.</p> <p>We recently used a tool from <a href="http://www.balsamiq.com/">Balsamiq</a> to produce mockups for a new project, which was very helpful in crystallizing design and gathering feedback from our client, as well as providing our UI developers with a starting point.</p> <p>I’ve no doubt that SketchFlow is going to be our new prototyping tool – not only does it allow us to do all of the above, but it actually creates an application in WPF.</p> <p>One of the great features of SketchFlow is that you are using real WPF and the prototype is a real application and not just screen shots. There all features built right in to allow folks to provide real feedback directly in the prototype, and ways for you to collect and review that feedback.</p> <p><a href="http://lh5.ggpht.com/_81s8BMvVcug/SwQ1C0QpIuI/AAAAAAAAAHI/g_s7UmPbDkY/s1600-h/image%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_81s8BMvVcug/SwQ1EH0jpHI/AAAAAAAAAHM/YDS_8BCPuRE/image_thumb%5B2%5D.png?imgmax=800" width="444" height="334" /></a> </p> <p>SketchFlow allows to use Sketch styles to give your prototype a “draft,” or work-in-progress, look and feel – this is actually important to get the right feedback from people at a higher level instead of letting people get bogged down in details like button size and color, etc.</p> <p><a href="http://lh5.ggpht.com/_81s8BMvVcug/SwQ1E3YRBII/AAAAAAAAAHQ/Woztp4BwDc0/s1600-h/image%5B8%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/SwQ1FkWqUOI/AAAAAAAAAHU/4sSCh5HLBAg/image_thumb%5B4%5D.png?imgmax=800" width="561" height="294" /></a> </p> <p>In SketchFlow you create a high level map of the forms/windows that you are going to use in your application and then connect them together to create a flow. Then you can select any of those to mock up a particular form.</p> <p><a href="http://lh5.ggpht.com/_81s8BMvVcug/SwQ1G7LpU5I/AAAAAAAAAHY/lzbn7Vfl2QU/s1600-h/image%5B12%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/SwQ1IU5UJoI/AAAAAAAAAHc/ReknLF8C75A/image_thumb%5B6%5D.png?imgmax=800" width="623" height="468" /></a> </p> <p>You can also quickly add sample data to your application by defining data and filling it in right from within the application, or by connecting it to a database. You can drag and drop to create lists using your sample data, and manipulate the controls for how the data is displayed right in SketchFlow. You can put C# code behind various actions and events in the prototype as well, to simulate various aspects of the application you are prototyping, including storing global state information to keep track of from one screen to another.</p> <p>Once again SketchFlow uses WPF, so you can easily use it as the basis for a WPF application, and with some limitations you can use it for Silverlight as well. Although you can certainly use it to prototype other application types such as web sites, it will not generate ASP.NET etc. – the output is WPF or Silverlight only.</p> <p>SketchFlow also has a cool feature to allow you to export your prototype to Microsoft Word, creating a table of contents and including all of the screen shots – this will certainly help create documentation much more quickly (we copied and pasted about 40 images in a recent specification document, this will automate that task in future specs for us in 1 click!).</p> <p>After attending the SketchFlow session yesterday I immediately installed Expression Studio 3, and I am getting ready to use this with our designer for prototyping the front end of new project I have coming up.</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6778398696660183974.post-54860021023618817512009-11-17T17:03:00.001-08:002009-11-17T17:03:07.077-08:00PDC09 Day 1 – Data-tier Application projects in VS 2010<p>I made it back to PDC this year – although I wasn’t able to get in early to attend any preconference workshops. After getting up a 4am and catching a 6am flight to LAX, it was the LA traffic that got me in 45 minutes after the keynote started. I hear I didn’t miss too much, mostly just Ray Ozzie talking about cloud computing.</p> <p>The first session that I attended was about a new project type in Visual Studio 2010 called a Data-tier Application. It doesn’t replace the existing Database Projects (e.g. with VS.NET 2008 Database Edition + GDR2) – coming out of the session, my understanding is that it is an alternative project type with some really cool features, but intended for “smaller” projects (“departmental” vs. “LOB/Mission Critical”, or “under 1000 objects”). I’m not quite sure what to make of that – based on some commentary I believe that it has a couple limitations but also the intended audience is different.</p> <p>First off, here are some of the really cool features discussed. I imagine that some of these features apply to all SQL edited in VS.NET 2010 and not just this new project type:</p> <ul> <li>Intellisense when editing SQL in VS.NET</li> <li>Full integration with the VS.NET 2010 debugger – e.g. when debugging a stored procedure, you can add watches and view the call stack. I hope you can do more – look at a temp table for example, or see what the results of a query might be, but that wasn’t specifically discussed. In any case this is a big leap forward.</li> <li>“Compile” errors and warnings, for example, warning if you use “select *” in a view or stored procedure.</li> </ul> <p>The following cool new features are definitely specific to the new project type only:</p> <ul> <li>Deployment from either VS.NET or SSMS using the “dacpac” output</li> <li>Migration to new schema during deployment without writing any migrations scripts – it looks at the old schema, the new schema, and “makes it so”! How cool is that?!</li> <li>When you deploy the dacpac using something called a Control Point, you can get a really cool dashboard in SQL Server that gives you usage statistics and other interesting information about your database</li> </ul> <p>Sounds too good to be true?</p> <p>Here are the “gotchas,” as far as I can tell, for the Data-tier Application project:</p> <ul> <li>It only works with SQL 2008 R2, which is still in CTP (not released officially yet)</li> <li>It is intended for “departmental” databases</li> <ul> <li>“up to 1000 objects” was mentioned</li> <li>only a subset of T-SQL is supported</li> <li>limitations to the ability to perform automatic migrations when changes are made</li> <li>other? not clear</li> </ul> <li>It is not yet released… I’m sure it’s leveraging the years of experience leading up to the GDR2 of the database edition, but that was a long and painful road – anyone who’s run into a bug in VSDB that blocked a deployment can relate to this fear</li> </ul> <p>Bottom line: it sounds very promising with some great productivity features and general advancements in the managing database development, but when it comes to the new project types I would proceed with caution and have a backup plan (such as switching to the tried and true database projects).</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-67099432945785868432009-11-04T20:38:00.001-08:002009-11-04T20:38:40.528-08:00Accessing a remote network via VPN on a Virtual Machine<p>I have Windows 7 64-bit which means I can't use the Cisco VPN Client (hey Cisco, if you are listening, please give us a 64-bit Windows 7 compatible version of your IPSec client!), even though I have several clients who's hardware requires it -- so I bought <a href="http://www.ncp-e.com/">NCP’s Universal IPSec VPN Client</a> as a replacement and it has worked really well. That is, until I needed to connect to a new client’s network using SonicWall. I couldn’t get NCP to work with the SonicWall, so I installed a Virtual Machine (I still use Virtual PC 2007 SP1 since Windows Virtual PC is still in beta and causes my HOST to crash with the BSOD) with Windows XP on my Windows 7 laptop. I installed the SonicWall client on there, and I am able to connect to their network from the VPC.</p> <p>However, accessing my client’s network only from the virtual machine is inconvenient -- I can't use SQL Management Studio or VS.NET Database Edition to connect directly to a database on my client's network because all those tools are installed on my host machine, and I really don’t feel like reinstalling them all onto a bloated virtual guest.</p> <p>I heard from a colleague about the possibility of routing traffic through the VPC guest machine from the host, but I had trouble finding a specific guide to doing this. The good news is that I finally got it working, and I am about to tell you how!</p> <p>There are of course security considerations (you are essentially creating a path of connectivity between two networks, so malicious software may be able to spread, etc.), so please use your best judgment as to when and how to make use of this.</p> <p>Here's what I did:</p> <p>1. Install a Loopback Adapter on the Windows 7 host OS (Add Hardware or run HDWWIZ.EXE, select “manual”, “network interface”, “Microsoft Loopback Adapter”...)</p> <p>2. Configure the loopback adapter to use a static IP 192.168.0.2 / 255.255.255.0 (leave everything else blank -- no gateway etc.)</p> <p>3. With the XP guest OS shutdown, use VirtualPC to change the settings so that it has two Network Adapters: the Loopback Adapter that you just added and Shared Networking (or whatever other NIC you want to use for the XP VPC to connect to the Internet).</p> <p>4. Boot up the XP guest OS. I'm assuming you've already installed the VPN software (SonicWall in my case). If not, install it and make sure it can connect.</p> <p>5. Change the Loopback adapter on the XP VPC to a static IP 192.168.0.1 / 255.255.255.0 (leave everything else blank -- no gateway etc.). You may need to turn off the firewall on this NIC. Ping the host (192.168.0.2) from here, and vice versa, to make sure everything is working.</p> <p>6. In Network Connections on the XP VPC, open Properties for the VPN's NIC and turn on Internet Connection Sharing. Set the "Home Networking Connection" the Loopback Adapter. This will force its IP to 192.168.0.1 which is why I chose 192.168.0.2 for the host machine. Use Settings to select the services that should pass through (Remote Desktop is on by default).</p> <p>7. On the host (Windows 7 in my case), you need to add routes for the specific IP addresses in the VPN network that you want to access. I needed to get a machine at 192.168.8.88, which is the SQL Server on the network I am VPN’ing into. I created AddRoute.bat with the command "route add 192.168.8.88 MASK 255.255.255.255 192.168.0.1" and put a shortcut to that in my Quick Launch. Run that command (as an administrator).</p> <p>Go to the XP virtual machine and connect to the VPN. Now go back to the Windows 7 host OS and you should be able to Ping the destination machine, in my case "ping 192.168.8.88". If this works, celebrate! Now you'll be able to Remote Desktop, connect to SQL Server, use File Sharing, etc.</p> <p>There are some pros and cons to using this method. On the downside, you don’t have DNS and you do have to route to each IP address explicitly. On the plus side, you can get to a couple remote machines on one VPN and you could even VPN into another network (like your corporate headquarters) from the host OS at the same time.</p> <p>One other thing to note -- before I tried the ICS method, I had enabled IP forwarding in the XP VPC by setting the IPEnableRouter registry setting (see <a href="http://technet.microsoft.com/en-us/library/cc962461.aspx">http://technet.microsoft.com/en-us/library/cc962461.aspx</a>). I was able to verify with Network Monitor that the requests were indeed re-broadcast to the VPN interface, but it was ignoring them - presumably because they came from another IP address. I didn't turn that setting back off, so I can't confirm that isn't required for all this to work.</p> <p>Another note – I tried this with the Cisco VPN client on the guest OS and it did not work. I’m not sure why I could get it to work with SonicWall and not Cisco, so your mileage may vary. Please do comment if you have anything to add on this topic.</p> <p>Originally I did all this with Vista 64-bit as my host. When I upgraded to Windows 7, it deleted my Loopback Adapter and I had to re-add it, but all the settings came back when I did so.</p> <p>Finally, there may be better solutions out there. I read that with Windows Virtual PC you can, say, run the Cisco VPN Client from the XP virtual machine as a program from the host OS and use it to connect the host OS to a VPN. That’s a pretty cool workaround to a lame problem (Hey Cisco, how about adding 64-bit support to the IPSec VPN client?! Sure, I’d rather use SSL VPN, but I don’t have control over what my clients’ IT departments support!). On that note, I’ve gotten reports that the free <a href="http://www.shrew.net/">Shrew Soft VPN Client</a> works well under Vista and Windows 7 64-bit but I haven’t personally tried it yet. At one point I had a solution using vpnc on cygwin working on Vista, but it stopped working and I never got it going again.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-56378874375925631142009-10-30T11:49:00.000-07:002009-10-30T12:34:38.116-07:00Dependency Analysis of .NET and SQL Server ApplicationsRecently I starting making some small updates to a very cool dependency analysis application that I designed last year. The basic premise is to be able to trace dependencies from the database up through the user interface or vice versa in order to answer questions such as, if we change table XYZ, what stored procedures and ASP.NET forms will be affected?<br /><br />The application is actually very good at answering those questions. Field level analysis was more difficult to achieve, so left that out, but at the object level (table/view/proc/class/form) it works pretty well.<br /><br />I'll describe here, at a high level, the approach I took for each type of data. Let me know if you are interested in code samples for specific implementations!<br /><br />To analyze .NET, I analyze the actual assemblies using disassembly and reflection. This was tricky but very do-able. Essentially I grab a copy of all the assemblies used by an application and load them "for reflection only" into a separate AppDomain. I gather some dependencies by looking at the types references in member signatures (using reflection) as well as referenced in code (using the disassembler). I used Lutz Roeder's .NET Reflector for the disassembler (<a href="http://blog.lutzroeder.com/">http://blog.lutzroeder.com/</a>), which RedGate now has available for free (<a href="http://reflector.red-gate.com/">http://reflector.red-gate.com/</a>).<br /><br />Also, on the .NET side, I parse configuration files for connection strings and I have "framework" specific code to look for particular attributes or other means that various projects I am analyzing have used to map .NET code to database objects.<br /><br />To analyze SQL Server database schema, we used the RedGate tools and parsed the xml output. To analyze SSIS packages, we used the SQL Server 2005 Metadata Toolkit (which is actually on codeplex at <a href="http://code.msdn.microsoft.com/SqlServerMetadata/">http://code.msdn.microsoft.com/SqlServerMetadata/</a>, contrary to what the download link at Microsoft <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=182bd330-0189-450c-a2fe-df5c132d9da9&displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyID=182bd330-0189-450c-a2fe-df5c132d9da9&displaylang=en</a> says.).<br /><br />To analyze stored procedure, functions, and views T-SQL code for dependencies, we used <a href="http://www.sqlparser.com/">http://www.sqlparser.com/</a>.<br /><br />The basic idea is to record the objects I find and the relationships between those objects, and then provide ways to explore the data. I came up with a system to record two dimensions, or types, of relationships -- "contains" (e.g., a database contains a table) and "uses" (e.g. a stored procedure "uses" a table, or a .NET class "uses" a stored procedure, etc.).Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-53242145847440189222009-10-30T11:10:00.000-07:002009-10-30T11:33:55.354-07:00Normalizing the Day of the Week in SQL ServerThere is a very interesting in article in SQL Server Magazine about normalizing the day of the week -- this is very relevant if you need to write code that performs business logic based on, say, Fridays, and you want that code to work properly no matter where in the world the SQL Server is installed:<br /><br />Normalizing the First Day of the Week, September 2009<br /><a href="http://www.sqlmag.com/Articles/ArticleID/102470/102470.html?Ad=1">http://www.sqlmag.com/Articles/ArticleID/102470/102470.html?Ad=1</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-31075987561282804922009-10-23T11:07:00.001-07:002009-10-23T11:07:44.880-07:00Capturing StandardOutput AND StandardError from a Process<p>I recently revived a program I had written to do an automated build and deploy of a solution containing a Visual Studio Database Edition project as well as a client UI in WPF. The build program runs a bunch of command-line programs, capturing all the output along the way and keeping track of errors, etc. When I was going through the code, I noticed a bug. I am using ProcessStartInfo and Process and I thought I was capturing both StandardOutput and StandardError but I wasn’t. My code looked like this:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:57F11A72-B0E5-49c7-9094-E3A15BD5B5E6:a2fbca47-d402-4ef4-85fc-297b2614237c" class="wlWriterEditableSmartContent"><pre style="background-color:White;overflow: auto;"><span style="color: #000000;"> psi.UseShellExecute </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">false</span><span style="color: #000000;">;<br /> psi.RedirectStandardOutput </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">true</span><span style="color: #000000;">;<br /> psi.RedirectStandardError </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">true</span><span style="color: #000000;">;<br /> Process p </span><span style="color: #000000;">=</span><span style="color: #000000;"> Process.Start(psi);<br /> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> output </span><span style="color: #000000;">=</span><span style="color: #000000;"> p.StandardOutput.ReadToEnd();<br /> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> error </span><span style="color: #000000;">=</span><span style="color: #000000;"> p.StandardOutput.ReadToEnd();<br /> p.WaitForExit();</span></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p>So I “fixed” the code so now it looked like this:</p><br /><br /><div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:57F11A72-B0E5-49c7-9094-E3A15BD5B5E6:c9bcbf91-018f-41f5-adb1-7e01bfd6432c" class="wlWriterEditableSmartContent"><pre style="background-color:#FFFFFF;white-space:-moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; white-space: pre-wrap; word-wrap: break-word;overflow: auto;"><span style="color: #000000;"> ...<br /> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> error </span><span style="color: #000000;">=</span><span style="color: #000000;"> p.StandardError.ReadToEnd();<br /> ...</span></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p>But when I ran it, one of the programs seemed to hang forever. I was able to determine that the program did in fact finish, and was actually my code that hung forever. It turns out that if you use ReadToEnd() on both StandardOutput and StandardError, you can reach a blocking situation that hangs your code. This is even documented on MSDN <a href="http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardoutput.aspx">here</a>:</p><br /><br /><blockquote><br /> <p><em>“A deadlock condition results if the parent process calls p.StandardOutput.ReadToEnd followed by p.StandardError.ReadToEnd and the child process writes enough text to fill its error stream. The parent process would wait indefinitely for the child process to close its StandardOutput stream. The child process would wait indefinitely for the parent to read from the full </em><a href="http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standarderror.aspx"><em>StandardError</em></a><em> stream.”</em></p><br /></blockquote><br /><br /><p>This is exactly what was happening in my code. I was up until 4am updating the Build program, and at this point I wanted a quick solution. The MSDN documentation hinted at the solution (asynchronous threads) but did not provide code outright. I did some searching on the Internet and found some solutions out there, but they were all much heavier than I wanted. I wanted something simple, preferably just a few lines of code and very modular, so after I couple hours of sleep and some coffee, I came up with a solution. First, I created a very simple class, which could be called from a new Thread, to capture the output of a stream:</p><br /><br /><div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:57F11A72-B0E5-49c7-9094-E3A15BD5B5E6:15fa3b95-0108-4d2e-bcf2-6afa7daf04a3" class="wlWriterEditableSmartContent"><pre style="background-color:#FFFFFF;white-space:-moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; white-space: pre-wrap; word-wrap: break-word;overflow: auto;"><span style="color: #000000;"> </span><span style="color: #0000FF;">class</span><span style="color: #000000;"> MyStreamReader<br /> {<br /> StreamReader _sr </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">null</span><span style="color: #000000;">;<br /> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> _text </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">null</span><span style="color: #000000;">;<br /> </span><span style="color: #0000FF;">public</span><span style="color: #000000;"> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> Text { </span><span style="color: #0000FF;">get</span><span style="color: #000000;"> { </span><span style="color: #0000FF;">return</span><span style="color: #000000;"> _text; } }<br /><br /> </span><span style="color: #0000FF;">public</span><span style="color: #000000;"> MyStreamReader(StreamReader sr)<br /> {<br /> _sr </span><span style="color: #000000;">=</span><span style="color: #000000;"> sr;<br /> }<br /><br /> </span><span style="color: #0000FF;">public</span><span style="color: #000000;"> </span><span style="color: #0000FF;">void</span><span style="color: #000000;"> Go()<br /> {<br /> _text </span><span style="color: #000000;">=</span><span style="color: #000000;"> _sr.ReadToEnd();<br /> }<br /> }</span></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p>Then I used that class to capture the standard error and standard output from my newly launched process, as follows:</p><br /><br /><div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:57F11A72-B0E5-49c7-9094-E3A15BD5B5E6:b52cc20a-d3bd-4853-bae6-699470f1c758" class="wlWriterEditableSmartContent"><pre style="background-color:#FFFFFF;white-space:-moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; white-space: pre-wrap; word-wrap: break-word;overflow: auto;"><span style="color: #000000;"> ProcessStartInfo psi </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">new</span><span style="color: #000000;"> ProcessStartInfo(commandToRun);<br /> psi.UseShellExecute </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">false</span><span style="color: #000000;">;<br /> psi.RedirectStandardOutput </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">true</span><span style="color: #000000;">;<br /> psi.RedirectStandardError </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">true</span><span style="color: #000000;">;<br /> Process p </span><span style="color: #000000;">=</span><span style="color: #000000;"> Process.Start(psi);<br /><br /> </span><span style="color: #008000;">//</span><span style="color: #008000;"> Create my objects to capture output asynchronously</span><span style="color: #008000;"><br /></span><span style="color: #000000;"> MyStreamReader msr_stdout </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">new</span><span style="color: #000000;"> MyStreamReader(p.StandardOutput);<br /> MyStreamReader msr_stderr </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">new</span><span style="color: #000000;"> MyStreamReader(p.StandardError);<br /><br /> </span><span style="color: #008000;">//</span><span style="color: #008000;"> Create the thread objects to run the code asynchronously</span><span style="color: #008000;"><br /></span><span style="color: #000000;"> Thread t_stdout </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">new</span><span style="color: #000000;"> Thread(msr_stdout.Go);<br /> Thread t_stderr </span><span style="color: #000000;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">new</span><span style="color: #000000;"> Thread(msr_stderr.Go);<br /><br /> </span><span style="color: #008000;">//</span><span style="color: #008000;"> Launch both threads</span><span style="color: #008000;"><br /></span><span style="color: #000000;"> t_stdout.Start();<br /> t_stderr.Start();<br /><br /> </span><span style="color: #008000;">//</span><span style="color: #008000;"> Wait for both output and error streams to finish</span><span style="color: #008000;"><br /></span><span style="color: #000000;"> t_stdout.Join();<br /> t_stderr.Join();<br /><br /> p.WaitForExit();<br /><br /> </span><span style="color: #008000;">//</span><span style="color: #008000;"> retrieve the output and error text</span><span style="color: #008000;"><br /></span><span style="color: #000000;"> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> output </span><span style="color: #000000;">=</span><span style="color: #000000;"> msr_stdout.Text;<br /> </span><span style="color: #0000FF;">string</span><span style="color: #000000;"> error </span><span style="color: #000000;">=</span><span style="color: #000000;"> msr_stderr.Text;<br /><br /> Console.WriteLine(output);<br /> Console.WriteLine(error);</span></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p>I tested out my new code now everything works properly – now I just need to thank the developer who checked in the post deployment scripts that caused the errors, otherwise I might not have caught this problem to begin with!</p> Unknownnoreply@blogger.com8tag:blogger.com,1999:blog-6778398696660183974.post-74373419322668086372009-10-16T01:23:00.001-07:002009-10-16T01:25:33.272-07:00Opening a Visual Studio Solution from a Source Control Repository<p>Whether you are using a new machine that you have just setup, or a new developer is joining the project, the situation often comes up where a Visual Studio solution or project is already in source control and now you want to open it in Visual Studio and have all the source control bindings work properly.</p> <p>This seems like such a simple task, but over the years I have seen people lose hours of productivity running into problems getting the bindings to work properly. Over the course of those years, I have found the following steps to work really well. I usually use SourceGear’s Vault product, but these general steps apply to just about any source control system that integrates with Visual Studio, including Visual SourceSafe, Team Foundation Server, and Ankh SVN.</p> <p>You need to get the bindings to source control setup for a solution on your local development machine once, and then after that you can easily just click on the SLN file or open the solution from Recent Projects in Visual Studio, and the bindings will work properly (even using the correct source control plug-in if you use multiple plug-ins). </p> <p>The following are the steps for the one-time initialization process – or re-initialization if your bindings have somehow gone awry. These steps assume that you do not have the source control program (if it has a standalone client) oepn or Visual Studio open when you start the steps. </p> <ol> <li>Open your source control client outside of Visual Studio</li> <li> <p>Set the working folder, if it is not already set, at solution level or higher, make sure there are no overrides lower in the folder tree</p> <ol> <li>Especially for Vista or Win7, make sure the working folder doesn't require admin rights – e.g. in C:\Users and not C:\ etc. </li> </ol> </li> <li>Get Latest Version on Solution, in Overwrite mode (this might be called “check out” in SVN, but we are just retrieving here, not locking).</li> <li>Open Visual Studio </li> <li>Tools/Options - Choose source control client to be the appropriate source control client, e.g.: <br /><a href="http://lh4.ggpht.com/_81s8BMvVcug/Stgt-dpqz_I/AAAAAAAAAGk/RpN0UAxyx3A/s1600-h/image%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/Stgt_IdQIFI/AAAAAAAAAGo/X1mj9xo3lOE/image_thumb%5B2%5D.png?imgmax=800" width="587" height="353" /></a> </li> <li>Open <MySolution>.sln from Source Control using File -> Source Control -> Open from Source Control (note that the exact command name varies by source control plug-in, but you get the idea) </li> <li>Verify that entire solution loaded correctly </li> <li>Close Visual Studio </li> <li>Close your source control client, if you had one open </li> <li>For now on, open solution by opening <MySolution>.sln on your hard drive. (then it won't matter if you set your default source control provider to something else, because the <MySolution>.sln is bound to the one you chose above)</li> </ol> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-64169351333188921332009-10-16T01:12:00.001-07:002009-10-16T01:12:28.630-07:00VSDB: Schema Compare and “Update Action” Override Persistence<p>On my team, we frequently use the Schema Compare tools to push local database changes into the project (and then into source control). In the process, we often override the Update Action to Skip for items which correspond to changes someone else has made to the project since we last did a deploy.</p> <p>For example, let’s say Bob does a deploy and then changes his local database. In the meantime, Jane adds a new stored procedure to the project. Now, when Bob does a Schema Compare, he will not only see his changes, but the Schema Compare will suggest Dropping the new stored procedure that Jane created since it does not exist in Bob’s database. Bob switches his view to Non Skip objects and notices this, and changes it from Drop to Skip. Well, the next time Bob opens that same Schema Compare, this same stored procedure will be skipped again – but Bob may want to get a “fresh” schema compare and see all differences (and thus “Non-Skip” items). Bob could create a new Schema Compare, but then he would have to carefully select all the Options again….</p> <p>When you open a Schema Compare session and you choose to override an "Update Action" – e.g., SKIP one of the proposed updates, it actually remembers that overridden setting for that object even when you refresh, or save and re-open, the Schema Compare. This could actually be really useful at times but it can also cause chaos and misery if you don't know about it or have forgotten that you are overriding something to Skip that you actually want to update now.</p> <p><a href="http://lh3.ggpht.com/_81s8BMvVcug/Stgq4eEZKhI/AAAAAAAAAGM/69ZwxQsq4PY/s1600-h/image%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_81s8BMvVcug/Stgq5Rau50I/AAAAAAAAAGQ/QLzs6u1y5D0/image_thumb%5B1%5D.png?imgmax=800" width="505" height="301" /></a> </p> <p>These Update Action overrides are persisted in the <strong>.scmp</strong> file. If you want to clear them all, you close the schema compare and then open the .scmp file in a text editor (such as the Visual Studio XML Editor): </p> <p><a href="http://lh5.ggpht.com/_81s8BMvVcug/Stgq5rhOdFI/AAAAAAAAAGU/o0IcCcIdwMY/s1600-h/image%5B7%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/Stgq585-tHI/AAAAAAAAAGY/DARzPqeee_E/image_thumb%5B3%5D.png?imgmax=800" width="356" height="116" /></a> </p> <p>Then delete all the nodes between the <ExcludedSourceElements> and </ExcludedSourceElements> as well between <ExcludedTargetElements> and </ExcludedTargetElements> (note that below I collapsed the SourceModelProvider, TargetModelProvider, and SchemaCompareSettingsService nodes for brevity): </p> <p><a href="http://lh3.ggpht.com/_81s8BMvVcug/Stgq6FoenuI/AAAAAAAAAGc/z3tYHP6VGAM/s1600-h/image%5B11%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/Stgq601xnQI/AAAAAAAAAGg/xHiJVSeWucA/image_thumb%5B5%5D.png?imgmax=800" width="487" height="251" /></a> </p> <p>Save this version of your schema compare and check it into source control. Then in the future if you want clear your overrides you can simply do an Undo Checkout from source control, or choose not to Save your Schema Compare when you close it.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-12058200840086515752009-10-16T00:52:00.001-07:002009-10-16T00:52:36.346-07:00VSDB: Tracking down Deploy errors<p>I’m working on a project that has thousands of lines of post-deployment scripts. Once I have done a successful build, I still sometimes get errors in the deploy, usually indicating a problem in one of my post-deployment scripts. Unfortunately, Visual Studio Database Edition rarely gives me enough information to easily identify the location or source of the error. </p> <p>I believe the problem is two-fold. First off, any output from PRINT statements that may help you locate the error might be useless – I believe that the PRINT output is buffered and that when the error occurs, the buffer might not be flushed. So there may still be a bunch of PRINT output between what you see and when the error really occurs (hey, if anyone from the VSDB team is reading this, please fix this in VS.NET 2010!). The second problem is that VSDB does not locate the error for you and tell you which file and what line the error is on. That’s probably because it concatenates all of the post deployment scripts together with the database creation script into one huge SQLCMD file.</p> <p>So, what's a developer to do? </p> <p>Once of the fastest ways to figure out the issue is to leverage another tool that will show you ALL of the PRINT output as well as tell you what line of code the error occurs on: SQL Server Management Studio. </p> <p>When you do a deploy, VSDB creates a big script that creates the entire database – this script also includes all of the contents of the post-deployment scripts. You want to run this script in SSMS to find out where the error is. Then, find that same piece of code in the project (probably in the post-deployment scripts section). Hopefully you will quickly recognize it once you see it. </p> <p>Here's what to do: </p> <ol> <li> <p>Locate the database project's folder (your working folder on your PC). If you're not sure you can find it here: <br /><a href="http://lh3.ggpht.com/_81s8BMvVcug/StgmMt8s4yI/AAAAAAAAAFk/lwwtHAlmMe0/s1600-h/image%5B2%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_81s8BMvVcug/StgmNLuWoSI/AAAAAAAAAFo/b_LEh-2o8Cw/image_thumb.png?imgmax=800" width="340" height="282" /></a> </p> </li> <li> <p>Navigate to the "sql" folder: <br /><a href="http://lh3.ggpht.com/_81s8BMvVcug/StgmNc7RYPI/AAAAAAAAAFs/2I15tQzeuMQ/s1600-h/image%5B5%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/StgmNor3TNI/AAAAAAAAAFw/uaKzUe7cNAU/image_thumb%5B1%5D.png?imgmax=800" width="166" height="120" /></a> </p> </li> <li> <p>Then navigate to the folder corresponding to the Build Configuration you used, e.g. "debug": <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/StgmOOE0AAI/AAAAAAAAAF0/9P-UMhp446Q/s1600-h/image%5B21%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_81s8BMvVcug/StgmOQ-jY1I/AAAAAAAAAF4/HiFSuE3tn8Q/image_thumb%5B9%5D.png?imgmax=800" width="213" height="96" /></a> </p> </li> <li> <p>Find the <strong>.sql</strong> file that matches the name of your deployment database (you specified this in your database project's properties' Deploy tab at some point, otherwise it might default to the name of the project): <br /> <a href="http://lh5.ggpht.com/_81s8BMvVcug/StgmOrOuy4I/AAAAAAAAAF8/qx9EO1Gq_n0/s1600-h/image%5B18%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_81s8BMvVcug/StgmPB9Cp9I/AAAAAAAAAGA/DRXsfD3lr-A/image_thumb%5B8%5D.png?imgmax=800" width="361" height="86" /></a> </p> </li> <li> <p>Open that file in SQL Server Management Studio. </p> </li> <li> <p>Note that this .SQL file is a "SQLCMD" file, using special syntax with commands that start with ":" to set variables etc. Don't worry about this too much, but once the script is open in SSMS, be sure to toggle on SQLCMD in the Query menu: <br /><a href="http://lh3.ggpht.com/_81s8BMvVcug/StgmPkC8TlI/AAAAAAAAAGE/0LJlxXmZA2E/s1600-h/image%5B25%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/StgmQ2dSfKI/AAAAAAAAAGI/OmX7rZzlo-A/image_thumb%5B11%5D.png?imgmax=800" width="503" height="479" /></a> </p> </li> <li> <p>Make sure you are connected to the correct SQL Server\Instance before you run the script. It will probably drop and re-create the database you had specified, just like the Deploy command would, so keep that in mind. <em>Note that you can tweak a couple things if you want – changing the DatabaseName variable for example, and removing the specification of the MDB/LDF file names from the database creation.</em></p> </li> <li> <p>You can now run the script (F5). </p> </li> <li>Now hopefully you will get the same error message again, but this time when you double-click on the error message you will get to the exact script line that is causing the error, so that you can correct it in the project. Also, you will probably get all of the PRINT output leading up to that, which may help.</li> </ol> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6778398696660183974.post-19749006759355736282009-10-16T00:36:00.001-07:002009-10-16T00:36:09.639-07:00VSDB: Deployments<p>Over the course of working on a couple VSDB projects with multiple developers, I have found that productivity loss and confusion can result from developers’ settings producing unexpected results during a deploy – for example, having some changes to the database project in source control not get applied to the developer’s database; or the developer deploying to a different database than expected.</p> <p>I have found that the following settings provide consistent results.</p> <p>For the Database Project, edit your settings (right click the Database Project and select Properties): </p> <p><a href="http://lh4.ggpht.com/_81s8BMvVcug/StgiYDIjldI/AAAAAAAAAFU/gXDJAlWtnfY/s1600-h/image%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_81s8BMvVcug/StgiY_m6R1I/AAAAAAAAAFY/u_obniN_lVQ/image_thumb%5B1%5D.png?imgmax=800" width="808" height="438" /></a> </p> <p>Now make sure you UNCHECK "Block incremental deployment If data loss might occur" (shown checked here) </p> <p>CHECK "Always re-create database" and of course "Back up database before deployment“.</p> <p><a href="http://lh4.ggpht.com/_81s8BMvVcug/StgiZXJWDWI/AAAAAAAAAFc/tIgtYKR9-KI/s1600-h/image%5B9%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_81s8BMvVcug/StgiaALqk-I/AAAAAAAAAFg/Vtc9qEGyZ54/image_thumb%5B5%5D.png?imgmax=800" width="510" height="426" /></a> </p> <p></p> <p></p> <p>These settings will ensure that your local development database is always exactly what is checked into source control – nothing less and nothing more. That should help avoid issues where one developer inadvertently undoes the changes of another developer, etc.</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6778398696660183974.post-90041448576877841852009-10-16T00:13:00.001-07:002009-10-16T00:19:56.864-07:00Using Visual Studio Database Edition (VSDB)<p>Database Edition is designed so that each team member works with their own isolated database. Some folks will also be making changes to the database, and others may not be.</p> <p>If you are planning to make changes to the database, here are the steps I recommend:   </p> <ol> <li><strong>Check with other team members</strong> to see if you might be changing some of the same objects or not. Coordinate so that you do not overwrite each other's changes.   </li> <li><strong>Get latest version</strong> of at least the database project files from source control.   </li> <li><strong>Update your database</strong> from the latest database project files. There are a couple ways to handle this. Let's assume you do NOT have any changes or data in your database that you need to keep. Simply do a <strong>DEPLOY</strong> of the database project to get the latest version. (If you do have changes you need to keep, then skip to Step 5 and be VERY careful about what you sync up.) If you are not sure, you can run a schema compare to see the differences. </li> <li><strong>Make your changes</strong>.   </li> <li> <p><strong>Sync your changes back to the project</strong>.   </p> <ol> <li> <p>Run a <strong>Schema Compare</strong></p> <ol> <li><strong>Source: your isolated DEV database</strong></li> <li><strong>Target: Project</strong></li> <li><strong>Options: see my previous email and sure the correct Object Types are being ignored</strong></li> </ol> </li> <li> <p>Carefully <strong>review the Schema Compare output</strong></p> <ol> <li>Filter to Non-Skip Objects <br /><a href="http://lh5.ggpht.com/_81s8BMvVcug/StgelpRC-qI/AAAAAAAAAFE/-DkPblpdrdc/s1600-h/image%5B5%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/_81s8BMvVcug/StgemHYnneI/AAAAAAAAAFI/YoP_FJldXGg/image_thumb%5B1%5D.png?imgmax=800" width="183" height="244" /></a> </li> <li>Scroll through the list of Objects </li> <li> <p>For any objects that you did not intend to change, delete, or add, <strong>change the Update Action to Skip</strong></p> <ol> <li>Be sure that you are not deleting stored procs, etc., that someone else recently added – e.g. if you have done another Get Latest Version on the database project since you deployed your isolated database </li> </ol> </li> </ol> </li> <li><strong>Write Updates</strong> to the target – this will update all non-skip items you reviewed above <br /> <a href="http://lh3.ggpht.com/_81s8BMvVcug/StgemUjl_TI/AAAAAAAAAFM/wAiDFweRXqw/s1600-h/image%5B12%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_81s8BMvVcug/Stgem0BnVNI/AAAAAAAAAFQ/TGwQqrotlgY/image_thumb%5B4%5D.png?imgmax=800" width="414" height="135" /></a> </li> </ol> </li> <li><strong>Check In</strong> your changes to the Project in   </li> <li><strong>Let the team know</strong> that you checked in database changes and what they might affect. The team may need to update other code and get your changes before they make DB changes.  </li> </ol> <p><strong>Problems are likely to occur if folks get careless about reviewing schema compare output and what will get updated.</strong> For example, let's take the following example: </p> <ol> <li>Person A gets latest and deploys </li> <li>Person B gets latest and deploys </li> <li>Person A creates new proc MyProcA, syncs back to the project, and checks in </li> <li>Person B creates new proc MyProcB </li> <li> <p>Person B does schema compare to project (maybe a get latest version happened in the project, too – for example, by adding a new object the solution file will need to be checked out) </p> <ol> <li>NOTE: Schema compare will want to ADD MyProcB and DELETE MyProcA (because MyProcA does not exist in Person B's isolated database) </li> </ol> </li> <li>Person B ignores output and does Write Updates and then Checks In … now MyProcA has been removed from the project </li> </ol> <p>So that's more or less why the steps outlined above are in general good practice and will hopefully keep you out of trouble. </p> <p>Also note that Scripts are not touched by Schema Compare (e.g. post-deployment scripts which seed data). </p> <p>For more reading… (this is from the DB edition documentation, available at): </p> <p><a href="http://msdn.microsoft.com/en-us/library/aa833404(VS.100).aspx">http://msdn.microsoft.com/en-us/library/aa833404(VS.100).aspx</a></p> <p>Or download the whole doc at (see Documentation.zip at the end in the Downloads section): </p> <p><a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en</a></p> Unknownnoreply@blogger.com0