Using XML datatype to parse comma-delimited columns

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

I Heart Unix

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

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


MySQL’s version of SQL*Loader

Just found the nice command built into MySQL to load data files. It couldn’t have been simpler to use. Just be sure to through the right newline. Using ‘\r\n’ worked for a file generated in SSIS in my case.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]


See more magic here from the MySQL manual.

Dynamicallly build URLs in RightNow

I always forget this, despite it being one of the most important features.

If you want to build a tab, that can pass a value from a custom field in RightNow as part of the parameter you can do it as follows:

• $p_ccf_ — use with contact custom fields
• $p_orgcf_ — use with organization custom fields
• $p_icf_ — use with incident custom fields
• $p_acf_ — use with answer custom fields
• $p_spcf_ — use with sales product custom fields
• $p_ocf_ — use with opportunity custom fields

Why would the “H” would one do this? Think of it this way. Client ABC has an existing portal that uses friendly URLs (like Ruby on Rails). So if I had an account-id custom field on an organization, I could pass that value and the web page shown in the control would present information pertinent to that account-id (aka. organization). HUGE savings.

Turn a WSDL to a referenceable DLL

Found this will surfing the intertubes. I’m using it to build my first Webservice integration into RightNow using their desktop integration API.

Here were my steps in creating the dll.

1. Turn the WSDL into buildable, C-Sharp code

"C:\Program Files\Microsoft SDKs\Windows\v6.0a\bin\wsdl.exe" /l:cs /protocol:SOAP https://www.penproplus.com/pppwebservice/pppwebservice.asmx?WSDL

2. Compile the C-Sharp code into a referenceable DLL

c:\windows\microsoft.net\Framework\v3.5\csc /t:library /r:System.Web.Services.dll /r:System.Xml.dll PPPWebServices.cs

I had a couple of wonky path issues when running these commands. I had to make sure I was running the CSC compiler from the .Net Framework directory. Otherwise, I get some complaints about missing dlls and other grumpy messages

RightNow Desktop Integration .. it begins

I’ve started exploring the new capabilities of RightNow desktop integration. It looks to be quite powerful, assuming I can get my hacking skills around it.

Unit testing code is a pain with the integration API. Each change requires a restart of RightNow. That can take a while, even with a fast connection & computer.

One recommendation, however, is to add the following in the project’s Post-build event. This will automatically deploy the new compiled dll and move it to the proper directory for development testing. It should be part of the default set of templates, but it isn’t.

mkdir "%USERPROFILE%\RightNowDev"
mkdir "%USERPROFILE%\RightNowDev\AddIns"
mkdir "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)"
copy /Y "$(TargetDir)$(TargetName).*" "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)\"

Great stuff.

RightNow Connect DLL

I’ve found myself doing some .Net coding these days. A rest from Ruby on Rails (which is friggin’ awesome).

When using the RightNow Connect DLL I always would get the most common error of all..

“Invalid Server Version. Server ‘ ‘. Connect ‘8.5.0.127′.”

Scratching my head. I closed the new solution in Visual Studio, which caused the solution’s folder structure to be created. After reopening it and re-pointing the reference to the RightNow Connect DLL. Things seem to work. No problems with that A-N-N-O-Y-I-N-G “invalid server version” message. Viola! It works. Good enough.

And now for something completely different…mongrel

I’ve been switching gears lately. I’m not riding the ruby rails or trying to at least.

Thanks to this entry, I’m finally up and running. I’ll probably need this in the future, so I’m saving it for future reference. Thanks for the post.

Configure the Mongrel Cluster
mongrel_rails cluster::configure -e production -p 8000 -N 2 -c /home/railsuser/rails/appname -a 127.0.0.1
A note about this:
-N 2 : Two instances of Mongrel will be started up as part of this cluster
-p 8000: The Mongrel instances will listen at ports starting at 8000 (i.e. 8000 and 8001)
/home/railsuser/rails/appname : Path to the folder where your Rails app resides
-a 127.0.0.1 : This restricts access to localhost.


Vista Upgrade – RDP to Windows 2003

I was having difficulty after the Vista Business upgrade connecting to clients via RDP. I’m doing a lot of work these days from home. The setup is typical, using the RDP ActiveX control to connect to a remote machine.  This didn’t work, however, in Vista.  Instead, the page would slowly draw and eventually crash.

The solution was found! Thanks to this post.  I have no idea what these two little commands do, but it worked like a charm.

netsh interface tcp set global rss=disabled
netsh interface tcp set global autotuninglevel=disabled

Crazy SQL Server Syntax

Crazy, but I must admit the convenience. This came up after loading a table. I needed to generate an incremented value in the SQL Server database. Normally, you could do this by setting the column to be of type IDENTITY. I didn’t have the luxury and didn’t want to reload it anyway.

SQL Command:
declare @intCounter int
set @intCounter = 234234  — some random number

–set the column and increment
update stage_conference
set @intCounter = conference_id = @intCounter + 3 

Thanks to Google, which landed me here.