Migrating data from Access to SQL Server

Suppose that you have a simple Microsoft Access database that you need to migrate to another database such as MS SQL Server. You model the tables and relationships in the new database, and now you need to export the actual data from Access and import it into the new database. This is how I did it:

I began by exporting the data as CSV. I found the easiest way was to simply use copy/paste. First though, open each tables in Design View. There is a tab named “Lookup” at the bottom. Ensure that any lookup field is set to display “Value List” instead of “Table/Query”. Then you can view the table normally and copy all data to the clipboard (Ctrl+A, Ctrl+C) and paste it into a .csv text file saved with UTF-8 encoding.

Next I used PowerShell to transform the CSV data into to SQL statement. While doing this I also adapted the structure of the data. For example, some fields were renamed, and I also had multiple fields that I wanted to merge into one. Here is a generic version of the script I put together that you can modify to your own needs:

Note: Normally you let SQL Server generate the ID of key fields. In this case though, we already have ID:s from the Access database. Since these ID:s specify the primary-foreign key relations, it is important to keep intact. To allow insertion of keys like that, IDs SET IDENTITY_INSERT Chemicals ON  is used.

Finally, open the generated SQL script (provisioning.sql) in SQL Server Management Studio (or similar) and run it to provision your data!

Posted in Development, Tutorials | Tagged , , , , , , | 1 Comment

Applying SharePoint Retention Labels Programmatically

So you’ve created and published Retention Labels in Office 365 and now you want to apply labels by CSOM code. It’s not very well documented. The following (crude) code should help you get started applying labels to both individual items and to set as default for the whole library:

First some boilerplate initialization…

Get all labels available on the site:

Get the default label for a list:

Get labels for all items in a list:

Write label to a list item:

Set the default label for a list:

SetComplianceTag and SetListComplianceTag have parameters (isTagPolicyHold, isTagPolicyRecord and isEventBasedTag) that are not documented what they actually do. Above I have set them to false. If you know what they do, please leave a comment!

Posted in Development | Tagged , , , , , , , , , | 4 Comments

If Snipping Tool freezes and blocks your screen…

Note: Be sure to read the comments for more help!

Today when I used the Snipping Tool in Windows 10 it stopped working. I could select an area over and over again, but it never closed after that as it should. The screen was dimmed and I could not get to any other program because Snipping Tool was on top of everything, even Task Manager! I almost gave up doing a hard reset, when I found a way to get rid of it:

Alt+Tab still worked though. There is a small X that appears if you hover an application thumbnail with the mouse. Using this you can close Snipping Tool!


Posted in Tips | Tagged , , | 77 Comments

How to create searchable user profile properties in SharePoint Online

In this post I will go through the steps of creating a custom user profile attribute that is searchable and shows on the user profile in Delve. It’s not very hard but involves quite a few steps to keep track of. This is written for SharePoint Online, but the basics apply to SharePoint 2013/2016/2019 as well.

Add a new User Profile Property

Go to SharePoint Admin > User Profiles > Manage Users Properties then click on New Property. Configure the basic settings such as names and type. (Remember that you can’t changes the type after it has been created)

To make the property show up on Delve profiles, ensure the following settings:

  • Default Privacy Setting: Everyone
  • Show in the Profile Properties section of the user’s profile page: Checked
  • Show on the Edit Details page: Checked
  • Indexed: Checked

Ensure the field is crawled

Now we need to wait until the property has been crawled by search. This will not happen unless we fill out the field on at least one user profiles. So start by editing a profile or two.

Then go to SharePoint Admin > Search > Manage Search Schema > Crawled Properties  and wait until your new property shows up. Have patience as this may take a little while (anything from 15 minutes to four hours in my experience).

Map to a managed property

Go to SharePoint Admin > Search > Manage Search Schema > Manage Properties. Create a new Managed Properties with the following settings:

  • Type: Text
  • Searchable: Checked
  • Queryable: Checked
  • Retrievable: Checked
  • Click Advanced Searchable Settings and select PeopleIdx in the popup window
  • Click the blue button “Add a Mapping” and find the crawled property we just created

Finish up

Now we wait again to let search crawl pick up the new managed property. This may take quite a while, and you never know when it happens in SharePoint Online. I typically leave it during the night and check in the morning…

If you have done everything correct, you will now have a custom user property that you can freely search for all over Office 365 to find people, and that you can view on peoples Delve profiles!

Notes & tips

  • If you have business requirements to only allow a limited set of values to enter into the property, you can create a term set in the Term Store with allowed values. Then, when creating the new property, select string as type and tick the “Configure a Term Set to be user for this value” checkbox.
  • The properties you create are visible in Delve under “Additional Information” at the bottom of user’s profile pages. If you allow users to edit the properties themselves, they can do so on the SharePoint Profile page. To find it, go to https://tenant-my.sharepoint.com/_layouts/15/editprofile.aspx
  • Managed properties that you create yourself in SharePoint Online can not be sorted or refined in search results. If you need to do this, you can add another managed property, but instead of creating it yourself use one of the predefined RefinableString properties.
  • To easily update properties in multiple user profiles, check out the Bulk User Profile Update Api for SharePoint Online.
  • There is no way in SharePoint Online to control when search will crawl user properties. This script may help ensuring that your user profiles are re-indexed: https://github.com/wobba/SPO-Trigger-Reindex
Posted in Tutorials | Tagged , , , , , , , , | 1 Comment

Popup opens as new browser window in Edge and Internet Explorer

Problem: Opening a popup with JavaScript in Edge or Internet Explorer results in a new browser window instead of a popup window. For example:

Communication between the new window and originating site is not possible either, since both popup (above) and window.opener (in the new window) are undefined.

Solution: This can happen if the originating site’s URL and popup URL are not in the same security zone. Ensure that both are in the Trusted Sites zone in Internet Options!

Posted in Development | Tagged , , , , , | Leave a comment

How to quickly get the Group ID for an Office 365 Group

Microsoft Graph typically requires us to use the Group ID when working with O365 groups and modern sites. But Group ID can be tricky to find since it is rarely displayed. Here are some ways I found to quickly get hold of it during when experimenting with MS Graph:

1. The group ID is made available on our trusty old friend _spPageContextInfo  in the group’s SharePoint site. Knowing this we can easily find it by going to the site and type _spPageContextInfo.groupId  in the JS console. _spPageContextInfo is however not available on modern pages, but you can go to a page that is not modern such as Site Settings to find it.

2. Using Graph Explorer we can query for the group. By default mailNickname is used for the site URL. So if the site URL is  https://tenant.sharepoint.com/sites/MyTeamSite  then GET  https://graph.microsoft.com/v1.0/groups?$filter=mailNickname eq 'MyTeamSite'&$select=id  will give you the Group ID. Note that mailNickname is  not guaranteed to be the same as the site URL, for example if you have changed the site URL.

3. Group ID is also hidden deep in the page source of modern sites. To find it you can view the page source (CTRL+U) and search for spPageContextInfo. Triple click the paragraph you found to select it, copy the paragraph to the clipboard, open the JS console and paste it between two brackets { CTRL+V } and press enter. Now you can easily inspect the object and find the group id.

4. We can actually get to the page context object on modern pages too, but it is hidden deep down: window.spModuleLoader._bundledComponents["b6917cb1-93a0-4b97-a84d-7cf49975d4ec"].PageManager._instance.pageContext.legacyPageContext.groupId (FYI: The GUID here refers to the web scoped feature for “modern” pages.). I made a bookmarklet to make it easier to access:  javascript:void%20function(){alert(window.spModuleLoader._bundledComponents[%22b6917cb1-93a0-4b97-a84d-7cf49975d4ec%22].PageManager._instance.pageContext.legacyPageContext.groupId)}();

Posted in Tips | Tagged , , , , | 2 Comments

Excluding external users from search results in SharePoint Online

Search in SharePoint Online returns both internal and external users by default. This may  not be desirable. Here’s how to exclude external users from the search results.

External users have account names containing #ext# which makes them easy to filter out. Typically when you search for people you will query against the Local People Results result source. In SharePoint Online we can’t modify this result source like On Premises, but we can clone it and add our own custom filters:

  1. Open SharePoint Admin
  2. Go to search settings and select Manage Result Sources
  3. Copy Local People Results (click the down arrow to find the copy command)
  4. Edit your new result source and find Query Transform
  5. Add  -AccountName:"#ext#"  to the query
  6. Verify that it works using the Query Builder
  7. Save

Remember that you will need to configure search pages and other solutions to use this new result source instead of the default Local People Results!

Posted in Tips | Tagged , | 2 Comments

Try this if sound in Windows stops working

Several of my computers have had problems with sound suddenly not working. Typically this seems to happens when switching context, such as going from plugged in to battery, but sometimes it seems random. At work we use Skype for Business. When it encounters sound problems it tends to freeze up. I’ve found the following to work at restoring sound (and S4B) again:

  1. Right click on the sound icon in system tray. Select “Playback Devices”.
  2. Double click on “Speakers / Headphones”. Go to the “Advanced” tab in the dialog that opens:
  3. Click on “Restore Defaults” or the “Test” button:

Depending on the problem you have, one of these buttons tends to restore sound playback again!

Posted in Tips | Tagged , , , , , , | Leave a comment

Enable Mometum on an Alps Glidepoint Touchpad

My old computer has a touchpad from Synaptics with a feature called “Momentum”. (I’ve also seen this called “Inertia” och “Inertial Scrolling”.) It causes the mouse pointer move a little bit before stopping after you’ve released your finger, as if friction is slowing it down. I find this makes using a touchpad more enjoyable, since it allows you to flick your finger when moving the mouse.

My new computer came with  an Alps Glidepoint Touchpad. I was disappointed when I could not find any momentum settings in the settings. So I started digging through the registry to see if I could find some hidden settings, and I did!

Simply find this key using RegEdit and set it to 1 :

You can easily test the new setting by toggling the touch pad on/off in the settings dialog:

Sadly I find that the Alps implementation of momentum applies too much “friction”, making the mouse stop too fast. I have not been able to find any settings controlling this. If you do, please post a comment!

Posted in Tips | Tagged , , , , | 1 Comment

Stopping Software Center from restarting your computer

IT-departments may require you to restart your computer to complete updates they have pushed out. This is at best annoying, at worst it can ruin your work if you for example have a long running process in progress. (My personal favorite is when the company forced me to restart my computer in the middle of a service window when I was upgrading a system for said company…)

Note: Skip to Update 2 at the bottom for the latest working method!

If your company uses System Center you may recognize the following dialog informing you that Your Computer is About to Restart:

You can’t close this window or ask it to stop the shut down. So what can you do? I’ve seen others talk about shutting down Windows Services. That never worked for me, but I discovered that there was an easy way to stop the process. Simply open a command prompt and type  shutdown /a

This will abort the shutdown:

The countdown window will still be open, but when reaching zero nothing will happen!

Now you can finish your work at your own pace, before restarting your computer.

Note: If it does not work and you get a message that there is no shutdown in progress, try again later. I’m guessing that the shutdown command is not issued until 15-30 minutes before it should go off. Let me know in the comments if it works.

Update: Since the update window is “always on top” and can’t be closed it blocks your view in a pretty annoying way. Luckily we can use a bit of PowerShell and WinAPI to to hack the window properties to hide it :-)


Update 2: Above does not work for me anymore. But I found that you can simply stop the service:

  1. Open Services (run -> services.msc)
  2. Find “SMS Agent Host”
  3. Open it and click “Stop”

Also run shutdown -a  to make sure your computer does not restart itself.

The shutdown notification should go away. It might return after a while, but you repeat the steps above over and over.


Posted in Tips | Tagged , , , , , | 37 Comments