Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Friday, August 18, 2017 
 
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
   
Stats: 
Article has been read 9006 times

Rating: - 5.0 out of 5 by 1 users
 

fiogf49gjkf0d
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]);
            }
            else
            {
                DisplayHelp();
                return;
            }

            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;

            conn.Open();

            /* 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(r.GetString(0));
                    else
                        Console.Write("\n" + r.GetString(0));
                    i++;
                }

                r.Dispose();
            }
            else
            {
                Console.Write("Database names do not match, check the config file.");
            }

            cmd.Dispose();
            dbcheck.Dispose();
            conn.Dispose();
        }

        static void DisplayHelp()
        {
            Console.WriteLine("Paramaters:");
            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"?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <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;" />
    </connectionStrings>
<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
WITH EXECUTE AS 'XPCmdShellProxy'
AS
BEGIN
    /* 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;
END
GO

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
GO

CREATE LOGIN XPCmdShellProxy WITH PASSWORD = 'password', CHECK_POLICY = OFF;
GO

CREATE USER XPCmdShellProxy FOR LOGIN XPCmdShellProxy;
GO

GRANT EXECUTE ON sys.xp_cmdshell TO XPCmdShellProxy;
GO


USE SalesLogixDB
GO

CREATE USER XPCmdShellProxy FOR LOGIN XPCmdShellProxy;
GO

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

fiogf49gjkf0d


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
fiogf49gjkf0d

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
fiogf49gjkf0d

Hello,


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.


Jason

 
Mark Hallberg
 

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

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


mark


 


 


 

 
Jason Van Pee
 

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

Hi Mark,


 


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


 


  /* create the temp table for ids */
  CREATE TABLE #tmpID
  (
   RowNum INT IDENTITY(1,1) NOT NULL
   , ID NVARCHAR(12) NOT NULL
  )
  
  /* get the ids */
  INSERT INTO #tmpID
  (
   ID
  )
  EXEC dbo.usp_GetNextID 'ADDRESS', @nInsertCount;


 


Let me know if you have any other questions.


 


Jason

 
Patrick Gaule
 

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

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
fiogf49gjkf0d

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,


 


Jason

 
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
fiogf49gjkf0d

Patrick,


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.


Ryan

 
 

       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 © 2017 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): 8/18/2017 5:34:05 AM