SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 cannot create SP ( error 2714 )
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/07/2007 :  10:34:44  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

Edited by - funketekun on 09/07/2007 10:35:12

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 09/07/2007 :  10:50:43  Show Profile  Reply with Quote
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

Australia
491 Posts

Posted - 09/07/2007 :  10:51:28  Show Profile  Visit funketekun's Homepage  Reply with Quote
why do you think im not serious?

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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/07/2007 :  10:52:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 09/07/2007 :  10:55:36  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 09/07/2007 :  11:00:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes it can.

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/07/2007 :  11:02:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/07/2007 :  11:28:52  Show Profile  Reply with Quote
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 - 09/07/2007 :  13:23:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

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

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/07/2007 :  14:14:44  Show Profile  Reply with Quote
/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 - 09/07/2007 :  14:16:30  Show Profile  Reply with Quote
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




Edited by - X002548 on 09/07/2007 14:20:05
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/07/2007 :  14:37:25  Show Profile  Reply with Quote
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 - 09/07/2007 :  14:40:50  Show Profile  Reply with Quote
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

USA
37133 Posts

Posted - 09/07/2007 :  14:47:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 09/07/2007 14:48:07
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/10/2007 :  14:11:45  Show Profile  Visit funketekun's Homepage  Reply with Quote
thanks ..grabbed a piece from valentines code.

=============================
http://www.sqlserverstudy.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000