2 Replies - 5364 Views - Last Post: 14 January 2010 - 12:50 PM

#1 xheartonfire43x   User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Problems with SQL CREATE and SQL USE command

Posted 24 April 2009 - 07:52 AM

I am trying to basically make a carbon copy of one database on MS SQL 2000 SP 3 without any of the actualy data. I am fairly new to SQL (I am a ColdFusion Developer) when I run the SQL I have it throws errors. All I did was go through the DataBase that I already have and use the CreateTo: in the right click menu and then copied all of that SQL.

USE [master]
GO
/****** Object:  Database [CRE_TeMemberData]	Script Date: 04/24/2009 10:14:10 ******/
CREATE DATABASE [CRE_TeMemberData] ON  PRIMARY 
( NAME = N'CRE_TeMemberData_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\CRE_TeMemberData_Data.MDF' , SIZE = 1600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'CRE_TeMemberData_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\CRE_TeMemberData_Log.LDF' , SIZE = 1536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'CRE_TeMemberData', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CRE_TeMemberData].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [CRE_TeMemberData] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET ARITHABORT OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [CRE_TeMemberData] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [CRE_TeMemberData] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [CRE_TeMemberData] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [CRE_TeMemberData] SET  READ_WRITE 
GO
ALTER DATABASE [CRE_TeMemberData] SET RECOVERY FULL 
GO
ALTER DATABASE [CRE_TeMemberData] SET  MULTI_USER 
GO
ALTER DATABASE [CRE_TeMemberData] SET PAGE_VERIFY TORN_PAGE_DETECTION  
GO
ALTER DATABASE [CRE_TeMemberData] SET DB_CHAINING ON



error

Quote

The CREATE DATABASE process is allocating 1.56 MB on disk 'CRE_TeMemberData_Data'.
The CREATE DATABASE process is allocating 1.50 MB on disk 'CRE_TeMemberData_Log'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

(0 row(s) affected)
Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'TORN_PAGE_DETECTION'.
Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'DB_CHAINING'.


Second query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[AddressCards]	Script Date: 04/24/2009 10:15:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AddressCards](
	[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AddressCards_ID]  DEFAULT (newid()),
	[contactid] [nvarchar](50) NULL,
	[addr1] [nvarchar](60) NULL,
	[addr2] [nvarchar](60) NULL,
	[city] [nvarchar](30) NULL,
	[state] [nvarchar](2) NULL,
	[zip] [nvarchar](15) NULL,
	[type] [nvarchar](20) NULL,
	[lastmodified] [smalldatetime] NULL,
	[recordedby] [nvarchar](50) NULL,
	[recorddadte] [smalldatetime] NULL CONSTRAINT [DF_AddressCards_recorddadte]  DEFAULT (getdate()),
	[deleted] [char](1) NULL CONSTRAINT [DF_AddressCards_deleted]  DEFAULT ('N'),
	[dnc] [char](1) NULL,
	[preferred] [char](1) NULL CONSTRAINT [DF_AddressCards_preferred]  DEFAULT (1),
	[note] [nvarchar](60) NULL,
 CONSTRAINT [PK_AddressCards] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Error

Quote

Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.


Third Query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[Contacts]	Script Date: 04/24/2009 10:19:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contacts](
	[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Contacts_id]  DEFAULT (newid()),
	[Prefix] [nvarchar](20) NULL,
	[Fname] [nvarchar](20) NULL,
	[Middle] [nvarchar](1) NULL,
	[Lname] [nvarchar](30) NULL,
	[Suffix] [nvarchar](20) NULL,
	[recorddate] [smalldatetime] NULL CONSTRAINT [DF_Contacts_recorddate]  DEFAULT (getdate()),
	[tempid] [nvarchar](50) NULL,
	[recordedby] [nvarchar](30) NULL,
	[deleted] [char](1) NULL CONSTRAINT [DF_Contacts_deleted]  DEFAULT ('N'),
	[deletedate] [smalldatetime] NULL,
	[simpleid] [int] IDENTITY(1,1) NOT NULL,
	[type] [nvarchar](1) NULL,
	[preferred_email] [nvarchar](100) NULL,
	[preferred_phone] [nvarchar](50) NULL,
	[website] [nvarchar](100) NULL,
	[preferred_addr1] [nvarchar](50) NULL,
	[preferred_addr2] [nvarchar](50) NULL,
	[preferred_city] [nvarchar](50) NULL,
	[preferred_state] [nvarchar](2) NULL,
	[preferred_zip] [nvarchar](15) NULL,
	[company] [nvarchar](100) NULL,
	[title] [nvarchar](100) NULL,
	[security_level] [nvarchar](1) NULL,
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Error

Quote

Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near '('.


Fourth Query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[EmailAddresses]	Script Date: 04/24/2009 10:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmailAddresses](
	[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_EmailAddresses_id]  DEFAULT (newid()),
	[contactid] [nvarchar](50) NULL,
	[type] [nvarchar](20) NULL,
	[email] [nvarchar](60) NULL,
	[preferred] [char](1) NULL CONSTRAINT [DF_EmailAddresses_preferred]  DEFAULT (1),
	[dnc] [char](1) NULL,
	[deleted] [char](1) NULL CONSTRAINT [DF_EmailAddresses_deleted]  DEFAULT ('N'),
	[recordedby] [nvarchar](20) NULL,
	[note] [nvarchar](60) NULL,
 CONSTRAINT [PK_EmailAddresses] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Error

Quote

Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '('.


Fourth Query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[Notes]	Script Date: 04/24/2009 10:25:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Notes](
	[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Notes_id]  DEFAULT (newid()),
	[contactid] [nvarchar](50) NULL,
	[recordedby] [nvarchar](50) NULL,
	[recorddate] [smalldatetime] NULL CONSTRAINT [DF_Notes_recorddate]  DEFAULT (getdate()),
	[notes] [text] NULL,
	[subject] [nvarchar](120) NULL,
	[deleted] [char](1) NULL CONSTRAINT [DF_Notes_deleted]  DEFAULT ('N'),
	[Private] [char](1) NULL CONSTRAINT [DF_Notes_Private]  DEFAULT ('N'),
 CONSTRAINT [PK_Notes] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Error

Quote

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '('.


Fifth Query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[PhoneNumbers]	Script Date: 04/24/2009 10:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PhoneNumbers](
	[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PhoneNumbers_ID]  DEFAULT (newid()),
	[contactid] [nvarchar](50) NULL,
	[type] [nvarchar](20) NULL,
	[area] [char](3) NULL,
	[prefix] [char](3) NULL,
	[num] [char](4) NULL,
	[ext] [char](10) NULL,
	[recordedby] [nvarchar](20) NULL,
	[preferred] [char](1) NULL CONSTRAINT [DF_PhoneNumbers_preferred]  DEFAULT (1),
	[DNC] [char](1) NULL,
	[Deleted] [char](1) NULL CONSTRAINT [DF_PhoneNumbers_Deleted]  DEFAULT ('N'),
	[note] [nvarchar](60) NULL,
 CONSTRAINT [PK_PhoneNumbers] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Error

Quote

Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.


Sixth Query
USE [CRE_TeMemberData]
GO
/****** Object:  Table [dbo].[Relationships]	Script Date: 04/24/2009 10:26:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Relationships](
	[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Relationships_id]  DEFAULT (newid()),
	[contactid] [nvarchar](50) NULL,
	[relationid] [nvarchar](50) NULL,
	[groupname] [nvarchar](20) NULL,
	[fullname] [nvarchar](50) NULL,
	[recordedby] [nvarchar](20) NULL,
 CONSTRAINT [PK_Relationships] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]



Error

Quote

Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '('.


I left out pplenty of queries because many of them worked. Thank you all in advance for the help... I know it is a lot of SQL.

I don't get why SQL that MS SQL produced is erroring.

This post has been edited by xheartonfire43x: 24 April 2009 - 08:39 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Problems with SQL CREATE and SQL USE command

#2 xheartonfire43x   User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Problems with SQL CREATE and SQL USE command

Posted 24 April 2009 - 11:26 AM

The problem is actually a bug in MS SQL 2000. I put practically the same code (just with the paths to the mdf and log files changed) and it works fine.
Was This Post Helpful? 0
  • +
  • -

#3 sonicus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 14-January 10

Re: Problems with SQL CREATE and SQL USE command

Posted 14 January 2010 - 12:50 PM

I suppose you may try an application for {REMOVED SPAM LINK}, there are a lot of data recovery tools
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1