Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 )
 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
11752 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
11752 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
2875 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
2875 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
2875 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

2167 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
38200 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  
 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.09 seconds. Powered By: Snitz Forums 2000