Converting SQL for embedded use within VBA

Converting SQL for embedded use within VBA

After creating and testing SQL to embed within a VB or VBA application, it needs to be added to a VB project in usable strings. How to convert your SQL easily without introducing errors? Here’s a PowerShell script that takes in a SQL file with a header (SQL) and condenses it into 80+ character strings for copying into your VB code.

$MyFile=get-content -Path "sql.txt"  
$outfile = "sqlNew.txt"
Remove-Item $outfile -ErrorAction SilentlyContinue
for ($i=0; $i-lt $MyFile.Count; $i++)
if ($str.length -gt 80)
if ($firstLine)
$str = '"' + $str + '" _'
$str = '& "' + $str + '" _'
Add-Content $outfile "$($str)`n"
$nextLine = $MyFile[$i]
$nextLine = $nextLine.Replace("`t"," ");
$nextLine = $nextLine.Replace("  "," ");$nextLine = $nextLine.Replace("  "," ");$nextLine = $nextLine.Replace("  "," ");
$idx = $nextLine.indexof("--");
if ($idx -ge 0)
$nextLine = $nextLine.Substring(0,$idx)
$str = $str + ' ' + $nextLine;
if ($firstLine)
$str = '"' + $str + ' "'
$str = '& "' + $str + ' "'
Add-Content $outfile "$($str)`n"
$str = $null;

Smart filtering in BCS

Smart filtering in Business Connectivity Services

Business Connectivity Services allows the rapid creation of reference connections to live legacy data such as tables or views in SQL Server. The wildcard filtering is great, but what if you want to customize it?

Smart MyFilterParm Filtering in BCS

Using a stored procedure requires deviating from the easy out-of-box dynamic SQL, and defining the input parameter(s).

Here’s the pseudocode T-SQL for the smart MyFilterParm filtering. Note that first up to 10 MyFilterParms are listed, based on matching on a specific field, then 50 more generic wildcard matches are matched. This has the advantage of a high speed response, even if a user enters the letter “a” for search, making long distance (inter-farm) lookups more responsive. For the Union, note the fields from each Select need to match precisely in sequence, name and type. Best is if the exact same fields and names are returned that we are using today.

CREATE procedure dbo.sp_MySmartSearch
@MyFilterParmSmart nvarchar(255) = null
SELECT 10 FROM [MyDataBase].[dbo].[CompanyView]
WHERE MyFilterParm LIKE @MyFilterParmSmart + '%'
SELECT 50 FROM [MyDataBase].[dbo].[CompanyView]
WHERE CompanyNM LIKE '%' + @MyFilterParmSmart + '%'

Once this Stored Procedure is written, export the BDCM (using SPD) and edit the XML to provide hard-coded reference to the above Stored procedure, MyFilterParm filter parameter, and fields returned. The BDCM import is not done in SPD, but is instead done in Central Admin in the BCS service app config. Here’s the XML Pseudocode to replace within the Methods XML group in the BDCM (important parts highlighted in larger font):

<Property Name="BackEndObject" Type="System.String">sp_MySmartSearch
<Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine
<Property Name="RdbCommandText" Type="System.String">[dbo].[sp_MySmartSearch]
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure
<Property Name="Schema" Type="System.String">dbo
<Parameter Direction="In" Name="@MyFilterParmSmart">
<TypeDescriptor TypeName="System.String" AssociatedFilter="Wildcard" Name="@MyFilterParmSmart">

You’ll find the XML much easier to edit in Visual Studio (any version) as the nesting is a bit much to handle in Notepad.

MSDN offers a similar example of a stored procedure, in this case, designed to return precisely one row:

Scot Hillier’s BCS book is also an excellent reference:,descCd-DOWNLOAD.html

Sync DB grows indefinitely

Sync DB is a database that is a part of the User Profile Service related to Active Directory synchronization.   Unfortunately it tends to grow in an unconstrained fashion.  SharePoint has no built-in cleanup mechanism.  The table that grows without bounds is “InstanceData”.  The followed Stored Procedure should be first run in waves, so as not to overload the transaction logs or tempDB.  Running on at least a monthly basis is recommended.

The following is a report that shows monthly growth in rowcount:

SELECT COUNT ([lastUpdated]) as countOfInstancedata, month ([created]) as month, YEAR([created]) as year
       FROM [Sync DB].[dbo].[InstanceData]
  group by month ([created]), YEAR ([created]) order by YEAR ([created]), month ([created])

Here’s the stored procedure:

USE [Sync DB]
CREATE PROCEDURE [fim].[TruncateInstanceData]
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)

Alternatively, I’ve been running this loop to gradually delete rows.  You can adjust the loop number, delete size (rowcount) and delay to taste:

EXEC sp_spaceused N'dbo.Instancedata';
 -- Declare local variables
DECLARE @NumberOfLoops AS int;
SET @NumberOfLoops = 500;
DECLARE @CurrentLoop AS int;
SET @CurrentLoop = 0
WHILE @CurrentLoop < @NumberOfLoops BEGIN
set rowcount 10000
delete from [Sync DB].[dbo].[InstanceData] 
where [Sync DB].[dbo].[InstanceData].created <CONVERT(DATETIME,'2012-02-01 00:00:00', 102)
WAITFOR DELAY '00:00:01:00';
SET @CurrentLoop = @CurrentLoop + 1;END
-- Check space used by table after we are done
EXEC sp_spaceused N'dbo.BigLoggingTable';

A more generic WHERE clause I’ve successfully used is:
WHERE [Sync DB1].[dbo].[InstanceData].created <= DATEADD(day, -60, GETUTCDATE())

Direct filtered access to SharePoint Timer Job History

Have you ever needed to scroll through the Timer Job History in Central Administration?  Wow, do a lot of jobs run!  Nice that you can view 2,000 at a time, but even that’s not enough to scroll to a previous day’s Timer Job History.   You can just jump into SQL Studio and use this query to extract the timeframe you want.  I needed a two minute window almost three days ago, here’s the simple query, enjoy!

FROM [SharePoint_Config].[dbo].[TimerJobHistory] 
WHERE starttime >'1/1/12 4:59:00'and EndTime <'1/1/12 5:01:00'