'Create a new connection
Set oConn = CreateObject("ADODB.Connection")
'Open the connection to the database
oConn.ConnectionString = "Provider=SQLNCLI10;Server=ImperialPayroll;Database=Test;User ID=sa;Password=access"
Call oConn.Open
set oCMD = CreateObject("ADODB.Command")
set oRS = createObject("ADODB.Recordset")
oCMD.ActiveConnection = oConn
oCMD.CommandText = "FP_PAGetNextDocNumber"
oCMD.CommandType = 4
'Execute the stored procedure
set oRS = oCMD.Execute
set strDoc = oRS("PATSdoccounter")
DestinationFields("Timesheet Entry.Document Number") = cstr(strDoc)
'Close the connection
Call oConn.Close
-- =============================================
-- Create basic stored procedure template
-- =============================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'FP_PAGetNextDocNumber'
)
DROP PROCEDURE dbo.FP_PAGetNextDocNumber
GO
CREATE PROCEDURE dbo.FP_PAGetNextDocNumber
AS
declare @intPos int
declare @vchrSopnumbeBegin varchar(21)
declare @vchrSopnumbeEnd varchar(21)
declare @vchrSopnumbeNew varchar(21)
declare @vchrTemp varchar(21)
declare @intTemp int
declare @intRowCount int
-- turn off the rowcount display
set nocount on
--get a new sop number
declare @blnOKtoExit bit
set @blnOKtoExit = 0
declare @PATSdoccounter varchar(17)
set @PATSdoccounter = ''
begin tran
while @blnOKtoExit = 0
begin
--assume that this is all going to work
set @blnOKtoExit = 1
select
@PATSdoccounter = rtrim(PATSdoccounter)
from pa41801 with (TABLOCKX HOLDLOCK)
if isnull(@PATSdoccounter,'') = '' begin
rollback tran
raiserror('Invalid DOCID',16,2)
return
end
--loop through the sopnumber backwards, find the first non-numeric character.
set @intPos = len(@PATSdoccounter)
while isnumeric(substring(@PATSdoccounter,@intPos,1)) = 1
begin
set @intPos = @intPos - 1
end
set @vchrSopnumbeEnd = right(rtrim(@PATSdoccounter),len(@PATSdoccounter) - @intPos)
set @vchrSopnumbeBegin = left(@PATSdoccounter,@intPos)
set @intTemp = @vchrSopnumbeEnd
set @intTemp = @intTemp + 1
set @vchrSopnumbeEnd = right('000000000000000000000000000' + convert(varchar(21),@intTemp),len(@PATSdoccounter) - @intPos)
set @vchrSopnumbeNew = @vchrSopnumbeBegin + @vchrSopnumbeEnd
update pa41801 set patsdoccounter = @vchrSopnumbeNew
-- if there is no rowcount, do it again
set @intRowCount = @@rowcount
if @intRowCount = 0 begin
--print 'fail1'
set @blnOKtoExit = 0
end
if exists (select sopnumbe from sop10100 with (nolock) where sopnumbe = @PATSdoccounter) begin
set @blnOKtoExit = 0
--print 'fail2'
end
if exists (select sopnumbe from sop30200 with (nolock) where sopnumbe = @PATSdoccounter) begin
set @blnOKtoExit = 0
--print 'fail4'
end
end
commit tran
select @PATSdoccounter as PATSDoccounter
GO
grant exec on FP_PAGetNextDocNumber to public