Sql2K Invalid Column Name in Stored Procedure

[mlibeson]

I wrote a stored procedure and I am getting an error that tells me I have an invalid column name 'FclsA'. Here is an excerpt from my procedure where the error is coming from:

SELECT DISTINCT ClientId, Account, Famno, ApplSeq,
CAST('FCLSXXXX' AS VARCHAR(8)) AS 'FclsA', CAST('FCLSXXXX' AS VARCHAR(8)) AS 'FclsPrev'
INTO #FCLSLIST
FROM #History WITH (NOLOCK)
ORDER BY ClientId, Account, Famno, ApplSeq;

UPDATE #FCLSLIST
SET FclsA = dbo.fn_GetFclsFromAudit(ClientId, Account, Famno, ApplSeq, ApplSeq, @CutOff),
FclsPrev = dbo.fn_GetFclsFromAudit(ClientId, Account, Famno, ApplSeq , ApplSeq, (@CutOff - 7));

The error is in the update.

Can anyone explain to me why I am getting the error?


Michael Libeson

 

[SQLDenis]

why are you doing an extra step?

Wouldn't this work?

SELECT DISTINCT ClientId, Account, Famno, ApplSeq,
dbo.fn_GetFclsFromAudit(ClientId, Account, Famno, ApplSeq, ApplSeq, @CutOff) as FclsA , 
dbo.fn_GetFclsFromAudit(ClientId, Account, Famno, ApplSeq , ApplSeq, (@CutOff - 7)) as FclsPrev
INTO #FCLSLIST
FROM #History WITH (NOLOCK)
ORDER BY ClientId, Account, Famno, ApplSeq;

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mlibeson]

I am working on performance issues. So I have many of the steps broken up. I do not understand why I am getting the invalid column name.

Michael Libeson

 

[SQLDenis]

strange this works for me

create table #History (id2 int)
insert  #History values(1)


SELECT DISTINCT id2 as 'ID'
INTO #FCLSLIST
FROM #History WITH (NOLOCK)


UPDATE #FCLSLIST
SET id =2


select * from #FCLSLIST



drop table #FCLSLIST,#History

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mlibeson]

I agree that it is strange. I thought it would work, but apparently it is not for some reason. What is different about your example test and mine is that I am updating 2 fields, not just one. Also, The fields I am updating are VARCHAR type and not INTEGER. In the original INSERT into the temp table I have place holders "CAST('FCLSXXXX' AS VARCHAR(8))" where you have a field defined from a previous table creation and mine was a SELECT INTO. I do not know if any of this information helps, but I am really stumped. Just another note, I am running it in Query Analyzer.

Michael Libeson

 

[Catadmin]

Why are you using single quotes around your column aliases? I've never need to, even with a Select...Into command.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

i tested with and without single quotes (set quoted_identifier on and off) and it works the same on my box



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Could it have anything to do with the two repeated ApplSeq?

This is just a WAG, BTW.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mlibeson]

The double ApplSeq are just parameters to a function call and would not affect the update.I am beginning to think it may be a bug in query analyzer. I merged the two statements back together in order to avoid the issue for now. Thank you both for trying to help.


Michael Libeson