Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, March 28, 2024 
 
View User Profile  

 User Profile - Jason Van Pee  
Jason Van Pee
Sub-Zero | Wolf

fjrigjwwe9r1SiteUser:UserBio
fiogf49gjkf0d


Log in to send this member a message!
 


 Jason Van Pee's Contributions
Jason Van Pee has contributed 8 comments and 2 articles.
 
Select to view:    
Comment: Re: Generating Table IDs in T-SQL - Version 2
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



Author: Jason Van Pee - 12/9/2013

 
Comment: Re: Generating Table IDs in T-SQL
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



Author: Jason Van Pee - 8/29/2012

 
Comment: Re: Generating Table IDs in T-SQL - Version 2
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



Author: Jason Van Pee - 2/28/2012

 
Comment: Re: Generating Table IDs in T-SQL - Version 2
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



Author: Jason Van Pee - 12/5/2011

 
Comment: Re: Generating Table IDs in T-SQL
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



Author: Jason Van Pee - 11/14/2011

 
Comment: Re: Generating Table IDs in T-SQL
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



Author: Jason Van Pee - 11/9/2011

 
Comment: Re: Generating Table IDs in T-SQL
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



Author: Jason Van Pee - 11/6/2011

 
Comment: Re: Generating Table IDs in T-SQL
fiogf49gjkf0d
Thanks. I am glad you like it. If you have any questions let me know.

Author: Jason Van Pee - 5/11/2010

 


 
 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): 3/28/2024 9:16:25 PM