Linkdump: BIML, BI, Elastisearch-Kibana, SS Interviews, Data protection

BIML Business Markup Language

BI – Logi Analytics. Responsive design, Dashboard design

Helping your BI Consultant be successful:


Command line interface:

Elastisearch and Kibana

Client guide:


Getting started:


SS Interview

Moving the Master Database

Data Protection

SS Stuff

Announcing dbachecks – Configurable PowerShell Validation For Your SQL Instances

Azure Data lakes

Script to find unused indexes

BI Filters in reports

Killing a service broker SPID

Levels of Concurrency

The Many Levels Of Concurrency

Machine Learning described

Download SSMS (SQL Server Management Studio)

Keeping Power BI Dashboards up-to-date

Career tips for the Lone DBA

Idera Resource Center


Posted in Business Intelligence, Linkdump, Project Management, SQL Server | Leave a comment

General implementation checklist

A group that shall remain unnamed at my organization has really borked some recent implementations.  My thoughts on a general implementation checklist they could have considered to radically improve execution:

  1. Realize and prioritize with sponsor input.
  2. Educate stakeholders if they are not informed.
  3. Design with stakeholder input.
  4. Create the solution.
  5. Measure current state.
  6. Consult stakeholders and sponsor.
  7. Sponsor authorizes.
  8. Execute.
  9. Report results to stakeholders and sponsor.
  10. Close out- log, file docs with exceptions and allowances, etc.
Posted in Business | Leave a comment

Gartner magic quarter analyses for BI, Q1 2018

Exploring 2017 Gartner BI Magic Quadrant Results


Posted in Business Intelligence, Linkdump | Leave a comment

Linkdump: Various learnings: SD, JS. SS/BI/BD

10 books every web developer should read to increase their software IQ

Backtracking algorithmic approach:

Javascript framework Comparison:

Inferno Javascript framework:

SQL Server / Business intelligence / Big Data /  Azure Elastic queries:

SQL Server internals:

App shipping / development / delivery:

Red Hat Open Shift Application Runtimes:

Microsoft Visual Studio App Center:

Posted in Business Intelligence, Linkdump, Programming, SQL Server | Leave a comment

T-SQL: Trim after newline / carriage return

USE [db_mes_sandbox]

EXEC [dbo].[usp_CompleteHierarchy3]
@pSerial_number = N’110160766′,
@pFlag_IncludeCcaComponents = 0,
@pFlag_IncludeReplacedParts = 0,
@pDebug = 1

,description = /* This CASE statement returns the first line of description when it contains the word ‘alternate’. */
WHEN CHARINDEX(CHAR(10),REPLACE(description,CHAR(13),CHAR(10)),1) > 0 /*Find values with multiple lines,*/
AND PATINDEX(‘%Alternate%’,description) > 0 /*and, find values containing the word ‘alternate’.*/
THEN LEFT(REPLACE(description,CHAR(13),CHAR(10)), CHARINDEX(CHAR(10),REPLACE(description,CHAR(13),CHAR(10)),1)-1) /*Use only the first line.*/
ELSE description
,raw_description = description
FROM qs_products
WHERE flags&1=0
–AND name=’266698-1′

Posted in SQL Server | Leave a comment

save to DB & update DT+DGV, vs Total UI requery from DB

Here’s code for this… save to database + update local datatable + update local datagridview = faster than refreshing UI from database

if (lblSerialNumberId.Text != “”)
MesQueries.SRUSheetCreation sru = new MesQueries.SRUSheetCreation(_mq);
string serial_number_id = lblSerialNumberId.Text;
string change_orders = txtChangeOrders.Text;
sru.SaveInspectionInfo(serial_number_id, change_orders);
// Save in local DataTable.
int rowNumToUpdate = -1;
for (int i = 0; i < _dtCHData.Rows.Count; i++)
if (_dtCHData.Rows[i][“serial_number_id”].ToString().Equals(serial_number_id))
rowNumToUpdate = i;
break; // early exit of for loop.
_dtCHData.Rows[rowNumToUpdate][“drawing_revision”] = drawing_revision;
_dtCHData.Rows[rowNumToUpdate][“change_orders”] = change_orders;

// Save in local datagrid for display to UI.
dgvSruSheet.Rows[_selectedGridRow].Cells[“drawing_revision”].Value = drawing_revision;
dgvSruSheet.Rows[_selectedGridRow].Cells[“change_orders”].Value = change_orders;

_flagDirtyData = false;

Possibly also useful:

DataTable.AcceptChanges(); //to force update of DataTable.

DataGridView.Invalidate() or InvalidateColumn(), …Row(), …Cell(). Or DataGridView.RefreshEdit(). Or, .UpdateCellValue().  I have not yet found which if any of these are functional.

Posted in C#, Programming | Leave a comment

Meltdown and Spectre patches, via SQLServerCentral


Posted in Linkdump, SQL Server, System/DB Administration | Leave a comment

SQL Server diagnostics notes

This week’s amusement: performance of a recently-implemented Stored Procedure dropped and dropped to intolerable levels. This system cannot be easily upgraded. Significant fragmentation is involved. Memory pressure is involved. Inserts to table variables in multiple stored procs are involved. This post records some investigative googling.

Adjusting file autogrowth in SQL Server production systems:

Scope of Temporary Objects:

Scope of table variable and temp table

Temp tables and table variables scope and persistence

IF object_id(‘tempdb..#MyTempTable’) IS NOT NULL


<TSQL Batch>



Investigating performance of stored procedures:

After a bit of research on MSDN ( I was able to figure out the following query that will provide the list of running processes:

object_name(st.objectid) as ProcName
sys.sysprocesses as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
object_name(st.objectid) is not null

Currently running procedure name:

Fragmented database files / VLF counts:

High Virtual Log File (VLF) Count including :

How Do I Lower a Database’s VLF Count?

Once armed with a list of databases that have high VLF counts, the next step is to shrink the logs to as small as possible then grow them back to the original size, ideally in a single growth. This is best done during off-peak times

Transaction log performance:

Average Disk Queue Length:

SQL Server disk performance metrics – Part 2 – other important disk performance measures

How To: Troubleshooting SQL Server I/O bottlenecks

Increase speed of inserts:

Indexes on Table Variables:

Indexes on Temporary Tables:


Posted in Linkdump, Programming, SQL Server, System/DB Administration | Leave a comment

Enum tip: Initialized

When using a custom Enum to control features like application states, I always include an explicit Initialized state. This eliminates any uncertainty of which value to use for a necessarily-initialized variable that may not have a defined state- for example, an out parameter.

Posted in Uncategorized | Leave a comment

Tips for adjusting TableLayoutPanel

UI adjustments feel clumsy until you struggle through several, but there are some techniques that can help. It’s obviously better to see these in use, but a list is at least something to start with.

  • TableLayoutPanel displays a flyout menu that can be used to adjust dimensions by hand. This opens a dialog with a table of Columns, or Rows if you select that from the droplist. Each row/col has an Absolute or Percentage value. ALWAYS give one of the entries a percentage value- that’s the one that will expand when the user stretches the screen. The exception is when nothing should ever change size. You can make every row exactly the same size, match precise margin/border measurements, etc.
  • After you establish which rows/columns are absolute and which are percentage, you are able to manually adjust the absolute rows/cols by dragging on the UI. This is almost always faster than entering numbers, and usually alleviates the need to know those precise margin/border measurements…
  • Form fonts will auto-scale in the tables. This works well for normal values, but extreme values can look stupid. So be it. Users might deny it, but they know better than to use font size 48 on data entry forms. Users who try to break things get no pity from me.
  • Anchor and Dock properties are similar in practice. I normally use Dock (Fill), but I think Anchor (L R T B) would work equally well. Docked objects cannot be auto-moved; Anchored can. There are pros and cons to each.
  • In really complicated UIs that have several nested TableLayoutPanels or Splitters, always give those nested objects a name so you know what you have selected as you rearrange the UI. This can be far more important than it sounds, especially for redesign.
Posted in C#, Musings, Programming | Leave a comment