Home | Forums | Contact | Search | Syndication  
 [login] [create account]   Saturday, May 25, 2024 
Generating Table IDs in T-SQL - Version 2  
Description:  In an earlier article I demonstrated how to generate SalesLogix IDs in SQL using the CLR. The method I described still does work but I have found a few issues with it since it has been in production. We'll take a look at these and how to solve them in this follow-up article.

Category:  SalesLogix OLE DB Provider
Author:  Jason Van Pee
Submitted:  11/14/2011
Article has been read 20701 times

Rating: - 4.5 out of 5 by 20 users

In an earlier article I demonstrated how to generate SalesLogix IDs in SQL using the CLR. The method I described still does work but I have found a few issues with it since it has been in production. The primary issues are:
  1. A 32 bit SQL Server is required because there is not a 64 bit SLX OLEDB driver.
  2. A 32 bit SQL Server has a relatively small amount of memory that the CLR can run in. If a large number of IDs are generated, it is possible to consume all this memory and essential cause SQL to crash.
  3. Adding an assembly with unsafe permission is not the best idea. I had that in there for testing and failed to remove it before publishing the article.
This article will address the three issues above and still provide the same basic functionality.

All three issues can be avoided by not using the CLR. To get around the CLR we are going to create a completely separate command line executable (using essentially the same code as before). This executable will take the table name and the number of IDs to generate as parameters (it also takes the database name as a parameter for an extra level of safety), and return the number of IDs by simply writing the IDs to the console. This executable can then be called using xp_cmdshell to retrieve the results. So, here is the code to this handy executable:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;

namespace SalesLogix_GetNextID
    class Program
        static void Main(string[] args)
            string DatabaseName,TableName;
            int IDCount;

            if (args.Length == 3)
                DatabaseName = args[0].ToString();
                TableName = args[1].ToString();
                IDCount = Convert.ToInt32(args[2]);

            SalesLogix_GetNextID.Properties.Settings gnidsetting = SalesLogix_GetNextID.Properties.Settings.Default;

            OleDbConnection conn = new OleDbConnection(gnidsetting.SLXConnString);
            ArrayList resultCollection = new ArrayList();

            switch (TableName.ToUpper())
                case "ATTACHMENT": TableName = "FILEATTACH"; break;
                case "USERNOTIFICATION": TableName = "USERNOTIFY"; break;
                case "AGENTS": TableName = "HOSTTASK"; break;
                case "RESOURCELIST": TableName = "RESOURCE"; break;
                case "USEROPTION": TableName = "USERVIEW"; break;
                case "JOINDATA": TableName = "JOIN"; break;
                case "PROCEDURES": TableName = "PROCEDURE"; break;
                case "SEC_FUNCTIONOWNER": TableName = "FUNCTIONHANDLER"; break;
            OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', {1})", TableName, IDCount), conn);
            OleDbCommand dbcheck = new OleDbCommand("SELECT DB_NAME()",conn);
            OleDbDataReader r;


            /* this is just a saftey check.  We want to make sure we are returning IDs for the correct database */
            if (dbcheck.ExecuteScalar().ToString() == DatabaseName)
                r = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                int i = 0;

                while (r.Read())
                    /* do not add a return on the first one */
                    if (i == 0)
                        Console.Write("\n" + r.GetString(0));

                Console.Write("Database names do not match, check the config file.");


        static void DisplayHelp()
            Console.WriteLine(" DatabaseName - The SalesLogix database name, this is just a safty check");
            Console.WriteLine(" TableName - The SalesLogix table name without schema name");
            Console.WriteLine(" IDCount - The number of IDs to return");
            Console.WriteLine("\nExample to return 5 ACCOUNTIDs:");
            Console.Write(" SalesLogix_GetNextID.exe SLXSZ_DEV ACCOUNT 5");

As you can see there really is not much special in this, and I even added a little DisplayHelp() method.

The config file will look something like this:

<?xml version="1.0"?>
        <add name="SalesLogix_GetNextID.Properties.Settings.SLXConnString"
            connectionString="Provider=SLXOLEDB.1;Password=adminpassword;Persist Security Info=True;User ID=admin;Initial Catalog=SalesLogixDB;Data Source=SLXServerName;OLE DB Services=-4;Extended Properties=&quot;PORT=1706;LOG=OFF;CASEINSENSITIVEFIND=ON;&quot;" />
<startup><supportedRuntime version="v2.0.50727"/></startup></configuration>

You will need to modify the connection string for your environment. Now this executable can be called very easily from sql using xp_cmdshell. Here is what we use:

* Create Date: 07/20/2011
* Created By: Jason Van Pee
* Description: Gets the next IDs for SalesLogix tables.
* Paramaters:
*    @TableName - the table to generate ids for
*    @IDCount - the desired number of ids
CREATE PROCEDURE [dbo].[usp_GetNextID]
    @TableName sysname
    , @IDCount INT
    /* the location of the external exe that generates the ids */
    DECLARE @ExeLocation AS VARCHAR(256) = 'c:\MSSQL\SalesLogix_GetNextID\SalesLogix_GetNextID.exe';
    DECLARE @cmd AS VARCHAR(512);

    SELECT @cmd = @ExeLocation + ' ' + DB_NAME() + ' ' + @TableName + ' ' + CONVERT(VARCHAR(10),@IDCount);

    EXEC sys.xp_cmdshell @cmd;

You may need to change where the executable is being stored and before creating this stored procedure in your environment you will need to set up the XPCmdShellProxy user. We are doing this so we do not have to give everybody using this stored procedure sysadmin rights to the server. This XPCmdShellProxy user will need to be added to your SalesLogix database and it will need to have execute access to xp_cmdshell in the master database. Here is a script you can use to create this user:

USE master



GRANT EXECUTE ON sys.xp_cmdshell TO XPCmdShellProxy;

USE SalesLogixDB


The password should be modified and you may want to turn CHECK_POLICY = ON. The last thing to consider is making sure you have a proxy account set up for xp_cmdshell. You can use the sp_xp_cmdshell_proxy_account stored procedure to set that. We created a local account with local admin rights for our proxy account. I would really like to limit the security for this account but it seems to fail on opening the connection if the account is not a local admin. If anybody knows how I can get around this, please let me know.

About the Author

Jason Van Pee
Sub-Zero | Wolf


View online profile for Jason Van Pee

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
Please log in to rate article.

Related Articles 
 - Generating Table IDs in T-SQL - Submitted by: Jason Van Pee


Comments & Discussion you must log-in to add comments. [login here] 
Author Article Comments and Discussion


Re: Generating Table IDs in T-SQL - Version 2
Posted: 12/5/2011 12:57:01 AM

Hello, thanks for your tutorial

I have a problem, when i execute usp_GetNextID receive:

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

I am create credential ##xp_cmdshell_proxy_account## with user XPCmdShellProxy and password

Please help me

Jason Van Pee

Re: Generating Table IDs in T-SQL - Version 2
Posted: 12/5/2011 8:27:50 AM


It sounds like you need to set up the proxy account.  If you did already please make sure the proxy account information is valid.  To set up the proxy account use the "sp_xp_cmdshell_proxy_account" stored procedure.  Here is an example from books online (you will need to change the account and password):

EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65';

Let me know if you still have problems.


Mark Hallberg

Re: Generating Table IDs in T-SQL - Version 2
Posted: 2/28/2012 7:20:25 PM

hello Jason, 

thanks for the great article.  I think I have the code and the SP working ok , but how can I capture the output?  When calling 

declare @NEXTID varchar(10)

exec Saleslogix_demo.dbo.usp_GetNextSLX_ID 'Salesorder', @NextID OUTPUT

I do get the next id to the console but I want to use that id in something like 

declare @NEXTID varchar(10)

exec Saleslogix_demo.dbo.usp_GetNextSLX_ID 'Salesorder', @NextID OUTPUT

insert into Saleslogix_demo.sysdba.SALESORDER 

           (salesorderid,ACCOUNTID,..yada yada )

SELECT    @NEXTID,...ect

from salesales order 


thanks much





Jason Van Pee

Re: Generating Table IDs in T-SQL - Version 2
Posted: 2/28/2012 10:08:48 PM

Hi Mark,


Thanks for the comments.  I save the output to a temp table like so:


  /* create the temp table for ids */
  /* get the ids */
  EXEC dbo.usp_GetNextID 'ADDRESS', @nInsertCount;


Let me know if you have any other questions.



Patrick Gaule

Re: Generating Table IDs in T-SQL - Version 2
Posted: 12/9/2013 11:45:48 AM

Hey Jason,

I know this post is very old, but I'm hoping you still read this...

Is there any way to get this working as a User Defined Function on x64 SQL Server, similar to your original solution?  Your 64-bit solution requires a stored procedure (probably because you need to save the output of xp_cmdshell into a temp table, which is not allowed in a UDF).

We used the UDF/CLR method in a lot of external aplications and it would be too much of a burden to change all of thoese references to call a SPROC instead of a UDF.  We are now upgrading to x64 SQL Server and I ran into the roadblock of having the 32-bit SlxOleDb provider.  So far, it seems like our only option is to downgrade to 32-bit SQL server on the new server...any other ideas?

Jason Van Pee

Re: Generating Table IDs in T-SQL - Version 2
Posted: 12/9/2013 1:38:27 PM

Hi Patrick,


A big part of why I came out with this second version is because of 64 bit sql.  You need to run the code that generates the ID in 32 bit so it needs to run outside of SQL somehow.  You are also correct that the reason I used a stored procedure instead of a UDF is because you can not perform an insert into in a UDF.


Unfortunately I don't have a great response for you.  There is probably some way to make it work but I have never done it.  Instead of creating an EXE you might be able to create a web service and use ole automation inside the UDF to create a MSXML2.ServerXMLHTTP object.  You could then parse the XML returned from that to get the generated ID.  If I think of any other possibilities I will let you know.


I hope that helps you out a little.  If you do get something working let me know; I would be curious.


Thanks much,



Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Generating Table IDs in T-SQL - Version 2
Posted: 12/24/2013 2:12:56 AM


If you don't have remote users that synchronize, you can use the stored procedure I provided here http://customerfx.com/pages/crmdeveloper/2013/12/23/sql-stored-procedure-to-create-saleslogix-table-id-values.aspx

This will allow you to create SLX table ID values from direct SQL and can be used where ever you want.



       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...

 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
page cache (param): 5/25/2024 9:54:31 AM