Getting Back sysadmin Access

 There comes a time in every DBA's life where they mislay the sa password, or discover a SQL Server somewhere that doesn't have the AD sysadmin group added and no one can remember the sa password.

As long as you have administrator access to the server, getting back access is relatively easy in concept, but can be a bit rough in practice. 

The general principal is to:

• restart SQL Server in single user mode
• connect to the SQL Server
• either reset the sa password or add in the sysadmin group
• restart SQL Server in  multi-user mode

I prefer to do all of this from a command prompt, here's what I do.

1. Logged into the host Windows server as a local administrator, start a command prompt as an administrator, i.e. using the Run as administrator option
2. Stop the SQL Server Agent service
   net stop SQLSERVERAGENT
3. Stop the SQLServer service 
   net stop MSSQLSERVER
4. Here's the trick, start the service in single user mode (/m) and only allow connections from sqlcmd. Also start sqlcmd in the same command. This stops anything else grabbing the connection before you can get to it.
   net start MSSQLSERVER /m"SQLCMD" && SQLCMD
5.  Now the password reset/group add can be carried out, remembering that sqlcmd needs semi-colons and a GO statement to execute.
6. Once the account reset has been done, restart the service in normal mode and start the SQL Agent service.
   net stop MSSQLSERVER && net start MSSQLSERVER
net start SQLSERVERAGENT

You should now be able to connect as normal

Updating Dynamics365 from Azure Data Factory

 Updating an entity in Dynamics365 from Azure Data Factory should be easy right? I mean, they're both Microsoft products, there's a native connector for Dynamics365 in ADF and it all seems to connect up ok to pull data from Dynamics so should be straight forward. Well, turns out it's not so grab a coffee, because this one takes some explaining.

Now to be fair, a lot of this is documented on various other posts, but there was a fair bit of subtlety and nuance. Hopefully this post has all the bits so if I ever need to do this again it will be a piece of cake.

TL;DR;

For any lookup field/column in the entity:

• Create a derived column with a name of "<field schema name>@odata.bind", e.g. for field my_accountid the column name needs to be "my_AccountId@odata.bind" with the quotes
• The value of the column needs to reference the lookup table with an 's' added to the end, e.g. my_accounts field is an account id from the account table so the value needs to be /accounts(<accountid guid>) 
• in the mapping, the target needs to be the schema name with @odata.bind on the end, e.g my_AccountId@odata.bind, so the mapping in this example would be ["my_AccountId@odata.bind" -> my_AccountId@odata.bind] 
• null values in the lookup guid can potentially cause issues 

Scenario Setup

So for this actual bit of work we were replacing a feed from a SQL Server database view that updated a Dynamics entity. The original feed was a PowerApps pull from the database, but security didn't like that so we needed to create something to push to Dynamics instead.

Service principal access works correctly, data is good and entities are all ready to go. For this post I'll simplify the schema structure and not dwell too much on the specifics other than where there were issues.

Approach 1 - Copy Data Activity

 For a simple first cut I tried a simple Copy Data activity. This actually worked really well - grab data from the database view and push it into the table, everything flowed nicely and the data was visible in the front end.

Except ...

The primary key values in the entity weren't known by the database so we'd get duplicate records. We could've used an alternate key, but that would require marking the field as an alternate key in Dynamics and the team wasn't keen to do that at this stage, due to other work and lack of time for testing.

It was easy enough to add steps to do a data pull from the entity, match the data using our alternate key and then write the data back to the entity, and this approach did work, but was clunky and slow. We'll keep this as the fallback solution, but meanwhile try ...

Approach 2 - Data Flow with Lookup

Next approach was to use a data flow with a lookup onto the entity, using our alternate key as the join and then the primary key for an upsert. First stab at this worked well, but some columns weren't updated. From here, things turned sour pretty quick.

The columns that weren't updating were Lookup fields in the entity. For the explanations that follow we'll call the Dynamics entity my_servicedelivery and the lookup field my_accountid, which is a guid column referencing the account entity.

The data flow was pretty simple; 2 sources - 1 for the view and 1 for the entity, a left join between the view data and the entity, an Alter Row upsert using the my_servicedeliveryid key and then the sink to the entity,

 

The Errors

Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload 

 The first error encountered was "Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload"

Turns out that entity attributes have both a logical name and a schema name. The logical name is what you see in the attributes for mapping and in the Dynamics database tables, the schema name is a camel case version of the logical name, so in this case logical name my_accountid had a schema name of my_AccountId. 

This error means you need to use the schema name, rather than the logical name. MS documentation states that you should use schema name for any custom attributes, but I found it was only necessary for the lookup ones

You can use a tool like FetchXML builder in Xrm Toolbox  to find the schema name and also see if the attribute is a lookup type.

Updating the column name in the view fixed this problem, leading to ...

Microsoft.OData.ODataException: A 'PrimitiveValue' node with non-null value was found when trying to read the value of the property 'my_AccountId'; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.

 Now, this error has been reasonably well noted, but there are a few nuances to it. Basically, Dynamics is getting a string value from ADF, but wants an object.

There are 2 bits to this; 

1. The column name needs to be the schema name with @odata.bind added to the end of it, so in this example the my_accountid source column needs to be called my_AccountId@odata.bind
2. The value of the column needs to reference the lookup entity, which in this case would be account, but it needs to have an s on the end and be in the format /entitys(guid). So for this example the value in the column should be '/accounts(<guid for the accountid>)'

Ok, easy enough, update the view to have a column called [my_AccountId@odata.bind] and set the value to be CONCAT(N'/accounts(', my_accountid, N')'). Job done.

Except it wasn't. The debug run still failed with the same error.

ADF, and maybe Dynamics, doesn't handle the column name in the view very well and doesn't present it properly (or Dynamics doesn't treat it properly). So, we rolled back the view changes and instead created a derived column in ADF before the Alter Row upsert, setting the column name to have the @odata.bind on the end of the name and the concat logic in the expression. After a bit (ok, quite a bit) of playing with different names and expressions it turns out the column name also needs to be in double quotes, "my_AccountId@odata.bind"

 

So that must be it, right? Well, not quite. Same error.

The last step was to set the target column in the sink mapping to also be in the format schema_name@odata.bind, as shown in the JSON script snippet from the dataflow

 

 Once all of this was set the flow debugged correctly and data was updating as expected.

In our actual data flow we had multiple lookup fields, so created multiple derived column transforms prior to the upsert but the mapping principle was the same for each field.

This took a bit of time to get sorted, but having the lookup to get the entity id does work better for performing updates and is a lot safer in avoiding duplicates. 

Entra Provisioning Expressions FormatDateTime and IIF

 Lately when configuring an Entra ID Enterprise Application for SCIM integration to an external system, I ran into an issue passing through dates. The cause of the problem was that we use an extension attribute to hold this particular date, but we also add text into the field.

My first inclination was to use the IIF expression to do some basic format checking, but this fails as the FormatDateTime function is eager and checks for the value of the field before processing the IIF part. For example this statement (simplified)

IIF(Len([extensionAttribute9])="10", FormatDateTime([extensionAttribute9], "dd/MM/yyyy", "yyyy-MM-dd"), "")

will fail if [extensionAttribute9] has non date values.

The fix was to move the IIF statement inside the FormatDateTime expression, so the example above becomes

FormatDateTime(IIF(Len([extensionAttribute9])="10", [extensionAttribute9], ""), "dd/MM/yyyy", "yyyy-MM-dd")

Moving the logic inside the FormatDateTime expression means that the eager validation evaluates the logical function before applying the value.

IIF Limitations

Something else that came up was limitations in how IIF behaves, notably that it doesn't support AND or OR for multiple comparisons. This is well documented on the expressions page, https://learn.microsoft.com/en-us/entra/identity/app-provisioning/functions-for-customizing-application-data#iif, but did require a rethink.

In the end I found it worked best to check for not equal rather than equal. The final expression ended up as 

FormatDateTime(IIF(IsNullOrEmpty([extensionAttribute9]), "", IIF(Len([extensionAttribute9])<>"10", "", IIF(Instr([extensionAttribute9], "/", , )<"1", IIF(Instr([extensionAttribute9], "-", , )<"1", "", [extensionAttribute9]), [extensionAttribute9]))), "32", "dd/MM/yyyy", "yyyy-MM-dd")

The crude check being check that it's not null or empty, then check for a length of 10 which fits our date format, then check for the presence of "/" or "-" characters and if these all pass then format as date from "dd/MM/yyyy" to "yyyy-MM-dd". Obviously this isn't a super accurate check but given the limitations with the provisioning expressions it should cover most cases and we can update source data for any failures.

Local Testing an Azure Function with a Timer Trigger

Following on from the last post on  Azure Function Deployed But Not Visible, when investigating this I wanted a way to test the function without having to deploy it to Azure. Here's how I did it, using Visual Studio Code and Postman API.

Extensions 

 First up was to install a couple of extra extensions in VS Code; Azurite and Azure Functions Core Tools.

Azurite is an extension that allows "mocking" Azure Blob Storage functionally and can be installed from the VS Code Extensions tab. More info on installing can be found here, https://learn.microsoft.com/en-us/azure/storage/common/storage-install-azurite?tabs=visual-studio-code%2Cblob-storage, and the page has links to other pages on what Azurite is and how to use it.

Azure Functions Core Tools is the tool set that lets you run functions locally. You can try installing by hitting the F1 key then choosing Azure Functions: Install or Update Azure Functions Core Tools but may find you need to download and install manually. The download and further instructions can be found here, https://learn.microsoft.com/en-us/azure/azure-functions/functions-run-local

Running the Function

Before we can run the function we need to start the function runtime environment. To do this, hit the F1 key then find and select Azure Functions: Start. When this runs you'll get prompted to log into Azure and select the subscription and resource group you want to use. Alternatively enter func start in the terminal window. I've found the Azure Functions: Start command a bit hit and miss, so using func start seems to work more consistently.

 

Note that for Python, you'll need to start the Python virtual environment if using that config, otherwise you'll get errors about modules not being found. This will be something like running & <project_path>/.venv/Scripts/Activate.ps1 in the terminal.

 

Postman

Once the function is running in VS Code you'll be able to send a Postman request to trigger the function. By default the function runs on port 7071 (I don't know how to change this, as this worked fine for me) so the call for Postman will be a POST request to http://localhost:7071/admin/functions/function_name, e.g. http://localhost:7071/admin/functions/MyTestFunction. 

 

Authentication isn't needed, but there does need to be a body, so if the function doesn't take parameters then just add empty brackets to the Postman request body. 

Here's how the request should look

 

 

All going well, Postman will get an HTTP 202 response, and function output will appear in the VS Code terminal window.

Settings etc.

The functions local runtime uses settings in the local.settings.json file, so these might need to be changed temporarily for testing. Also, if using service principals or managed identities for any resource authentication these will probably fail, so updating these to a different auth (e.g. SAS key or SQL login) might be needed for testing.

 

Azure Function Deployed But Not Visible

 Playing around with Azure functions lately and struck a weird issue when trying to get the function running up in Azure (as opposed to just testing on my laptop).

 This particular function is a Python script, developed and deployed from Visual Studio Code to an existing Function App. The function behaved correctly in the VS Code dev environment, and appeared to deploy fine with no errors, however when checking in the Azure Portal there was no function to be found. Deleting and recreating the Function App didn't help

The fix was to add "AzureWebJobsFeatureFlags": "EnableWorkerIndexing", to the local.settings.json file, so it looks something like

{

  "IsEncrypted": false,

  "Values": {

    "FUNCTIONS_WORKER_RUNTIME": "python",

    "AzureWebJobsFeatureFlags": "EnableWorkerIndexing",

  }

}

 In addition, the .funcignore file was excluding local.settings.json, so I removed this as well.

Once this was done and the function redeployed everything turned up as expected 

SSIS For Loop AssignExpression Error

 Just a quick note on a frustrating error adding an AssignExpression to an SSIS For Loop Container.

The expression being added was

@[User::LoopCount] = @[User::LoopCount] + 1

which looks fine, but continually gave an error of "the equals (=) sign at position 20 was unexpected". 

Changing the variable formats, and other syntactic changes didn't help. And the cause?

A space at the front of the expression. If I'd counted to position 20 I might have found it earlier, as the = sign was at position 19, but then again, probably not. Removing the space solved the error and allowed the loop to run.

Azure Data Factory Metadata-Driven Pipelines - 2

 As mentioned in the previous post, we're using some custom tables behind our metadata-driven pipeline to provide some flexibility and hopefully allow it to be expandable. The schema is still a work in progress, and has some obvious limitations that could be improved on once we have some more time available.

Overview 

High-level, the solution has some tables to hold the entities/tables that are to be loaded, along with column mappings, "high water" values for delta loads, and any pre-copy scripts to be run. Here's the ERD.

 

 

Table	Purpose
DataFeed	High level data feed details
DataFeedEntity	Tables to load
ColumnName	Column names to cut down duplication in table rows
DataFeedColumn	Source and destination columns
DataFeedEntityScript	The pre-copy script to run if required
HighWaterValue	Column and value used for delta loads

Most of the tables have an "Active" column which provides flexibility of which entities and columns to include in the ADF copy activities.

On top of the tables is a view which is used to present the table data to the ADF pipeline. In theory it should be possible to have multiple views for different data loads, but we haven't tested this yet.The easiest way to create the view is to use the table that the ADF wizard creates then recreate the table output in the view. I'll include our current version below for reference. There's also a stored proc to update the high water values, and a table valued function which gets a count of active entities to be loaded.

Limitations

There're a few baked in limitations in our solution which were design choices based on our loads and keeping it simple (ish) to start with. These should all be easy to adapt and we'll probably look at that in the down time. The key limitations are:

• we assume the destination table has the same name as the source entity
• also assume that the target schema is dbo. This was a bit of laziness and we'll probably add a destination schema column to the DataFeedColumn table in the near future
• the data type columns in DataFeedColumn are the ADF data types, e.g. String rather than varchar. We have a separate mapping table that we use when populating the table, but this could be added to the schema 
• We don't store connections in these tables, which could be a useful enhancement 

Example SQL View

SELECT CONCAT (
            N'{
            "entityName": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS SourceObjectSettings,

        NULL AS [SourceConnectionSettingsName],

        NULL AS [CopySourceSettings],

        CONCAT (
            N'{
            "schema": "dbo",
            "table": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS[SinkObjectSettings],

        NULL AS [SinkConnectionSettingsName],

        concat(N'{
            "preCopyScript": ', ISNULL(QUOTENAME(pre.ScriptBody, '"'), 'null') ,
            ',
            "tableOption": null,
            "writeBehavior": ', CASE WHEN dfe.LoadType = 'FullLoad' THEN '"insert"' ELSE '"upsert"' END, ',
            "sqlWriterUseTableLock": true,
            "disableMetricsCollection": false,
            "upsertSettings": {
                "useTempDB": true,
                "keys": [
                    "', keycols.KeyColumnName, N'"
                ]
            }') AS [CopySinkSettings],

        REPLACE(CAST(N'{
            "translator": {
                "type": "TabularTranslator", 
                "mappings": [{X}]
             }
          }' AS NVARCHAR(max)), N'{X}', ca.X) AS [CopyActivitySettings],

        N'MetadataDrivenCopyTask_ftq_TopLevel' AS [TopLevelPipelineName],

        N'[
            "Sandbox",
            "Manual"
        ]' AS [TriggerName],

        CONCAT (
            N'{
            "dataLoadingBehavior": "',
            dfe.LoadType,
            N'",',
            N'"watermarkColumnName": "',
            cm.ColumnName,
            N'",',
            N'"watermarkColumnType": "DateTime",',
            N'"watermarkColumnStartValue": "',
            convert(VARCHAR(40), hv.TimestampValue, 126),
            N'"',
            N'}'
            ) AS [DataLoadingBehaviorSettings],

        dfe.EntityGroup as [TaskId],

        dfe.Active [CopyEnabled],

        ROW_NUMBER() OVER(ORDER BY dfe.[EntityGroup], dfe.[DataFeedEntityId] DESC) AS RowNumber,

        dfe.DataFeedEntityId

    FROM adf.DataFeedEntity dfe
    JOIN (
        SELECT c.DataFeedEntityId,
            STRING_AGG(N'{"source":{"name":"' + cast(c.[SourceColumnName] AS NVARCHAR(max)) + N'",
            "type": "' + c.SourceDataType + '"},"sink":{"name":"' + cast(c.[DestinationColumnName] AS NVARCHAR(max)) + '"}}', ',') X
        FROM adf.vw_ColumnMapping c
        GROUP BY DataFeedEntityId
        ) ca ON ca.DataFeedEntityId = dfe.DataFeedEntityId
    LEFT JOIN adf.HighWaterValue hv ON dfe.DataFeedEntityId = hv.DataFeedEntityId
    LEFT JOIN adf.ColumnName cm ON hv.ColumnNameId = cm.ColumnNameId
    LEFT JOIN (
        SELECT  c.ColumnName as KeyColumnName, 
                dfc.DataFeedEntityId
        FROM [adf].[DataFeedColumn] dfc
        JOIN [adf].[ColumnName] c ON dfc.DestinationColumnNameId = c.ColumnNameId
        WHERE dfc.IsKey = 1
    ) keycols ON dfe.DataFeedEntityId = keycols.DataFeedEntityId
    LEFT JOIN adf.DataFeedEntityScript pre ON dfe.DataFeedEntityId = pre.DataFeedEntityId
        AND pre.ScriptType = 'PreCopy'
    WHERE dfe.Active = 1 

 

 

Part 1: Azure Data Factory Metadata-Driven Pipelines - 1

SSIS Azure SQL Connection Login Failure

 Issue

Running an SSIS package using environment variables failed when trying to connect to an Azure SQL database. The error returned pointed to a password issue with the SQL user.

Solution

There was a couple of issues at play here.

1. The server name component of the connection string needed to have the full Azure database name, i.e. my-sql-server.database.windows.net rather than just my-sql-server
2. We also had to add the Persist Security Info=true parameter to the connection string. I think this is because the user we connect as is a contained user to the specific database.

Also worth noting that as we use a contained user the database needs to be specified in the connection string 

Azure Data Factory Metadata-Driven Pipelines - 1

 I've had a bit of free time work wise lately so figured I'd finally get onto revamping one of our Azure Data Factory (ADF) extract processes. 

This particular one gets data from our Dynamics CRM and exports to the landing stage of our data warehouse. It was stood up in a hurry due to Microsoft retiring the previous, CRM driven, export process, and us internally missing the notifications so that by the time we realised what was happening the drop-dead date was almost upon us.

It's a pretty simple process which just dumps data from selected Dynamics entities into SQL tables - no transforms and very little filtering, and has been pretty stable though we have had to filter a couple of the entities due to size, and also reduce the number of attributes/columns as our customisations have resulted in 2 particular entities having > 500 attributes. 

ADF Metadata-Driven copy task 

What I've decided on is to update this to use the Metadata-Driven copy task, as well as cleaning up the extract attributes and implementing some better filtering to reduce the daily data extract quantity.

There's already a lot of info out there about the metadata-driven task, so I'm not going to get too into it here. Basically it will allow effectively a single process that will cycle through all the entities to be exported, rather than creating multiple datasets and copy tasks. 

I always forget how to get to it though, so here's how. Once you're launched the ADF Studio from the portal you create a new task by creating a new Factory Resource and choosing the Copy Data tool.

 

 This will open a window/blade that lets you select Metadata-driven copy task as the copy task.

A few notes about this - some of my decisions during the create will be explained later:

• It needs an existing database to create the SQL objects needed for storing the task metadata. The dataset can be created from the blade but the SQL Server and database need to already exist and be accessible to the data factory
•  If you want the pipelines to run on a specific runtime environment that needs to already exist
•  Source and destination connections can be created during the wizard steps, or you can use existing ones
• For this setup I was intending to customise it afterwards, so I just selected 2 tables to use as examples/templates 
•  I chose Configure for each table separately as the loading behaviour, and then Delta load for one table and left the other as Full load. For delta loading you need to select a Highwater column which will be used to track whether changes have occurred.
•  Allow the wizard to generate column mappings
• Also set the destination properties. We want to use Upsert for our Write behaviour so set this with the appropriate key. You can also add a Pre-copy script for example purposes and any other settings that you think might be useful later on

 Once all the selections have been made the wizard will create the ADF resources such as pipelines and datasets, as well as the SQL database tables and stored procs. 

From the ADF side this is 3 pipelines named xxx_TopLevel, xxx_MiddleLevel, xxx_BottomLevel. The top level pipeline is the main orchestrator and the one which will be scheduled to run. This calls the middle level, which then calls the bottom level one to do the actual export and destination population.

The heart of this SQL side is a table which contains a row for each entity that's being extracted, with columns of JSON values of the metadata that ADF interprets to run the pipelines.

Limitations and Next Steps

 I might be missing something, but updating the copy task for changes to existing items or adding new ones looks to be a bit of a chore. Seeing as I have time on my hands I'm going to try altering the generated objects to allow using some custom tables - sort of a metadata-driven metadata-driven task. More on this in the next post

 

 

SSIS ScriptComponent Outputs

 Something else for the "Stuff I Always Forget" category.

 There's a couple of tricks when using the SSIS Script Component as a data source.

Nulls

If one of the outputs is null you need to set the _IsNull property to true

(N)Varchar Max 

Max string columns need to be output using the Column.AddBlobData(System.Text.Encoding.Unicode.GetBytes(data)) format. 

Note that this needs to be Encoding.Unicode not Encoding.UTF8 otherwise you get the infamous odd byte number error  

DateTime 

DateTime output column data types should be of type database timestamp [DT_DBTIMESTAMP], even for datetime2 otherwise you get an overflow error

 

Example

ApiOutputBuffer.AddRow();

// Check nulls
                    if (string.IsNullOrEmpty(fullEndpoint))
                    {
                        ApiOutputBuffer.Endpoint_IsNull = true;
                    }
                    else
                    {
                        ApiOutputBuffer.Endpoint = fullEndpoint;
                    }

// Nvarchar max
                        ApiOutputBuffer.Response.AddBlobData(System.Text.Encoding.Unicode.GetBytes(content));
 

                    ApiOutputBuffer.ResponseDateTime = DateTime.Now; 

Carnivore Week 3 and Sum Up

 Week 3 was week 2 with dairy - mainly cream, butter and cheese. I realise this isn't strictly carnivore (seems to be referred to as dirty carnivore), so maybe more inline with zero carb or "animal based".

So how was it

This was the best week yet and I could comfortably eat like this the majority of the time. Everything felt pretty good, weight dropped another ½ kilo. The addition of butter for cooking made the meals much more enjoyable, and I had really missed cheese.

The Verdict and General Thoughts

My intention for doing this was to try and determine if there is anything to all the hype around this style of eating, or is it mainly a vehicle for getting social media credit. Putting it on here is just so I have a record of it in times to come (and I haven't done anything technically interesting of late).

The verdict - I think there's definitely something in it, and something I'll be looking to work in as a permanent change going forward.

 As with anything there are pros and cons. The biggest pros are health related - losing extra weight, better bowel function and feeling generally better. The cons were more "soft" cons, but still things that would need to be addressed to do this long term.,

Cons

Firstly, the rest of the family aren't keen on this style of eating, which means separate meal prep. Not a huge problem but a bit of a pain logistically having to cook multiple meals. Meal prep is, for me, another con. I enjoy cooking and experimenting with different foods and flavours.

I also like to eat a lot of the food that I gave up - though this is a problem with any diet change, and I suspect undoubtedly why many people fail to change their eating patterns.

These cons could probably be resolved pretty easily, e.g. having set days to eat and prepare more detailed meals, and if there're no real health issues having more of an "animal based" diet than strict carnivore.

Now for a short rant. 

It's also difficult to determine the long term effects. Trying to get information online swings from the "you'll die if you don't eat fruit and veg" to "fruit and vegetables are trying to kill you". Conventional medical and diet practitioners seem to be firmly in the first camp with most being very reluctant to hold a view outside of the 5+ a day and plenty of fibre mindset. The carnivore camp is getting almost as dogmatic in their views as the vegan community with an almost religious dedication to what can and can't be eaten, and digital crucifixion of anyone who decides they want to start incorporating any sort of plant. Ok, so probably only a few with this mindset, but it is (disappointingly) growing from what I've seen on social media since looking into this. It could also just be the algorithms trying to rage bait me, but does contribute to the difficulty of finding accurate information.

There's also the tendency for people to want to make money by selling you something - "vital" supplements, books/content to stop you making fatal mistakes etc. And man do some people go overboard - a simple statement to eat more fat results in people telling you to drink lard and add copious amounts of butter to your coffee, when I'm pretty sure the intent was to not cut the fat off your meat and maybe cook your eggs in some butter.

Rant over.

Other (Possible) Pros 

Other than the health benefits already mentioned, there were a handful of things that I noticed during the journey which I can't categorically say are related to the diet, but they do appear to be worth closer investigation. 

Smell/Odour - I try to limit the use of deodorant if not leaving the house, and must admit it can get a bit fragrant particularly in hot weather. This seemed to be significantly diminished by week 3.

Sun Tolerance -  I'm a pale specimen that rapidly turns pink in direct sunlight, but I avoid sunscreen where possible, preferring to cover up with clothing. We went for an impromptu walk during week 3 and although I had a hat, was only wearing short sleeves and the sun was pretty intense so I was expecting some arm heat and redness. This didn't eventuate as I thought it would, but maybe the sun UV wasn't as strong as I thought,

Skin itchiness - Occasionally my skin will have a very mild itch, which is noticeable but easy to ignore. This seemed to disappear in week 3.

So What Next?

As mentioned above, this had many benefits so I'm keen to work this into my regular eating. I'm thinking more of a zero carb, animal based diet, but with plant based herbs, spices and seasonings, for most of the week, then maybe a bit less rigid over the weekend to cater for some cooking enjoyment. Restricting treat foods to special occasions, which I had been doing but slipped a bit over Christmas. Hopefully this gives similar health outcomes while allowing me to scratch the mental itch around food preparation and enjoying different foods. Time will tell

Windows Missing Taskbar Icons Fix

 For a while I've had the problem that apps like Outlook and Teams have been missing their icons on the taskbar when running. I tried the recommended fixes of deleting icon and thumbnail caches but none of these worked so I just put up with it, as the apps were still running and hovering over the space on the taskbar brought up the preview so I could tell which one it was.

Finally got tired of it when I started getting more apps from Windows Store and realised that all of these were affected in the same way. Popular Internet solutions were:

• Uninstall Google Drive
• Create a new user profile and copy all personal items from the old profile to the new one
• Reinstall Windows 

Google drive wasn't installed, and migrating profiles sounded painful. Windows reinstall was never going to happen. 

Thanks to an Internet stranger the fix was actually pretty straight forward.

• Open regedit
• Navigate to HKEY_CLASSES_ROOT 
• Expand .png key
• Delete any keys under the shellex key
• Restart explorer

 

That was it, all of the taskbar items are back and harmony is restored

 

Carnivore Week 2 Thoughts

 Week 2 of the carnivore "experiment" was less strict than week 1, with the addition of eggs and other meat sources - mainly chicken, fish and organ meats.

So how was it? 

Adding in the extra foods made the week a lot more enjoyable. Where I think I could do the lion diet if I needed to, I could happily largely stick to what I ate during week 2 for a reasonable duration. 

Most of the results were along similar lines to week 1, however meal prep was more interesting and the meals themselves were also less boring. I have still been avoiding herbs and spices, with the exception of some pepper this week

The Detail

Weight loss continued though at a much reduced rate, around 1/2 kg down this week.

TMI section. Reduced frequency of bowel movements continued this week, even though it felt like I was eating more. Still no constipation symptoms, but it still just seemed like there was significantly less waste product to be moved. When movements do happen they're quick and easy, so all in all this seems like a pro.

Intestinal "unrest" has quietened down and isn't noticeable. This could be due to the added variety or just that I've adapted

The Cheat

Well, sort of. We were out away from home one night towards the end of the week and decided to grab some Maccas for dinner, which is not something we have very often at all. I expected some bloating/discomfort and there was a bit of that, but I also noticed that my nose started a slight run which made me realise I'd been blowing my nose a lot less frequently during the past week. I'm usually good for a nose blow to start the day but hadn't been doing that for a few days. Something to look into. I also found it not as enjoyable to eat

Week 3

So onto week 3. This week will include dairy (super exciting!) and basically anything that comes from an animal.

 

Carnivore Week 1 Thoughts

 Week 1 of the carnivore "experiment" done, completed on a diet of beef and lamb, with water, black coffee and salt the only other ingredients.

So how was it? 

All in all not bad. Here's the tldr; more detail below

Pros

Meal prep is very easy

Weight loss

Better energy

Food consumption awareness

 Cons

Slight intestinal discomfort 

Less bowel movements

Boring

Difficult if not at home

Slightly anti-social 

The Detail

While it appears there's more cons that pros, the pros are pretty significant and the cons pretty minor. The most noticeable effect was weight loss - 3kg for the week. Now most of this is probably fluid but this is still significant. It's highly likely that I wasn't eating as many calories as usual, but I definitely didn't get hungry as often. I usually do an OMAD type fast most days anyway and continued that this week.

TMI section. Related to this (I think) was a very reduced frequency of bowel movements. Not constipation,  as there were none of the symptoms of this, but it just seemed like there was significantly less waste product to be moved. This could be a pro but was a bit disconcerting to start with.

There was also some slight intestinal unrest. Very minor and I'm guessing this will disappear as my body adjusts.

Energy levels seemed a bit more consistent - not necessarily more energy, but less fluctuations during the day.

Especially on the first couple of days I became aware of how often I opened the fridge. I wasn't hungry, and didn't get any cravings so this was just a habit

Probably the hardest part of this was that, for me, this was quite boring. I like different foods, and cooking, so just prepping and cooking like this soon became a bit boring, though it did free up some time to do other things.

Week 2

So onto week 2. This week will include eggs and other meats, so a bit more variety which is exciting.

 

Carnivore Experiment

 Something a little different, and not tech related.

I've been seeing a lot of stuff online about the carnivore diet, and on initial reading it makes a lot of sense nutritionally and biologically. The carnivore community can be a little intense, like most of these sorts of communities, so I haven't dived too far into those, but figured I'd give it a short trial to see what it's all about.

We tend to eat a reasonably low carb diet anyway, though Christmas and New Year certainly didn't stick to that, and I don't have any significant health concerns that I'm trying to heal, which seems to be a common reason for switching to carnivore.

Before starting out though I thought I'd get some basic, baseline, tests done then depending how it goes repeat them at the end. My primary comparison is against the markers for metabolic syndrome (https://healthify.nz/health-a-z/m/metabolic-syndrome/), rather than the seemingly more common focus on total cholesterol.

Diagnostic Tests

Cholesterol

 Total cholesterol is at a point where doctors would be reaching for the statin prescription pad, but that won't be happening so I'm not concerned about that. In my case HDL was higher than recommended and triglycerides were lower (both good) so I'm not concerned with this

Blood Pressure

Blood pressure was a bit high but I had just had coffee and walked down some stairs. Something to keep an eye on as this is a risk factor for metabolic syndrome.

Weight/BMI/Waist Measurement

BMI is a bit on the high side, but waist measurement is lower than the marker, so again not too concerned. Weight has jumped a little over what I like over the festive season so this coming down would be a good thing. 

Blood Glucose

This one was also fine, though towards the higher end of fine so something to keep an eye on 

The Test

First off, this is not very scientific and also not very intense. The plan is to spend about 3 - 4 weeks eating this way to see how physically and mentally sustainable it is, and if there are any immediately noticeable effects. I acknowledge that there's often an adjustment period and effects might not be noticeable immediately, but I'm just looking to see what the lifestyle is like. Also, I'm not cutting out coffee! So here's the plan:

Week 1 - Basically the Lion diet. Red meat from ruminants, salt and water (and coffee). This is the most intense form of carnivore so jumping straight in will give an idea of the mental requirements of this style of eating.

Week 2 - More in line with a standard carnivore diet. Other forms of meat and eggs. I'm going to keep avoiding dairy and processed meats like bacon and just see how introducing some small changes impacts the mind and body

Week 3 - Adding dairy. This will be a good test to see if dairy does have any negative impacts

Week 4 and beyond - Who knows? I might decide enough's enough after 3 weeks, or could be a full on, intense, convert

 

So, onto the test. Just a note that the area I live in has a strong agricultural sector so all red meat we buy is grass raised and generally local within about a 100km distance so is all relatively low carbon footprint

SSIS – Unicode data is odd byte size for column. Should be even byte size

I'm posting this here as I always forget what the fix is when I run into this error. Massive thanks to LSDBTECH for this fix.

When using a script component in SSIS with a Unicode text stream (DT_NTEXT) output column to an NVARCHAR(MAX) database column you can hit the error:

Unicode data is odd byte size for column <column number>. Should be even byte size

This usually happens because the method for adding the large content takes a byte array argument, and the common way to convert the string to a byte array is using Encoding.UTF8.GetBytes function. The blog post linked above has this explanation:

 “Notice that compressed Unicode strings are always an odd number of bytes. This is how SQL Server determines that the string has actually been compressed, because an uncompressed Unicode string—which needs 2 bytes for each character—will always be an even number of bytes” Source

The fix is to use Encoding.Unicode.GetBytes instead. Here's a code snippet, in this case reading an Azure blob

... (Azure connect stuff goes in here)

var content = blob.DownloadText();

BlobPropertiesBuffer.AddRow();
BlobPropertiesBuffer.Name = blob.Name;
BlobPropertiesBuffer.LastModified = blobProperties.LastModified.Value;

// This next line will cause the error "Unicode data is odd byte size for column 3. Should be even byte size"
BlobPropertiesBuffer.Content.AddBlobData(Encoding.UTF8.GetBytes(content));

// Instead use this line
BlobPropertiesBuffer.Content.AddBlobData(Encoding.Unicode.GetBytes(content));

Also noting that adding to the output buffer column for nvarchar(max)/text streams needs to use the function AddBlobData rather than assigning the value to the column which you do for strings and numeric values (see snippet above)

Azure Blob Container SAS Key Authentication Error

 Setting up Azure blob container access with a SAS key recently and kept hitting a wall with authentication errors trying to list the container contents. The SAS key had read and list permissions, but even granting full permissions kept coming back with the error

Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature.

I was testing this using PowerShell Get-AzStorageContainer, and this was the problem. Getting the storage container needs a SAS key generated at the storage account level, changing the call to use Get-StorageBlob without specifying a blob name allowed the requests to succeed with the read and list permissions.

Here's the full test code as an example (actual resource names removed)

$storageAccount = "my-storage-account"
$container = "my-container"
$sas = 'sp=rl&st=2024-04-08T04:56:21Z&se=2024-04-08T12:56:21Z&spr=https&sv=2022-11-02&sr=c&sig=sig'

$context = New-AzStorageContext -StorageAccountName $storageAccount -SasToken $sas 

# this line fails with a 403 authentication error
#(Get-AzStorageContainer -Context $context -Name ci-finance-samples -)

# this one will succeed and output a list of blobs in the container
Get-AzStorageBlob -Context $context -Container $container

Visual Studio Build Error - CS0006 Metadata File Could Not Be Found

 Interesting error in Visual Studio when trying to build a project

CSC : error CS0006: Metadata file '<project_path>\packages\Microsoft.IdentityModel.7.0.0\lib\net35\microsoft.identitymodel.dll' could not be found

This was repeated for every referenced dll in each project in the VS solution.

The problem turned out to be a problem in the solution folder name. The folder name had a space in the name, but when cloned from AzureDevOps the cloned folder name used the html encoded version of the name to create the file system folder, putting %20 in place of the space. 

In the build errors the name has the space rendered correctly in the output log, however going into the console and trying a dotnet restore command gave error messages that showed the paths that the command was actually looking for

C:\Program Files\dotnet\sdk\5.0.407\NuGet.targets(290,5): error MSB3202: The project file "C:\Users\bob\source\repos\My Project\Test\Test.csproj" was not found. [C:\Users\bob\source\repos\My%20Project\Test.sln]

So after much head scratching the fix was simple, close Visual Studio and rename the solution folder to replace %20 with a space.