[SQLSTATE 42000] (Error 102) - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

[SQLSTATE 42000] (Error 102)

Author

Topic

madscientist
Starting Member

30 Posts

Posted - 2007-11-29 : 18:48:08

Hello everyone. I am new to this forum and hoping to get some help. I am running a new job that is using T-SQL stored procedure but am getting the error:

Incorrect syntax near '01_ADD_COLUMNS_NOT_EXIST'. [SQLSTATE 42000] (Error 102). The step failed.

I am relatively new to MS SQL Server 2005 and this is the first time I have programmed a job. In the Command field I typed:

EXEC "01_ADD_COLUMNS_NOT_EXIST" which calls the stored procedure.

When I execute the stored procedure it works fine but in a job it fails and gives me the error.

Here is the syntax for the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[01_ADD_COLUMNS_NOT_EXIST]
AS
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id = object_id('COVERAGE')
AND name = 'COVERAGE_ANNUAL_PREMIUM')
ALTER TABLE COVERAGE ADD COVERAGE_ANNUAL_PREMIUM FLOAT NULL

Am I missing something somewhere? Thank you very much in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 19:06:25

In the job, you shouldn't have double quotes around the stored procedure name. Just EXEC SprocName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madscientist
Starting Member

30 Posts

Posted - 2007-11-30 : 16:34:40

Thank you very much Tara.

Another issue I noticed when I removed the double quotes is that the stored procedure cannot begin with a #. It still wasn't working until I changed the stored procedure to ADD_COLUMNS_NOT_EXIST. Now it works so thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources