Customized Plugins Query

August 15, 2010

 If you need to get a list of customized plugins out of an SLX database—for doing upgrades, etc.—the following query will be useful:

SELECT Family AS Family, [Name], ModifyDate, Company,
    CompanyVersion, Author, Family AS Family2,
    [Type] = CASE [Type]
	WHEN 0 THEN 'Process'
	WHEN 1 THEN 'Sales Process'
	WHEN 2 THEN 'Scripts, Basic (Legacy)'
	WHEN 3 THEN 'Views, Sales (Legacy)'
	WHEN 4 THEN 'Profiles'
	WHEN 5 THEN 'Scripts, SQL'
	WHEN 6 THEN 'System Reports'
	WHEN 7 THEN 'Templates, SalesLogix'
	WHEN 8 THEN 'Groups, Sales'
	WHEN 9 THEN 'Report Profiles'
	WHEN 10 THEN 'Reports, SalesLogix'
	WHEN 11 THEN 'SalesLogix WP templates'
	WHEN 12 THEN 'Templates, Word (2.x, 3.x, 4.x)'
	WHEN 13 THEN 'Macros'
	WHEN 14 THEN 'Strips, Menu'
	WHEN 15 THEN 'Strips, Toolbar'
	WHEN 16 THEN 'Nav Bars'
	WHEN 17 THEN 'Reports, IQ'
	WHEN 18 THEN 'Bitmaps'
	WHEN 19 THEN 'Reports, Crystal'
	WHEN 20 THEN 'Context Menus'
	WHEN 21 THEN 'SupportLogix Reports'
	WHEN 22 THEN 'Listviews, Support (Legacy)'
	WHEN 23 THEN 'Groups, Support'
	WHEN 24 THEN 'Detail Views, Support'
	WHEN 25 THEN 'Templates, Word (5.x)'
	WHEN 26 THEN 'XML Database Schema'
	WHEN 27 THEN 'VBScript'
	WHEN 28 THEN 'Active Forms'
	WHEN 29 THEN 'ActiveX Controls'
	WHEN 30 THEN 'MainViews'
	WHEN 31 THEN 'Global Scripts'
	WHEN 32 THEN 'Ticket'
	WHEN 33 THEN 'Contract'
	WHEN 34 THEN 'XML File'
	WHEN 37 THEN 'FileAttach'
	WHEN 38 THEN 'Text'
	END, Description
FROM sysdba.Plugin
WHERE Released = 'T'
AND Company <> 'SalesLogix'
AND Company <> 'Best Software'
AND Company <> 'Sage Software'
AND Company <> 'Sage'
AND Company <> 'Legacy'
-- and Type IN ('14', '15', '16', '27', '28', '30',
-- '31', '2', '3', '8', '23')
-- and Name NOT LIKE 'C[_]%'
-- underscore character is a wildcard in SQL
AND [Name]+Family IN (SELECT [Name]+Family 
     FROM sysdba.Plugin
     WHERE IsNull(Released, 'F') = 'F' 
     AND Company = 'SalesLogix')
     -- to return only plugins which are customized versions
     -- of OTB ones
ORDER BY [Type], Family, [Name]

Scalable “Cursors” in SQL

July 28, 2010

In T-SQL stored procedures and user-defined functions, there will be times when you need to iterate through a recordset/table, to perform some operation on each record.

That’s what cursors are made for. Except that they don’t scale at all, so you should never use them for sets of more than a few dozen records.

What you can do instead is to “build your own cursor” using a temporary table or table variable, which does scale, even to processing millions of records:

DECLARE @Count int
DECLARE @AccountID char(12)
DECLARE @UserField1 varchar(128)

CREATE TABLE #tblAccount (
     [AccountID] [char](12),
     [UserField1] [varchar](128)

ON #tblAccount (AccountID)

INSERT INTO #tblAccount
FROM sysdba.Account

SELECT @Count = Count(*) FROM #tblAccount

WHILE @Count > 0
          SET @UserField1 = iCount

          SELECT @AccountID = MIN(AccountID) FROM #tblAccount

          UPDATE sysdba.Account
          SET UserField1 = @UserField1
          WHERE AccountID = @AccountID

          DELETE FROM #tblAccount
          WHERE AccountID = @AccountID

          SET @Count = @Count - 1

DROP TABLE #tblAccount

Deleting each record after you’ve processed it speeds up the processing by (IIRC) around an order of magnitude. Using a temporary table and putting an index on the field you’re using for the primary key has similarly positive effects. (You can’t explicitly put an index on a table variable, but you can do so implicitly just by setting a primary key for it.)

T-SQL InStrRev

May 21, 2010

At some point you will need a VB-like InStrRev function in T-SQL. This is a script I found somewhere, for that: 

CREATE FUNCTION dbo.InStrRev (@StringCheckIn VARCHAR(254),@StringCheckFor VARCHAR(254))

Declare @StringHere INT
Declare @SpotCount INT
Declare @LookAtChars VARCHAR(254)
Declare @LengthOfStringCheckIn INT
Declare @LengthOfStringCheckFor INT

SET @StringCheckIn = LTrim(RTrim(@StringCheckIn))
SET @StringCheckFor = LTrim(Rtrim(@StringCheckFor))
SET @LengthOfStringCheckIn = Len(@StringCheckIn)
SET @LengthOfStringCheckFor = Len(@StringCheckFor)
SET @SpotCount = @LengthOfStringCheckIn - @LengthOfStringCheckFor

WHILE @SpotCount > '1'
        SET @LookAtChars = Right(Left(@StringCheckIn,@SpotCount),@LengthOfStringCheckFor)
        IF @LookAtChars = @StringCheckFor
                SET @StringHere = @SpotCount
                SET @SpotCount = @SpotCount - 1

    RETURN @StringHere

To test the function run the following in Query Analyzer:

Declare @string1 VARCHAR(254)
Declare @string2 VARCHAR(254)
Declare @PosString INT

Set @string1 = 'This is the string we are checking for the word are from the right side of the string.'
Set @string2 = 'are'

Set @PosString = dbo.InStrRev(@string1,@string2)

Print @PosString