Python: Clean SQL views for Documentation

One of the most tedious tasks of working with databases is to write and maintain documentation, in particular writing reports from tables and views.

So why not try to make this task a bit less heavy by using Python?

Step0-The input and the output

The script requires that the views are created using create view.
Example:

Right click on view

img1

Copy view

img2

Output view after python script

img3


Step1 – Extract Columns Function
def extract_columns(in_txt):
    """Simple function to extract columns between select and from"""
    # Splitting by select i only choose text after select
    out_view = re.split(r'(?i)\bselect\b',in_txt)[1]
    # Splitting by from i only choose text before from
    out_view = re.split(r'(?i)\bfrom\b',out_view)[0]
    # Cleaning extra spaces and tabs
    out_view.strip()
    return out_view

Let’s start by defining a function with the purpose of extracting only the columns from the script of a view. Thus ignoring everything before the Select statement and everything after the From statement.


Step2 -Let’s start from the variables
import re

# We open the file containing the view
view = extract_columns(open('input.txt','r',encoding='utf-8').read())
# Now we insert text into the list and we create a new list
view_l = view.split('\n')
out_l = []

Now we declare the variables we are going to use.

  • view: contains the input text
  • view_l: is a list created by splitting text by line
  • out_l: is the output list

Step3-Main Code
# Now we loop through all the lines of the view
for num,line in enumerate(view_l):
    # Clean extra spaces
    line = line.strip()
    # Remove Comments
    line = line.split('--')[0]
    # Remove Commas
    line = line.replace(',','',1)
    # Substitute Tabs with Spaces
    line = line.replace('\t',' ')
    # While two spaces are in line we substitute the with one space
    while '  ' in line:
        line = line.replace('  ',' ')
    # Remove [ and ]
    line = line.replace ('[','').replace(']','')
    # If line is not empty
    if len(line)>0:
        # If line is not a comment
        if line[0]!='-':
            # We add the new line to out_l
            out_l.append(re.split(r'(?i)\bas\b',line))

Finally we come to the main code.
The first part is a For loop, using the list we created in step2.
For each line the script removes useless characters and comments and then adds the line to the new list.


Step4-Output Text
# Open output file
with open('out.txt','w') as out_txt:
    # For line in output list
    for line in out_l:
        # Try / Except to output all the values to the file separated by ','
        print(line)
        if len(line)>1:
            out_txt.write('as'.join(line[0:-1]).strip()+'|'+str(line[-1]).strip()+'\n')
        else:
            out_txt.write(str(line[0]).strip()+'|'+str(line[0]).strip()+'\n')

In the last part we write to an external file using a Try/Except to avoid IndexErrors.

Run stored procedures with report data as input parameters

How can I run stored procedures with report data as input parameters?

Stored procedures are one of SQL’s most powerful tools to update, insert or delete data in your database. Since most reports are designed to provide Business Intelligence to users, there could be an action the user needs to take based on the presented data. Wouldn’t it be great if the user could take that action without leaving the report?
This post describes how to run a stored procedure directly from your report with row data as input parameters.
I will use a simple example with a custom table and a small stored procedure so you get the picture. Soon you will discover that with these steps and a creative mind, the options are near limitless for taking direct actions in your database! Think about checklist reports or scheduling reports.
1) Create a custom table in your database (you can also use existing tables if you know what you’re doing)
Create TABLE [dbo].[AdvancedSSRS_CusOrd]
([ID] [int] IDENTITY(1,1) NOT NULL,
[Account] [Varchar](50) NOT NULL,
[OrderNo] [Varchar](50) NULL
) ON [PRIMARY]
GO
2) Create a stored procedure that Inserts new records in the table created in step 1
SET ansi_nulls ON 
go 

SET quoted_identifier ON 
go 

CREATE PROCEDURE Advancedssrs_insertintoadvancedssrs_cusord 
@Account VARCHAR(50), 
@OrderNo VARCHAR(50 

AS 
  BEGIN 
      SET nocount ON; 

      –Check if record already exists 
      IF EXISTS (SELECT NULL 
                 FROM   advancedssrs_cusord 
                 WHERE  account = @Account 
                        AND orderno = @OrderNo) 
        — if exists 
        BEGIN 
            UPDATE advancedssrs_cusord 
            SET    account = @Account, 
                   orderno = @OrderNo 
            WHERE  account = @Account 
                   AND orderno = @OrderNo 
        END 
      — if record is new 
      ELSE 
        BEGIN 
            INSERT INTO advancedssrs_cusord 
                        (account, 
                         orderno) 
            VALUES      (@Account, 
                         @OrderNo) 
        END 
  END 

go 
Since I don’t want to get multiple records of the same Customer/Order combination I included a check to see if the record exists. If it does, the stored procedure will overwrite the row with the same values. If it doesn’t, the stored procedure will add an extra row.
3) Add report parameters that will serve as input parameters for the stored procedure
In this example I want to mark some orders as “special”, so I will add a parameter for @Account and @OrderNo. Make sure to allow blank values.
 
4) Add a dataset that exectutes the stored procedure created in step 2
The dataset checks if the report parameter(s) are not NULL (which is how the report runs by default). Only if the parameters have values, the stored procedure will be executed.
If you use data from your custom table in your report, you want to make sure this dataset is the first dataset the report will run. Unfortunately you cannot move the position of the dataset, so if you have an existing dataset, copy the query, delete the dataset and recreate it.
5) Add a column in your Tablix to launch the stored procedure
Insert text or an image and go to its properties. Browse to the Action tab. Choose to “Go to report” and choose the report you are working on. Add parameters to pass on so you pass the value in a row to the report parameters.
You see where this is going? Once the user cicks the image/text, the report will be launched again (refreshed) but this time the parameters are not blank. This will trigger the dataset created in step 4 to exectute the stored procedure. In my scenario a row will be inserted in the table created in step 1.
6) Test your report
7) Hide the report parameters

Use Change Tracking on SQl server

Here, we will explain change tracking functions, show code examples and demonstrate how to read the Change Tracking results

Change tracking functions

There is no out-of-the-box option to see the change tracking information. To see the rows that were changed and change details, use change tracking functions in T-SQL queries [1]

The CHANGETABLE(CHANGES) function shows all changes to a table that have occurred after the specified version number. A version number is associated with each changed row. Whenever there is a change on a table where Change tracking is enabled, the database version number counter is increased

The CHANGETABLE (VERSION) function “returns the latest change tracking information for a specified row“ [2]

SELECT * FROM CHANGETABLE(CHANGES <table_name>, <version>) AS ChTbl

Note that the table used in the CHANGETABLE function has to be aliased

Table changes that have occurred after the specified version number

The CHANGE_TRACKING_CURRENT_VERSION() function retrieves the current version number, i.e. the version number of the last committed transaction

SELECT NewTableVersion =  CHANGE_TRACKING_CURRENT_VERSION()

Returns NULL if Change tracking is not enabled, an integer otherwise. The minimal value returned is 0. In the example above, it returns 17

The CHANGE_TRACKING_MIN_VALID_VERSION() function shows the minimum version number that can be used to get change tracking information for the specified table using the CHANGETABLE function

SELECT MinVersion = 
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Person.Address'))

In the example above shows 14

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function shows whether the specific column was updated or not. If it was updated, the value is 1, otherwise 0. It can only be used if the TRACK_COLUMNS_UPDATED parameter for enable change tracking on a table is set to ON

Reading the Change Tracking results

Here’s an example for the data changes executed on the Person.Address table

  1. Execute
    SELECT TableVersion = CHANGE_TRACKING_CURRENT_VERSION();
    SELECT * FROM Person.Address;
    

    The Change Tracking results show that this is the first version of the tracked table and the current records in the Person.Address table

    Change tracking results - the first version of the tracked tables

  2. Modify the records in the Person.Address table, either using T-SQL or editing rows in the SQL Server Management Studio grid. The changes I made are highlighted – I updated the rows with AddressIDs 1, 5 and 2, in that order

    Modifying records using T-SQL or editing rows in SSMS

  3. I added a row. Note that the AddressID is 32522

    Row is added into a table

  4. I deleted the row I added in the previous step
    DELETE Person.Address WHERE addressid = 32522;
  5. To read the Change Tracking results, execute
    SELECT
    NewTableVersion = CHANGE_TRACKING_CURRENT_VERSION()
    
    SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION,
    AddressID
    FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;
    

The results are:

Showing current results

The values shown in the ChOp column indicate the changes made. ‘U’ stands for update, ‘D’ for delete, ‘I’ for insert. There are three updates on the rows with AddressID 1, 2, and 5 and deletion of the row with AddressID = 32522. There is no clear indication that the 32522 row was first inserted, but according to the Change Creation Version (ChCrVer) and Change Version (ChVer) values 5 and 6, there were 2 changes. The second one was a delete, but we don’t know what the first one was

I re-inserted the same 32522 row and refreshed the results

Re-inserting the same row and refreshing the results

As expected, the current version number is 7, increased by 1 as there was one more change. But the information about the 32522 row is even vaguer when it comes to row history

Tracking individual column updates

If you add the SYS_CHANGE_COLUMNS column to the query, you will get the binary number of the column that was changed. The value is NULL only if the column change tracking option is not enabled, or all columns expect the primary key in the row were updated

Showing binary number of the changed column

“Column tracking can be used so that NULL is returned for a column that has not changed. If the column can be changed to NULL, a separate column must be returned to indicate whether the column changed.” [2]

To present column changes in a more readable format, use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function. It has to be called for each column individually. In the following example, I’ll check whether the columns AddressLine1 and AddressLine2 have been modified

SELECT
ChVer = SYS_CHANGE_VERSION,
ChCrVer = SYS_CHANGE_CREATION_VERSION,
ChOp = SYS_CHANGE_OPERATION,
AddLine1_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine1', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddLine2_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine2', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddressID
FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;

Using column tracking to change the column

The value 1 in the AddLine1_Changed and AddLine2_Changed columns indicates that the specific column has been changed

As shown, SQL Server Change Tracking is a synchronous process that can be easily configured on your tables. It is supported in all SQL Server editions, so there are no additional licensing expenses. It can be utilized in applications designed for one-way and two-way data synchronization, as it can seamlessly synchronize several databases, each at a different time

The Change Tracking feature is not designed to return all information about the changes you might need, it’s designed to be a light auditing solution that indicates whether the row has been changed or not. It shows the ID of the row changed, even the specific column that is changed. What this feature doesn’t provide are the details about the change. You can match the change information to the database snapshot and the live database to find out more about the changes, but this requires additional coding and still doesn’t bring all the information that might be needed for auditing

Change tracking doesn’t answer the “who”, “when”, and “how” questions. Also, if there were multiple changes on a specific row, only the last one is shown. There is no user-friendly GUI that displays the results in just a couple of clicks. To see the change tracking records, you have to write code and use change tracking functions

The execution of the SELECT statements and database object access is not tracked. These events have nothing to do with data changes, but as SQL DBAs request these features when it comes to auditing, it should be mentioned

Enabling Multiple Remote Desktop Connections in Windows Server

To enable multiple remote desktop connections in Windows Server 2012 or Windows Server 2016, you’ll need to access the server directly or through Remote Desktop. Once you’ve logged in, press the Windows key in Windows Server 2012 to open the Start screen or simply type the following into the Start bar in Windows Server 2016: gpedit.msc.

This will launch the Group Policy Editor (gpedit.msc), which is a management console through which you can configure many Windows system properties or run scripts.

Once the Group Policy Editor is running, navigate to:

Computer Configuration > Administrative Templates > Windows Components > Remote Desktop Services > Remote Desktop Session Host > Connections.

From here, first set the Restrict Remote Desktop Services user to a single Remote Desktop Services session parameter to Disabled.

Next, double-click on Limit number of connections and then set the RD Maximum Connections allowed to 999999.

Handle DistinctCount counts NULL

Analysis Services handles NULL and 0 as the same thing, and 0 would normally be considered something worth counting, whereas NULL wouldn’t.

When you count for example the sex of  distinct customer, you dont’ want to count who not have sex, (the null value). you have to do these:

  1. Create a new column on the fact table that indicates whether the field we want to distinctcount is null. To do this we add a new column and give it the fixed value 1 when the Attribute is empty.
  2. Create three measures
    • DistinctOriginal: Standard measure, DistinctCount on attribute, hidden measure
    • IsAttributeNumberNull:Standard measure, MAX on isAttibuteNull, hidden measure
    • DistinctAttribute: Calculated measure, Formula: DistinctOriginal – IsAttributeNumberNull

And that’s it, use the new calculated measure for a distinct count that does not count NULLs.

SQL – Dynamic Sorting

Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. i this specific case user want to order sales of a specific reporting services report based on different date. This is the specific query:

SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate,

PurchaseOrderNumber, AccountNumber, SubTotal,

TaxAmt, Freight, TotalDue

FROM Sales.SalesOrderHeader

The user want to order data by: ORder date, Due date or shipdate.

To do this we use a stored procedure where we read the selected time attribute and give a query order by it.

The specific query it is :

DECLARE @SortOrder VARCHAR(50) = ‘[Time attribute selected]’;

SELECT rn,

 [list of attributes]

FROM (

SELECT CASE @SortOrder

WHEN ‘OrderDate’ THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC)

WHEN ‘DueDate’ THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC)

WHEN ‘ShipDate’ THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC)

END AS rn,

[list of attributes]

FROM Sales.SalesOrderHeader

) AS x

ORDER BY rn ASC;

With this symple query you can order dataset with dynamic variable.

SQL – Replacing unwanted characters

Sometimes when you work with raw data or input by user it’s possible to have some problem with special characters. To resolve these problems we use a special feature of SQl server:  SQL CLR assembly

  1. First step is open visual studio and creating a new class library project with this code:
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public class RegEx

{

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlString ReplaceIfMatch

(SqlString ip, SqlString exp, SqlString replace)

{

// some error handling

if (exp.IsNull || ip.IsNull || replace.IsNull)

return  SqlString.Null ;

Regex r = new Regex(exp.ToString());

//bool match = r.Match(ip).Success;

return new SqlString( r.Replace(ip.ToString(),

replace.ToString()));

}

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlBoolean IsMatch

(SqlString ip, SqlString exp)

{

// some error handling

if (exp.IsNull || ip.IsNull )

return SqlBoolean.False;

Regex r = new Regex(exp.ToString());

return new SqlBoolean( r.Match(ip.ToString()).Success);

}

// repeating for normal windows

public static string ReplaceIfMatchN

(string ip, string exp, string replace)

{

// some error handling

if (string.IsNullOrEmpty(exp)

|| string.IsNullOrEmpty(ip)

|| string.IsNullOrEmpty(replace))

return string.Empty;

Regex r = new Regex(exp);

return r.Replace(ip, replace);

}

}

Save the class library as dll in a specific folder. after that open management studio and select your db, and launch this script:

USE AdventureWorks2008R2;

GO

EXEC SP_CONFIGURE ‘clr enabled’,1;

GO

RECONFIGURE;

GO

After that reconfigure Sql server and assembly, specific privous path where you are saved the file:

CREATE ASSEMBLY

RegExBase

FROM

‘D:\<PATH>\RegExBase.dll’

WITH PERMISSION_SET = SAFE ;

GO

Last step i to create a function to use the assembly :

CREATE FUNCTION

RegExReplace

(@ip NVARCHAR(4000),@exp NVARCHAR(4000), @replaceNVARCHAR(4000) )

RETURNS NVARCHAR(4000)

AS EXTERNAL

NAME RegExBase.RegEx.ReplaceIfMatch ;

GO

 

Now we can invoke the function and see the result.