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

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

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

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

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

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

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