Invalid Column Name does not cause error
-
I have a SQL Server 2000 sproc that selects data into a temporary table.
If certain conditions are met, the sproc then uses a join on the temporary table to update an other table.
In the join for this update, the join column on the temporary table is named incorrectly.
This code is called as part of a sequence of calls to hundreds of sprocs. The thing is the code has never raised an error, the successful execution of the sproc is logged to another table. I would expect the code to raise something like
Server: Msg 207, Level 16, State 3, Line ..
Invalid column name 'dddddddd'.
I am aware of deferred name resolution but I think that when SQL Server compiles the procedure the error should be raised.
Of course I can edit the sproc but the client requires evidence of malfunction. Any help appreciated.
-
Can you post the Offending code
* Noel -
Hi,
In the temporary table, the column is named rt_pol_num, in the join it is referenced as TT.num_pol_dcrt. I have run the sproc with the recompile option and it does not fail.
BEGIN
SELECT DISTINCT
rt_pol_num = DCAM.num_pol_dcrt
Blah, Blah …
INTO #tdcam_t
FROM dcam_t dcam
WHERE dcam.stge_rec_err_ind <> 'Y' AND
dcam.prces_act_cd IN('ISRT') AND
EXISTS
(
SELECT src_key_vlu_txt
FROM alternate_payee_t
WHERE alternate_payee_t.src_key_vlu_txt = 'DCAM' +
DCAM.num_pol_dcrt +
DCAM.num_cert_dcrt +
DCAM.ctlnum_reg_cntrl_number +
UPPER(REPLACE(CONVERT(CHAR(11), DCAM.dte_alt_mail_eff_dcam, 106), ' ', ''))
)
SELECT @vErrNum = @@ERROR, @vIsrtRecCount = @@ROWCOUNT, @vStatrcTblNm = 'dcam_t'
IF @vErrNum <> 0
BEGIN
SELECT @vErrLocNm = 'cannot select from dcam_t into #tdcam_t',
@vErrTblNm = 'dcam_t',
@vStatDsc = 'procedure failed'
GOTO ON_ERROR
END
ELSE
SELECT @vDropTmp = 'DROP TABLE #tdcam_t'
IF @vIsrtRecCount > 0
BEGIN
IF @pWriteTable <> 'N'
BEGIN
UPDATE dcam_t
SET stge_rec_err_ind = 'Y'
WHERE EXISTS
(
SELECT stge_rec_err_ind
FROM #tdcam_t TT
WHERE TT.num_pol_dcrt = dcam_t.num_pol_dcrt
)
SELECT @vErrNum = @@ERROR, @vUpdtRecCount = @@ROWCOUNT, @vStatrcTblNm = 'dcam_t'
IF @vErrNum <> 0
BEGIN
SELECT @vErrLocNm = 'cannot update dcam_t',
@vErrTblNm = 'dcam_t',
@vStatDsc = 'procedure failed'
GOTO ON_ERROR
END
-
PK this kind of problem is typical at run time only when alias are not used (which is not your case)
About the error at compile time is not going to happen because it comes from a dynamically created table and that is not checked.Temp tables are not checked either.
Are you saying that if you run the procedure with @pWriteTable <> 'N' it does not reports errors ? Are you sure that @pWriteTables has been <> 'N'?
* Noel -
Hi,
@pWriteTable is 'Y'
I suspect that the other test, @vIsrtRecCount > 0,
is never true.
-
That makes sense. If that part of the Procedure is never executed you will never get the error!!
Cheers
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply