FREE SQL System Reports!

Are you a DBA that has no funding for fancy SQL System Health Reports like RedGate, SQLTools, Foglight, SentryOne, etc? Do you hate looking at dmv queries because there are so many of them to remember? Well did you know that SQL Management Studios comes with System Health Reports? Below are steps to get System Health Reports at no cost.

To view the System Reports

Right click on “your Server” -> Reports -> Standard Reports -> “select a report”

To view the Database Specific Reports

Right click on “your Database” -> Reports -> Standard Reports -> “select a report”

Each report can be expanded for detail information.

As always, hope this helps!

 

SQL Server Alert System: ‘Severity Level 020: Error In Current Process – High’

I get this very vague Alert in a weekly basis and it was driving me nuts. I reached out to the IT Network/OS for months to see if they could pin down the issue and were not able to find anything on their logs. I provided an Event Handler Email Task to the developers to put inside their SSIS Packages and when I would get Severity Level 020 Alerts, I would now see their Packages. The common errors are

• Named Pipes Provider: The specified network name is no longer available.
•  

  A fatal error occurred while reading the input stream from the network. The session will be terminated

• The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <named pipe>]
• Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.
• SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The “OLE DB Destination.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “OLE DB Destination.Inputs[OLE DB Destination Input]” specifies failure on error.
• Communication link failure

It did not matter when the Job ran (on a schedule, manual, Visual Studio Data Tools), it would still randomly fail. Most of these SSIS are straight forward where they pull 5 million records from Teradata to SQL– Just a straight SELECT * FROM Table (these tables are the final product of an ETL performed in Teradata). Then one day, I get a new error “Communication link failure” so I Googled it and here’s where it lead me to https://support.microsoft.com/en-us/help/942861/general-network-error-communication-link-failure-or-a-transport-level

I ran the command “Netsh int tcp show global” to see the following

In lamens term, Receive-Side Scaling is like the waiter directing traffic at a Food Buffet. The line can only move as fast as the waiter can find you a table. If you disable Receive-Side Scaling, it’s Free For All. You have eyes and the stranger next to you have eyes and you both can find an empty table without the help of the waiter therefore there’s no wait or less wait. Here’s a link to a more technical term https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling

After I disabled Received-Side Scaling (Network Adapter -> Properties -Configure.. -> Advanced -> and increased the SQL (Properties -> Advance) Remote Login Timeout from 20 to 35,  the SSIS Packages began to run without Severity Level 020.

I hope this helps someone that is experiencing the same issue.

Modify Column Data Type

Syntax:
USE [YourDatabaseName]
GO
ALTER TABLE YourTableName
ALTER COLUMN column_name datatype;

Senario:
I have a database call Afterowl with a table call Birds. In my Birds table, I want to change TypeOfBird data type from NVARCHAR(10) to VARCHAR(100).

Example:
USE Afterowl
GO
ALTER TABLE Birds
ALTER COLUMN TypeOfBird VARCHAR(100)

List Calendar Dates

I use this to bounce against my weekly ETL’s to make sure that every day of the week data is captured. This helps me quickly identify a Calendar date with no ETL date which means no data was pulled.

DECLARE @start_date DATETIME= ‘2017-01-01’;
DECLARE @end_date DATETIME= GETDATE();
WITH CalendarDates
AS (
SELECT CAST(@start_date AS DATE) AS Calendar_Day
UNION ALL
SELECT DATEADD(DAY, 1, Calendar_Day)
FROM CalendarDates
WHERE Calendar_Day < @end_date)

SELECT Calendar_Day,
DATENAME(dw, Calendar_Day) AS Day_of_Week,
DATENAME(week, Calendar_Day) AS Week_Number
FROM CalendarDates OPTION(MAXRECURSION 0);

 

Afterowl Invoice Me free Windows Application

Check out the FREE Afterowl Invoice Me Windows Application.

Click here to go to Invoice Me

LIKE Regular Expression

Here’s a great trick to find specific patterns for a column. The LIKE CLAUSE can be use as a RegEx

Here’s a link to LIKE from Microsoft.

Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol	Meaning
LIKE ‘5[%]’	5%
LIKE ‘[_]n’	_n
LIKE ‘[a-cdf]’	a, b, c, d, or f
LIKE ‘[-acdf]’	-, a, c, d, or f
LIKE ‘[ [ ]’	[
LIKE ‘]’	]
LIKE ‘abc[_]d%’	abc_d and abc_de
LIKE ‘abc[def]’	abcd, abce, and abcf

 

In this example, I want to find all user names that starts with the letter “S” and is follow by numbers 0-9.

 

SELECT Text File using OpenRowSet

Here’s a simple query to directly SELECT a Text File.

HDR = Header Row. If YES, that means your first row is a header.

SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Text;Database=E:\;HDR=YES’, ‘SELECT * FROM Log.txt’)

If you need the Microsfot ACE OLEDB 12 driver, please refer to  Microsoft Jet OLEDB 12

 

The requested OLE DB provider Microsoft.Jet.OLEDB.12.0 is not registered

You’re probably getting this error message in BIDS/Data Tools

Error 1 Validation error. Data Flow Task: Package1: The requested OLE DB provider Microsoft.Jet.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. Package1.dtsx 0 0

The issue here is that your computer is 64 bit and both versions (32 bit and 64 bit) of Excel are installed. By installing Microsoft Access Database Engine 2010 Redistributable your issue should go away but this alone did not work for me.

What worked for me is to also install 2007 Office System Driver: Data Connectivity Components which corrected the Microsoft.Jet.OLEDB.12.0 issue.

File Type (extension)                                                        Extended Properties
———————————————————————————————
Excel 97-2003 Workbook (.xls)                                      “Excel 8.0”
Excel 2007 Workbook (.xlsx)                                         “Excel 12.0 Xml”
Excel 2007 Macro-enabled workbook (.xlsm)            “Excel 12.0 Macro”
Excel 2007 Non-XML binary workbook (.xlsb)         “Excel 12.0”

 

I hope this helps solve your issue.

Why use SQLCMD

What’s wrong with using SQL Server Management Studio (SSMS)? Nothing! What’s wrong with you! Well, I’m in a hurry.

As much as I love GNR Patience, I don’t have any… well not so much of it. Thanks Smart Phone era.

I always use SQLCMD when SSIS is not available– not all of my customers install SSIS along with SQL. I once had a project where I had to scan through terabytes of files to find certain image files. Once the files were found, it would immediately write the path to the database. Since this had to be performed and completed over the weekend, I ran hundreds instances of my C# application to seek out the files.

So here’s how it works

In the command prompt (cmd.exe), run sqlcmd /? and you’ll see the following

There’s a lot of options and the ones I frequently use are
-S Server Name
-E Trusted Connection
-Q Query

In the example below, I’m querying a table, however you can always call a stored procedure to INSERT/DELETE/UPDATE/SELECT your data instead of writing out the query– SP is safer

I hope this gives you some insight as to why/when you would use SQLCMD

How to get the Previous and Next Row Data

If you’ve ever wrote reports that requires comparing numbers from previous weeks, months, quarter, and year SQL 2012 and later versions will solve your issue.

In this example, I have a table call HBClaimsRemits_DL_NW. Everyweek I bring in new data into this table. However, I would like to see if the data grew or shrank.

To do this, we use LAG and LEAD. Both of these functions requires that we use PARTITION so that we’re getting the LAG and LEAD data for the specific group of data.

LAG gets the data from the previous record. If no previous record exists, a NULL value will return.

LEAD gets the next row data. If no future record exits, a NULL value will return

Here is the script to show Previous (LAG) and Future (LEAD) records.

SELECT
Name,
RecordCount AS Current_Record_Count,
ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Previous_Record_Count,
ISNULL(LEAD(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Next_Record_Count,
CreateDate
FROM SSIS_Log.dl.event

I like to simplify the results to something more of an Indicator (KPI).

Here is the script with an Indicator field. There’s more scripting involved but the end results is easier to read.

SELECT
Name,
RecordCount,
CASE
WHEN ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) = 0 THEN ‘No Changes’
WHEN RecordCount ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0)
THEN ‘Up by ‘ + CAST(FORMAT(RecordCount – ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0), ‘##,###’) AS VARCHAR(20))
END AS Indicator,
CreateDate
FROM SSIS_Log.dl.event