Miscellaneous Ramblings on Hacking

June 8, 2010

RightNow .NET API Performance = Not So Good

Filed under: CRM, RightNow — Zack Bethem @ 1:59 am

Just a little update to my previous posting on RightNow‘s .NET API performance.

This API has me CRYING for their Apex Data Loader. Apex was FAST!!! It was extremely fast for selecting records. But it was even fast inserting and updating records. That was an awesome tool — dump the records out, massage them in Access using SQL, pump the records back in, go home an eat lunch. Done in 3hrs.

So here are the latest metrics:

RightNow .Net API

Extract: 1.5MM records in 1hr 4min

Update: 1.5MM records 3days+

Some of you may ask what is the update and how involved is it? I believe it to be a fairly simple task, myself. I’m updating each contact record and setting a custom field’s value to an externally assigned primary key. The one excuse I will allow RightNow is that there are 250 or so custom fields for the contact. I would expect some performance degradation, but a data conversion of 1.5mm records taking longer than 3days is just stupid.

I love coding in .Net, but man is this worthless!

RightNow Analytics — The SQL Way

Filed under: CRM, RightNow — Zack Bethem @ 1:47 am

I always lose sight of this when I need it. That and RightNow’s website hides this from anyone’s view (side rant: really, how does one search in Google for RightNow the product?). Gartner says RightNow is #1 for customer support KB search, but I can never find shit using their support KB search. (side rant: seriously how much was Gartner paid?)

Create a New Report via SQL

To do this, we’re going to be using a custom script.

  1. Define a filter that will always return zero rows.
    filter: 1=0
  2. Select Level > Custom Scripts
  3. Select the ‘Finish tab of custom scripts
  4. Paste in the following while modifying the SQL to your liking:$temp=array();
    $row_idx = 0;
    //Run the desired query
    $query=sql_prepare
    (sprintf("
    SELECT label,ac_id, header_code, init_code, process_code, exit_code
    FROM ac_scripts a, labels l
    where a.ac_id = l.label_id
    and tbl=121
    and (header_code is not NULL
    or init_code is not NULL
    or process_code is not NULL
    or exit_code is not NULL)
    "));
    //Each column in the SELECT clause will need to be returned as the appropriate data-type (INT for integer, NTS for string, DTTM for datetime)
    sql_bind_col($query,1,BIND_NTS,80);
    sql_bind_col($query,2,BIND_INT,0);
    sql_bind_col($query,3,BIND_NTS,1000);
    sql_bind_col($query,4,BIND_NTS,1000);
    sql_bind_col($query,5,BIND_NTS,1000);
    sql_bind_col($query,6,BIND_NTS,1000);
    //For each record returned in the above query, return it as one row of output, each field in its respective column
    while ($temp = sql_fetch($query))
    {
    $exit_obj[$row_idx][0]->val = $temp[0];
    $exit_obj[$row_idx][1]->val = $temp[1];
    $exit_obj[$row_idx][2]->val = $temp[2];
    $exit_obj[$row_idx][3]->val = $temp[3];
    $exit_obj[$row_idx][4]->val = $temp[4];
    $exit_obj[$row_idx][5]->val = $temp[5];
    ++$row_idx;
    }
    //Clear your buffer
    sql_free($query);
  5. $exit_obj is the array returned, whose values are subsequently displayed in the report

May 26, 2010

Outlook Barfing on GMAIL via IMAP

Filed under: Uncategorized — Zack Bethem @ 5:56 pm

No idea what happened, but found that Outlook wouldn’t send anymore messages via GMAIL IMAP. I found this little morsel and viola… problem fixed.

http://theofficecubicle.com/2008/02/01/outlook-2003-red-x-where-embedded-image-should-be-in-email/

  1. Exit out of Outlook 2003 on the machine with the issue
  2. Open the registry (Start->Run->type cmd->click Ok)
  3. Navigate and open the following registry key HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security\OutlookSecureTempFolder
  4. Within the key…it should say something like  %USERPROFILE%\Local Settings\Temporary Internet Files\OLK3D\
  5. Highlight and delete %USERPROFILE%\Local Settings\Temporary Internet Files\OLK3D\
  6. Close out of the registry and open Outlook 2003!
  1. Exit out of Outlook 2003 on the machine with the issue
  2. Open the registry (Start->Run->type cmd->click Ok)
  3. Navigate and open the following registry key HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security\OutlookSecureTempFolder
  4. Within the key…it should say something like  %USERPROFILE%\Local Settings\Temporary Internet Files\OLK3D\
  5. Highlight and delete %USERPROFILE%\Local Settings\Temporary Internet Files\OLK3D\
  6. Close out of the registry and open Outlook 2003!

February 24, 2010

Fun with c# and the RightNow API

Filed under: .Net, RightNow — Zack Bethem @ 12:29 am

I wanted to dump out data from RightNow’s Contact tables. The intention was to load a SQL Server table that would then be used in later ETL for data quality rules.

The code does the magic I want.

  • Execute a RightNow report for a range of contacts.c_id values
  • Store the results in a SQL Server table on my local machine

The only problem is performance. SFDC can dump out 2-3million rows in an hour. My code was far from efficient as it was built for a quick-exercise. The code for 1.5million rows would finish in about 14hours. YUCK! Update: I was able to score 760k records in 1.5hrs … much better.

Here’s my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using obj.api.rightnow.com;
using fault.api.rightnow.com;
using api.rightnow.com;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Becker_DataExtract
{
    class Program
    {
        static void Main(string[] args)
        {

            RNOWObjectFactory ofactory = new RNOWObjectFactory(Properties.Resources.RNOWConnection);
            ofactory.login(Properties.Resources.RNOWLogin, Properties.Resources.RNOWPass);

            //database stuff
            string connString = "server=(local)\\SQLEXPRESS;Initial Catalog=BeckerPOC;User ID=becker;Password=becker";
            string insertSQL = @"insert into RNContacts(c_id,org_id,last_name,first_name,email,email1,email2,street
,city,prov,postal_code,country,ph_asst,ph_fax,ph_home,ph_mobile,ph_office, RNLastUpdated)
values
(@c_id, @org_id, @last_name, @first_name, @email, @email1, @email2, @street
,@city, @prov,@postal_code,@country,@ph_asst,@ph_fax,@ph_home,@ph_mobile,@ph_office, @RNLastUpdated)
";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            object[][] reportResult;

            System.Console.WriteLine("Staring process: " + DateTime.Now.ToShortTimeString());

            for (int i = 1; i <= 25; i++)
            {
                List<RNOWAcFilter> reportFilters = new List<RNOWAcFilter>();
                RNOWAcFilter reportFilter = new RNOWAcFilter();
                reportFilter.Operator = Convert.ToInt32(RNOWUtil.SearchOperator.RANGE);
                reportFilter.Value = i.ToString() + "|" + (i * 1000).ToString();
                reportFilter.FilterId = 1;
                reportFilters.Add(reportFilter);
                reportResult = ofactory.ExecuteReport(Convert.ToInt32(Properties.Resources.RNOWExtractReport), reportFilters.ToArray());

                foreach (Object[] result in reportResult)
                {
                    SqlCommand cmdSQL = new SqlCommand(insertSQL, conn);

                    SqlParameter c_id = new SqlParameter("@c_id", Convert.ToString(result[0]));
                    cmdSQL.Parameters.Add( c_id );

                    SqlParameter org_id = new SqlParameter("@org_id", Convert.ToString(result[1]));
                    cmdSQL.Parameters.Add(org_id);
                    SqlParameter last_name = new SqlParameter("@last_name", Convert.ToString(result[2]));
                    cmdSQL.Parameters.Add(last_name);
                    SqlParameter first_name = new SqlParameter("@first_name", Convert.ToString(result[3]));
                    cmdSQL.Parameters.Add(first_name);
                    SqlParameter email = new SqlParameter("@email", Convert.ToString(result[4]));
                    cmdSQL.Parameters.Add(email);
                    SqlParameter email1 = new SqlParameter("@email1", Convert.ToString(result[5]));
                    cmdSQL.Parameters.Add(email1);
                    SqlParameter email2 = new SqlParameter("@email2", Convert.ToString(result[6]));
                    cmdSQL.Parameters.Add(email2);
                    SqlParameter street = new SqlParameter("@street", Convert.ToString(result[7]));
                    cmdSQL.Parameters.Add(street);
                    SqlParameter city = new SqlParameter("@city", Convert.ToString(result[8]));
                    cmdSQL.Parameters.Add(city);
                    SqlParameter prov = new SqlParameter("@prov", Convert.ToString(result[9]));
                    cmdSQL.Parameters.Add(prov);
                    SqlParameter postal_code = new SqlParameter("@postal_code", Convert.ToString(result[10]));
                    cmdSQL.Parameters.Add(postal_code);
                    SqlParameter country = new SqlParameter("@country", Convert.ToString(result[11]));
                    cmdSQL.Parameters.Add(country);
                    SqlParameter ph_asst = new SqlParameter("@ph_asst", Convert.ToString(result[12]));
                    cmdSQL.Parameters.Add(ph_asst);
                    SqlParameter ph_fax = new SqlParameter("@ph_fax", Convert.ToString(result[13]));
                    cmdSQL.Parameters.Add(ph_fax);
                    SqlParameter ph_home = new SqlParameter("@ph_home", Convert.ToString(result[14]));
                    cmdSQL.Parameters.Add(ph_home);
                    SqlParameter ph_mobile = new SqlParameter("@ph_mobile", Convert.ToString(result[15]));
                    cmdSQL.Parameters.Add(ph_mobile);
                    SqlParameter ph_office = new SqlParameter("@ph_office", Convert.ToString(result[16]));
                    cmdSQL.Parameters.Add(ph_office);
                    SqlParameter RNLastUpdated = new SqlParameter("RNLastUpdated", Convert.ToString(result[17]));
                    cmdSQL.Parameters.Add(RNLastUpdated);

                    //da.InsertCommand = cmdSQL;
                    cmdSQL.ExecuteNonQuery();
                    cmdSQL.Dispose();
                }

                System.Console.WriteLine("Done with loop run: " + i.ToString());
            }

            System.Console.WriteLine("End of process: " + DateTime.Now.ToShortTimeString());
            conn.Close();
            ofactory.logout();
            ofactory.Dispose();

        }

    }

}

Update:

Looking at the comments on this post, it appears that I have RightNow checking my code. Yes. there is a bug in it. The idea is to use a report to dump out data. So the start range needs to be incremented more than it is in the AcFilter.

February 17, 2010

C# interacting with a web form

Filed under: .Net, RightNow — Zack Bethem @ 11:45 pm

Sweet.. I love the web. I needed to build a POC for a RightNow project I’m working on. BAZINGA

Not only was it an awesome domain name. But, the code is exactly what I require. THANKS!

February 11, 2010

Querying SQL Server for Table sizes

Filed under: Uncategorized — Zack Bethem @ 9:23 pm

Found this gem and I MUST save it for future reference.

http://www.mssqltips.com/tip.asp?tip=1177

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 
INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
SET @table_name = REPLACE(@table_name, ']',''); 
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname  insert into temp_table_sizes

FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

I followed it up with the following modification, however:

select *, cast(substring(data,1, charindex(' ',data,1)-1)/1024/1024 as decimal) as data_GB from temp_table_sizes order by cast(substring(data,1, charindex(' ',data,1)-1) as integer) desc , row_count desc

December 22, 2009

Fun with Unica Easysets

Filed under: Uncategorized — Zack Bethem @ 3:42 am

I don’t feel like looking this up everytime.. so here it is.

Problem

ETL finishes and you need to automate the refresh of your Unica easysets in CustomerInsight.

Solution

This dos script does the work.. yeah that’s right.. so sexy it is! DOS.

@echo off
@echo "Refreshing Customer EasySet"
e:
cd e:\unica\Insight
nettracker customer -update
@echo "Customer Easyset refresh complete"
@echo
@echo "Refreshing Sales EasySet"
nettracker bintest -update
@echo "Sales EasySet refresh complete"
@echo "Refreshing Response EasySet"
nettracker responses -update
@echo "Response Easyset refresh complete"
@echo on

Some things of note. The script is updating three easysets of the name: customer, bintest, and responses. This is their system name and not the “friendly” name.

October 20, 2009

SSIS: Waiting for a file

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 4:50 pm

With longer running processes, it is a common practice to create a 0-byte file as a signal to a dependent process. An example would be the data transfer via FTP of several files to a staging directory. This post shows how to wait for the 0-byte file.

Control Flow

When complete, the control flow for this package will have the following items defined:

  • For Loop Container – this makes the process continue in a loop until the 0byte file is found
  • Check File script task – this checks for the presence of a file and sets a Boolean variable to True or False
  • Sleep script task – this will create a time delay so that the process isn’t checking every nano-second
  • Move CheckFile script task – this moves the 0byte file to avoid conflicts with subsequent data runs

The control flow will appear as follows:

Control Flow

Variables

The following variables need to be defined:

Variable DataType Purpose
CheckFile String Filename of the 0-byte file to look for
CheckFileExists Boolean Boolean flag that is set to true upon detection of the 0-byte file
FFDataPath String Staging directory where the 0-byte file is expected to be placed
SleepMinutes Int32 Number of minutes to sleep between checks for the 0-byte file
Definition: For Loop Container

Configured to loop while User variable User::CheckFileExists is equal to ‘False’.

InitExpression
EvalExpression @[User::CheckFileExists]==False
AssignExpression
Definition: CheckFile Script Task

Configured to manipulate the value of the User variable User::CheckFileExists to ‘True’ or ‘False’.

ReadOnlyVariables User::CheckFile, User::FFDataPath
ReadWriteVariables User::CheckFileExists

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO


Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Created by: Zack Bethem @ AmberLeaf
        '
        Dim fileLoc, fileName As String
        If Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::CheckFile") = True Then
            fileLoc = CStr(Dts.Variables("User::FFDataPath").Value)
            fileName = CStr(Dts.Variables.Item("User::CheckFile").Value)
            'debug System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + "FileName:" + fileName)
            If File.Exists(fileLoc + fileName) Then
                Dts.Variables.Item("User::CheckFileExists").Value = True
                'debug System.Windows.Forms.MessageBox.Show("File exists")
            Else
                Dts.Variables.Item("User::CheckFileExists").Value = False
                'debug System.Windows.Forms.MessageBox.Show("File not exists")
            End If
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub

End Class

The script task is configured to execute the Sleep task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Capture2

Definition: Sleep Script Task

Configured to sleep or pause the process.

ReadOnlyVariables User::SleepMinutes
ReadWriteVariables

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Zack Bethem @ AmberLeaf
        ' Based on: http://blogs.pragmaticworks.com/mike_davis/2009/06/make-an-ssis-package-delay-or-wait-for-data.html
        '
        If Dts.Variables.Contains("User::SleepMinutes") = True Then
            Dim min As Double = Convert.ToDouble(Dts.Variables("SleepMinutes").Value) * 60
            Dim ms As Int32 = Convert.ToInt32(min * 1000)
            System.Threading.Thread.Sleep(ms)

            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

    End Sub

End Class

The For Loop container is configured to execute the Move CheckFile task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Capture3

Definition: Move CheckFile Script Task

Configured to move the 0-byte handoff file to an Archive location with the appended suffix of the date in yyyyMMddHH format.

ReadOnlyVariables User::CheckFile,User::FFDataPath,User::FFArchivePath
ReadWriteVariables

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        'check if vars exist
        If Dts.Variables.Contains("User::CheckFile") = True AndAlso _
        Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::FFArchivePath") = True Then

            Dim srcFile, tgtFile, tgtFileName, searchStr As String

            srcFile = CStr(Dts.Variables("User::FFDataPath").Value) + _
            CStr(Dts.Variables("User::CheckFile").Value)

            tgtFileName = CStr(Dts.Variables("User::CheckFile").Value)

            'add a datastamp on handoff file
            tgtFileName = Left$(tgtFileName, InStrRev(tgtFileName, ".") - 1) + _
            "_" + CStr(Format(Now, "yyyyMMddHH")) + _
            Mid(tgtFileName, InStrRev(tgtFileName, "."))

            'System.Windows.Forms.MessageBox.Show(tgtFileName)

            tgtFile = CStr(Dts.Variables("User::FFArchivePath").Value) + tgtFileName

            'System.Windows.Forms.MessageBox.Show(tgtFile)

            Try
                File.Move(srcFile, tgtFile)
                Dts.Events.FireInformation(0, "", "File moved to: " + tgtFile, "", 0, True)
                Dts.TaskResult = Dts.Results.Success
            Catch ex As Exception
                Dts.Events.FireInformation(1, "", "File move failure. Tried to move to: " + tgtFile, "", 0, False)
                Dts.TaskResult = Dts.Results.Failure
            End Try

        End If

    End Sub


End Class

July 15, 2009

Using XML datatype to parse comma-delimited columns

Filed under: Uncategorized — Zack Bethem @ 5:20 am

This was the coolest trick I’ve seen in a while.
Problem: need to parse a comma delimited file


select email
, Split.a.value('.','Varchar(50)') as kid_age
, row_number() over (partition by email order by email) as row_cnt
from (
select email
, cast('' + REPLACE(kid_age,',','') + '' as xml) as kid_age
from stg_profile_data ) x
cross apply kid_age.nodes('/M') split(a)

Mucho thanks to where Google found it

July 3, 2009

I Heart Unix

Filed under: Uncategorized — Zack Bethem @ 7:35 pm

Use SED to get rid of those nasty empty lines in a text file:

sed '/^$/ d' someFile > someNewFile
mv someNewFile someFile

« Newer PostsOlder Posts »

Blog at WordPress.com.