Stored procedure works in 2K, does not work in 2K8R2

[wbodger]

I have this stored procedure

USE [Onyx]
GO

/****** Object:  StoredProcedure [dbo].[wsp_Generate_Random_AlphaNumeric_SerialNumbers_Shortened_030811]    Script Date: 03/08/2011 10:28:12 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[wsp_Generate_Random_AlphaNumeric_SerialNumbers_Shortened_030811](
@ProdNum varchar(20),
@NumSN int,
@Distributed bit,
@Partner varchar(60)
) AS

BEGIN
DECLARE @chProductNumber char(20)
DECLARE @vchKeyGen varchar(255)
DECLARE @vchKeyGenData varchar(255)
DECLARE @SNType varchar(255)
DECLARE @SNTemplate nvarchar(255)
Declare @vchSN nvarchar(50)
DECLARE @vchKey varchar(255)

DECLARE @howmany bigint
DECLARE @bDistributed bit
DECLARE @vchPartnerAllocatedTo varchar(255)
DECLARE @dtPartnerAllocatedTo datetime
DECLARE @dtAllocated datetime

DECLARE @bMakeSequential bit
DECLARE @iSeqStart bigint




DECLARE @c TABLE (
	iEntityID int
)

SELECT @chProductNumber=chProductNumber,
	@vchKeyGen=vchKeyGen,
	@vchKeyGenData=vchKeygenData,
	@SNType=vchSerialNumberType,
	@SNTemplate=vchSerialNumberTemplate
FROM dbo.ProductMaster_extended
WHERE  chProductNumber = @ProdNum
		
IF @Distributed = 1
	BEGIN
	SET @dtAllocated = getdate()
	END
ELSE
	BEGIN
	SET @dtAllocated = NULL
	END


SELECT @howmany = @NumSN,
	@bMakeSequential = 0,	-- if set to 1, will generate numbers from @iSeqStart to @iSeqStart+@howmany
	@iSeqStart = 0,
	@bDistributed = @Distributed,
	@vchPartnerAllocatedTo = @Partner,
	@dtPartnerAllocatedTo = @dtAllocated
		

declare @tmp varchar(50)

declare @kg int			-- used to hold the AX control for the key generator
declare @rt int
declare @rv varchar(255)

set nocount on

BEGIN

	IF @SNType IS NULL 	-- assume it's our "standard" format SN
	BEGIN	
		-- figure out where in the template we are going to insert a random number
		DECLARE @pat_start int
		declare @pat_len int
		SET @pat_start = PATINDEX('%-xxxxxx-%',@SNTemplate)+1
		SET @pat_len = CHARINDEX('-',@SNTemplate, @pat_start) - @pat_start
		
		if @pat_start = 1 or @pat_len <> 6
			SELECT 0/0	-- error!
	
	END

	
	-- create an instance of CodeGen.dll active X wrappers
	SET @rt = 0
	if @vchKeyGen = 'ARMADILLO'
		exec @rt = sp_OACreate 'CodeGenAX.CodeGenDLL.1', @kg OUTPUT
	ELSE IF @vchKeyGen = 'NA'
		SELECT @vchKeyGen = @vchKeyGen -- do nothing
	ELSE
		SET @vchKey = CAST(0/0 as varchar(200))	-- DIE!

	if @rt <> 0
	BEGIN
		exec master.dbo.sp_OAGetErrorInfo @kg, @rt
		print 'error! 1'
		return 
	END


	DECLARE @cnt bigint
	SET @cnt = 0

	while @cnt < @howmany
	BEGIN
		if @SNType is null	-- assume our good old key format
		BEGIN
			-- we insert a random number into the middle of the SN
			-- and we need to make sure it's unique!
			SET @vchSN = null
			WHILE @vchSN is null or (@bMakeSequential = 0 and exists (SELECT * FROM dbo.tMasterSerialNumbers WHERE vchSerialNumber = @vchSN))
			BEGIN
				if @bMakeSequential = 1 
				BEGIN
					-- create sequential numbers
					SET @tmp = LEFT(CAST(CAST(@cnt + @iSeqStart AS BIGINT) AS VARCHAR(10)), @pat_len)
				END
				ELSE	
				BEGIN
					-- create random alpha-numeric and replace i,o,u,z with 1,0,v,2
					--SET @tmp = LEFT(newid(), 6)
					SET @tmp = newid()
					SET @tmp = REPLACE(@tmp, '-', '')
					SET @tmp = REPLACE(@tmp, 'i', '1')
					SET @tmp = REPLACE(@tmp, 'o', '0')
					SET @tmp = REPLACE(@tmp, 'u', 'v')
					SET @tmp = REPLACE(@tmp, 'z', '2')
					SET @tmp = REPLACE(@tmp, 'I', '1')
					SET @tmp = REPLACE(@tmp, 'O', '0')
					SET @tmp = REPLACE(@tmp, 'U', 'v')
					SET @tmp = REPLACE(@tmp, 'Z', '2')
					SET @tmp = LEFT(@tmp, @pat_len)
			END

				SET @vchSN = STUFF(@SNTemplate, @pat_start, @pat_len, REPLICATE('0',@pat_len))
				SET @vchSN = STUFF(@vchSN, @pat_start + (@pat_len - len(@tmp)), len(@tmp), @tmp)
			END
		END 
		-- New code inserted 4/19 to allow generation of bulk SNs for ShareDirect
		ELSE 
			IF @SNType='SHAREDIRECT'
			set @vchSN = newid()
		else 
			select 0/0	-- die!
	
		-- generate a key for this Serial Number
		SET @rt = 0
		if @vchKeyGen = 'ARMADILLO'
			exec @rt = sp_OAMethod @kg, 'AxCreateKey', @vchKey OUTPUT, @vchSN,'',@vchKeyGenData
		ELSE IF @vchKeyGen = 'NA'
			SET @vchKey = NULL	-- make sure that there isn't a KEY
		ELSE
			SET @vchKey = CAST(0/0 as varchar(200))	-- DIE!
	
		if @rt <> 0
		BEGIN
			exec master.dbo.sp_OAGetErrorInfo @kg, @rt
			print 'error! 2'
			return 
		END


	
		print @vchSN + ' -- ' + ISNULL(@vchKey,'<NULL>')

			
		INSERT INTO tMasterSerialNumbers (vchProductNumber, vchSerialNumber, vchKey, bDistributed, vchPartnerAllocatedTo, dtPartnerAllocatedTo)
		VALUES (@chProductNumber, @vchSN, @vchKey, @bDistributed, @vchPartnerAllocatedTo,@dtPartnerAllocatedTo)
		
		INSERT INTO @c (iEntityID)
		VALUES (@@IDENTITY)
		

		SET @cnt = @cnt + 1
	END

	-- show all the SN's we allocated
	SELECT vchSerialNumber, vchProductNumber, vchKey
	FROM dbo.tMasterSerialNumbers S INNER JOIN @C C ON S.iEntityID = C.iEntityID

	-- clean out our table
	DELETE FROM @c
	
	
	-- destroy the active X control.. we may need a different one on the next iteration
	if @vchKeyGen = 'ARMADILLO'
		exec sp_OADestroy @kg
	ELSE IF @vchKeyGen = 'NA'
		print '' -- do nothing
	ELSE
		SET @vchKey = CAST(0/0 as varchar(200))	-- DIE!

END

set nocount off
END

GO

CodeGenAX.dll is an Armadillo wrapper in an Active X control. Both boxes are 64-bit, the DLL is registered on both boxes and works just fine on the SQL 2K box, but I simply get a return of 0 on the SQL 2K8R2 box. No errors, it just doesn't work. I have checked the log files and there are no errors there. I am at a loss as to why it would not work and where I can look to try to figure out more as to what is going on. I have tried placing Print commands in the stored procedure to no avail. I have gone thru the Surface Area Configuration and corrected some early issues there with OLEAutomation (had to enable it).

Any thoughts/suggestions out there?

wb

 

[gmmastros]

Check the windows account used to start the SQL Server service.

Click Start -> Run
Type: Services.msc
Scroll down to the SQL Server service (MSSQLSERVER on sql2000).
Right Click -> Properties
"Log on" tab.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Well, sp_OACreate and the like are deprecated. If you need to use such functionality, you need to go CLR route.

PluralSight Learning Library

 

[wbodger]

I did forget one important piece of information. There are some products that use the dll and some that don't. The sproc works fine if it does not use the dll, so it looks like it may be more along the lines of what markros is saying. However, I have really no idea how to make that work using CLR. Can you give me any direction or help em with what that would look like?

Thank you!
WIllie