fiogf49gjkf0d
Generating SalesLogix IDs outside of SalesLogix can be a challenging concept. Third party applications go through the SalesLogix OLEDB provider and call the “slx_dbids” function to generate an ID for the specified table. A terrific article about how to do this can be found in the article Creating SalesLogix Table ID Values - The Definitive Guide.
The problem with this approach is that I don’t want to go through a third party application to insert records into my SalesLogix database. The slx_dbids function actually performs a read from the SITEKEYS table, and then writes back to the SITEKEYS table. We can perform this read and write ourselves from within SQL Server, but I would not recommend it because it is not a supported method by SalesLogix and if the ID generation ever changes in a future version, you will be out of luck.
SQL Server 2005 introduced the CLR and this technology allows for the use of managed code in SQL Server using the .NET framework. Using the CLR we can now perform a number of tasks, including making OLEDB connections to databases. As a result, we can now call slx_dbids from SQL Server, through the CLR.
The first step in this process is to create a new C# Class project in Visual Studio. I decided to create two functions in my class; one to retrieve one ID at a time, and a second to retrieve a specified number of IDs at a time. The reason I want two is because retrieving one at a time is convenient, but when performing a large insert, one at a time is not very efficient.
Here is the function to return one ID at a time:
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static string slx_GetNextID_ConnString(string table, string ConnectionString)
{
string retval;
// the connection string can not be pulled from a config file in SQLCLR
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
string keyname = table;
switch (table.ToUpper())
{
case "ATTACHMENT": keyname = "FILEATTACH"; break;
case "USERNOTIFICATION": keyname = "USERNOTIFY"; break;
case "AGENTS": keyname = "HOSTTASK"; break;
case "RESOURCELIST": keyname = "RESOURCE"; break;
case "USEROPTION": keyname = "USERVIEW"; break;
case "JOINDATA": keyname = "JOIN"; break;
case "PROCEDURES": keyname = "PROCEDURE"; break;
case "SEC_FUNCTIONOWNER": keyname = "FUNCTIONHANDLER"; break;
}
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', 1)", keyname), conn))
{
retval = (string)cmd.ExecuteScalar();
cmd.Dispose();
}
conn.Dispose();
}
return (retval);
}
As you can see this function is passed the table name, and a connection string. There are a few table exceptions and those are taken care of with a switch. The connection string is passed to the function because this string will need to change for each environment it is used in.
Here is the function for multiple IDs:
[SqlFunction(FillRowMethodName = "slx_GetNextIDs_ConnString_Fill", TableDefinition = "ID NVARCHAR(12)", DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static IEnumerable slx_GetNextIDs_ConnString(string table, int count, string ConnectionString)
{
OleDbConnection conn = new OleDbConnection(ConnectionString);
ArrayList resultCollection = new ArrayList();
string keyname = table;
switch (table.ToUpper())
{
case "ATTACHMENT": keyname = "FILEATTACH"; break;
case "USERNOTIFICATION": keyname = "USERNOTIFY"; break;
case "AGENTS": keyname = "HOSTTASK"; break;
case "RESOURCELIST": keyname = "RESOURCE"; break;
case "USEROPTION": keyname = "USERVIEW"; break;
case "JOINDATA": keyname = "JOIN"; break;
case "PROCEDURES": keyname = "PROCEDURE"; break;
case "SEC_FUNCTIONOWNER": keyname = "FUNCTIONHANDLER"; break;
}
OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', {1})", keyname, count), conn);
conn.Open();
OleDbDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (r.Read())
{
resultCollection.Add(r.GetString(0));
}
r.Dispose();
cmd.Dispose();
conn.Dispose();
return (resultCollection);
}
private static void slx_GetNextIDs_ConnString_Fill(object source, out string ID)
{
ID = (string)source;
}
This function is a little different because it is returning a table instead of a single value. It loops through the result returned from slx_dbids and adds the results to an array, which is then returned. As a result a fill function is required, and in this case is named slx_GetNextIDs_ConnString_Fill.
Next the project can be built, and the assembly can be added to SQL. If the dll is stored in a location where SQL can access it, you can use the following SQL command. I have named the assembly "SalesLogixHelper".
CREATE ASSEMBLY [SalesLogixHelper]
AUTHORIZATION [dbo]
FROM 's:\MSSQL\CLR\SalesLogixHelper.dll'
WITH PERMISSION_SET = UNSAFE
Now that the assembly has been added to our database we can create SQL functions to access these assembly functions. Here are the two SQL functions to call the assembly:
CREATE FUNCTION [dbo].[fn_GetNextSLXID_ConnString](@table [nvarchar](100), @ConnectionString [nvarchar](max))
RETURNS [nvarchar](12) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SalesLogixHelper].[SLXHelper].[slx_GetNextID_ConnString]
CREATE FUNCTION [dbo].[fn_GetNextSLXIDs_ConnString](@table [nvarchar](100), @count [int], @ConnectionString [nvarchar](max))
RETURNS TABLE (
[ID] [nvarchar](12) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SalesLogixHelper].[SLXHelper].[slx_GetNextIDs_ConnString]
Please note how these functions have the same parameters as the CLR functions. Feel free to use these functions to get your IDs. The issue I have with these functions is they need to be called with a connection string to the same database they are being called from. As a result I created the following functions which simply call above functions, but the connection strings are hardcoded into the function.
CREATE FUNCTION [dbo].[fn_GetNextSLXID] (@table nvarchar(100))
RETURNS nvarchar(12)
WITH ENCRYPTION
AS
BEGIN
DECLARE @ConnString nvarchar(max)
/* OLE DB Services=-4 is needed or you will get an error when called in a sp */
SELECT @ConnString = CASE
WHEN @@SERVERNAME = 'SLXDEV' AND DB_NAME() = 'SLX_Dev' THEN 'Provider=SLXOLEDB.1;Password=slxdevpassword;Persist Security Info=True;User ID=admin;Initial Catalog=SLX_DEV;Data Source=SLXDEV;OLE DB Services=-4;Extended Properties="PORT=1706;LOG=OFF;CASEINSENSITIVEFIND=ON;"'
WHEN @@SERVERNAME = 'SLXDEV' AND DB_NAME() = 'SLX_Test' THEN 'Provider=SLXOLEDB.1;Password=slxtestpassword;Persist Security Info=True;User ID=admin;Initial Catalog=SLX_TEST;Data Source=SLXDEV;OLE DB Services=-4;Extended Properties="PORT=1706;LOG=OFF;CASEINSENSITIVEFIND=ON;"'
ELSE NULL
END
IF @ConnString IS NULL
BEGIN
RETURN NULL
END
RETURN dbo.[fn_GetNextSLXID_ConnString](@table, @ConnString)
END
GO
CREATE FUNCTION [dbo].[fn_GetNextSLXIDs] (@table nvarchar(100), @count int)
RETURNS @retTable TABLE
(
RowNum INT /* used to join the results */
, [ID] [nvarchar](12) NULL
)
WITH ENCRYPTION
AS
BEGIN
DECLARE @ConnString nvarchar(max)
/* OLE DB Services=-4 is needed or you will get an error when called in a sp */
SELECT @ConnString = CASE
WHEN @@SERVERNAME = 'SLXDEV' AND DB_NAME() = 'SLX_Dev' THEN 'Provider=SLXOLEDB.1;Password=slxdevpassword;Persist Security Info=True;User ID=admin;Initial Catalog=SLX_DEV;Data Source=SLXDEV;OLE DB Services=-4;Extended Properties="PORT=1706;LOG=OFF;CASEINSENSITIVEFIND=ON;"'
WHEN @@SERVERNAME = 'SLXDEV' AND DB_NAME() = 'SLX_Test' THEN 'Provider=SLXOLEDB.1;Password=slxtestpassword;Persist Security Info=True;User ID=admin;Initial Catalog=SLX_TEST;Data Source=SLXDEV;OLE DB Services=-4;Extended Properties="PORT=1706;LOG=OFF;CASEINSENSITIVEFIND=ON;"'
ELSE NULL
END
IF @ConnString IS NULL
BEGIN
INSERT INTO @retTable ([RowNum],[ID])
SELECT NULL, NULL
END
ELSE
BEGIN
INSERT INTO @retTable ([RowNum], [ID])
SELECT
ROW_NUMBER() OVER (ORDER BY [ID]) as RowNum
, [ID]
FROM dbo.[fn_GetNextSLXIDs_ConnString](@table, @count, @ConnString)
END
RETURN;
END
There are a few things to note in these functions. First is WITH ENCRYPTION; admin passwords are in here, so just to keep this a little more secure, they are encrypted. Second, the function checks the server name and the database name to decide on a connection string so the exact same function can be used in every SalesLogix database you are using. Third is the “OLE DB Services=-4”, in the connection string. This is used to turn off connection pooling and if connection pooling is left on, you will get an error in SQL 2008. Lastly, we are also including a ROW_NUMBER() in the table valued function. This is not required, but it comes in handy when assigning IDs to rows.
These functions can now be called with the following commands:
SELECT * FROM dbo.fn_GetNextSLXIDs('ACCOUNT',5);
SELECT dbo.fn_GetNextSLXID('ACCOUNT');
A full merge would look something like this:
DECLARE @nIDCount AS INT
/* get all the records to insert/update and store them in a temp table */
SELECT
ROW_NUMBER() OVER (ORDER BY d.C_DISTRIBUTORID) as RowNum /* get the rownumber for joining purposes, we really only use the rownumber for records where C_DISTRIBUTORID is null so order by that */
, d.C_DISTRIBUTORID
, extd.ZIP
, extd.DISTRIBUTORNAME
, d.MODIFYDATE /* we are getting this so we know what records it is safe to update */
INTO #tmpDist
FROM ExternalDB.dbo.Distributor extd
LEFT OUTER JOIN sysdba.C_DISTRIBUTOR d ON extd.ZIP = d.ZIPCODE
/* determine if an insert/update needs to be made */
WHERE (COALESCE(d.DISTRIBUTORNAME,'') <> COALESCE(extd.DISTRIBUTERNAME,'')
/* the insert records currently have a null C_DISTRIBUTORID column so populate them */
/* we could avoid doing this by using this function in the above select statement, but that would get one at a time, and is less efficient when processing a lot of records */
/* first we need the count */
SELECT @nIDCount = COUNT(1) FROM #tmpDist WHERE C_DISTRIBUTORID IS NULL
IF (@nIDCount > 0)
BEGIN
UPDATE d
SET C_DISTRIBUTORID = i.ID
FROM #tmpDist d
INNER JOIN dbo.fn_GetNextSLXIDs('C_DISTRIBUTOR',@nIDCount) i on d.RowNum = i.RowNum
END
MERGE sysdba.C_DISTRIBUTOR as target
USING #tmpDist as source
ON target.C_DISTRIBUTORID = source.C_DISTRIBUTORID
WHEN MATCHED AND target.MODIFYDATE = source.MODIFYDATE /* comparing against the rows original modifydate */
THEN UPDATE
SET DISTRIBUTORNAME = source.DISTRIBUTORNAME
, MODIFYDATE = GETUTCDATE()
, MODIFYUSER = 'ADMIN'
WHEN NOT MATCHED
THEN INSERT
(
C_DISTRIBUTORID
, CREATEUSER
, CREATEDATE
, MODIFYUSER
, MODIFYDATE
, ZIPCODE
, DISTRIBUTORNAME
)
VALUES
(
source.C_DISTRIBUTORID
, 'ADMIN'
, GETUTCDATE()
, 'ADMIN'
, GETUTCDATE()
, source.ZIP
, source.DISTRIBUTORNAME
)
OUTPUT $ACTION,INSERTED.C_DISTRIBUTORID;
IF (OBJECT_ID('tempdb..#tmpDist') IS NOT NULL)
DROP TABLE #tmpDist
For the above example I used an example custom table called C_DISTRIBUTOR. One thing you will notice with this method is that it is much faster and can handle much more data than the typical SalesLogix integration tool. This is because most of these tools insert/update one record at a time. The advantage of inserting/updating one record at a time is that the database locks do not last very long. If the above method was used on a large amount of data during production hours, there is a good chance you will lock out users and as a result get some phone calls.
In an attempt to mimic this one-at-a-time processing during production hours, but still perform massive updates outside of production hours I came up with the following function that analyzes current SalesLogix activity and decides on the number of records that should be inserted/updated at a time:
CREATE FUNCTION [dbo].[fn_GetBatchSize]()
RETURNS INT
AS
BEGIN
DECLARE @nConnCount INT
/* get the number of connections to the database */
SELECT @nConnCount = SUM(
CASE
WHEN p.[blocked] <> 0 OR p.[waittime] <> 0 THEN 500 /* we don't want to do much if there are currently blocked or waiting processes */
WHEN p.[program_name] = 'SalesLogix' THEN 2 /* SalesLogix is more important so give it 2 */
ELSE 1
END)
FROM master.dbo.sysprocesses p
WHERE p.[dbid] = DB_ID() /* current database */
AND p.[spid] > 50 /* only count user processes */
/* decide the batch size */
RETURN(SELECT
CASE
WHEN @nConnCount < 20 THEN 999999999 /* a really big number, we could use -1, but then we would need to put logic in the update to check for that */
WHEN @nConnCount >= 20 AND @nConnCount < 50 THEN 10000
WHEN @nConnCount >= 50 AND @nConnCount < 100 THEN 1000
WHEN @nConnCount >= 100 AND @nConnCount < 150 THEN 500
WHEN @nConnCount >= 150 AND @nConnCount < 200 THEN 100
WHEN @nConnCount >= 200 AND @nConnCount < 300 THEN 10
WHEN @nConnCount >= 300 AND @nConnCount < 400 THEN 5
WHEN @nConnCount >= 400 AND @nConnCount < 500 THEN 2
WHEN @nConnCount >= 500 THEN 1
END)
END
You will obviously need to adjust these numbers to your system, but this is what works for us. A nice way to test these numbers is to create a SQL job that calls this function and writes the result a table throughout the day.
To use this function with the pervious merge example, we can now do something like this:
DECLARE @nIDCount AS INT
DECLARE @nRowCount AS INT = 1
DECLARE @nBatchSize AS INT = 1
/* get all the records to insert/update and store them in a temp table */
SELECT
ROW_NUMBER() OVER (ORDER BY d.C_DISTRIBUTORID) as RowNum /* get the rownumber for joining purposes, we really only use the rownumber for records where C_DISTRIBUTORID is null so order by that */
, d.C_DISTRIBUTORID
, extd.ZIP
, extd.DISTRIBUTORNAME
, d.MODIFYDATE /* we are getting this so we know what records it is safe to update */
INTO #tmpDist
FROM ExternalDB.dbo.Distributor extd
LEFT OUTER JOIN sysdba.C_DISTRIBUTOR d ON extd.ZIP = d.ZIPCODE
/* determine if an insert/update needs to be made */
WHERE (COALESCE(d.DISTRIBUTORNAME,'') <> COALESCE(extd.DISTRIBUTERNAME,'')
/* the insert records currently have a null C_DISTRIBUTORID column so populate them */
/* we could avoid doing this by using this function in the above select statement, but that would get one at a time, and is less efficient when processing a lot of records */
/* first we need the count */
SELECT @nIDCount = COUNT(1) FROM #tmpDist WHERE C_DISTRIBUTORID IS NULL
IF (@nIDCount > 0)
BEGIN
UPDATE d
SET C_DISTRIBUTORID = i.ID
FROM #tmpDist d
INNER JOIN dbo.fn_GetNextSLXIDs('C_DISTRIBUTOR',@nIDCount) i on d.RowNum = i.RowNum
END
/* now we have all the records to insert/update so use a merge and get this data loaded */
WHILE @nRowCount = @nBatchSize /* while there are still records to update */
BEGIN
/* get the batch size */
/* we are doing this here in case it changed since the last merge */
SET @nBatchSize = dbo.fn_GetBatchSize()
MERGE TOP (@nBatchSize) sysdba.C_DISTRIBUTOR as target
USING #tmpDist as source
ON target.C_DISTRIBUTORID = source.C_DISTRIBUTORID
WHEN MATCHED AND target.MODIFYDATE = source.MODIFYDATE /* comparing against the rows original modifydate */
THEN UPDATE
SET DISTRIBUTORNAME = source.DISTRIBUTORNAME
, MODIFYDATE = GETUTCDATE()
, MODIFYUSER = 'ADMIN'
WHEN NOT MATCHED
THEN INSERT
(
C_DISTRIBUTORID
, CREATEUSER
, CREATEDATE
, MODIFYUSER
, MODIFYDATE
, ZIPCODE
, DISTRIBUTORNAME
)
VALUES
(
source.C_DISTRIBUTORID
, 'ADMIN'
, GETUTCDATE()
, 'ADMIN'
, GETUTCDATE()
, source.ZIP
, source.DISTRIBUTORNAME
)
OUTPUT $ACTION,INSERTED.C_DISTRIBUTORID;
SET @nROWCOUNT = @@ROWCOUNT
END
IF (OBJECT_ID('tempdb..#tmpDist') IS NOT NULL)
DROP TABLE #tmpDist
There you have it a convenient and efficient way to insert/update data in SalesLogix from within T-SQL that won’t cause problems during production hours.