table columns not recognized in stored procedure

Question

Post reply

table columns not recognized in stored procedure

r_ahimsa_m

Old Hand

Points: 327
More actions
November 1, 2007 at 1:37 am

#95503

I have syntax error in stored procedure that I do not understand.
The procedure tries to use DocumentsNumbering table which has the following definition (DocumentYear is PK column, DocumentNumberCounter is an important column):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DocumentsNumbering](
[DocumentYear] [smallint] NOT NULL,
[StartingDocumentNumber] [dbo].[DocumentNumber] NOT NULL,
[DocumentNumberCounter] [dbo].[DocumentNumber] NULL,
[PrecedeWithYear] [bit] NOT NULL CONSTRAINT
[DF_DocumentsNumbering_PrecedeWithYear] DEFAULT ((0)),
[EndWithYear] [bit] NOT NULL CONSTRAINT [DF_DocumentsNumbering_EndWithYear]
DEFAULT ((0)),
[Separator] [char](1) COLLATE Polish_CI_AS NULL,
CONSTRAINT [PK_DocumentsNumbering_1] PRIMARY KEY CLUSTERED
(
[DocumentYear] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DocumentsNumbering] WITH CHECK ADD CONSTRAINT
[CK_DocumentsNumbering_Separator] CHECK (([PrecedeWithYear] IS NULL AND
[EndWithYear] IS NULL AND [Separator] IS NULL OR ([PrecedeWithYear] IS NOT
NULL OR [EndWithYear] IS NOT NULL) AND [Separator] IS NOT NULL))
GO
ALTER TABLE [dbo].[DocumentsNumbering] CHECK CONSTRAINT
[CK_DocumentsNumbering_Separator]
Here's the code of the procedure. The procedure generates and keeps last value of Documents.DocumentNumber (which is PK) in DocumentsNumbering.DocumentNumberCounter column.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewDocumentNumber]
(
@DocumentYear SMALLINT,
@DocumentNumberCounter DocumentNumber OUTPUT -- not null if generated
)
AS
BEGIN
DECLARE @Counter DocumentNumber;
SET @DocumentNumberCounter = NULL;
BEGIN TRANSACTION;
BEGIN TRY
SET @Counter = NULL;
SELECT @Counter = DocumentNumberCounter + 1 FROM DocumentsNumbering WHERE
DocumentYear = @DocumentYear;
IF @Counter IS NULL -- no documents yet
SELECT @Counter = StartingDocumentNumber FROM DocumentsNumbering WHERE
DocumentYear = @DocumentYear;
IF @Counter IS NULL -- year not configured
BEGIN
ROLLBACK TRANSACTION;
RAISERROR (N'Nie zdefiniowano numeracji dokumentów dla tego roku!', 16, 1);
RETURN;
END;
IF @Counter >= ISNULL((SELECT MIN(DocumentNumberCounter) FROM
DocumentsNumbering WHERE DocumentYear > @DocumentYear), 2147483647) --
maxint
BEGIN -- maximum number (per year) exceeded
ROLLBACK TRANSACTION;
RAISERROR (N'Osiągniętą maksymalną możliwą liczbę dokumentów w roku!', 16,
1);
RETURN;
END;
UPDATE DocumentsNumbering SET DocumentNumberCounter = @DocumentNumberCounter
WHERE DocumentYear = @DocumentYear;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
RAISERROR (N'Błąd aktulizacji licznika dokumentów!', 16, 1);
RETURN;
END CATCH;
COMMIT TRANSACTION;
SET @DocumentNumberCounter = @Counter;
END;
I don't understand why I receive the following compile-time errors:
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 11
Invalid column name 'DocumentYear'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 11
Invalid column name 'DocumentNumberCounter'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 13
Invalid column name 'DocumentYear'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 13
Invalid column name 'StartingDocumentNumber'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 20
Invalid column name 'DocumentYear'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 20
Invalid column name 'DocumentNumberCounter'.
Msg 207, Level 16, State 1, Procedure NewDocumentNumber, Line 26
Invalid column name 'DocumentYear'.
It looks like SQL Server does not recognize DocumentsNumbering table columns
(?). But why? Spelling is fine, and I have no other object called DocumentsNumbering.
/RAM/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Please check What does sp_help DocumentsNumbering return you. Also check if there is something wrong with your UDT.

Prasad Bhogadi
www.inforaise.com

r_ahimsa_m Old Hand Points: 327 More actions · Answer 2

I called 'sp_help DocumentsNumbering' and I received:

Name: Owner: Type:

DocumentsNumbering dbo user table

and more information. The result is OK.

User-defined Data Types are also OK.

Hmmm...

/RAM/

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 3

Any chance there is another table of the same name in your schema (e.g. username.table rather than dbo.table)?

r_ahimsa_m Old Hand Points: 327 More actions · Answer 4

I don't know what happend but when I rebooted my computer I tried again to add the stored procedure I succeded.

PROBLEM SOLVED.

Thanks for help.

/RAM/

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

That is interesting. Can you check the SQL Server logs and see if you could find anything that might have caused this. Your input may help others who may get into similar issues.

Thanks

Prasad Bhogadi
www.inforaise.com

Boris Goldshmit SSC Enthusiast Points: 148 More actions · Answer 6

I get this problem all the time. Have no idea what the cause is, but once I generate a SELECT code for the table and cut and paste the column name into the Stored Proc code, the problem goes away.

The catch is that what I cut and paste is virtually always 100% identical to what is there in the first place. Go figure...