Referencing a formatted text source field, using the SQLQUERY function and 'LIKE'

Scribe has the ability to insert SQL queries against a source or target (which does not have to be a database for Scribe). This works great when searching against distinct values.

However, what if you need to format a source field to strip out and prefix text, then match it up to a larger field? In SQL you can declare a variable and perform a LIKE statement.

Plugging that statement into Scribe will cause it to fail. Here is the workaround.

Format the source variable, and save it as a calculated variable to call later.

RIGHT( S1, LEN(S1)-SEARCH(" ",S1 ) ) = cvTrim

Then call your query.

SQLQUERY( cv_Trim, "T", "SELECT [TableField] FROM [Database].[dbo].[Table] WHERE [TableField] LIKE '%' + '%s' + '%' " )

%s references the source field.

Comments

SCSM Data Warehouse Cube / Dimensions do not process - Event 33573 / 35574

Recently I received an alert from our SQL server "OLE DB error: OLE DB or ODBC Error: Operation Cancelled HY008. Around that same time, I found a few errors in our Operations Manager log on our SCSM 2012 R2 DataWarehouse Management Server. Specifically Events 33573 and 33574. Event 33573 Warning Message : An Exception was encountered while trying during cube processing. Message= Processing warning encountered - Location: , Source: Microsoft SQL Server 2012 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'WorkItemDim', Column: 'WorkItemDimKey', Value: '18553'. The attribute is 'WorkItemDimKey'.. Well, that appears that a overnight job did not run for our SCSM data warehouse. However, when I looked at the Data Warehouse Jobs in the SCSM Console, all of the status were either set to Not Started, or success. Going back to the logs on t...

Mass Export Service Manager 2012 [SCSM2012] Management Packs

Backup All Unsealed Management Packs In preparation to upgrade our instance from SCSM 2012 R2 to SCSM 2016, I wanted to backup all management packs in the current instance. Here is a PowerShell script to export all of the management packs to a backup directory. Alter at your pleasure. Import-Module smlets Get-SCManagementPack|where-object {! $_.Sealed}|Export-SCManagementPack -TargetDirectory c:\backup This script requires smlets. You can get smlets from here: http://smlets.codeplex.com/ Use at your own risk, I take no responsibility or infer any warranty on this code.

PowerShell - Add telnet client to Windows Server 2012 R2

I was trying to check port specific communication on a server, so I decided to power up good old telnet. Much to my surprise it was not installed on the Server. PowerShell to fix that issue! Add-WindowsFeature telnet-client All set. No Reboot needed. -AJ

My Dynamic World

Search This Blog