Swap a SQL script with commas on the left to the right side

Commas to the left or to the right?

It's a SQL debate for the ages and both sides have terrific arguments.

In case you find yourself needing to change a SQL script with commas on the left here is a quick little Notepad++ regex to do so:

 /\n|\s{2,},

 and simply replace with ,\r\n

Windows short/abbreviated path to long using Python pathlib.resolve()

Some Python packages generate a Windows shortened path. For instance, a call to tempfile.TemporaryDirectory generates a url like "C:\Users\STEPHE~1.BEA\AppData\Local\Temp\company_r85o5axn".

Unfortunately, some other processes have trouble with this shortened path and will fail with file not found errors.

The solution to this problem lies in Python's pathlib module. Calling .resolve() on any Path will get you the fully elongated path!

To get the fully elongated path simply:

long_file_name = Path("C:\Users\STEPHE~1.BEA\AppData\Local\Temp\company_r85o5axn").resolve()

Generate a simple 4-5-4 retail calendar (not restated) using T-SQL

Generate a NRF 4-5-4 retail calendar as described here.

https://gist.github.com/sbealer/327ec8ab7c2814d9064bb52a1f5ea3bb

Select from stored procedure that uses temp tables

Don't do this. But if you HAVE to do this...

USE YOUR_DATABASE

GO

CREATE OR ALTER PROCEDURE DBO.EXAMPLE_SELECT_FROM_PROCEDURE_WITH_TEMP_TABLES
AS

BEGIN
    /* 
    This procedure creates a simple temp table of numbers and selects from it.
    This is used in an example showing how to select from a procedure that 
    utilizes temp tables (which is normally not possible)
    */

    SET NOCOUNT ON;

    BEGIN
        DROP TABLE IF EXISTS #TMP;
        /*Create table first, not select into, otherwise you will lock the catalog */
        CREATE TABLE #TMP (RESULT_COLUMN1 INT);
    END;
    BEGIN
        WITH CTE
        AS
        (SELECT
                1 AS NUM
            UNION ALL
            SELECT
                NUM + 1
            FROM CTE
            WHERE NUM <= 100)

        INSERT INTO #TMP
        SELECT
            NUM AS RESULT_COLUMN1
        FROM CTE;
    END;

    SELECT
        RESULT_COLUMN1
    FROM #TMP;

END;



/* Finally, using a previously established loopback linked server, we 'query' the procedure.
WITH RESULT SETS is *required* to make this work!
Details on creating a loopback linked server: https://blog.sqlauthority.com/2017/06/08/sql-server-quickest-way-add-loopback-linked-server-openquery/
*/

--this could be created as a view!
SELECT
    *
FROM OPENQUERY(YOUR_DB_LOOPBACK, 'exec YOUR_DATABASE.dbo.EXAMPLE_SELECT_FROM_PROCEDURE_WITH_TEMP_TABLES WITH RESULT SETS ((RESULT_COLUMN1 INT))')

Microsoft Teams HMAC verification using Python 3

The example provided by Microsoft is in C#.

Here is the equivalent in Python 3:

from hashlib import sha256
import hmac
import base64

ms_teams_auth_token = <"Provided by Teams upon outgoing webhook bot creation">

def return_calculated_hmac(post_body):

    key = base64.b64decode(bytes(ms_teams_auth_token,'utf-8')) 
    raw = bytes(post_body,'utf-8')  
    hashed = hmac.new(key, raw, sha256)
    
    # The signature
    return f"HMAC {base64.b64encode(hashed.digest()).decode('utf-8')}"

If the value returned by this function matches the provided HMAC key (in the Authorization header) then the request is legit.

SQL Column DRY - Here's how to reuse a heavily modified field without repeating the modification multiple times.

--Using the values clause you can later reference a column that has been modified in the query.

SELECT
(SELECT
SUBSTRING(DIGITS2, 1, 3) + '-' + SUBSTRING(DIGITS2, 4, 3) + '-' + SUBSTRING(DIGITS2, 7, 99) +
CASE
WHEN DIGITS2 LIKE '1%' THEN NULL
WHEN LEN(B.DIGITS2) < 10 THEN NUll --Originally used to remove extra long numbers but removes extension numbers so can't use. --WHEN LEN(DIGITS2) > 10 THEN null
ELSE ''
END
FROM (VALUES((SELECT
RIGHT(DIGITS, CASE
WHEN DIGITS LIKE '1%' THEN 10
ELSE 99
END)
FROM (VALUES(LEFT(REPLACE(TRANSLATE(PHONENUMBER, 'abcdefghijklmnopqrstuvwxyz+()- ,#+.&;_!:', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', ''), 11))) AS A (DIGITS)))) AS B (DIGITS2))
AS PHONE_FIX
, *

FROM [YOURDB].DBO.[CUSTOMERS] a

ceODBC & Python 3

1. Download the ceODBC .whl file from here & copy it to C:
2. Open an administrator command prompt and run the following command:
1. pip3 install c:\ceODBC-2.0.1-cp37-cp37m-win_amd64.whl
3. Profit.

Write to a gzipped csv from io.TextIO

I needed to get a compressed (gzip) csv to an Amazon S3 bucket.

The below Python 3 code utilizes csv.writer and gzip in one step.

Once the gzip memory file is written, the file is shipped to s3 using boto3.



import io
import gzip
import csv
import boto3
import os


destination_bucket = 'your-bucket'
destination_directory = 'your/directory/'
destination_filename = 'text.csv.gz'

your_access_key = os.environ['AWS_ACCESS_KEY']
your_secret_key = os.environ['AWS_SECRET_KEY']

data = [['col1','col2','col3','col4'],
['data','data','three','four'],
['data','data','three','four'],
['data','data','three','four']]

mem_file = io.BytesIO()

with gzip.GzipFile(fileobj=mem_file,mode='w') as gz:
buff = io.StringIO()
writer = csv.writer(buff)

writer.writerows(data)

print("Writing data to gzipped file.")
gz.write(buff.getvalue().encode())
print("Data written")
gz.close()
mem_file.seek(0)

s3 = boto3.client('s3',aws_access_key_id=your_access_key,aws_secret_access_key=your_secret_key)

print("Sending to S3")
s3.upload_fileobj(Fileobj=mem_file, Bucket=destination_bucket, Key=destination_directory+destination_filename)
print("Sent")




SQL Server recursive table valued function to find all required objects for an object

Do you have views built on views built on views? Would you like to analyze the dependency chain of some objects? The below function is a recursive table valued function that retrieves all required objects for the initial input view as well as the required objects for all objects down the chain.



CREATE FUNCTION [DBO].[GET_REQUIRED_OBJS] (@OBJ_SERVER_OR_NULL VARCHAR(2000),
@OBJ_DB VARCHAR(2000),
@OBJ_SCHEMA VARCHAR(2000),
@OBJ_NAME VARCHAR(2000),
@LVL INT)
RETURNS @RES TABLE (
OBJ_SERVER VARCHAR(2000)
,OBJ_DB VARCHAR(2000)
,OBJ_SCHEMA VARCHAR(2000)
,OBJ_NAME VARCHAR(2000)
,OBJ_TYPE VARCHAR(2000)
,LVL INT
)
--WITH ENCRYPTION|SCHEMABINDING, EXECUTE AS CALLER|SELF|OWNER|USER
AS

BEGIN
DECLARE @TEMP_RES TABLE (
OBJ_SERVER VARCHAR(2000)
,OBJ_DB VARCHAR(2000)
,OBJ_SCHEMA VARCHAR(2000)
,OBJ_NAME VARCHAR(2000)
,OBJ_TYPE VARCHAR(2000)
,LVL INT
);
DECLARE @VAL INT;
DECLARE @CUR_LVL INT = @LVL + 1;
DECLARE @CUR_OBJ_SERVER VARCHAR(2000)
DECLARE @CUR_OBJ_DB VARCHAR(2000)
DECLARE @CUR_OBJ_SCHEMA VARCHAR(2000)
DECLARE @CUR_OBJ_NAME VARCHAR(2000)
DECLARE @CUR_OBJ_TYPE VARCHAR(2000)
DECLARE MY_CURSOR CURSOR FOR

SELECT

SED.REFERENCED_SERVER_NAME
,SED.REFERENCED_DATABASE_NAME
,SED.REFERENCED_SCHEMA_NAME
,SED.REFERENCED_ENTITY_NAME
,COALESCE(OBJ_TYPE.TYPE_DESC, 'Other DB Object')
,@CUR_LVL
FROM SYS.SQL_EXPRESSION_DEPENDENCIES SED
JOIN SYS.OBJECTS O
ON (O.OBJECT_ID = SED.REFERENCING_ID)
JOIN SYS.SCHEMAS S
ON (S.SCHEMA_ID = O.SCHEMA_ID)
LEFT JOIN SYS.OBJECTS AS OBJ_TYPE
ON (OBJ_TYPE.OBJECT_ID = SED.REFERENCED_ID)
WHERE O.NAME = @OBJ_NAME
AND S.NAME = COALESCE(@OBJ_SCHEMA, 'dbo')

AND SED.REFERENCED_ENTITY_NAME != @OBJ_NAME

AND O.TYPE_DESC IN ('VIEW', 'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')

OPEN MY_CURSOR;

FETCH NEXT FROM MY_CURSOR INTO @CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @CUR_OBJ_TYPE, @LVL;

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @TEMP_RES
SELECT
@CUR_OBJ_SERVER
,@CUR_OBJ_DB
,@CUR_OBJ_SCHEMA
,@CUR_OBJ_NAME
,@CUR_OBJ_TYPE
,@LVL


INSERT INTO @TEMP_RES
SELECT
*
FROM DBO.GET_REQUIRED_OBJS(@CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @LVL) GRO

;


FETCH NEXT FROM MY_CURSOR INTO @CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @CUR_OBJ_TYPE, @LVL;

END;

CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

INSERT INTO @RES
SELECT
OBJ_SERVER
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_DB)), ''), DB_NAME())
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_SCHEMA)), ''), 'dbo')
,OBJ_NAME
,OBJ_TYPE
,MAX(LVL)
FROM @TEMP_RES
GROUP BY OBJ_SERVER
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_DB)), ''), DB_NAME())
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_SCHEMA)), ''), 'dbo')
,OBJ_NAME
,OBJ_TYPE;

RETURN
END




Unable to find vcvarsall.bat

Install Microsoft Visual C++ Compiler for Python 2.7:

https://www.microsoft.com/en-us/download/details.aspx?id=44266