Stored Procedure Error - 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)

Stored Procedure Error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:04:01

hi am trying to create a stored procedure?

when tring to execute an error message

Msg 156, Level 15, State 1, Procedure FULL_PAS_DAILY_RECORD_COUNT, Line 14
Incorrect syntax near the keyword 'DECLARE'.

this is my stored procedure.

USE [PAS_AUDIT]
USE PAS_RDB

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.FULL_PAS_DAILY_RECORD_COUNT
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
--
-- Change History
---==============
-- SS 07/04/2010 Initial Creation
-- =============================================

DECLARE @TableName sysname, ----error messsage points to this line.
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT
go
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES, getdate() as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1
IF (Object_ID(N'FULL_PAS_DAILY_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_PAS_DAILY_RECORD_COUNT
go
SELECT * INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
FROM TOTAL_RECORD_COUNT

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 06:05:43

You forgot the AS after CREATE PROCEDURE dbo.FULL_PAS_DAILY_RECORD_COUNT

Reporting & Analysis Specialist

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 06:06:34

You've missed the AS keyword

Need to do

CREATE PROCEDURE xxxxx AS ......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-07 : 06:07:14

[code]
create procedure <procedure name>
as
. . .
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-07 : 06:07:56

i must be getting old

KH
[spoiler]Time is always against us[/spoiler]

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:10:47

Hello
thanx, i missed that one after create[----] now i corrected my script,

btw when i execute there is another error message

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TOTAL_RECORD_COUNT' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'FULL_PAS_DAILY_RECORD_COUNT' in the database.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:20:24

can i use 2 databases in a stored procedure?

PAS_RDB for getting data and another PAS_AUDIT i am inserting data into that..and finally stored procedure running on PAS_AUDIT.

COULD U PLS CHECK MY SCRIPT FOR ANY CHANGES.

THANX

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 06:23:49

remove all the "go" statements in the first and see what happens. they shouldn't be there.

Reporting & Analysis Specialist

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:28:19

Hi..
removed go at the bottom of my script
now 1 error message

Msg 2714, Level 16, State 3, Procedure FULL_PAS_DAILY_RECORD_COUNT, Line 99
There is already an object named 'FULL_PAS_DAILY_RECORD_COUNT' in the database.

i dont want to create a new object but the message is saying that object already exists.

Satya

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 06:31:06

quote:
Originally posted by satya068

can i use 2 databases in a stored procedure?

PAS_RDB for getting data and another PAS_AUDIT i am inserting data into that..and finally stored procedure running on PAS_AUDIT.

COULD U PLS CHECK MY SCRIPT FOR ANY CHANGES.

THANX

This works:
-- Go to the db PAS_RDB
USE PAS_RDB
-- Exec against the audit database on the same server
SELECT * FROM PAS_AUDIT.dbo.tablename

Reporting & Analysis Specialist

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 06:32:45

quote:
Originally posted by satya068

Hi..
removed go at the bottom of my script
now 1 error message

You should remove ALL go inside the stored procedure. It's only used by the Query Analyzer...

Reporting & Analysis Specialist

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:35:37

Hello Askin,

could you plz guide me where to use that select statement in my script .

thanx

Satya

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 06:37:46

Can you post the sp again, please?

Reporting & Analysis Specialist

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:39:28

when i check under programbulity--- storedprocedures
i am not getting my new storedprocedure is not getting.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 06:41:29

-- Go to the db PAS_RDB
USE PAS_RDB
-- Exec against the audit database on the same server
SELECT * FROM PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
--
-- Change History
---==============
-- SS 07/04/2010 Initial Creation
-- =============================================

DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT

SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES, getdate() as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1
IF (Object_ID(N'FULL_PAS_DAILY_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_PAS_DAILY_RECORD_COUNT

SELECT * INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
FROM TOTAL_RECORD_COUNT

Satya

Asken
Starting Member

38 Posts

Posted - 2010-04-07 : 07:03:38

quote:

IF (Object_ID(N'FULL_PAS_DAILY_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_PAS_DAILY_RECORD_COUNT

Replace the above with (fourth line from the end):
TRUNCATE TABLE PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT

INSERT INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
SELECT * FROM TOTAL_RECORD_COUNT

Reporting & Analysis Specialist

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 07:08:11

Msg 102, Level 15, State 1, Procedure SP_FULL_PAS_DAILY_RECORD_COUNT, Line 96
Incorrect syntax near 'PAS_AUDIT'.

asken do u have any idea on this error message..table name n database are correct only.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 07:13:58

This is the error whic i am unable to trace..

Msg 2714, Level 16, State 3, Procedure SP_FULL_PAS_DAILY_RECORD_COUNT, Line 102
There is already an object named 'SP_FULL_PAS_DAILY_RECORD_COUNT' in the database.

Satya

sharonmtowler
Starting Member

12 Posts

Posted - 2010-05-04 : 12:55:29

change create to alter, if it is there it can only update by the alter

quote:
Originally posted by satya068

This is the error whic i am unable to trace..

Msg 2714, Level 16, State 3, Procedure SP_FULL_PAS_DAILY_RECORD_COUNT, Line 102
There is already an object named 'SP_FULL_PAS_DAILY_RECORD_COUNT' in the database.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:58:09

better to use like


IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='SP_FULL_PAS_DAILY_RECORD_COUNT')
  DROP PROC SP_FULL_PAS_DAILY_RECORD_COUNT

CREATE PROC SP_FULL_PAS_DAILY_RECORD_COUNT.....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-05 : 04:18:07

Jsut to follow up Visakh's post.

If you drop and recreate you'll need to grant any permissions that were on the object to the new instance of the stored proc.

This may not be an issue for you but I've stumbled on that before!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources