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


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

Friday, 15 February 2019

Do You Want Multiple Wickr Accounts?

How to get another Wickr Account To Work

By Strictly-Software

If you are like me you might have an Android phone with Wickr the encrypted chat app installed on it.

However if you have been blocked by someone, locked out, forgotten your password or just want another Wickr account it's not easy as Wickr is tied to your Google account and device and if you try and install it from Google Play Store on a different device it will just say "Already Installed". 

You have to either remove the app from your phone and start again or use a different phone with a different Google Account linked to it as Google Play always knows what devices you have installed and which apps are on it.

I tried the obvious route first, use an Android Emulator such as NOX, and then created a new Google Account and Email address and then went to the PlayStore to download Wickr. The only thing was the link was broken and it kept sending me to an error page.

I tried downloading the APK file from a site I searched for Wickr Download APK, but even after installing it, it got to the 99% of "encrypting your device" e.g my NOX Emulator on Windows 8.1, and just hung forever.

Therefore I couldn't get the emulator to work. I could have installed another one but whats the point of slowing down a PC with multiple Android emulators just to watch free TV from Navisport etc?

Therefore I actually did a search for "Download Wickr For Windows 8.1" and all I got were help articles telling me to download further emulators and then install from Google Play. This was obviously a no go.

Therefore I actually tried the following which did work on Windows 8.1 from this link.

The Windows 10 link is the first one on the left. The rest are APK, Mac and IOS files.

To install on a Windows 8.1 machine do the following, without the need for an Android Emulator.
  1. Create a new Google Account, username, password, add it to the 12 you already have.
  2. Log out of any Google account you might be in e.g especially if you are using Chrome.
  3. Go to this link
  4. Select "Windows - For use with Windows 10"  and download the application.
  5. Install the MSI file called WickrMe-5.4.3.msi.
  6. It may take a while but it should start to install, even on Windows 8.1.
  7. Once installed, create a new Wickr account and username.
  8. Find your old contacts and start chatting from your laptop or PC.

It works for me, so it should work for you, don't spend hours trying to getting your emulator to install an APK file that just hangs. Try the Windows 10 Desktop MSI file first.

By Strictly-Software

Wednesday, 23 January 2019

SQL Regular Expressions - Extracting Income From PayPal IPN Data

Extracting Payment Amounts From PayPal IPN Data Using SQL 

By Strictly-Software

Recently I had to come up with a way on one of my sites,, to find the total amount of income received by certain members from their PayPal subscription information. 

This is all stored in an MS SQL Payments table along with the Member ID, the PayPal IPN data and Payment type e.g P for Payment, C for Cancelled, W for Waiting, E for EOT etc.

I also have a table of Member Subscriptions which stores details of every subscription they have ever had including their Member ID, a custom GUID to identify the subscription as they could have had multiple subscriptions. Also I store their PayPal Subscription ID and details of their Sign Up Date, Cancellation Date and Next Payment Date alongside other relevant information.

If you don't know about PayPal and how to make a payment system using their shopping cart or a custom payment system then you can read up about it on their site here.

However the main thing to know is that when payments are made, recurring subscriptions started or cancelled then PayPal will send your site an Instant Payment Notification (IPN) to a callback page for you to handle.

This IPN data is a long string of text including the payers name, email, address, recurring payment information such as the number of days payments are made, plus any custom values you may need for your site such as a unique identifier (GUID).

This is useful in website systems so that you can save the new member in a database table with an ID before they go off to PayPal. When they return to your site this information can be sent back to your IPN callback page so that you can analyse the IPN data and ensure it's a valid callback attempt by making a handshake with PayPals system. Once you know it's valid information you can link the callback and IPN data string to a member by matching their Member ID with the one you stored earlier in the database.

An example of an IPN string looks like the text below and contains all the information about an IPN. There are many types of IPN strings such as those that handle cancellations, waiting, EOT (End Of Term) etc but the one I am interested in that contains two bits of information I require is the Payment Notification. 

You can see the two pieces of information in the IPN text below as they are highlighted in blue and red. The start of the string contains one part and the other is halfway through the data.


As you can see the amount I want is in the first part is at the beginning of the string, just after mc_gross e.g mc_gross=30.00.

However due to legacy issues of the system and I also need to connect the users PayPal subscription ID to the one I store in the members subscription table.

This is highlighted further along in the string e.g subscr_id=I-PLD46W039GB.

An example of the CLR User Defined Function I use to allow for Regular Expression Replacements can be seen below. It is connected to a DLL that contains the C# code that runs the regular expression replacing, however that is another topic altogether. You just need to know the format of the parameters for the SQL I show you later on.

Personally I store this function in the MSDB system database so that all my databases on the server can utilise it for regular expression replacements.

ALTER FUNCTION [dbo].[udf_SQLRegExReplace](@Pattern [nvarchar](500), @MatchString [nvarchar](max), @ReplaceString [nvarchar](2000))
EXTERNAL NAME [asbl_SQLRegExpr].[UserDefinedFunctions].[RegExReplace]

The SQL code I use for totaling up all amounts for a certain member is below.

I use two embedded SQL UDF Replacement Functions to extract just the monetary amount and remove everything after it. I also use the same function to join the Subcriber ID in my table to the subscr_id value in the string. 

This involves using a sub-query that does the cleaning and replacing and returns the monetary value. Then an outer query uses that value and a SUM function to total all the values up.

  SELECT CAST(msdb.dbo.udf_SQLRegExReplace('&.+?$',msdb.dbo.udf_SQLRegExReplace('^.*mc_gross=',PayPalDetails,''),'') as money) as val 
  FROM MEMBERS_PAYMENTS as p with (nolock) 
  JOIN MEMBER_SUBSCRIPTIONS as ms with (nolock) 
   ON ms.MemberFK = p.MemberFK
    AND ms.PaypalSubscriptionID = dbo.udf_SQLRegExReplace('&.+?$',dbo.udf_SQLRegExReplace('^.*subscr_id=',PayPalDetails,''),'')
    AND ms.MemberFK = 10342
  WHERE PaymentStatus = 'P'
 ) as t 

As you can see the SQL function calls are wrapped in an inner SQL statement so that it just returns payment values and then the outer SQL statement returns the SUM amount for a particular member ID value.

This is a good example of having to use SQL CLR Regular Expressions as well as the use of embedded functions to extract hard to get values from a long complicated string of data.

You could attempt to write the regular expression calls so that only one function call is made for the monetary amount and another one for the subscriber ID. However the IPN strings are not always similar in format and sometimes it is better for performance to break your regular expressions down into multiple replacement calls instead of writing a complicated expression to cover all possible formats.

Try it and see for yourself. See if you can re-write the query with one regular expression per extraction, and then compare the performance of the two queries with SQL Performance Monitor.

By Strictly-Software

© 2019 Stictly-Software

Thursday, 27 December 2018

Boggle - Online Game


By Strictly-Software


















Enjoy the game, we did!

Wednesday, 28 November 2018

ShowBox Has Stopped Working - Getting Popcorn Time to install on a Kindle Fire Tablet

ShowBox Has Stopped Working! Get a replacement application onto your Kindle Fire or Tablet

By Strictly-Software

Showbox has stopped working.

Hopefully this is a temporary situation but as it provides great TV and Films I needed a replacement. This is how I got Popcorn Time onto my Kindle Fire, a tablet that didn't seem to want to take any .APK file from the web in the first place.

If you have a standard tablet then you should just be able to do a search for the Popcorn time APK and download and install the APK file on your tablet as long as you have turned on the ability to install applications from unsafe locations (e.g the web and not Playstore/Amazon store).

Remember to go to Settings -> Security -> Install Unknown Apps and enable whilst trying to install from a website.

If you want an easy way you can just go to the website and then you can select films or TV shows and watch them directly on your laptop.

If you have a Chromecast you can stream the content to a TV that its plugged into OR if you have a WiFi enabled TV you should be able to select the TV of your choice.

Another way would just be to connect your laptop to your TV with an HDMI cable and get the picture that way. However I wanted the application on my Kindle Fire as its easy to hold and I can take it from room to room and stream it from that device to the main TV or bedroom TV or just watch it on the tablet.

If you want to use a tablet try using the USB cable and connect it to your laptop first and see if it shows as an external device. If it does then just copy the Popcorn APK file across and install it.

I tried this but the Kindle didn't show up despite updating drivers. However try this before the FTP steps below.

Full Requirements for these steps to work...
-A Wifi enabled TV or Chromecast that can handle streamed content
-Kindle Fire or another tablet
-ES File Explorer application working on your tablet (download from PlayStore or web)
-TOR Browser (download from Google to get around blocked sites)
-FileZilla FTP App (or another FTP app - free to download

So to get Popcorn TV onto my Kindle I had to do the following steps...
1. Use TOR Browser and go to (other sites I checked wanted payment for download or had dead links)
2. Download the file to your laptop/PC and remember the location
3. Open the ES File Explorer on Kindle/tablet
4. Go to Remote Manager
5. Press the "Turn ON" button
6. You will be given a local IP address for viewing your tablets files by FTP e.g If you enter that into a web browser you should see the contents of your Kindle however to transfer the downloaded file I used FileZilla a free FTP app for transferring files.
-Open Filezilla and select "New Site"
-Enter the IP address and port number into the boxes
-Chose FTP Plain Insecure
-Chose Anonymous login
-Select the Transfer Settings tab and ensure "Passive" is selected
-Hit connect and as long as the "TURN ON" button is still enabled on your Kindle/Tablet in ES FIle Explorer you should get to see your tablets Hard Drive
-You can then copy the popcorn-time-3-2-2.apk file from the file you downloaded it to on your computer to the downloads folder on your Kindle/Tablet.
-Select the Download folder in ES File Explorer once copied and select "Install"
-It should install the APK
-You can then open it, select a file and hit the Cast button to stream it to your TV or ChromeCast
-I had to open the app a couple of times at first as it was a bit shaky but it is now playing a movie on my TV okay.

Hope this helps people out ....

© 2018 Strictly-Software

Monday, 26 November 2018

Obtaining an external IP address in memory for use in a Firewall Rule


By Strictly-Software

As I am currently using an ISP which constantly changes my external IP address due to excessive use of DCHP, I have to regularly update external firewalls on servers to allow my computer remote access.

This is obviously a right pain to do and I have no idea why my ISP changes my IP address so much when my old ISP used DCHP and kept it for months at a time.

Therefore I created this little noddy VBScript to sit on my desktop to obtain my IP address and hold it in my clipboard memory ready for me to just open up my firewall and paste it in.

The code uses the MSXML2.ServerXmlHttp object to obtain the IP address from an external website which just prints it on the page and I store the Response.Text into a variable.

I then use WScript.Shell object to open a new Notepad window and write the IP address out into it.

Then I use SendKeys to select the IP address and copy it into the clipboards memory before shutting down Notepad.

This means I can just quickly double click my desktop shortcut icon to obtain the IP address ready to paste it straight into a Firewall rule.

Check this script out and when you are ready and can see that hitting CTRL + V pastes the IP address out elsewhere you should remove the "TEST SECTION" and uncomment the part above it that closes down Notepad. This ensures you are not leaving empty objects around in your computers memory.

It's not exactly amazing code but it's very helpful at this time and saves a lot of time visiting a website manually to get my external IP address.

You might find this useful yourselves!

Option Explicit

Dim IPAddress, objShell
Dim objHTTP : Set objHTTP = WScript.CreateObject("MSXML2.ServerXmlHttp")

'* Obtain external IP address and store it in a variable
objHTTP.Open "GET", "", False
IPAddress = objHTTP.ResponseText

'* Open Notepad
Set objShell = WScript.CreateObject("WScript.Shell")
objShell.Run "notepad.exe", 9

WScript.Sleep 1000

'* Write out the IP address to a blank notepad file
objShell.SendKeys IPAddress

'* select the IPAddress and copy it into memory
objShell.SendKeys "^{a}"
objShell.SendKeys "^{C}"

'* uncomment the following section and remove the "TEST SECTION" when you are ready

'* Close notepad with the IP address in clipboard ready to be pasted
'* Open Save Dialog
'* objShell.SendKeys("%{F4}")
'* Naviagate to Don't Save
'* objShell.SendKeys("{TAB}")
'* Exit Notepad
'* objShell.SendKeys("{ENTER}")

'* TEST SECTION - Proves that the IP address can be pasted elsewhere
WScript.Sleep 1000

'* Proof that the IP address is in the clipboard and can be pasted out
objShell.SendKeys "Test Paste Works"
objShell.SendKeys "{ENTER}"
objShell.SendKeys "^{V}"
objShell.SendKeys "{ENTER}"
objShell.SendKeys "Try a manual CTRL and V to check the IP address is still in memory"
objShell.SendKeys "{ENTER}"

'* Kill objects - DO NOT REMOVE!!
Set objShell = Nothing
Set objHTTP  = Nothing

By Strictly-Software

© 2018 Strictly-Software

Wednesday, 31 October 2018

Invalid File Handle - Movie Downloads Beware!

Invalid File Handle - Movie Downloads Beware!

By Strictly-Software

Recently I downloaded a film, Con Man (2018), however when I try to do anything with the file such as open, run, copy, move or delete it I get a Windows "Invalid File Handle" error message.

I did some research and found that on certain file systems such as NTFS, CDFS, exFAT, UDFS, FAT, and FAT32, there are some reserved file names that you should never use which causes this error. One of these is con. Others include: PRN, AUX, NUL, COM1, COM2, COM3, COM4, COM5, COM6, COM7, COM8, COM9, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8, and LPT9.

I checked the filename of the movie and the person had only named it Con.Man.2018.1080p.mp4, meaning that Windows saw the filename as Con preventing me doing anything with this file.

At the moment it is permanently stuck on my file system as I cannot remove the files containing folder either.

I did have a copy of this file on a USB stick however which I could remove, obviously due to the different format of the USB disk drive. If I had used uTorrent to download the file directly to the USB drive instead of first to a folder on my Windows drive I wouldn't have had this issue.

I am basically looking for ways to remove this file as I cannot rename it to something else, either from Windows or the Command Prompt, to enable deletion.

I saw someone suggest renaming the file to \\.\ but this does not work as I cannot get a file handle to rename it in the first place.

Therefore this is more of a warning write up than a "How To Guide", although you can be sure I will update this when I find a solution.

So if you are downloading files such as movies to your computer be careful and check the filename first and if you are using a tool such as a Torrent downloader ensure the downloaded file name is changed to something an NTFS or FAT type disk can handle, or download it straight to a USB drive with a different disk format which will allow file deletion.

For more information you can read this MSDN article, called "Naming Files, Paths, and Namespaces".


I will update this when I get a solution.

Latest Cheap Amazon Goods