Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, June 27, 2017 
 
Generating Table IDs in T-SQL  
Description:  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. 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. This article will look at how to generate SalesLogix table ID values using the .NET CLR in a SQL Function and call via T-SQL.

Category:  SalesLogix OLE DB Provider
Author:  Jason Van Pee
Submitted:  4/22/2010
   
Stats: 
Article has been read 26414 times

Rating: - 5.0 out of 5 by 2 users
 

fiogf49gjkf0d
Generating Table IDs in T-SQL

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.


CLR Code

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



SQL Functions

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.


Using the Functions

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



Database Locks

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.

 

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 - Version 2 - Submitted by: Jason Van Pee

 

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

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

Re: Generating Table IDs in T-SQL
Posted: 4/22/2010 6:20:41 PM
fiogf49gjkf0d
What a completely awesome article. Thanks.
 
Jason Buss



Re: Generating Table IDs in T-SQL
Posted: 5/11/2010 9:39:50 AM
fiogf49gjkf0d
Yes, excellent article!
 
Jason Van Pee
 

Re: Generating Table IDs in T-SQL
Posted: 5/11/2010 9:55:10 AM
fiogf49gjkf0d
Thanks. I am glad you like it. If you have any questions let me know.
 
Sarbello
 

Re: Generating Table IDs in T-SQL
Posted: 11/5/2011 7:36:32 AM
fiogf49gjkf0d

Hello - Great article. I've done most of the work outlined here however when I run  SELECT dbo.fn_GetNextSLXID('ACCOUNT'); I get an error message saying the  ( The 'SLXOLEDB.1' provider is not registered on the local machine.) We are using Version 7.5.4. After doing some research it seems “slx_dbids” is only available in that particuliar provider yet some of the documentation I've been reading indicates SalesLogix should be connecting via SQLCLI10.1?


Any advice regarding SLXOLEDB.1 or what my next steps are?

 
Ryan Farley

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

Re: Generating Table IDs in T-SQL
Posted: 11/5/2011 2:09:22 PM
fiogf49gjkf0d

Sarbello,


The SLX procedures are not database procedures. Instead they are implemented only in the SLX OLEDB Provider. To use the SalesLogix function slx_dbids to create table ID values (which the functions outlined in this article use) the SalesLogix OLEDB Provider must be installed and a valid SLX connection string must be used. Although the functions in this article exist in SQL, they are merely calling the slx_dbids function wil is internal to the SLX OLEDB Provider.


Ryan

 
Sarbello
 

Re: Generating Table IDs in T-SQL
Posted: 11/6/2011 6:23:53 AM
fiogf49gjkf0d

Ryan,

 Thanks for the response. I understand what you're saying that SLX_DBIDS exists in the SLX OLEDB provider. My issue involves the SLX OLEDB Provider not actually being installed on the SQLServer machine, and the result is when I make the call and go through the DLL I get (The 'SLXOLEDB.1' provider is not registered on the local machine). Not installed? It's odd but true even though it appears someone has also installed the SalesLogix Client (on the SQLServer 2008 machine). If I wanted to install the SLX OLEDB Provider on the physical box that SQLServer 2008 is on - where do you think I could get that provider / install base? On a side note, The only options available  when I open connection manager are SQLOLEDB or SQLCLI10?


Thanks again

 
Jason Van Pee
 

Re: Generating Table IDs in T-SQL
Posted: 11/6/2011 8:50:09 PM
fiogf49gjkf0d

Sarbello,


Do you have a 64 bit or 32 bit SQL Server?  In order for this to work you need a 32 bit SQL Server because there is not a 64 bit SalesLogix OLE DB provider.  There are some other methods to get around this in a 64 bit environement so let me know if that is what your running under and I can tell you how to do that.


If you have a 32 bit server and if the SalesLogix client is already installed on your sql server you should not need to install the ole db driver.  If the client is installed, first try getting the client to work and once that is working the rest should fall into place.  If you do need to install the ole db provider, it should be on your install cd in the "Client OLE DB Provider" folder.


If you continue to have problems please let me know.


Thanks much,


Jason

 
Sarbello
 

Re: Generating Table IDs in T-SQL
Posted: 11/7/2011 7:42:28 PM
fiogf49gjkf0d

Hi Jason,


 Thanks for the insight. Yes it's 64 bit Windows 2008 R2 Server. What advice could you recommend at this point?


Dave

 
Jason Van Pee
 

Re: Generating Table IDs in T-SQL
Posted: 11/9/2011 1:03:40 PM
fiogf49gjkf0d

Dave,


I sent you a message on 11/07/2011 with some additional information.  I hope you got it.


I did just submit a follow-up article that should help you out.  Hopefully it will be added to the site soon.


Thanks much,


Jason

 
Ryan Farley

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

Re: Generating Table IDs in T-SQL
Posted: 11/14/2011 10:11:51 AM
fiogf49gjkf0d

Jason's follow-up article to this one is posted here http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=107

 
Jason Van Pee
 

Re: Generating Table IDs in T-SQL
Posted: 11/14/2011 10:20:48 AM
fiogf49gjkf0d

Thanks for posting the follow-up article.  The method in the new article can be used on a 64 bit server.  If you run into any issues let me know.


Jason

 
Alan Frelich
 

Re: Generating Table IDs in T-SQL
Posted: 8/29/2012 8:25:48 AM
fiogf49gjkf0d

I am receiving the error below when trying to select using the function fn_GetNextSLXID. The provider used in the connection manager on the SLX Server is SQLNCLI.1 Could this be the issue? I had this running at one time with this provider. The SLX admin tools and client are installed on the SLX Server. I can connect to the database through the SLX client apps on the server. We are running SLX version 7.5.2 on SQL Server 2005 Sp4. Any help is appreciated.


 Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_GetNextSLXID_ConnString":
System.Data.OleDb.OleDbException: Failed to connect to SLXServer. Check if server is running and is compatible with the current provider version.
System.Data.OleDb.OleDbException:
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at SLXIDGen.slx_GetNextID_ConnString(String table, String ConnectionString)

 
Jason Van Pee
 

Re: Generating Table IDs in T-SQL
Posted: 8/29/2012 9:21:36 AM
fiogf49gjkf0d

Alan,


I would recommend using the method I described in the follow-up article (http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=107).  Using the CLR is not as robust of a solution and does not work if your sql server is 64 bit.  I hope the other solution works for you.  If not; let me know.


Thanks much,


Jason

 
 

       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): 6/27/2017 7:16:57 AM