Invalid Column Name does not cause error

Hi,
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.Could you please post the DDL of the offending procedure?
ML|||The table is created with a column rt_pol_num. The join is the non-existent
column num_pol_dcrt. I have called the sproc with the recompile option but
the error is not raised.
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|||Non-existing Tables are not schema validated. If the parser notifies a
table which exists the columns are checked for existence, but not if
the table isn=B4t present during compilation time (like in your case)
CREATE PROCEDURE Testproc
AS
BEGIN
CREATE TABLE #testtable
(
SomeColumn int
)
Select somenotexsitingcolumn from #testtable
END
--VS
CREATE TABLE testtable
(
SomeColumn int
)
CREATE PROCEDURE Testproc2
AS
BEGIN
Select somenotexsitingcolumn from testtable
END
HTH, Jens Suessmeyer.|||The OP also states there are no errors at execution.
ML|||Hi,
The error is not raised because during compilation the temporary table does
not exist and SQL Server defers examination of the statement until run-time.
@.vIsrtRecCount > 0
is always false and so the code is never entered. If
@.vIsrtRecCount > 0
is true, the error 'Invalid column name' occurs.
Thanks to all who replied.

Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
Thanks,
Bing
After looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:

> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the table
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>

Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
--
Thanks,
BingAfter looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:

> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the tab
le
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>

Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
--
Thanks,
BingAfter looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:
> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the table
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>