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
DECLARE @SQLVERSION INT
SELECT @SQLVERSION = dbo.udf_GET_SQL_VERSION(@@VERSION) -- returns 2000 or 2012

IF @SQLVERSION > 2005
  BEGIN
        -- use a more modern CTE and temporary view
        EXEC dbo.usp_SQL_GET_WORKERS_CTE_VERSION
  END
ELSE -- handle everything under 2005 when CTE's and temporary views came out
  BEGIN
        -- use old stack method for adjency 
        EXEC dbo.usp_SQL_GET_WORKERS_STACK_VERSION
  END

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')
SELECT  SERVERPROPERTY ('productlevel')
SELECT	SERVERPROPERTY ('edition')

Which returns....

11.0.2100.60
RTM
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

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 https://pro-download.wickr.com/#/version/pro
  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, www.fromthestables.com, 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.

mc_gross=30.00&protection_eligibility=Eligible&address_status=confirmed&payer_id=NW2XBXPZ6C78W&address_street=Some+Street&payment_date=13%3A27%3A02+Jan+17%2C+2019+PST&payment_status=Completed&charset=windows-1252&address_zip=D02TX83&first_name=Paul&mc_fee=1.37&address_country_code=IE&address_name=Paul+Hickey¬ify_version=3.9&subscr_id=I-PLD46W039GB&custom=%7B7CD66296-0664-4A51-9ACD-05FE46821D44%7D&payer_status=verified&business=rob%40somesite.com&address_country=Ireland&address_city=Dublin&verify_sign=AczUU94BLMilZ9uHs3gDJVFDFmnrAhoedso-UI.71KEbRJ9deMwoa8KS&payer_email=paulhickey1234%40hotmail.com&txn_id=91X75169EH810362V&payment_type=instant&last_name=Hickey&address_state=DUBLIN&receiver_email=rob%40somesite.com&payment_fee=&receiver_id=5Z7P6UFG56B3P&txn_type=subscr_payment&item_name=Strictly+Software&mc_currency=GBP&item_number=%7B24C686D0-BBF5-54B6-A1D6-215AP099CD43%7D&residence_country=IE&transaction_subject=Membership&payment_gross=&ipn_track_id=98d91af71234

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.

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[udf_SQLRegExReplace](@Pattern [nvarchar](500), @MatchString [nvarchar](max), @ReplaceString [nvarchar](2000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
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.


SET ANSI_NULLS OFF
SELECT SUM(VAL) as Amount
FROM (
  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 
WHERE ISNUMERIC(t.VAL)=1

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

Latest Cheap Amazon Goods