Transaction Logs

May 29, 2010

If you’re receiving backup copies of client databases, to do customizations or migrations on, and they haven’t been truncating their transaction logs, it’s not unusual for a restored database to have a 3 GB .log file attached to it.

Those can fill up the space on a VPC very quickly.

To shrink them down to size, right-click on the database in SSMS, and select Properties. In the Options section, set the Recovery model to Simple, and click OK.

Right-click on the database again in SSMS, and select Tasks -> Shrink -> Database. Click OK.

Voila. No more Gigabytes.

Advertisements

SLX History Types

May 25, 2010

SLX History Types:

262156  Database Change    DBCHANGE
262150  Dialog             DIALOG
262153  Document           DOC
262154  E-Mail             EMAIL
262151  Event Selection    ESP
262155  Fax                FAX
262157  Form               FORM
262163  Literature Request LITREQ
262145  Meeting            APPT
262148  Note               NOTE
262158  OLE                OLE
262161  Page               PAGE
262162  Personal Activity  PERSONAL
262146  Phone Call         CALL
262160  Process            PROCESS
262149  Question           Q
262152  Report             REPORT
262144  Start              START
262164  Stop               STOP
262147  To-Do              TODO

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))
     RETURNS INT AS
BEGIN

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'
    BEGIN
        SET @LookAtChars = Right(Left(@StringCheckIn,@SpotCount),@LengthOfStringCheckFor)
        IF @LookAtChars = @StringCheckFor
            BEGIN
                SET @StringHere = @SpotCount
                BREAK
            END
        ELSE
            BEGIN
                SET @SpotCount = @SpotCount - 1
                CONTINUE
            END
    END

    RETURN @StringHere
END

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

Tips and Tricks – IV

May 20, 2010

Tips and Tricks IV: The Voyage Home.

  • SalesLogix performance will suffer with cursors being left open. If you are coding in SalesLogix it is very important that you close your Recordsets, setting your Recordsets to Nothing is NOT enough, you should always have code like this:
If oRS.State = 1 Then
    oRS.Close
End If
Set oRS = Nothing
' Need to restrict the Lookup to records where
' Account.Type='Customer' and Account.Location='US'):
LookUpName = "Account:Account"
RestrictAlways = True
Lookup.LookupRestrictField = "TYPE'"
Lookup.LookupRestrictOp = "='Customer' and LOCATION="
Lookup.LookupRestrictValue = "US"
  • Create a “.txt” file. Rename the file to “.udl”. When you double-click on the file it will display the standard connection designer!

And lots of good stuff from Ryan Farley’s site:


SQL for Migrations

May 15, 2010

If you don’t have a fancy tool like Scribe lying around, or if you need to work around its shortcomings/bugs/features, or if you simply need to import into a single table and need more flexibility than the Import Wizard gives you, you can do that in straight SQL, through WG Admin.

First, in Database Manager, set the primary key of the table you’ll be importing into, to be auto-increment. (Right-click on the field, select Properties…, and check the Field is Auto Increment checkbox.)

Then, use SQL like the following to build a result set that contains the SQL INSERT strings (this is for transferring all of the Account and Contact Address records from one SLX database to another, but the concept would obviously be the same for any other sources or tables):

SELECT 'INSERT INTO Address (ENTITYID, TYPE, DESCRIPTION, ADDRESS1, ADDRESS2, CITY, STATE, POSTALCODE, COUNTY, COUNTRY, ISPRIMARY, ISMAILING, SALUTATION, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, ROUTING, ADDRESS3, ADDRESS4, TIMEZONE, OLDADDRESSID) VALUES ('
+ '''' + CASE WHEN SUBSTRING(ENTITYID, 1, 1) = 'C' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFCONTACT WHERE SourceKey = sysdba.Address.EntityID), '') WHEN SUBSTRING(ENTITYID, 1, 1) = 'A' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFACCOUNT WHERE SourceKey = sysdba.Address.EntityID), '') END + ''', '
+ CASE WHEN TYPE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TYPE, '''', '''''') + '''' END + ', '
+ CASE WHEN DESCRIPTION IS NULL THEN 'NULL' ELSE '''' + REPLACE(DESCRIPTION, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS1 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS1, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS2 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS2, '''', '''''') + '''' END + ', '
+ CASE WHEN CITY IS NULL THEN 'NULL' ELSE '''' + REPLACE(CITY, '''', '''''') + '''' END + ', '
+ CASE WHEN STATE IS NULL THEN 'NULL' ELSE '''' + REPLACE(STATE, '''', '''''') + '''' END + ', '
+ CASE WHEN POSTALCODE IS NULL THEN 'NULL' ELSE '''' + REPLACE(POSTALCODE, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTY, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTRY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTRY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISPRIMARY IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISPRIMARY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISMAILING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISMAILING, '''', '''''') + '''' END + ', '
+ CASE WHEN SALUTATION IS NULL THEN 'NULL' ELSE '''' + REPLACE(SALUTATION, '''', '''''') + '''' END + ', '
+ CASE WHEN CREATEDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), CREATEDATE) + '''' END + ', '
+ CASE WHEN CREATEUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN MODIFYDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), MODIFYDATE) + '''' END + ', '
+ CASE WHEN MODIFYUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN ROUTING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ROUTING, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS3 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS3, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS4 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS4, '''', '''''') + '''' END + ', '
+ CASE WHEN TIMEZONE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TIMEZONE, '''', '''''') + '''' END + ', '
+ '''' + ADDRESSID + ''');'
FROM sysdba.Address
WHERE ENTITYID IN (SELECT AccountID FROM sysdba.Account)
OR ENTITYID IN (SELECT ContactID FROM sysdba.Contact)

Run that SQL statement in Management Studio. When the result set appears, right-click, and Select All. Then right-click again, and Copy.

Open Notepad, and Paste from the clipboard into a blank file.

Save the file with ANSI encoding (not Unicode!). Note: If you instead right-click on the SSMS result set and select “Save Results As…”, it will save them as Unicode; so if you’re going that route, you’ll still need to open the file in Notepad, and re-save it as ANSI encoding.

In WG Admin, go to Tools -> Execute SQL…, then right-click over the SQL statement box, and Load Script. (If it shows up as just a few character of gibberish, you saved the Notepad file as Unicode.)

Scroll down to the bottom of the grid, and delete the final (blank) row. Then Test/Execute the SQL.

Clear the auto-increment flag on the PK field.

Note: If any of the fields you’re bringing across have semicolons anywhere in their data, that will crash the transfer: WG Admin automatically starts a new row whenever it’s loading a file and encounters a semicolon. To work around that, before you save the Notepad file, search and replace all of the “);” characters in the file into “):” strings; then manually replace each of the remaining semicolons with another string that doesn’t occur anywhere else in the file (e.g., “.,.”); then change the “):” strings back into “);” strings. After the import, Update the field in question, changing its “.,.” strings back into “;”.

Of course, if your valid data has the “);” string anywhere inside it, you’ll need to work around that, too.

😉


Tips and Tricks – III

May 7, 2010

Yet more Tips and Tricks….

  • To display the Owner lookup dialog, with a default item selected, use:
strSecCodeID = Application.BasicFunctions._
    LookupOwner(strDefaultSecCodeID)
  • To display the Select User dialog, use:
objUser = Application.BasicFunctions.LookupUser
strUserID = objUser.ID
strDisplayName = objUser.DisplayName

To have a default User selected in the lookup, use LookupUserEx instead

  • To have the user select an application to open an attachment with, use OpenAttachmentWith
  • SLX has a number of registry-related functions: RegSetValue, RegGetValue, RegDeleteValue all relate to HKEY_CURRENT_USER\Software\SalesLogix\UserValues and, unlike the WScript.Shell class, can only read/write String data types
  • To edit Activities, use Application.BasicFunctions.ShowActivity or ShowActivityNotePad[Ex]; use ShowHistory to edit History records. Also Application.Activities.GetActivityList and GetActivityByID
  • Processes: Use StartContactProcess to start a Contact Process against the specified ContactID, OpportunityID, or GroupID
  • To open a URL in the default browser, use WebOpen
  • You can store any data type in a SalesLogix global variable … include objects like recordsets
  • To access picklists and their items without using SQL, use (via Stephen Redmond’s DevLogix):
Set pl = Application.PickLists(strMyPickListName)
For i = 0 to pl.Count - 1
    strText(i) = pl.Item(i).Text
Next
    Also note the ItemByText and ItemByShortText methods.

  • Main Views have GroupsPane collections:
For i = 0 To MyMainView.GroupsPane.Selection.Count - 1
    strGroupID(i) = MyMainView.GroupsPane.Selection.item(i)
Next
    They also have similar Tabs collections, and a PopupMenu property which returns a PopupMenuX class object:
mv.PopupMenu.Items(0).Click
  • To set the current group: Application.BasicFunctions.SetCurrentClientGroup strGroupName
  • Application.Translator.StrToInt converts strings to integers, even if the strings are using comma characters as the decimal-separator rather than the thousands-separator (the function always uses the thousands separator specified in the Regional settings for the current Windows user.)
  • Form.Script.C_CheckDates would execute subroutine ‘C_CheckDates’ defined under the form Script tab
  • Microsoft Script Debugger is here
  • You can use the fx_rowaccess() “stored procedure” in a normal SQL statement:
SELECT account, type, division, employees, credithold,
creditamount, fx_rowaccess() from ACCOUNT

Tips and Tricks – II

May 5, 2010

More Tips and Tricks….

  • To execute a method of a control on another form:
Set oForm = Application.Forms("Ticket:Support Ticket Widgets")
oForm.DataGrid1.Refresh
  • You can turn off “Open project at startup” via the Tools > Options menu in Architect
  • To delete an old version of a plugin, right-click on it in Plugin Manager, select Properties…, and remove the checkmark from the Read Only checkbox. Click OK. Then press the Delete key. You will be prompted for whether you are sure that you want to delete the plugin. Click Yes
  • If you set the ShowGroupPanel property of a DataGrid to True, it will create a blank (dark gray) area above the grid’s table. You can then drag any column header from the grid into that area, allowing the user to group by that field
  • The SysInternals Suite from Microsoft contains, among many other goodies, a DebugView utility. If you put lines like
Application.Debug.WriteLine Err.Number
Application.Debug.WriteLine "Creating Recordset"
    into your SLX scripting, and then run the dbgView.exe, you can read the WriteLine’s there

  • The SLX Mail Merge engine calls the System:SLX Mail Merge OnCustomFieldName script whenever it encounters a custom field in a (Word template) document. “This allows us to insert whatever data we need to in the document at merge time” (Stephen Redmond)
  • The recommended way of creating new recordsets in SLX is to use this function:
Application.CreateObject("ADODB.Recordset")
  • Using DoEvents in SLX VBScripting works fine, but there’s also Application.DoEvents
  • To programmatically create a new activity, use the Application.Activities class:
Set Act = Application.Activities.Add(atPhoneCall)
Act.AccountID = strAccountID
Act.StartTime = DateAdd("d", 7, Now)
Act.Save
  • The “Insert:ContactAccount” function, for inserting new Accounts/Contacts, calls the System:ChooseContactAccount plugin
  • When closing a form via Application.BasicFunctions.CloseCurrentView blnValue, passing True causes the form to be closed as if the user clicked the Cancel button; passing False closes the form as if the OK button had been pressed (i.e., also running any validation code before it closes)
  • Use Application.BasicFunctions.GetGroupList(Family, Type) to retrieve a list of either all of the groups available to the current user (Type = 0 returns string names, Type = 1 returns the actual plugin IDs). Use GetGroupSQL to retrieve a particular group’s SQL (by its ID). Use GetGroupIDs to get a handle to the comma-separated list of IDs in a group, and then use GetGroupCount and GetGroupValue (0-based) with that handle to iterate through the list, to parse it out into its individual ID values
  • For parsing general CSV strings, use CSVCount and CSVField (to get the value in the 1-based position, from 1 to CSVCount)
  • Use GetLineCount and GetNthLine to parse strings where there is a carriage return and line feed separating the values (e.g., from ParseName)
  • GetOwnerName returns the name for a given SecCodeID value
  • To get an OTB path where you can create your own temporary files, use GetPersonalDataPath (SLX already stores copies of reports that are run, or checked out of Architect, in that folder)
  • To attach a new file to an ACO record, use InsertFileAttachment. If you already know the path to the file you want to attach, use InsertFileAttachmentEx instead
  • To transform a string in ISO format (yyyymmdd hh:mm:ss) into a date variable, use ISOToDate
  • In custom code (as opposed to the OTB SLX functionality) and on DataGrids, deleting an Account doesn’t automatically delete its Contact, Opportunities, Activites, or History records, etc. To cause that cascade-deletion to happen, you’ll need to call CascadeDelete strEntity, strEntityID for the parent entity. That will cascade through all of the Joined tables where CascadeDelete is set to “Delete”