Wednesday, May 8, 2019

Manually modifying a SQL Server *.bacpac file prior to importing

I encountered a situation where I needed to export a data-tier application (i.e. *.bacpac) from an Azure SQL Database and import it into a local instance of SQL Server. The *.bacpac created just fine; however, during import, it failed with a collation error. For various reasons, I did not want to modify the Azure SQL database and regenerate the *.bacpac. Instead, I found this helpful post:

Those instructions utilize a file called dacchksum.exe that is available at:

Here is the process that I used:

  • Unzip the *.bacpac file (I use 7-zip, but any normal zip tool should work)
  • Open the model.xml file
  • Find the offending stored procedure, comment the body of the procedure, and add a return statement
  • Save and close model.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile.bacpac)
  • Run the following command:
    dacchksum.exe /i:newfile.bacpac
  • Mark and copy the new checksum value
  • Open Origin.xml (from the unzipped folder)
  • Find the checksum (towards the bottom of the file) and replace it with the new checksum
  • Save and close Origin.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
  • Import the new *.bacpac (using SQL Server Management Studio)
This process was successful ... except that importing a *.bacpac fails on the first error (it does not report all errors on the first run). After I fixed the first import error, it failed a second time on a different error, so I had to repeat the process multiple times. In my case, the *.bacpac was over 750 MB, so zipping everything multiple times was taking several seconds each time. I wondered if there was a more efficient process (where I did not need to zip, compute checksum, alter checksum, then zip again). I opened dacchksum.exe with ILSpy and found that the checksum is just a SHA256 checksum. Conveniently, PowerShell has a Get-FileHash cmdlet that does exactly that. Using Get-FileHash instead of dacchksum.exe, the revised steps are as follows:

  • Unzip the *.bacpac file
  • Open the model.xml file
  • Find the offending stored procedure, comment the body of the procedure, and add a return statement
  • Save and close model.xml
  • Run the following PowerShell command:
  • Get-FileHash fullpathto\model.xml | Format-List
  • Copy the new checksum value
  • Open Origin.xml
  • Find the checksum (towards the bottom of the file) and replace it with the new checksum
  • Save and close Origin.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
  • Import the new *.bacpac (using SQL Server Management Studio)

This process was also successful, and was quicker because I only had to zip the content once.

Saturday, March 3, 2018

Printer friendly blogger posts

In a different blog, I had the need to print individual posts without all of the extra content (navigation, header, sidebar, comments, etc.). I found this post and adapted it as follows:

<style media='print' type='text/css'>
#header-wrapper, #header, .header, #sidebar-wrapper, .sidebar, #footer-wrapper, #footer, .post-meta-data, .comment-link, .comment-footer, #blog-pager, #backlinks-container, #navbar-section, .subscribe_notice, .noprint, .fauxcolumn-right-outer, .column-right-outer, .comments, #comments, .post-footer, .feed-links, .foot {display: none;}
#main-wrapper {width: 95%}
  .column-center-outer { width: 940px !important }
  /*.post-body {font-size:125% !important}*/

With the current version of Blogger, you need to edit your Theme and put this in the HTML <head> section.

Saturday, January 27, 2018

Synchronizing Photos between two Google Accounts

I have an interesting situation where I need to copy photos and videos from one Google Drive account (not Google Photos) into a separate Google Photos account. Without going into the contextual details of why I need to do this, here are the primary objectives:

  • Photos and videos are initially uploaded into Google Drive folders of account
  • Photos and videos need to end up in Google Photos of account
  • This is an ongoing activity (not a one-time activity)
  • I would prefer not to keep multiple copies of all files on my local machine
From what I have been able to tell, Google does not provide a simple way to accomplish the above objectives. The only way to accomplish these objectives is to download from one account and upload into the other account. However, I do not really want to do that manually on an ongoing basis. 

The solution is based on Google's Backup and Sync application. The application already allows you to add multiple accounts - but they must target different folders on your machine. With that limitation, I would need to manually copy all files from one synced folder to the other synced folder (and that would violate the objective of not keeping multiple copies of the files on my local machine). The solution is to use two different Windows accounts on your computer. The Backup and Sync application does not run as a Windows service at the operating system level; instead, it runs as a startup script for the Windows profile (e.g. it is only running while you are logged in). 

Setup steps:
  1. Make sure you have two different Windows accounts on your computer (e.g. one for each of the Google accounts that you want to sync.
  2. Create a file system folder that is outside of your Windows profile; perhaps you might call it C:\GoogleDriveAndPhotos
  3. Login to Windows as the first user (e.g.
  4. Install Google Backup and Sync
  5. Add the account
  6. On step 2 of account configuration, add the folder that you created in step 2 (that is where you want all of your My Drive files downloaded to).
  7. On step 3 of account configuration, uncheck all options (you are not trying to upload anything from your computer into this Google Drive account)
  8. Complete the configuration and let the sync process complete (may take a while, if you have a lot of files/photos)
  9. Login to Windows as the second user (e.g.
  10. Install Google Backup and Sync
  11. Add the account
  12. On step 2 of account configuration, uncheck all of the folders (you are not trying to download anything from this account to your computer)
  13. On step 3 of account configuration, choose the folder that you created in step 2 (that is where all of the photos/videos will be downloaded from the other account)
  14. Choose the option to only upload photos/videos (not all files). (I also chose the option to upload as "High Quality", not "Original")
  15. Complete the configuration and let the sync process complete (may take even longer, as upload speeds are often slower than download speeds)
Ongoing use:
  1. Periodically login to the first Windows account and let the sync process download new photos/videos
  2. Periodically login to the second Windows account and let the sync process upload new photos/videos

Sunday, January 7, 2018

The Parable of the Pterodactyl

As we begin this new year, let us ponder for a moment on the Parable of the Pterodactyl. Not long ago, in a small town, much like ours, was a congregation, much like ours. This congregation diligently sought for ways to improve themselves, individually, as a congregation, and as a community. Someone in the congregation thought to plan a special meeting, where everyone could come and learn a wonderful thing. One member scheduled the gymnasium weeks in advance. Others hung posters and handed out flyers. Many made personal invitations to friends and neighbors. A few planned and prepared a delightful dessert. Everyone in the community anxiously looked forward to the upcoming meeting.

The pleasant summer day finally came, and all were excited to attend. People arrived early, not wanting to miss any of the meeting. At the appointed time, all chairs were full - and a few late comers stood patiently in the back. An aura of enthusiasm saturated the air as people of all ages eagerly awaited. After a brief introduction, the long-awaited guest speaker came forward … the little known, but unusually talented, Parley P. Pterodactyl. Like most pterodactyls, Parley had years of personal flight experience. Unlike most pterodactyls, Parley could speak English and was a very charismatic speaker. Parley calmly explained to the congregation that he was going to teach each of them to fly.

As you might expect, many of the people in the room received this statement with considerable doubt and skepticism. People cannot fly - everyone knows that. Who is this Parley creature that thinks he can come teach a bunch of humans to fly. He must be as crazy as he is unusual. While some people were nurturing their doubts and drafting their excuses, others were opening their ears and expanding their vision. Parley proceeded to patiently and precisely explain the particulars of flight. He answered questions, demonstrated techniques, and taught of the convenience and wonder of flight.

Before long, a few people cautiously decided to give it a try. A few falls, a bevy of bumps, and a bushel of bruises did not quell their desire. They were inspired to do what they had learned. Their diligence paid off and they were soon flapping their arms and flying gracefully around the room. Through Parley’s persistence and the example of others, people of all ages and abilities were flying through the air. Eventually everyone was excitedly exercising their wonderful new skill. As all meetings do, this meeting eventually came to an end. The dessert was delicious, the seats were stored, and the prayer was prayed. Everyone thanked Parley, congratulated each other, left the building … and walked home.

While this parable itself is not true, it teaches a true principle. We are all creatures of habit and we all resist change - no matter how much it may bless our lives. Think back to recent weeks when you were inspired to do something wonderful. Perhaps you were inspired to light the world through acts of kindness. Perhaps you were inspired to become more self-reliant or study more diligently or pray more sincerely. Perhaps you were inspired to be more patient with a loved one or go visit a neighbor. Whatever that inspiration was, where is it now? Did you leave that inspiration right where you received it? Let us not be like Parley’s pupils who succumbed to the heavy chains of habit. Instead, let us do as James counsels and be “doers of the word, not hearers only” (James 1:22). Let us bless our own lives (and the lives of others) by implementing our inspirations.

Wednesday, November 15, 2017

VSTest.Console.exe TestContext.WriteLine messages

I had a situation today where I needed to run some automated tests (written using the Visual Studio UnitTest framework) on an Azure App Service Web App (to reduce latency to the test database, which is an Azure SQL Database).

My first step was to get VSTest.Console.exe (and its dependencies) into the Web App. I am using Visual Studio 2017, so I zipped this entire folder:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\TestWindow

Using Kudu/SCM, I created D:/home/vstest and uploaded/unzipped the file.

Next I copied up my *.runSettings file and my test assembly (with dependencies). With all of the necessary files in place, I ran this command line (also via Kudu/SCM) based on the documented command line options for VSTest.Console.exe:

D:/home/vstest/vstest.console.exe D:/home/product/EQuIS/Application/bin/EarthSoft.EQuIS.dll /Settings:D:/home/_Test/sql.runSettings /Tests:LoadEddPerformanceTest 

That seemed to work fine - except that it would not show me my TestContext.WriteLine messages. Within the test code itself, I was using TestContext.WriteLine to output some timing information.

After much trial and error, I finally found the undocumented "/Diag:{filename}" option. That option will output a lot of detailed information into the given filename, including the TestContext.WriteLine messages:

D:/home/vstest/vstest.console.exe D:/home/product/EQuIS/Application/bin/EarthSoft.EQuIS.dll /Settings:D:/home/_Test/sql.runSettings /Tests:LoadEddPerformanceTest /Diag:D:/home/TestResults/output.log

Thursday, July 27, 2017

Visual Studio Windows Form Designer Freezes

If a Windows Form includes third-party controls, Visual Studio 2015 may freeze when trying to close the designer tab (or the application itself). The only way to get around it, is to kill the Visual Studio process, then delete the *.suo file. It used to be that the *.suo file was stored in the folder as the solution file. However, the *.suo file may be stored in a subfolder:


If you delete the .suo file in the subfolder, then no tabs will open automatically when you open the solution in Visual Studio.

Tuesday, May 31, 2016

Windows Limited Connectivity (WLAN and LAN)

I was doing some work on a Windows 8.1 laptop for a friend. One of the problems was the WiFi did not work, and he also wanted to upgrade to Windows 10. It would "connect" to the network, but DHCP failed and the machine was never assigned an IP address. The connection was labeled as "Limited" or "Unidentified Network" (depending on where you looked) and the WiFi icon in the task bar had the yellow exclamation point overlay. The same issue (limited connectivity) happened with Ethernet (after plugging in a Cat5 cable).

I proceeded in the following order:
  • Uninstall Norton Security (subscription had expired, no longer needed)
  • I already had the Windows 10 installation files on a thumb drive, so I went ahead and did the upgrade to Windows 10 (hoping that would magically fix the connectivity issue)
After installing Windows 10, the connectivity issue persisted for both WiFi (WLAN) and Ethernet (LAN). I spent quite a while searching for solutions online and trying various driver configurations. I tried disabling DHCP and assigning an IP address manually. I tried various netsh commands. Nothing seemed to work and I was running out of ideas. I prayed for some divine help (from the omnipotent IT Guy - aka Heavenly Father). I kept trying various things and nothing worked, and I kept praying for help. I finally had the idea inspiration to disable Windows Firewall. And it came to pass ... that the issue was resolved.

Something in Windows Firewall was preventing network connectivity. Just to be certain, I enabled Windows Firewall ... and it broke again. I did not want to leave Windows Firewall off permanently (not a good idea, particular for a laptop that will be used on public networks). From Control Panel's Windows Firewall section, I clicked the "Restore Defaults" link to restore Windows Firewall to its original, default settings. After that, WiFi worked just fine (with Windows Firewall enabled). I cannot say for certain what Windows Firewall settings were causing the problem - but I can confirm that disabling Windows Firewall, and subsequently restoring the default settings, resolved the issue.

I am grateful for the divine inspiration that helped me get this issue resolved.