A couple of months ago Ryan Farley wrote an article about how to use a SQL stored procedure to create a Saleslogix table ID. One of the things I always struggled with is how to do it outside a stored procedure, so that you could do something like create a View of new data and add a new ID to that view. In taht way you could essentially insert data into Saleslogix from the result of a query (as long as you don’t have synchronization of course).
Today I created a function that is essentially a base 36 representation of the current date + the number of milliseconds from midnight. This gives a sufficiently unique base to a key. Then in conjunction with a T-SQL rank function I can get a truly unique key that can be used as a SalesLogix table ID. These IDs wont look the same as the Saleslogix ones, but they work to form a unique key. Lets take a look.
First we need the function to generate the base n.
Create Function sysdba.FXintToBase ( @n bigint, @base varchar(1000) -- you could hardcode this instead ) Returns varchar(1000) As Begin Declare @baseNum int Set @baseNum = len(@base) Declare @code varchar(1000) while (@n>0) Select @code=substring(@base,@n%@baseNum+1,1)+isnull(@code,''), @n=@n/@baseNum Return @code End Go
This function accepts to thing:
- A number to convert
- The string representation of the base to use.
With that function created we can then get the current date expressed as month+day+2 digit year+milliseconds past midnight.
So for January 16th, 2014 at 7:30PM we would get a string of “1161470200”. 1=month, 16=day, 14=year, 70200=milliseconds since midnight.
So with that formula I can place that in a SQL function. The date I pass into the first function specifying my base to use being similar to the Saleslogix keys of 0-9 and A-Z.
CREATE Function sysdba.FXGetID() Returns varchar(1000) As Begin Declare @n bigint Declare @base varchar(1000) Set @base = '0123456789ABCDFGHJKLMNOPQRSTUVWXYZ' set @n = cast(datepart(mm,getdate()) as varchar(2)) + cast(datepart(dd,getdate()) as varchar(2)) + right(cast(datepart(yy,getdate()) as varchar(4)) ,2) + cast(DATEDIFF(ms, convert(varchar(10),getdate(),101), getdate()) as varchar(10)); Return sysdba.FXintToBase(@n,@base) End GO
Now to use that I can include my second function in a SQL statement. Combining the results of the function with a rank command gives me a unique key.
Lets say I wanted to create an activity for the first 10 contacts in my database that do not have an activity scheduled. I can use a query like so:
select top 10
right('000000000000' +
sysdba.FXGetID() +
cast(rank() OVER (ORDER BY a.contactid) as varchar(10))
,12) as ActivityID,
a.contactid,
a.accountid,
a.account accountname,
a.lastname + ', ' + a.firstname contactname,
'262148' Type
--,...
from
contact a with(NOLOCK)
left join
activity b with(NOLOCK)
on a.contactid=b.contactid
where b.activityid is null
Obviously this isn’t all you need to create an activity, but you get the idea. Using this kind of function based key generation makes database level transactions work well.
