Wednesday, 18 March 2020

MS SQL Collation Issues

Fixing MS SQL Collation Differences

By Strictly-Software

With the quiet due to the COVID19 virus scare, and all major sporting events being cancelled for the foreseen future, I decided now would be a good time to get my old Automatic Betting BOT back up and working.

It used to run on a dedicated Windows 2003 server and MS SQL 2012 database before being outsourced to a French hosting company, OVH, which doesn't allow HTTP connections to online betting sites, basically making it useless. A decision that wasn't mine in anyway, but basically stopped my BOT from working.

It used to run as a Windows Service using Betfair's Exchange API to automatically create betting systems and then place bets based on systems with a rolling ROI > 5% over the last 30 and 100 days.

I am trying to get the BOT working on a local laptop and therefore it had already been backed up, the files FTP'd down to my laptop and restored in a local MS SQL Express database. 

However it seems that I had some issues when I previously attempted this some time ago as the database now had a different collation, a number of tables were now empty and had been scripted across without indexes and there was a collation difference between many columns and the new DB collation.

Just changing the Databases collation over isn't a simple act on it's own if numerous table column collations are different as well, especially those used in Indexes or Primary Keys as those references need to be removed before the collation can be changed.

A simple fix when it's only a small issue with one column might be to just edit the SQL where the issue arises and use a COLLATE statement so that any WHERE clause or JOIN uses the same collation e.g

SELECT  MemberID, MemberName
WHERE MemberName COLLATE SQL_Latin1_General_CP1_CI_AI = @Name

However when you have a large database and the issue is that half your tables are one collation, the other another, and you need to decide which to move to, and then it becomes more difficult.

My new Database was using the newer SQL_Latin1_General_CP1_CI_AI collation whilst a subset of the tables were still using the older collation from the servers copy of the database Latin1_General_CI_AS.

Therefore finding out which columns and tables were using this collation was the first task and can easily be done with a system view with some SQL like so:

-- find out table name and columns using the collation Latin1_General_CI_AS
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'Latin1_General_CI_AS'
ORDER BY table_name, column_name

From this I could see that the majority of my tables were using the older collation Latin1_General_CI_AS and that it would be easier to change the database collation to this, then the table collation.

However as there were very few indexes or keys I decided to do the reverse and use the newer collation SQL_Latin1_General_CP1_CI_AI, and change all columns using the LATIN to this new version.

However as I want to show you what to do if you need to change your MS SQL database and columns over to a new collation just imagine I am doing the opposite e.g I am changing my whole system from SQL_Latin1_General_CP1_CI_AI to Latin1_General_CI_AS.

If you did want to change the database collation after it had been created it is not as simple as just opening the databases property window and selecting a new collation OR just running the following query to ALTER the database. Just read the error message I was getting when attempting this.

USE master;

COLLATE Latin1_General_CI_AS ;

Msg 5030, Level 16, State 5, Line 18
The database could not be exclusively locked to perform the operation.

Msg 5072, Level 16, State 1, Line 18
ALTER DATABASE failed. The default collation of database 'MyDatabase' cannot be set to Latin1_General_CI_AS.

Apparently this locking issue is due to the fact that SSMS opens a second connection for Intellisense.

Therefore the correct way to do this, even if you are the only person using the database, as I was, is to put it into single user mode, carry out the ALTER statement, then put it back in multi user mode. #

This query does that.




Once the database has been changed to the right collation you will need to change all the tables with columns using the differing collation. However any columns being used in Indexes or Keys will need to have their references removed first. Luckily for me I didn't have many indexes at this point.

However I still needed to script out any Indexes and Key Constraints I had and save them into an SQL file so that they could easily be re-created afterwards.

I then dropped all the Indexes and Keys that needed to be removed and then ran the following piece of SQL which outputs a load of ALTER TABLE statements.

You may find like I did, that you actually need to add a COLLATE statement into the WHERE clause to get it to work without erroring as the system tables themselves may have a different collation than the one you are wanting to search for.

SELECT 'ALTER TABLE ' + quotename( + '.' + quotename( + 
       ' ALTER COLUMN ' + quotename( + ' ' + type_name(c.system_type_id) 
   WHEN c.max_length = -1 THEN '(max)'
   WHEN type_name(c.system_type_id)  = 'nvarchar' THEN '('+CONVERT(varchar(5),c.max_length/2)+')'
   ELSE '('+CONVERT(varchar(5),c.max_length)+')'   
  END + CASE WHEN c.[precision] = 0 THEN ' COLLATE '+c.collation_name   + ' ' ELSE ' ' END + LTRIM(IIF(c.is_nullable = 1, '', 'NOT ') + 'NULL ' )
FROM  sys.objects o
JOIN  sys.columns c ON o.object_id = c.object_id
JOIN  sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U' 
  AND c.collation_name <> 'Latin1_General_CP1_CI_AI' -- the collation you want to change from
ORDER BY o.[name]

You should get a load of SQL statements that can be run in a new query window to change all your columns.

It is best to run the previous SQL outputting to a textual window so that the output can easily be copied and pasted.

ALTER TABLE [dbo].[CANCEL_KILLED] ALTER COLUMN [RecordDetails] nvarchar(max) COL

Once you have run all these ALTER statements your whole database should be the collation that you require.

You can then take your copy of the Indexes and Keys that you created earlier, and run them to recreate any missing Indexes and Constraints. You should then be able to remove any quick fix WHERE clauses COLLATE statements that you may have used as a quick fix.

Collation differences are a right pain to resolve but if you do everything in order, and keep saved records of ALTER and CREATE statements that you need along the way it can be something fixed without too much work.

There are some large scripts to automate the process of dropping and re-creating objects if you want to use them however I cannot test to the reliability of these as I chose to do everything bit by bit, checking and researching along the way.

By Strictly-Software

Wednesday, 4 March 2020

Samsung Phones SmartView Application Bug

Fixing Problems With Samsung Phone SmartView Application

By Strictly-Software 

I have a Samsung Galaxy S8 mobile phone and recently I have had issues with the inbuilt SmartView application that allows you to mirror the screen of your phone to nearby devices such as Televisions, Rokus, Chromecasts and other Wifi enabled devices.

You access the app by using the swipe down settings panel at the top of the phone. It's on the second page accessed by swiping left and it's in the top right hand corner. See the below screenshot of my settings panel.

Whilst many applications that let you play videos have a "Play with external device" option that allows you to select the useful app AllCast before the device to then show the video on, some apps don't have a Chromecast beam square or option to play on an external device. 

This is where mirroring your screen is useful as you can just mirror yours phones screen to a TV and anything you do on the phone is then shown on the TV. The downside is that you can't use the phone or other apps whilst mirroring without your TV showing that app. However if you can use AllCast 
you can play a TV show, film or video from your phone on your TV but still use your phone to access other apps or make calls.

Up until last week everything worked fine, I could select SmartView and the apps little loading icon spins before local devices are shown to pick from. In my lounge I can pick from my Samsung 4k TV or the Chromecast device attached to it. This also allows me to beam videos from apps like YouTube that I don't want to mirror my phones screen to, or apps like sporting apps that don't have options to use AllCast to beam with.

In another room I have a Panasonic TV which also has a Roku attached and both could be used by AllCast or my phone to mirror to.The SmartView app used to be able to pick all 4 devices up and I could choose which TV or device to mirror my phone to.

However for some reason as I was watching a football game by mirroring my screen so the app could show the game on a big screen I left my pad. The connection was disconnected but when I came back I tried to use SmartView to re-connect it to my TV, but no devices were shown as available. In fact it didn't even look like it was scanning the room to find any devices to play to. Nothing was spinning in the corner and only the About Us and Contact us options were shown.

I filed numerous bug reports using Samsung's useful Members app and I had a reply that told me to go into System Settings, Applications, Select Show System Apps, choose SmartView then clear its cache. I did this but it didn't fix anything.

However tonight I came across a fix. I had to turn developer options on. You do this by going into Settings then About Phone and in there select Software Information before clicking on the Build Number about 7 times and it will give you the Developer Options.

In there you scroll down to Networking and turn on the option called "Wireless Display Certification"..

I then went into SmartView and it started spinning again, however a new panel appeared at the bottom with numerous WiFi connection options that I just ignored.

I then just scrolled to the bottom of these options and there appeared my selection of devices, in this case my TV and Chromecast.

And on choosing one, once again my phones screen was now mirrored to my TV.

I have no idea why this worked or what stopped SmartView from working in the first place but by doing my own debugging and playing around with the networking options I managed to solve what Samsung Tech Support couldn't. 

Hopefully this fix may help other Samsung phone users who have the same issue as me.

By Strictly-Software

© 2020 Strictly-Software

Friday, 24 January 2020

Making It Hard For Humans and BOTS to use SELECT Inputs

How To Ensure A Human Has To Select From A List Rather Than Just Hitting A Letter, Word or Number

By Strictly-Software

I noticed something the other day when logging into Halifax my bank, when you get to the memorable information form, you have to select 3 letters picked by random from your word

They will have 3 List boxes and they will pick 3 positions in your memorable phrase to select e.g letter 4, 7, 15.

I used to be able to just tab though the list boxes and hit the right letter or number and get it selected but no more.

At first I thought it might be something to prevent BOTS from using the form, and preventing bank fraud etc but when I inspected the SELECT inputs they used I noticed they were doing something else,

They have just put non breaking space entities in front of all the values and options e.g

<option value="&nbsp;a">&nbsp;a</option>

So they have just put a space character in front of the value and in the viewable list. However they haven't used a space as in a " " gap, they have used an encoded space as quick fingers would easily be able to select the box and hit the space bar and the letter to get to their value.

However it's not so easy to write out a whole &nbsp; character and the letter to select it without using your mouse.

I don't know how this would effect BOTS however as someone who examined their HTML code could easily write something that passed 3 letters to the server on submit with a non breaking space in front of them e.g &nbsp;a.

However it certainly makes sure that a human actually pays attention and USES the select box with a mouse rather than tabbing through.

If anyone can see how this increases security on Bank forms let me know as seems to not prevent BOTS - unless automated and those who don't know where they are from attacking a page. It might mean the rewriting of a generic intrusion tool to handle list boxes like this but that would be easy.

It just seems to make it harder for legitimate users from using their fingers and not the mouse from what I can see. However it does make sure you are more careful when entering the values. So maybe that is all they wanted to do.

If anyone can see any other reasons why putting a non breaking space character in the list box options is a good idea let me know please.

By Strictly-Software

© 2020 Strictly-Software

Sunday, 29 December 2019

Removing Chrome Has Sped Up My PC

How Removing Google Products Can Speed Up Your Laptop

By Strictly-Software

If you read my other recent pieces on speeding up my PC you might be interested in the fact that the other day I removed Google Chrome and now my PC is fast as road runner.

If you ever look at the task manager when you have 5+ tabs open in Chrome then you will see 5+ Google Chrome processes running. Their idea was that each tab of Google Chrome would run in it's own process so as not to interfere with the running of other tabs that maybe open.

Not only does that not seem to be the case in my situation, a slow tab will cause high memory, high CPU and DISK usage which then effects the other tabs, therefore I removed Google Chrome altogether and now just use Firefox, Opera and TOR Browser.

Just look at my task manager processes now that I have removed Chrome. Whilst I was suffering 100% DISK Usage, and high CPU and memory I am now showing only low numbers.

Currently as I write this article with 7 tabs open in Firefox including Facebook a heavy hitting site due to all the Ajax it uses and Betfair which updates constantly I am still only using 4% CPU, 54% Memory, 9% Disk Usage.

Not only has removing Google Chrome from my laptop has sped it up but if anyone has been paying attention, Google is an evil company (they had to remove that it didn't do evil from their motto), that is hooked into nearly every smart phone measuring when you sleep, walk, drive, the current temperature as well as listening to everything you say just in case it wants to be helpful.

I cannot count the number of times that I have been watching a football match streamed from my phone to my TV and then Google pops up to ask "What would you like to know about Liverpool" or some other nonsensical question I didn't want it to help me with in the first place.

This obviously means it is listening to you ALL THE TIME, otherwise it would not know when to offer help.

If you read the Terms and Conditions on your smart phone then you will see under privacy, a mile down the long winded notes, a section about how they are allowed to listen to you and send off to "unspecified 3rd parties" content that "may" help improve their service.

These unspecified parties maybe Google AdSense to show more relevant adverts by listening to the surrounding sound around the phone and then translating that to adverts you will see online. Or it maybe listening for more illicit speech that it can send local police enforcement or even the numerous 3 letter agencies in the UK and USA.

Will Binney from the NSA who was arrested for going public about the Prism program he had written and Edward Snowden had stolen to expose, told us about how he had designed it for spying on the Soviets in communist USSR. However once the cold war was over they flipped the script and used it to spy on US citizens illegally instead. 

If you read up on the web tech companies that were first involved in the unknowing spying on users, then they were Microsoft, Yahoo, AOL, Facebook, Google, Apple, PalTalk, YouTube, and Skype.

The UK was used as a test bed due to our lack of a real bill of rights or constitution and therefore we here in UK are rats roaming a maze designed by the NSA all so they can catch the right words and tell the correct authorities if need be.

The main problem is that you cannot remove Google products from Android phones and trying to do so will probably break your phone. Google is ingrained into most smart phones and almost impossible to remove totally. If you want an app you go to Google Play Store and trying to remove that from your phone would be a huge hassle.

Therefore I have done the logical thing due to the slowness of my PC and removed Google Chrome from it. Not only has it sped up my laptop but it means Google searches are no longer logged and kept for years as I now use DuckDuckGo or TOR Browser to surf the net. 

Even the browser Opera has a mini "VPN" built into it which goes through a proxy computer before the site you wanted to visit, hiding your real IP address, and making you look like you are in Germany or Bulgaria. 

Just try Opera and then go to this site and see what headers and IP address you are showing to websites and servers.

In another tab either type in the search box "Where am I now geo-IP" or just go to this link and you will see information like the results below when I just tested it with a new instance of Opera. My real IP is 86.164.1.XXX by the way.
However viewing my IP address on Opera with that search term or a header search reveals my location to be elsewhere from South East England. In fact they show me to be in the Scandinavian region of Europe with an IP address of

Their Geo-IP location data is complied from 3 sources, IP2Location, and they all use my Opera proxy IP of, and put my location in either Norway or Sweden, in either Oslo or Östergötland.

If you want more details on why Google is evil and Youtube is broken then read and watch the video on this blog. 

However if you don't care about Google working with the NSA and GCHQ and listening to you all the time through your phone and just want a faster PC, try removing Google Chrome from your machine and using another browser instead.

Remember to keep your task manager open to see if the DISK, CPU and Memory levels drop after a restart and a clearing of your registry and unused objects with CCleaner.

By Strictly-Software

© 2019 Strictly-Software

Sunday, 15 December 2019

Browser Slow Down May Have a Windows Cause

By Strictly-Software

Yesterday if you read my blog article about Firefox and Chrome slowing me down as if I was being dragged through mud by a 3 legged donkey, then you will know that I was blaming the browsers and their settings for the slow down.

However when I could take no more, of browser windows hanging and then seemingly Windows applications not opening when I clicked the desktop icon which I have never had before I thought it must be a virus or some sort of app slowing me down however virus scans with Windows Defender and MalwareBytes AntiMalware showed nothing at all.

I was trying to open applications like Open Office only to see the process in the task manager running as a background process. It was not up the top under Apps, but just sat there forever under background processes.

I also had 100% Disk usage with no singular app or process looking like it was causing it. I also had high memory and CPU usage. So I thought what I had done lately. I went to Programs and Features to see which applications had been updates or loaded recently. Skype was there, like it is today, and I removed it as I can no longer use it with Windows 8.1r.

There was also Spotify which I hardly ever use so I uninstalled that as well. However it was after this I started getting the problems.

So I went to System Restore Points and noticed a Critical Windows Update had been carried out on the 11th of this month. I ticked the box to show all restore points and chose the Automatic Restore Point before the update.

I did a system restore to that date which was 2 days before the Windows Update and after an hour I had my PC back and working. It was much faster and the browsers were fast as well, both Chrome and Firefox.

However I will not take away the points I made yesterday about over complicating the options and issues for privacy and security which seem far too over the top for the average user to understand.

So when I was finished with what I was doing I went to shut the laptop off and did the Windows Update again. This time it seems to not have slowed everything down. The Critical Update has been done and I have a much faster laptop. For example just look at the Task Manager, which for the same apps, with the same tabs open was showing 100% DISK usage and high Memory and CPU usage, with programs running in the background not as open Apps.

However I am not holding my breath as it seems that all these browsers and other apps like Rapport (banking crud software), seems to be slowing the laptop down.

I think it is time I do a proper search of the computer, remove apps I don't use, run Chkdsk -f (from the command prompt), and see if a particular browser is causing me issues.

At the moment I have 5% CPU, 40% Memory 3% Disk usage (Not the 100% I was seeing all the time).

I have no ideas what the Windows Update did to screw this up and when I look at programs recently installed I only see Google Chrome as having updated itself today. Firefox is set to auto update so I will wait to see what happens when the latest version comes in as I am currently on version 70, and yesterday I was on version 71. Since Chrome updated however the CPU and Disk Usage also started to spike again.

I have decided to uninstall Chrome and just use Firefox and since doing so my disk usage is running at 1%. I have no idea what Chrome could be doing to cause these memory leaks and disk usage. I did notice in their settings under advanced the other day an option to look for "malware" that could be slowing your computer down. I reckon it is more to do with all the holes that let PUPs and other files that keep popping up after a scan in the Chrome Roaming folder that they are looking for.

However it in not uncommon for people to have issues after Windows Updates. User Profiles going missing causing you to think you have lost all your apps and other weird things. Having to do restore points seems drastic but at the moment I am happy as my apps are all running and the task manager is showing good stats. However I still think Firefox's security and privacy options are too much for the average user and some of them even confused me as their was no explanation. So read that article if you can.
I think these auto updates behind the scenes can cause major slowdowns and they should all be optional. If I have a version of Chrome that is running fast, not causing major CPU, Memory or Disk usage then I should be able to keep it. I don't want some new buggy install being rolled out and then a fix for that buggy version rolled out later.

As I said earlier IE only brings a new version of IE out once every couple of years. I cannot really see the need for 70+ versions of Chrome or Firefox unless they contain bugs and future version are to fix them.

By Strictly-Software

© 2019 Strictly-Software

Thursday, 12 December 2019

Browsers New Automatic Settings Slowing Site Loads Down

Blocking All Social Media Cookies and Trackers Seems To Be Slowing Down Chrome and FireFox

By Strictly-Software

I have had recent automatic updates for Firefox and Chrome to versions

Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:71.0) Gecko/20100101 Firefox/71.0


Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36

How these two have got to versions nearing 80 so quickly is funny, when IE just rolls out a new version of it's browser every year or so not every time I try and open Firefox from my taskbar.

However these updates seem to contain some important settings, some may have been around for a while which I just haven't noticed. However it is the slowness of these browsers compared to Opera, that uses a proxy server in it's pretend VPN, so you are actually going through 2 servers to your site compared to the other browsers, that is doing my head in.

I liked Firefox, and Chrome when it first came out for their speed, and add-ons. However either my laptop is either deciding to slow down these 2 browsers for some reason and let Opera hop before loading a faster page or something else is going on.

My version of Opera with this "VPN" is:

Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36 OPR/65.0.3467.62

When I open it, it is faster than both Chrome and FireFox despite their VPN which when I check with a geo location website shows I have these details instead of my real ones:
  • Your Public IPv6 is: 2001:67c:2660:425:7::dfa
  • Your IPv4 is:
  • Location: Amsterdam, NH NL 
  • ISP: Hern Labs AB
I notice they have removed the word Opera and replaced it with OPR now, also nearing it's 70th edition. So not only does it protect my privacy a bit it is faster than Chrome and FireFox at the moment for me.

So today after upgrades to both Chrome (manual due to it's insane slowness) and an automatic update for FireFox that took almost 20 minutes, I noticed major slowdowns.

Chrome seems to always be showing a "resolving host" message in the status bar and loading in remote scripts from the big 3 spyware social networks, Twitter, Facebook and Google.

Of course site builders have put these outbound scripts into their code as they want people to Like, Follow and Tweet whatever crap they are selling and you may like seeing a Twitter scroller on the blog you are following and the ability to share the page to Facebook.

However I watched a video on the other night about how many #alttnews sites are moving their videos from YouTube to BitShute. YouTube is broken and Google is an evil company. And it seems they are indeed.

It is all about how these niche news outlets had created YouTube making it the biggest video sharing site online, and although the company claims they are only 1% of all videos watched they still feel the need to de-rank these alternative views and put "authoritative sources" above your searches in their algorithm tweaks. These tweaks were all admitted by a Google employee who described getting those Up Ticks and Likes as a "drug" that ensures people continue with their outpourings of every thing they do in life on Social Media.

Yes we do want to see where Jane is having lunch, who with, where the cafe is located and then everything else she does that day as we follow her goings about on Facebook and Instagram.Well you may want to but I don't. However to do so you need to load in Facebook scripts from their servers.

However it seems if you delve into the privacy and security settings for Firefox you get to see that their default setting is to stop tracking cookies from cross site and social media trackers which obviously means if you are loading a 3rd party script from another location you could see as I did today on one site the "trying to connect to" message appear dozens of times as the page tried to load. All the while the page was hung and unusable.

You can go into the FireFox settings and change your settings under Privacy and Security. The heading is...

Browser Privacy

Enhanced Tracking Protection

Trackers follow you around online to collect information about your browsing habits and interests. Firefox blocks many of these trackers and other malicious scripts.

The default setting will block Social media trackers, Cross-site tracking cookies, Tracking content in Private Windows and Cryptominers.

Obviously the latter few are definitely required but let me know if you have noticed a slow down with the standard setting that supposedly is "Balanced for protection and performance. Pages will load normally.", as they may load normally but they seem very slow to load, and off server scripts like Twitter and Facebook are attempted multiple times before a page is usable.

What happened to just loading the core code first to let the page be usable and load any off server scripts by Ajax in the background. It seems too many sites now use pure JavaScript and Ajax to load the content, probably to prevent content scraper BOTS however it does mean a lot of code has to run and be loaded before the page is usable. Have you had a look at the source HTML of lately?

Apart from some META tags after the HTML tag the whole source is JavaScript and probably Ajax to load in the content for what is really nothing more than a white page with a different image every now and then above a text input box for searching.

The links to your Google account and Gmail in the top right corner are just that links. We could shorten the load time and the code to a few lines of HTML in reality. I really think Google have gone overboard with their API Jizz all across their systems as their need to stop scrapers has just caused slow loading pages it seems.

Would you like all 3rd party scripts and cookies blocked, or would you like the site to work and load quickly? It seems a dilemma these browsers are making over complicated especially for non techies who wouldn't know half the words in Firefox's Privacy and Security settings.

The difference between Standard which says "Balanced for protection and security. Pages will load normally" and Strict which says "Stronger protection, but may cause some sites or content to break" seems to only be the addition of:
  • Tracking content in all windows - rather than standard mode which only blocks "Tracking content in Private Windows" and
  • Fingerprinters (blocking Browser finger printing, logging your add-ons, window size and other ways to identify you from just your browser)
They don't actually explain what a fingerprinter is, and to the average user they would be scratching their head thinking about their latest Samsung phone and the ability to login using your fingerprint. However these two extra blocks seem to be deadly for a working website as they state underneath :
Heads up! Blocking trackers could impact the functionality of some sites. Reload a page with trackers to load all content.
So god knows how someone is supposed to manage the 3rd option which is a custom way of blocking things you don't understand or know why they would break a site.

Of course they have a number of complicated Knowledge Base articles  for you to read and get your head round to try and understand whether they need to use Private Windows for browsing all the time, and why the prevention of loading certain features is going to stop your site loading.

Of course Firefox has a "simple way" to help you understand what is going on by just clicking on the shield in the address bar you can change the mode of protection on or off. 

You can view this site with "Enhanced Tracking Protection is OFF for this site" or ON and if you don't know what the difference is they have helpful little graphs that tell you about their Enhanced Tracking Protection, how many trackers they have blocked over the week and ways to look for data breaches. All very interesting but not very helpful information.

They helpfully clarify the situation by saying "Social networks place trackers on other websites to follow what you do, see, and watch online. This allows social media companies to learn more about you beyond what you share on your social media profiles."

Of course you could just disable 3rd party cookies and JavaScript by default with a web developer toolbar and see if the page loads or not. If it doesn't work turn on JavaScript and try again before white listing the site so it can use JavaScript again.

It seems that as Windows in numptifying the front end of their latest operating systems and making it harder for developers to dig in and get into the back end like Windows 8.1r which I still have - now without Skype support - the browsers are offering their users far too many options they probably don't understand or need to know about.

What I want from a browser is for websites to load quickly, any 3rd party hosted widgets like Facebook or Twitter widgets to load in asynchronously and not prevent the working of the site. I want the browser to do the dirty stuff behind the scenes and I don't want 100's of options to play about with. They should block dangerous content, warn users about dangerous sites and stop anything that may have a dangerous effect on my browsing or privacy.

Yes - Ask me if I want to load this soon to be outdated flash movie or allow notifications but don't give me too much to tweak about with.

The speed of loading a site is the most important factor for most users and also affects the sites SEO. If they want to give us an option for being as private as possible or allowing tracking cookies then just have a single option "Privacy HIGH or OFF" option, and then use their own browsing logic to work out if a page won't load instead of offering the user a whole list of options to try out if a page doesn't load.

What is wrong with just keeping incognito windows that are private as possible, don't allow trackers or fingerprinting and the logging of pages visited with a clear out of cookies automatically when I leave?

It just seems that as Chrome enters the laptop world with it's Chromebooks, that as Operating Systems continually ask for your admin password in Windows 10+ when opening an application. Hiding all the nitty gritty that really slows your PC down behind automated "maintenance jobs". That browsers are trying to become their own little PC within a PC.

Just give me fast loading pages and if I want to hide what I am doing from sites and other users of my laptop then make the incognito windows as private as possible. Stop trackers, fingerprinting, 3rd party cookies, and anything else you are now making a "choice" for the user under the settings.

It is bad enough that as everyone moves to HTTPS we see the TLS handshake message in the taskbar constantly which is obviously slowing down the loading of pages and their content, especially if it's mixed.

Just give me a fast browser. I thought using FireFox today would speed things up as Chrome is just getting unusable and as everyone realises they are actually evil, I don't want to help Google pass on my data from their browser or search engines and analytics trackers to advertisers and god knows who else.

From now on Opera with its extra server hop is going to be my standard browser. The "VPN" offers enough privacy and whilst some pages won't remember certain settings due to my location changing the browser is fast.

Anyone find their settings too complicated nowadays and the speed an issue?

By Strictly-Software

© 2019 Strictly-Software


Saturday, 11 May 2019

Getting SQL Server Information

Using TSQ to obtain MS SQL Server Information

By Strictly-Software

In the years gone by when we had relational database systems that worked across MS SQL Versions such as our DEV, DEMO and LIVE databases we sometimes needed to do a check to see what the Version of the server was before running the appropriate code.

Our DEV server might be pretty old compared to our LIVE server and code that would run on SQL 2005 wouldn't on 2000.

An example would be if the Server needed to know whether it could use a CTE and temporary view to return hierarchical information or whether it had to use a more basic stack style temporary table version.

For example the stored proc we would call on any server would be:

EXEC dbo.usp_get_workers_heriarchy

-- inside the stored proc we would have a function wrapped around the @@version code that would just return 2000,2005,2008,2012 etc
SELECT @SQLVERSION = dbo.udf_GET_SQL_VERSION(@@VERSION) -- returns 2000 or 2012

        -- use a more modern CTE and temporary view
ELSE -- handle everything under 2005 when CTE's and temporary views came out
        -- use old stack method for adjency 

So if the dbo.udf_GET_SQL_VERSION(@@VERSION) returned 2005, 2008, 2012, 2015 then it would use the most modern type of TSQL to handle returning a hierarchy whilst anything below that would use a stack version.

We would have to build this code up on an SQL system that handled CTES otherwise trying to save the stored procedure and the CTE would just cause invalid SQL errors as it wouldn't recognise the code.

So we built it on an SQL 2005+ machine and then had no problems as all calls were made from the front end by stored procedures and any machine over 2005 would handle a CTE or Stack response.

In the old days when we only had @@VERSION with very little text to parse it was pretty simple to just extract the SQL version from the return string, e.g remove 'Microsoft SQL Server ' or look for the first number after the word Server and LTRIM(RTRIM(@Version)) it to get 2005 such as this example piece of code which would have been used in the UDF dbo.udf_GET_SQL_VERSION in the example above.

DECLARE @Version varchar(30)
SELECT @Version = @@VERSION -- Microsoft SQL Server 2012 (RTM) - 14.0.1000.169 (X64)
SELECT @Version = REPLACE(@VERSION,'Microsoft SQL Server ','')
SELECT @Version = LEFT(@Version,4) -- to get just 2012 not the (RTM) - 14.0.1000.169 (X64)
SELECT @Version = CAST(@Version as INT) -- return as an integer
RETURN @Version -- e.g 2012

Now we have even more information in the @@VERSION system variable. For example now if you call @@VERSION on a later edition you will get.

SELECT  @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Web Edition (64-bit) on Windows NT 6.2  (Build 9200: ) <x64>

Which as you can see contains much more information than the old @@VERSION which just returned the bare basics of the server. Now we get the build version, the Edition type and Build details.

To access this information you can now use the SERVERPROPERTY function calls to get detailed information from the @@VERSION data but on it's own e.g

SELECT  SERVERPROPERTY ('productversion')

Which returns....

Web Edition (64-bit)

Two other interesting functions are:

SELECT	SERVERPROPERTY ('InstanceDefaultDataPath')
SELECT	SERVERPROPERTY ('InstanceDefaultLogPath')
Which return the paths of your default data and log files locations for the current SQL Server instance e.g

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

So as we can see using a branch off @@VERSION on any old code you may have lying about may need editing and if you are automating things such as data beign moved and paths being created you can see hopw the DefaultDataPath and DefaultLogPath variables maybe of use. We all live and learn and the world of SQL keeps turning ensuring jobs for people to fix old code!

By Strictly-Software