Getting started with Python Web Scraping

Getting started with Python on a Mac was fairly straightforward, but I had a few stumbling blocks on Windows. The easiest way to get started with Python, a decent IDE & terminal, and additional libraries, was to install Anaconda on Windows, and use the Spyder IDE.

Using Beautiful Soup for web scraping, the following is a script I wrote to get the current top non fiction audiobooks from Audible:

# Script to get top Audible personal development books  

import requests 
from bs4 import BeautifulSoup 

URL = 'https://www.amazon.co.uk/Best-Sellers-Books-Self-Help-How/zgbs/books/2996349031/ref=zg_bs_nav_b_3_2996114031' 
page = requests.get(URL) 
soup = BeautifulSoup(page.content, 'html.parser') 
results = soup.find('ol', class_ = 'a-ordered-list a-vertical') 

list_elems = results.find_all('li', class_ = 'zg-item-immersion') 
for list_elem in list_elems[:50]: 
    rank_elem = list_elem.find('span', class_ = 'zg-badge-text') 
    title_elem = list_elem.find('div', class_ = 'p13n-sc-truncate p13n-sc-line-clamp-1') 
    author_elem = list_elem.find('span', class_ = 'a-size-small a-color-base') 
  
    title_elem = title_elem.text.replace('  ', '') 
    title_elem = title_elem.replace('\n', '')  

    print(rank_elem.text.replace('#', '') + ' - ' + title_elem + ' - ' + author_elem.text.replace('\t','')) 

Returns the following:

 

SQL Server Migration using dbatools Powershell Module

Migrations between SQL Servers can be laborious, and doing them manually leaves a lot of room for human error. Scripting a migration using the dbatools Powershell module is incredibly simple, quick and robust.

Prerequisites

Powershell with dbatools module installed:

https://dbatools.io/download/

Steps

Run the following Powershell:

    $startDbaMigrationSplat = @{

    Source = 'sourceServer'

    Destination = 'destinationServer'

    BackupRestore = $true

    SharedPath = '\\path\both-servers-can-access'

    }

    Start-DbaMigration @startDbaMigrationSplat -Force | Select * | Out-GridView

This will migrate everything associated with the source instance, to the destination instance (inc logins, linked servers, startup procs...), using a backup and restore method for moving the databases. Use the -Exclude flag (see documentation link below) to exclude bits you don't want to be migrated over

Resources

dbatools migration documentation: https://docs.dbatools.io/#Start-DbaMigration

YouTube vid demo of dbatools migration: https://www.youtube.com/watch?v=hg8tovMRX2k

Fix issues with Powershell: https://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system

Stack Exchange article on migrations: https://dba.stackexchange.com/questions/258559/side-by-side-upgrade-and-migrate-sql

Primary Key Capacity

Use the following SQL to determine how "full" your primary keys are, based on the number of existing values, and the data type used:

select

       '[' + p.TABLE_SCHEMA + '].[' + p.TABLE_NAME + ']' as [table]

       ,c.COLUMN_NAME

       ,ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME) as MaxID

       ,cls.DATA_TYPE

       ,cast(100 - isnull(ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME), 0) * 100 /

                     case cls.DATA_TYPE

                           when 'int' then 2147483647

                           when 'smallint' then 32767

                           when 'tinyint' then 128

                     end as decimal(4, 1))

       as [% of range left]

from

       INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

       inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

              on c.TABLE_NAME = p.TABLE_NAME

              and c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

       inner join INFORMATION_SCHEMA.COLUMNS cls

              on c.TABLE_NAME = cls.TABLE_NAME

              and c.COLUMN_NAME = cls.COLUMN_NAME

where

       p.CONSTRAINT_TYPE = 'PRIMARY KEY'

       and cls.DATA_TYPE in ('int', 'smallint', 'tinyint')

order by

       5 asc

Importing XML into SQL Server Table

The following is an excellent example of how to import XML into a table within SQL Server, and query the XML once imported:

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

SSIS Packages With Excel Source Failing After Windows Updates

SSIS packages with Excel Sources may fail after Windows updates (specifically KB4041681) with the following error:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered

The resolution is to install the 2007 Office System Driver: Data Connectivity Components from here:

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

Note - SSIS packages must have the "Use 32 bit runtime" box checked if being scheduled via a SQL Agent Job

Finding out when updates / service packs were applied to SQL Server

It is possible to find out when updates were applied to SQL Server by checking in the following location:

c:\Program Files\Microsoft SQL Server\XXX\Setup Bootstrap\Log

See more here: https://stackoverflow.com/questions/2950911/how-to-determine-date-when-service-pack-was-applied-to-sql-server-instance

SSRS Subscriptions - What Goes On Under The Hood

Excellent post detailing what happens "under the hood" of SQL Server Reporting Services

https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/06/20/ssrs-subscriptions-what-goes-on-under-the-hood-3/