December 21, 2009 at 1:12 am
Dear Sir,
This is the problem when I am runing the below statment Please help me.
error message
----------------------------------------------
Server: Msg 2714, Level 16, State 5, Procedure sc_CRDB, Line 65535
There is already an object named 'sc_CRDB' in the database.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.ldf'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.mdf'.
The CREATE DATABASE process is allocating 20.00 MB on disk 'MNCPCORP_dat'.
The CREATE DATABASE process is allocating 10.00 MB on disk 'MNCPCORP_log'.
----------------------------------------------
This my Code
---------------------------------------------
CREATE PROCEDURE sc_CRDB
AS
IF EXISTS(SELECT 1 FROM master.dbo.sysobjects Where name='sc_CRDB' and Type='P')
DROP PROCEDURE sc_CRDB
GO
IF EXISTS(SELECT 1 FROM master.dbo.sysdatabases where name='MNCPCORP')
DROP DATABASE MNCPCORP
GO
CREATE DATABASE MNCPCORP
ON PRIMARY
(NAME=MNCPCORP_dat,
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.mdf',
SIZE=20MB,
MAXSIZE=100MB,
FILEGROWTH=10%)
LOG ON
(NAME=MNCPCORP_log,
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.ldf',
SIZE=10MB,
MAXSIZE=25MB,
FILEGROWTH=10%)
GO
--------------------------------------------------------
Thankig You
Debasis Bag
December 21, 2009 at 2:11 am
you need to modify your code like below.
IF EXISTS(SELECT 1 FROM master.dbo.sysobjects Where name='sc_CRDB' and Type='P')
DROP PROCEDURE sc_CRDB
GO
CREATE PROCEDURE sc_CRDB
AS
IF EXISTS(SELECT 1 FROM master.dbo.sysdatabases where name='MNCPCORP')
DROP DATABASE MNCPCORP
--GO
CREATE DATABASE MNCPCORP
ON PRIMARY
(NAME=MNCPCORP_dat,
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.mdf',
SIZE=20MB,
MAXSIZE=100MB,
FILEGROWTH=10%)
LOG ON
(NAME=MNCPCORP_log,
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\MNCPCORP.ldf',
SIZE=10MB,
MAXSIZE=25MB,
FILEGROWTH=10%)
--GO
GO marks the end of batch. Hence you can not use if while defining a stored procedure as your SP definition will end at the GO and the next part will be considered as a next batch.
Another problem, you are trying to drop the SP inside the SP definition. You will have to check and drop it before creating.
-Vikas Bindra
December 21, 2009 at 3:27 am
Thank you for your reply
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply