Blog

  • How to fix “Computed columns are not supported with external tables for sharded data.”

    This post was written the first day I encountered this error on Azure SQL Server because I could not find any web page that contained this error message.

    If you’re getting the error, Computed columns are not supported with external tables for sharded data. while trying to create External Tables, remove the computed column definitions. Treat computed columns like normal columns and provide a data type. Read this: https://stackoverflow.com/a/43382300/338432

  • Solving a Confusing MigrationBlocker While Moving to Azure SQL Database

    This week, I’m altering functions and stored procedures in a SQL Server 2008 database so that it can be migrated to Azure. The Data Migration Assistant does a great job of generating reports identifying MigrationBlockers, but one type of error was vague enough to confuse me for a few minutes. Here’s an example of that error:

        {
          "Recommendations": [
            {
              "ApplicableCompatibilityLevels": [
                "CompatLevel100",
                "CompatLevel110",
                "CompatLevel120",
                "CompatLevel130",
                "CompatLevel140"
              ],
              "ChangeCategory": "MigrationBlocker",
              "RuleId": "46010",
              "Title": "One or more objects contain statements that are not supported in Azure SQL Database [46010]",
              "Impact": "While assessing the schema on the source database, one or more syntax issues were found. Syntax issues on the source database indicate that some objects contain syntax that is unsupported in Azure SQL Database.",
              "ImpactDetail": "Function: [dbo].[GalleryXML] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near AS.",
              "Recommendation": "Note that some of these syntax issues may be reported in more detail as separate issues in this assessment.  Review the list of objects and issues reported, fix the syntax errors, and re-run assessment before migrating this database.",
              "MoreInfo": ""
            }
          ],
          "IsSelectedForMigration": true,
          "Eligibility": {
            "IsEligibleForMigration": true,
            "Explanation": "OK"
          },
          "ObjectName": "GalleryXML",
          "SchemaName": "dbo",
          "ObjectType": "UserDefinedFunction"
        },

    The error text, “Incorrect syntax near AS,” was just not a strong clue as to what was wrong with this function. The purpose of the function is to create structured XML that can be de-serialized into an instance of an object in C#. I use PATH Mode to accomplish this, and it is one of my favorite maneuvers when operating in SQL Server/.Net. Here is an abbreviated version of the query to help you understand the required changes to continue using this method in an Azure database:

    SELECT 
    ID,
    
    ...
    
    --Subquery for photos
    (
    	SELECT 
    	ID,
    
    	...
    
    	FROM GalleryPhotosTbl 
    	WHERE GalleryID = GalleryTbl.ID
    	FOR XML PATH('GalleryPhoto'), TYPE
    ) AS Photos
    FROM GalleryTbl
    WHERE GalleryTbl.ID = @ID
    FOR XML PATH('Gallery'), TYPE

    The syntax that Azure doesn’t support is “AS Photos.” Here is the solution:

    SELECT 
    ID,
    
    ...
    
    --Subquery for photos
    (
    	SELECT 
    	ID,
    
    	...
    
    	FROM GalleryPhotosTbl 
    	WHERE GalleryID = GalleryTbl.ID
    	FOR XML PATH('GalleryPhoto'), root ('Photos'), TYPE
    )
    FROM GalleryTbl
    WHERE GalleryTbl.ID = @ID
    FOR XML PATH('Gallery'), TYPE

  • Homebrew Competition 2019

    Homebrew Competition 2019

    Beer in the streets raises $103k for local charities. 11×17. https://lititz.beer

  • Hiding Specific Terms When Creating or Editing Posts

    The WordPress block editor uses the REST API to manipulate all information in the post object, so hiding terms from the category, tags, or a custom taxonomy meta box can be achieved by removing those terms from REST API responses.

    One hook that makes this easy is the rest_{taxonomy}_collection_params filter.

    Here is an example that shows how terms can be included in the editor only if they have a certain value for a term meta key. This isn’t the best code design because it uses a global variable to hold the taxonomy name, but that’s an artifact of my pulling this out of a larger class.

    For my application, the terms I’m excluding are outdated and won’t be added to any new content on the site. This method is effective and seems low risk. If you are aware of any risks associated with excluding terms from REST API responses, please comment below.

  • Pressure Washing in Lancaster, PA

    Pressure Washing in Lancaster, PA

    Business cards for a local pressure washing business, Clean & Co. LLC

  • Tasting Room & Bottle Shop

    Tasting Room & Bottle Shop

    I made this last year for the Stoll & Wolfe distillery in Lititz, PA.

  • Why Elementor Disobeys is_admin()

    I was surprised to learn that when editing a page using Elementor‘s page builder, is_admin() returns false. The reason for this is that Elementor is loading the page in an <iframe> element as if it were being viewed on the front-end.

    Use code like this to detect when posts or pages are being edited in Elementor. Code like this is useful to me because sometimes I add a meta refresh element to automatically start a download, and I don’t want the redirect and download to happen while editing the page.

    //don't do anything if we're editing in Elementor
    if( \Elementor\Plugin::$instance->editor->is_edit_mode() ) {
        return;
    }

    Here is other code that relies on a URL querystring parameter.

    //don't do anything if we're editing post 1112 in Elementor
    if( isset( $_GET['elementor-preview'] ) && '1112' == $_GET['elementor-preview'] ) {
        return;
    }
  • Using register_setting() and the REST API

    Here is a comment I just requested be added to the bottom of the register_setting() page.

    If you plan to use your setting in the REST API, use both the rest_api_init and admin_init hooks when calling register_setting() instead of just admin_init. The show_in_rest argument is ineffective when hooked into admin_init alone.

    Corey Salzano

    Someday, when my comment is approved, you’ll be able to see it on this page and save an hour or two of debugging time. If you can see my comment on this page, please let me know.

  • The Events Calendar List Widget Replacement

    I wrote a plugin to customize the Events List widget that ships with Modern Tribe’s The Events Calendar Pro plugin. The widget has an option to hide completely when there are no upcoming events. The alternative displays the dreaded “There are no upcoming events at this time” in an otherwise empty Events List widget. I love the idea of not including the widget at all if there are no events, but what if this is the only widget in a sidebar or widget area? Sometimes, I want to display something in place of the widget instead of leaving the space empty. Here’s a plugin that does exactly that.

    Download plugin

    https://github.com/csalzano/the-events-calendar-list-widget-replacement

    Where does the replacement content live?

    Create a file at wp-content/themes/your-theme/tribe-events/pro/widgets/list-widget-replacement.php that contains the content you wish to display in place of the Events List widget when there are no upcoming events.

    If you’re using the free version of The Events Calendar, create the file at wp-content/themes/your-theme/tribe-events/widgets/list-widget-replacement.php

    (This file sits right next to list-widget.php, the template you might create to modify the output of the Events List widget. I chose these locations in the spirit of The Events Calendar Themer’s Guide.)

    Here’s what my file looks like–I wrote some HTML that mimics a widget so I didn’t have to write any additional CSS styles for the replacement content. My client is a local municipality, so I put together some numbers that describe the size and age of a small Pennsylvania township.

  • Editing Terms & Term Meta with the WordPress REST API

    Here are some JavaScript snippets to manipulate WP_Term objects in WordPress using the REST API and the Backbone JavaScript client library.

    Insert a term

    Delete a term

    This next example assumes you’ve used wp_localize_script() to make the REST API endpoint and a nonce available in an object myplugin. If you need help doing this, please leave a comment below and I’ll expand this example.

    Edit a term meta value

    Let’s add a term meta value to identify the number of speeds in this automatic transmission.

    Thanks for reading. If you know a better way to edit terms and term meta in JavaScript, please leave a comment below.

  • WordCamp Lancaster 2019

    WordCamp Lancaster 2019

    I am the lead organizer for WordCamp Lancaster for the first time in 2019, and that means I was allowed to design this logo for the event. Thanks be to fellow organizer Dustin Leer for looking at draft versions of this and sharing insights.

  • What is $posted_data passed to Contact Form 7’s wpcf7_posted_data hook

    For a ContactForm7 form that has this source:

    <div class="wpcf7-lead-widget">[text* contact-name maxlength:50 placeholder "Your Name (required)"]
    [email* email maxlength:50 placeholder "Email (required)"]
    [text phone maxlength:15 placeholder "Phone"]
    [vehicle_form_field]
    [textarea comments x3 placeholder "Questions and Comments"]
    [submit class:_button class:_button-small "Check Availability"]
    [hidden context id:context "contact"]
    [hidden do-not-send-mail]</div>

    The $posted_data that is passed via the wpcf7_posted_data hook looks like this:

    Array
    (
    [_wpcf7] => 10610
    [_wpcf7_version] => 5.1.1
    [_wpcf7_locale] => en_US
    [_wpcf7_unit_tag] => wpcf7-f10610-p7983-o1
    [_wpcf7_container_post] => 7983
    [g-recaptcha-response] =>
    [contact-name] => Corey
    [email] => [email protected]
    [phone] => 8005556666
    [inventory-post-id] => 7983
    [comments] => Super interested in this sandbox
    [context] => contact
    [do-not-send-mail] =>
    )

    The first item is the form ID, and all items after g-recaptcha-response are the values of the fields provided by the the user (or the source in the case of the hidden fields). inventory-post-id is the value of a drop down created by the shortcode in our form, [vehicle_form_field].