Using stored procedure to build tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using stored procedure to build tables

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 11:32:56

MS Server2000

Using stored procedures to build tables.
All stored procedures are being executed by Job agent.

The last table builds about 30 thousand rows then fails - should have about 29m.
I've ran this sp independent of all of the others without failure.
The Job agent shows failure after 15hrs. The odd thing is the error displayed indicates failure but at the wrong table, 4th table down (108 tables to build).

All of the tables are built except the last one.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-22 : 13:04:45

<sarcasm>
line 512 of the 3rd stored procedure is causing a constraint violation
</sarcasm>

What help are you expecting? You didn't post any code or the error(s) that were raised. You didn't even ask a question, and I'm afraid to ask why you are even doing this.

Be One with the Optimizer
TG

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 13:30:24

The data is coming from a PICK os.
Pick allows multi values ']' multi sub values '\'
example:
attribute 1 contains: 1200\1400]1100\]
attribute 2 contains: upc\chi]ppc\]

To load this data I have to use a sp.
To move this to a flat table it looks like this

row 1 field 1 field2 etc..
1200 upc

I have no clue as to why the table will not build to completion.

Any clue as to how or where to trap for an error?

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-22 : 13:51:27

15 hours?

Good Lord.

Post the code and the error message you got.

How are you loading the table?

How big is the file (I assume file because you say load)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 14:11:18

First item: the sp that builds the table.

CREATE PROCEDURE dbo.dw_sp_xfer_MasterClaim_ProcLine_115_128 AS
BEGIN

SET NOCOUNT ON
-- work fields
DECLARE @mvSep as char(1)
DECLARE @mvSep2 as char(1)
DECLARE @clmProcSeq as int
-- Character indexes of each field (current MV seperator found)
DECLARE @Document as char(15) -- Field to hold cursor value

DECLARE @iLine_Line_Code as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_Line_Code as smallint -- Character index (last MV seperator found)
DECLARE @Line_Line_Code as varchar(448) -- Field to hold cursor value

DECLARE @iLine_Qty as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_Qty as smallint -- Character index (last MV seperator found)
DECLARE @Line_Qty as varchar(448) -- Field to hold cursor value

DECLARE @iLine_ReqAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_ReqAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_ReqAmt as varchar(448) -- Field to hold cursor value

DECLARE @iLine_MaxAwp as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_MaxAwp as smallint -- Character index (last MV seperator found)
DECLARE @Line_MaxAwp as varchar(448) -- Field to hold cursor value

DECLARE @iLine_CobAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_CobAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_CobAmt as varchar(448) -- Field to hold cursor value

DECLARE @iLine_WthdAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_WthdAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_WthdAmt as varchar(448) -- Field to hold cursor value

DECLARE @iLine_DdAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_DdAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_DdAmt as varchar(448) -- Field to hold cursor value

DECLARE @i_CoinsPct as smallint -- Character index (current MV seperator found)
DECLARE @iL_CoinsPct as smallint -- Character index (last MV seperator found)
DECLARE @_CoinsPct as varchar(448) -- Field to hold cursor value

DECLARE @iLine_CoinsAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_CoinsAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_CoinsAmt as varchar(448) -- Field to hold cursor value

DECLARE @i_Benefit as smallint -- Character index (current MV seperator found)
DECLARE @iL_Benefit as smallint -- Character index (last MV seperator found)
DECLARE @_Benefit as varchar(512) -- Field to hold cursor value

DECLARE @iLine_PayAmt as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_PayAmt as smallint -- Character index (last MV seperator found)
DECLARE @Line_PayAmt as varchar(448) -- Field to hold cursor value

DECLARE @iLine_Adj as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_Adj as smallint -- Character index (last MV seperator found)
DECLARE @Line_Adj as varchar(448) -- Field to hold cursor value

DECLARE @i_FeeTechnique as smallint -- Character index (current MV seperator found)
DECLARE @iL_FeeTechnique as smallint -- Character index (last MV seperator found)
DECLARE @_FeeTechnique as varchar(448) -- Field to hold cursor value

DECLARE @iLine_CapLine as smallint -- Character index (current MV seperator found)
DECLARE @iLLine_CapLine as smallint -- Character index (last MV seperator found)
DECLARE @Line_CapLine as varchar(448) -- Field to hold cursor value

--
DECLARE @xLine_Code as varchar(7) -- Field to hold extracted value
DECLARE @xQty float -- Field to hold extracted value
DECLARE @xReqAmt float -- Field to hold extracted value
DECLARE @xMaxAwp float -- Field to hold extracted value
DECLARE @xCobAmt float -- Field to hold extracted value
DECLARE @xWthdAmt float -- Field to hold extracted value
DECLARE @xDdAmt float -- Field to hold extracted value
DECLARE @xCoinsPct float
DECLARE @xCoinsAmt float -- Field to hold extracted value
DECLARE @xBenefit varchar(60)
DECLARE @xPayAmt float -- Field to hold extracted value
DECLARE @xAdj as char(4) -- Field to hold extracted value
DECLARE @xFeeTechnique as char(1)
DECLARE @xCapLine as char(1) -- Field to hold extracted value

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @mvSep = 'ý'
SET @mvSep2 = 'ü'
-- Cursor for raw table
DECLARE work_cursor CURSOR FOR
SELECT
Document
, Line_Line_Code
, Line_Qty
, Line_ReqAmt
, Line_MaxAwp
, Line_CobAmt
, Line_WthdAmt
, Line_DdAmt
, Line_CoinsPct
, Line_CoinsAmt
, Line_MVx_Benefit
, Line_PayAmt
, Line_Adj
, Line_FeeTechnique
, Line_CapLine

FROM fl1stDwImport.dbo.raw_MasterClaim_ProcLine_115_128 (NOLOCK)
WHERE Line_Line_Code IS NOT NULL
OPEN work_cursor
FETCH NEXT FROM work_cursor
INTO
@Document
, @Line_Line_Code
, @Line_Qty
, @Line_ReqAmt
, @Line_MaxAwp
, @Line_CobAmt
, @Line_WthdAmt
, @Line_DdAmt
, @_CoinsPct
, @Line_CoinsAmt
, @_Benefit
, @Line_PayAmt
, @Line_Adj
, @_FeeTechnique
, @Line_CapLine

WHILE @@FETCH_STATUS = 0
BEGIN
SET @clmProcSeq = 0
SET @iLLine_Line_Code = 0 -- Set previous index to 0
SET @iLine_Line_Code = dbo.dw_f_getMVSep(@mvSep, @iLLine_Line_Code, @Line_Line_Code) -- Find next MV separator
SET @iLLine_Qty = 0 -- Set previous index to 0
SET @iLine_Qty = dbo.dw_f_getMVSep(@mvSep, @iLLine_Qty, @Line_Qty) -- Find next MV separator
SET @iLLine_ReqAmt = 0 -- Set previous index to 0
SET @iLine_ReqAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_ReqAmt, @Line_ReqAmt) -- Find next MV separator
SET @iLLine_MaxAwp = 0 -- Set previous index to 0
SET @iLine_MaxAwp = dbo.dw_f_getMVSep(@mvSep, @iLLine_MaxAwp, @Line_MaxAwp) -- Find next MV separator
SET @iLLine_CobAmt = 0 -- Set previous index to 0
SET @iLine_CobAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_CobAmt, @Line_CobAmt) -- Find next MV separator
SET @iLLine_WthdAmt = 0 -- Set previous index to 0
SET @iLine_WthdAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_WthdAmt, @Line_WthdAmt) -- Find next MV separator
SET @iLLine_DdAmt = 0 -- Set previous index to 0
SET @iLine_DdAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_DdAmt, @Line_DdAmt) -- Find next MV separator
SET @iL_CoinsPct = 0
SET @i_CoinsPct = dbo.dw_f_getMVSep(@mvSep, @iL_CoinsPct, @_CoinsPct) -- Find next MV separator
SET @iLLine_CoinsAmt = 0 -- Set previous index to 0
SET @iLine_CoinsAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_CoinsAmt, @Line_CoinsAmt) -- Find next MV separator

SET @iL_Benefit = 0
SET @i_Benefit = dbo.dw_f_getMVSep(@mvSep, @iL_Benefit, @_Benefit) -- Find next MV separator
SET @iLLine_PayAmt = 0 -- Set previous index to 0
SET @iLine_PayAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_PayAmt, @Line_PayAmt) -- Find next MV separator
SET @iLLine_Adj = 0 -- Set previous index to 0
SET @iLine_Adj = dbo.dw_f_getMVSep(@mvSep, @iLLine_Adj, @Line_Adj) -- Find next MV separator
SET @iL_FeeTechnique = 0
SET @i_FeeTechnique = dbo.dw_f_getMVSep(@mvSep, @iL_FeeTechnique, @_FeeTechnique) -- Find next MV separator
SET @iLLine_CapLine = 0 -- Set previous index to 0
SET @iLine_CapLine = dbo.dw_f_getMVSep(@mvSep, @iLLine_CapLine, @Line_CapLine) -- Find next MV separator
WHILE @iLine_Line_Code > 0
BEGIN
-- Set work fields
SET @xLine_Code = dbo.dw_f_setMvVarCharValue(@iLine_Line_Code, @iLLine_Line_Code, @Line_Line_Code, 7)
SET @xQty = CONVERT(decimal(9,2),dbo.dw_f_setMvDecimalValue(@iLine_Qty, @iLLine_Qty, @Line_Qty)/100)
SET @xReqAmt = CONVERT(decimal(9,2),dbo.dw_f_setMvDecimalValue(@iLine_ReqAmt, @iLLine_ReqAmt, @Line_ReqAmt) / 100)
SET @xMaxAwp = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_MaxAwp, @iLLine_MaxAwp, @Line_MaxAwp) / 100)
SET @xCobAmt = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_CobAmt, @iLLine_CobAmt, @Line_CobAmt) / 100)
SET @xWthdAmt = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_WthdAmt, @iLLine_WthdAmt, @Line_WthdAmt) / 100)
SET @xDdAmt = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_DdAmt, @iLLine_DdAmt, @Line_DdAmt) / 100)
SET @xCoinsPct = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@i_CoinsPct, @iL_CoinsPct, @_CoinsPct)/ 100)
SET @xCoinsAmt = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_CoinsAmt, @iLLine_CoinsAmt, @Line_CoinsAmt) / 100)
SET @xBenefit = dbo.dw_f_setMvVarCharValue(@i_Benefit, @iL_Benefit, @_Benefit, 50)
SET @xPayAmt = CONVERT(decimal (9,2),dbo.dw_f_setMvDecimalValue(@iLine_PayAmt, @iLLine_PayAmt, @Line_PayAmt) / 100)
SET @xAdj = dbo.dw_f_setMvVarCharValue(@iLine_Adj, @iLLine_Adj, @Line_Adj, 4)
SET @xFeeTechnique = dbo.dw_f_setMvVarCharValue(@i_FeeTechnique, @iL_FeeTechnique, @_FeeTechnique, 1)
SET @xCapLine = dbo.dw_f_setMvVarCharValue(@iLine_CapLine, @iLLine_CapLine, @Line_CapLine, 1)
-- Insert into dw table
IF (@xLine_Code IS NOT NULL) AND (LEN(@xLine_Code) > 0)
BEGIN
SET @clmProcSeq = @clmProcSeq + 1
INSERT INTO dbo.dw_MasterClaim_ProcLine2

(
Document
, SeqNo
, Line_Code
, Qty
, ReqAmt
, MaxAwp
, CobAmt
, WthdAmt
, DdAmt
, CoinsPct
, CoinsAmt
, Benefit
, PayAmt
, Adj
, FeeTechnique
, CapLine
)
VALUES
(
@Document
, @clmProcSeq
, @xLine_Code
, COALESCE(@xQty,0.00)
, COALESCE(@xReqAmt,0.00)
, COALESCE(@xMaxAwp,0.00)
, COALESCE(@xCobAmt,0.00)
, COALESCE(@xWthdAmt,0.00)
, COALESCE(@xDdAmt,0.00)
, COALESCE(@xCoinsPct,0.00)
, COALESCE(@xCoinsAmt,0.00)
, @xBenefit
, COALESCE(@xPayAmt,0.00)
, @xAdj
, @xFeeTechnique
, @xCapLine
)
END
SET @iLLine_Line_Code = @iLine_Line_Code -- Set previous index to current
SET @iLine_Line_Code = dbo.dw_f_getMVSep(@mvSep, @iLLine_Line_Code, @Line_Line_Code) -- Find next MV separator
SET @iLLine_Qty = @iLine_Qty -- Set previous index to current
SET @iLine_Qty = dbo.dw_f_getMVSep(@mvSep, @iLLine_Qty, @Line_Qty) -- Find next MV separator
SET @iLLine_ReqAmt = @iLine_ReqAmt -- Set previous index to current
SET @iLine_ReqAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_ReqAmt, @Line_ReqAmt) -- Find next MV separator
SET @iLLine_MaxAwp = @iLine_MaxAwp -- Set previous index to current
SET @iLine_MaxAwp = dbo.dw_f_getMVSep(@mvSep, @iLLine_MaxAwp, @Line_MaxAwp) -- Find next MV separator
SET @iLLine_CobAmt = @iLine_CobAmt -- Set previous index to current
SET @iLine_CobAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_CobAmt, @Line_CobAmt) -- Find next MV separator
SET @iLLine_WthdAmt = @iLine_WthdAmt -- Set previous index to current
SET @iLine_WthdAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_WthdAmt, @Line_WthdAmt) -- Find next MV separator
SET @iLLine_DdAmt = @iLine_DdAmt -- Set previous index to current
SET @iLine_DdAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_DdAmt, @Line_DdAmt) -- Find next MV separator
SET @iL_CoinsPct = @i_CoinsPct
SET @i_CoinsPct = dbo.dw_f_getMVSep(@mvSep, @iL_CoinsPct, @_CoinsPct) -- Find next MV separator

SET @iLLine_CoinsAmt = @iLine_CoinsAmt -- Set previous index to current
SET @iLine_CoinsAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_CoinsAmt, @Line_CoinsAmt) -- Find next MV separator
SET @iL_Benefit = @i_Benefit
SET @i_Benefit = dbo.dw_f_getMVSep(@mvSep, @iL_Benefit, @_Benefit) -- Find next MV separator

SET @iLLine_PayAmt = @iLine_PayAmt -- Set previous index to current
SET @iLine_PayAmt = dbo.dw_f_getMVSep(@mvSep, @iLLine_PayAmt, @Line_PayAmt) -- Find next MV separator
SET @iLLine_Adj = @iLine_Adj -- Set previous index to current
SET @iLine_Adj = dbo.dw_f_getMVSep(@mvSep, @iLLine_Adj, @Line_Adj) -- Find next MV separator
SET @iL_FeeTechnique = @i_FeeTechnique
SET @i_FeeTechnique = dbo.dw_f_getMVSep(@mvSep, @iL_FeeTechnique, @_FeeTechnique) -- Find next MV separator

SET @iLLine_CapLine = @iLine_CapLine -- Set previous index to current
SET @iLine_CapLine = dbo.dw_f_getMVSep(@mvSep, @iLLine_CapLine, @Line_CapLine) -- Find next MV separator

END
-- Get the next raw table
FETCH NEXT FROM work_cursor
INTO
@Document
, @Line_Line_Code
, @Line_Qty
, @Line_ReqAmt
, @Line_MaxAwp
, @Line_CobAmt
, @Line_WthdAmt
, @Line_DdAmt
, @_CoinsPct
, @Line_CoinsAmt
, @_Benefit
, @Line_PayAmt
, @Line_Adj
, @_FeeTechnique
, @Line_CapLine

END
SET NOCOUNT OFF
CLOSE work_cursor
DEALLOCATE work_cursor
END
GO
---------------------------------------------------------
Error msg:
Executed as user: NT AUTHORITY\SYSTEM. ...4:47PM * [SQLSTATE 01000] (Message 0) * End PROCEDURE dw_sp_xfer_Authorization at Jun 21 2005 4:47PM, Elapsed: 53 seconds. * [SQLSTATE 01000] (Message 0) * Start PROCEDURE dw_sp_xfer_Authorization_Adj at Jun 21 2005 4:47PM * [SQLSTATE 01000] (Message 0) * End PROCEDURE dw_sp_xfer_Authorization_Adj at Jun 21 2005 4:49PM, Elapsed: 85 seconds. * [SQLSTATE 01000] (Message 0) * Start PROCEDURE dw_sp_xfer_Authorization_Claims at Jun 21 2005 4:49PM * [SQLSTATE 01000] (Message 0) * End PROCEDURE dw_sp_xfer_Authorization_Claims at Jun 21 2005 4:49PM, Elapsed: 29 seconds. * [SQLSTATE 01000] (Message 0) * Start PROCEDURE dw_sp_xfer_Authorization_Comment1 at Jun 21 2005 4:49PM * [SQLSTATE 01000] (Message 0) * End PROCEDURE dw_sp_xfer_Authorization_Comment1 at Jun 21 2005 4:50PM, Elapsed: 46 seconds. * [SQLSTATE 01000] (Message 0) * Start PROCEDURE dw_sp_xfer_authorization_Diagnosis at Jun 21 2005 4:50PM * [SQLSTATE 01000] (Message 0) * End ... The step failed.
-----------------------------------------------------------
sp that executes the sp

CREATE PROCEDURE dbo.dw_sp_xfer_ImportToProd AS
begin
DECLARE @errCode as int
DECLARE @errStatus as int
SET @errCode = 0
SET @errStatus = 0
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization_Adj'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization_Claims'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization_Comment1'
IF @ErrCode <> 0 SET @errStatus = 1
--EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization_Comment2'
--IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_authorization_Diagnosis'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dbo.dw_sp_xfer_Authorization_Mem_By_To_Addr'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Authorization_Proc'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_BusinessLine'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_BusinessLine_BenCat'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_BusinessLine_NonPcpCap'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_BusinessLine_NonPcpWh'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_BusinessLine_ValidCapacity'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ClAdj'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ClaimTypes'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Code'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Code_CptGroup'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Code_LOB'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Code_LOB_RVS'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Company'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Consultant'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Consultant_DRG'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Consultant_LOB'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Consultant_LOB_Region'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Consultant_YtdLOB'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_DelClaims'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Diagnosis'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Disenroll'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Disenroll_Enroll'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Disenroll_Hippa_Hist'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Disenroll_MemMsg'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Disenroll_Reasons'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_AsPremDt'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_Benefits_Amts'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_Chg'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_GrComments'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_PremDates'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Group_PBM'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_BenCategory'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_Comment1'
IF @ErrCode <> 0 SET @errStatus = 1
--EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_Comment2'
--IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_Cover'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_EmergCont'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_Group'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_NHC'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_NonPcpProv'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Members_ProbList'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MembersEnrollHist'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_AddlAddr'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_Comment1'
IF @ErrCode <> 0 SET @errStatus = 1
--EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_Comment2'
--IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_LOB'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_LOB_PcpAssoc'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_LOB_ProvRegions'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_LOB_SsoSpec'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Physician_ProvSpec'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Premium'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Place'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_Adj'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_CommContact'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_Diagnosis'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_Procedure'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_RecertDate'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_ReferralCode'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_ReferralComments'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_PreCert_SecOpnDate'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ProvRegion_EffDt'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ProvRegion_EffDt_Capacity'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ProvRegion_EffDt_PhysVendor'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dbo.dw_sp_xfer_ProvRegion_EffDt_Pool'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ProvType'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_ServiceTypes'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Specialty'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Subscribers'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Subscribers_DisMemList'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Subscribers_Members'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Subscribers_SubNoChgDt'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Subscribers_TransMems'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_SusClaims'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_BalComp'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_Comment'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_CompNumsInv'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_CompPeriodCur'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_CompPeriodLyr'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_Invoices'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_Vendor_OtherAddress'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_Checks'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_ChgDate'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_Diagnosis'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_InsNo'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_Pay'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_ProcLine_101_114'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_ProcLine_115_128'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_ProcLine_129_142'
IF @ErrCode <> 0 SET @errStatus = 1
EXECUTE @ErrCode = dw_sp_execProc 'dw_sp_xfer_MasterClaim_RiskPool'
IF @ErrCode <> 0 SET @errStatus = 1
IF @errStatus <> 0
BEGIN
PRINT '! At least one error encountered. !'
RETURN @errStatus
END
END
GO

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 14:11:40

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-22 : 14:14:46

Brett you asked for it.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-22 : 14:18:23

I need a drink.

Did you inherit this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 14:19:00

yes

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-22 : 14:26:57

Since we've come this far, what's the udf do? Does it access tables?

Not for nothing, but I would imagine this whole thing could be significantly simplified.

If modifying the process is not an option, I would start profiler, filter to the hostname you are running on, start the trace and the run your process.

If the udf is not real ugly, hell even if it is, post it anyway.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-22 : 14:27:39

quote:
Originally posted by dowens

yes

Good answer....or "it was like this when I got here"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 14:36:47

udf?

jhermiz

3564 Posts

Posted - 2005-06-22 : 14:45:22

I've seen complaints about this forum in the past, but one thing I like about it is you could type a novel into the message box and it still works! I dont get the erroneous (only can store 10k characters please reduce by 9000!) message.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-06-22 : 14:45:49

quote:
Originally posted by dowens

udf?

User Defined Function.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-06-22 : 14:49:09

That ones easy - returns the position of the @mvsep

CREATE FUNCTION dw_f_getMVSep (@mvSep char(1), @prevMvPos smallint, @Field varchar(1024))
RETURNS smallint
AS
BEGIN
DECLARE @mvSepFoundAt smallint
SET @mvSepFoundAt = 0
-- If @Field is null, return 0
IF ( @Field Is Null OR (LEN(RTRIM(LTRIM(@Field))) = 0) )
Return @mvSepFoundAt
-- If @mvSep is null, return 0
IF ( @mvSep Is Null OR (LEN(RTRIM(LTRIM(@mvSep))) = 0) )
Return @mvSepFoundAt
-- If @prevMvPos < 0 or > length of field, return 0
IF ( (@prevMvPos < 0) OR (@prevMvPos >= LEN(@Field)) )
Return @mvSepFoundAt
SET @mvSepFoundAt = CHARINDEX(@mvSep, @Field, @prevMvPos + 1)
-- If no MV found then set to LEN(@Field) + 1
IF (@mvSepFoundAt = 0)
SET @mvSepFoundAt = LEN(@Field) + 1
RETURN(@mvSepFoundAt)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources