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
 General SQL Server Forums
 New to SQL Server Programming
 cannot create SP ( error 2714 )

Author  Topic 

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-07 : 10:34:44
Hi, Trying to create a SP. But getting an error msg. Any ideas how to fix it? My SP gets the size of the tables in every DB.

Server: Msg 2714, Level 16, State 5, Procedure isp_allTableSize, Line 8
There is already an object named 'isp_allTableSize' in the database.
Server: Msg 208, Level 16, State 1, Line 18
Invalid object name 'master..TablesTemp'.
Server: Msg 208, Level 16, State 1, Line 63
Invalid object name 'master..TablesTemp'.
----------------------------------------------------------------------





-- TRUNCATE table TablesTemp

-- drop proc dbo.isp_allTableSize
CREATE PROC dbo.isp_allTableSize as
SET NOCOUNT ON



CREATE TABLE master.[dbo].[TablesTemp] (
[DatabaseName] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Data] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexSize] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unused] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/*
-- SELECT COUNT (*) from master..DatabaseTables
DECLARE @date DATETIME
SELECT @date = DATEDIFF(mi, currentdate, GETDATE())
FROM master..databaseTables
IF (@date > 7)
BEGIN
TRUNCATE TABLE master..databaseTables
END
*/

DELETE master..databaseTables
WHERE DATEDIFF(DAY, currentdate, GETDATE()) > 7



DECLARE @db VARCHAR(60)
DECLARE @sql VARCHAR(8000)

DECLARE icr_getDatabases CURSOR FOR

SELECT top 1 name FROM master..sysdatabases where name <> 'tempdb'

OPEN icr_getDatabases
FETCH icr_getDatabases INTO @db
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'USE [' + @db + ']

DECLARE @table_name VARCHAR(60)
DECLARE @cursor VARCHAR(8000)

DECLARE icr_getTables cursor for

SELECT table_name from information_schema.tables
WHERE table_type = ''base table''
AND table_schema = ''dbo''
AND table_name NOT LIKE ''dbo.%''

OPEN icr_getTables
FETCH NEXT FROM icr_getTables into @table_name
WHILE @@fetch_status = 0

BEGIN
INSERT master..TablesTemp
EXEC sp_spaceused @table_name

FETCH NEXT FROM icr_getTables INTO @table_name
END

CLOSE icr_getTables
DEALLOCATE icr_getTables'

EXEC (@sql)
FETCH NEXT FROM icr_getDatabases INTO @db
END
CLOSE icr_getDatabases
DEALLOCATE icr_getDatabases



INSERT master..DatabaseTables
SELECT *, GETDATE() FROM master..TablesTemp


-- TRUNCATE table DatabaseTables
-- SELECT * from master..DatabaseTables
DROP TABLE master..TablesTemp





=============================
http://www.sqlserverstudy.com

pootle_flump

1064 Posts

Posted - 2007-09-07 : 10:50:43
quote:
Originally posted by funketekun

Hi, Trying to create a SP. But getting an error msg. Any ideas how to fix it? My SP gets the size of the tables in every DB.

Server: Msg 2714, Level 16, State 5, Procedure isp_allTableSize, Line 8
There is already an object named 'isp_allTableSize' in the database.


You can't really be serious can you?
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-07 : 10:51:28
why do you think im not serious?

=============================
http://www.sqlserverstudy.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 10:52:18
because the error message tells you VERY clearly what is wrong.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-07 : 10:55:36
I have the SP created. Now it doesn't run the code inside..

can a nested cursor be run in a SP?

=============================
http://www.sqlserverstudy.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 11:00:17
yes it can.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-07 : 11:02:03
I found the error, too. Don't post anything new, I want to go make some popcorn so I can enjoy the show!

Jim
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-07 : 11:28:52
Pardon my post funketun, I jumped in too fast. Sorry (even tho' the time doesn't show it)

Jim
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-07 : 13:23:26
Does that say INSERT (INTO) master..table

????????????????????????????????????????


Run Away.....Run A W A Y!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-07 : 13:39:29
I will be forced to taunt you a second time!
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-07 : 14:14:44
/me patiently waits for the Peso +1

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-07 : 14:16:30
bwwwwwwwwahahahaha

SET @sql = 'USE [' + @db + ']


OK, that's not nioce.

This is what he wants

quote:

My SP gets the size of the tables in every DB.



Funk, instead of fixing what you gave us, can we show you have this should be done?





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-07 : 14:37:25
Oh, and DUDE, never use master...I'm sure there are somethings, but not for me

It's not a good idea

Create a DBA database or something and do the work there



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-07 : 14:40:50
One other thing I realized doing this "excersice" is that sp_spaceused seem like it can only be used for dbo owned tables

Unless I'm doing something wrong



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-07 : 14:47:12
Script to analyze table space usage, written by Michael Valentine Jones:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-10 : 14:11:45
thanks ..grabbed a piece from valentines code.

=============================
http://www.sqlserverstudy.com
Go to Top of Page
   

- Advertisement -