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.
Author |
Topic |
hornet
Yak Posting Veteran
96 Posts |
Posted - 2006-03-09 : 03:58:55
|
Hi! I have the script which create database. At first this database isn't exists in server.I have tried to do it in query analiser, but i have took a error.How I must do it?Thanks. |
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 05:49:10
|
Can you post the error message here please, and also the script that you used.Otherwise we'll just be guessing what the problem is!Kristen |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-09 : 08:16:43
|
Also, this is the Forum to post workable scripts. To ask questions, you should use other Forums like Developer, Transact-SQL, etcMadhivananFailing to plan is Planning to fail |
|
|
hornet
Yak Posting Veteran
96 Posts |
Posted - 2006-03-09 : 08:33:15
|
He has writtenServer: Msg 226, Level 16, State 7, Line 5DROP DATABASE statement not allowed within multi-statement transaction.Server: Msg 226, Level 16, State 5, Line 2CREATE DATABASE statement not allowed within multi-statement transaction.Server: Msg 226, Level 16, State 6, Line 1ALTER DATABASE statement not allowed within multi-statement transaction.sp_dboption command failed.script was created form enterprise manager by GenerateSQLScript |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-09 : 08:41:58
|
Can you post your script ?----------------------------------'KH' |
|
|
hornet
Yak Posting Veteran
96 Posts |
Posted - 2006-03-10 : 03:00:57
|
Here is it:IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TestASP') DROP DATABASE [TestASP]GOCREATE DATABASE [TestASP] ON (NAME = N'TestASP_Data', FILENAME = N'E:\HornetDeveloper\SQL\TestASP_Data.MDF' , SIZE = 3, MAXSIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'TestASP_Log', FILENAME = N'E:\HornetDeveloper\SQL\TestASP_Log.LDF' , SIZE = 1, MAXSIZE = 2, FILEGROWTH = 1) COLLATE Cyrillic_General_CI_ASGOexec sp_dboption N'TestASP', N'autoclose', N'false'GOexec sp_dboption N'TestASP', N'bulkcopy', N'false'GOexec sp_dboption N'TestASP', N'trunc. log', N'false'GOexec sp_dboption N'TestASP', N'torn page detection', N'true'GOexec sp_dboption N'TestASP', N'read only', N'false'GOexec sp_dboption N'TestASP', N'dbo use', N'false'GOexec sp_dboption N'TestASP', N'single', N'false'GOexec sp_dboption N'TestASP', N'autoshrink', N'false'GOexec sp_dboption N'TestASP', N'ANSI null default', N'false'GOexec sp_dboption N'TestASP', N'recursive triggers', N'false'GOexec sp_dboption N'TestASP', N'ANSI nulls', N'false'GOexec sp_dboption N'TestASP', N'concat null yields null', N'false'GOexec sp_dboption N'TestASP', N'cursor close on commit', N'false'GOexec sp_dboption N'TestASP', N'default to local cursor', N'false'GOexec sp_dboption N'TestASP', N'quoted identifier', N'false'GOexec sp_dboption N'TestASP', N'ANSI warnings', N'false'GOexec sp_dboption N'TestASP', N'auto create statistics', N'true'GOexec sp_dboption N'TestASP', N'auto update statistics', N'true'GOuse [TestASP]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Category_InfoHotels]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[Category] DROP CONSTRAINT FK_Category_InfoHotelsGO/****** Object: Stored Procedure slog.CursorTest Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[slog].[CursorTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [slog].[CursorTest]GO/****** Object: Stored Procedure dbo.prGetAllHotelsInfo Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetAllHotelsInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[prGetAllHotelsInfo]GO/****** Object: Stored Procedure dbo.prGetAllRoomsInCategory Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetAllRoomsInCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[prGetAllRoomsInCategory]GO/****** Object: Stored Procedure dbo.prGetOneHotelInfo Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetOneHotelInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[prGetOneHotelInfo]GO/****** Object: Stored Procedure dbo.spTestOb Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spTestOb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[spTestOb]GO/****** Object: View dbo.MaxIDClient Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MaxIDClient]') and OBJECTPROPERTY(id, N'IsView') = 1)drop view [dbo].[MaxIDClient]GO/****** Object: Table [dbo].[Category] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Category]GO/****** Object: Table [dbo].[InfoHotels] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InfoHotels]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[InfoHotels]GO/****** Object: Table [dbo].[Room] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Room]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Room]GO/****** Object: Table [dbo].[Seat] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Seat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Seat]GO/****** Object: Table [dbo].[rrr] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rrr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[rrr]GO/****** Object: Table [dbo].[test] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[test]GO/****** Object: Table [dbo].[Cities] Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Cities]GO/****** Object: Default dbo.Age Script Date: 10.03.2006 10:53:57 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Age]') and OBJECTPROPERTY(id, N'IsDefault') = 1)drop default [dbo].[Age]GO/****** Object: Login RADUGA\Lukshin Script Date: 10.03.2006 10:53:54 ******/if not exists (select * from master.dbo.syslogins where loginname = N'RADUGA\Lukshin') exec sp_grantlogin N'RADUGA\Lukshin' exec sp_defaultdb N'RADUGA\Lukshin', N'master' exec sp_defaultlanguage N'RADUGA\Lukshin', N'us_english'GO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/if not exists (select * from master.dbo.syslogins where loginname = N'RADUGA\merinat') exec sp_grantlogin N'RADUGA\merinat' exec sp_defaultdb N'RADUGA\merinat', N'TestASP' exec sp_defaultlanguage N'RADUGA\merinat', N'ðóññêèé'GO/****** Object: Login slog Script Date: 10.03.2006 10:53:54 ******/if not exists (select * from master.dbo.syslogins where loginname = N'slog')BEGIN declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'TestASP', @loginlang = N'us_english' if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb) select @logindb = N'master' if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english') select @loginlang = @@language exec sp_addlogin N'slog', null, @logindb, @loginlangENDGO/****** Object: Login BUILTIN\Àäìèíèñòðàòîðû Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'BUILTIN\Àäìèíèñòðàòîðû', sysadminGO/****** Object: Login RADUGA\Lukshin Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\Lukshin', sysadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', sysadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', securityadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', serveradminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', setupadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', processadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', diskadminGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', dbcreatorGO/****** Object: Login RADUGA\merinat Script Date: 10.03.2006 10:53:54 ******/exec sp_addsrvrolemember N'RADUGA\merinat', bulkadminGO/****** Object: User BUILTIN\Àäìèíèñòðàòîðû Script Date: 10.03.2006 10:53:55 ******/if not exists (select * from dbo.sysusers where name = N'BUILTIN\Àäìèíèñòðàòîðû' and uid < 16382) EXEC sp_grantdbaccess N'BUILTIN\Àäìèíèñòðàòîðû', N'BUILTIN\Àäìèíèñòðàòîðû'GO/****** Object: User dbo Script Date: 10.03.2006 10:53:55 ******//****** Object: User slog Script Date: 10.03.2006 10:53:55 ******/if not exists (select * from dbo.sysusers where name = N'slog' and uid < 16382) EXEC sp_grantdbaccess N'slog', N'slog'GO/****** Object: User slog Script Date: 10.03.2006 10:53:55 ******/exec sp_addrolemember N'db_owner', N'slog'GO/****** Object: User slog Script Date: 10.03.2006 10:53:55 ******/exec sp_addrolemember N'db_securityadmin', N'slog'GO/****** Object: Default dbo.Age Script Date: 10.03.2006 10:53:59 ******/create default [Age] as 32GO/****** Object: Table [dbo].[Cities] Script Date: 10.03.2006 10:54:00 ******/CREATE TABLE [dbo].[Cities] ( [CityID] [bigint] NOT NULL , [CityName] [nvarchar] (100) COLLATE Cyrillic_General_CI_AS NOT NULL , [Population] [float] NOT NULL ) ON [PRIMARY]GO/****** Object: Table [dbo].[InfoHotels] Script Date: 10.03.2006 10:54:01 ******/CREATE TABLE [dbo].[InfoHotels] ( [IDHotel] [int] NOT NULL , [NameHotel] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL , [Resort] [nvarchar] (60) COLLATE Cyrillic_General_CI_AS NOT NULL , [LevelHotel] [nchar] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ) ON [PRIMARY]GO/****** Object: Table [dbo].[Room] Script Date: 10.03.2006 10:54:01 ******/CREATE TABLE [dbo].[Room] ( [RowNumber] [int] IDENTITY (1, 1) NOT NULL , [EventClass] [int] NULL , [TextData] [ntext] COLLATE Cyrillic_General_CI_AS NULL , [NTUserName] [nvarchar] (128) COLLATE Cyrillic_General_CI_AS NULL , [ClientProcessID] [int] NULL , [ApplicationName] [nvarchar] (128) COLLATE Cyrillic_General_CI_AS NULL , [LoginName] [nvarchar] (128) COLLATE Cyrillic_General_CI_AS NULL , [SPID] [int] NULL , [Duration] [bigint] NULL , [StartTime] [datetime] NULL , [Reads] [bigint] NULL , [Writes] [bigint] NULL , [CPU] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[Seat] Script Date: 10.03.2006 10:54:01 ******/CREATE TABLE [dbo].[Seat] ( [IDSeat] [bigint] NOT NULL , [IDHotel] [int] NOT NULL , [InIDCategory] [int] NOT NULL , [InIDRoom] [int] NOT NULL , [NumSeat] [smallint] NOT NULL ) ON [PRIMARY]GO/****** Object: Table [dbo].[rrr] Script Date: 10.03.2006 10:54:02 ******/CREATE TABLE [dbo].[rrr] ( [iii] [int] NULL ) ON [PRIMARY]GO/****** Object: Table [dbo].[test] Script Date: 10.03.2006 10:54:02 ******/CREATE TABLE [dbo].[test] ( [rid] [int] NOT NULL , [qq] [char] (10) COLLATE Cyrillic_General_CI_AS NULL , [tt] [char] (10) COLLATE Cyrillic_General_CI_AS NULL , [Age] [float] NULL ) ON [PRIMARY]GO/****** Object: Table [dbo].[Category] Script Date: 10.03.2006 10:54:02 ******/CREATE TABLE [dbo].[Category] ( [InIDCategory] [int] NOT NULL , [OutIDCategory] [int] NOT NULL , [IDHotel] [int] NOT NULL , [NameCategory] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL , [ShortNameCat] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NOT NULL , [MainSeats] [smallint] NOT NULL , [ExtraSeats] [smallint] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Cities] WITH NOCHECK ADD CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED ( [CityID] ) ON [PRIMARY] GOALTER TABLE [dbo].[InfoHotels] WITH NOCHECK ADD CONSTRAINT [PK_InfoHotels] PRIMARY KEY CLUSTERED ( [IDHotel] ) ON [PRIMARY] GOALTER TABLE [dbo].[Room] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [RowNumber] ) ON [PRIMARY] GOALTER TABLE [dbo].[Seat] WITH NOCHECK ADD CONSTRAINT [PK_Seat] PRIMARY KEY CLUSTERED ( [IDSeat] ) ON [PRIMARY] GOALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [InIDCategory] ) ON [PRIMARY] GOALTER TABLE [dbo].[Seat] WITH NOCHECK ADD CONSTRAINT [DF_Seat_NumSeat] DEFAULT (1) FOR [NumSeat]GO CREATE INDEX [IX_Category] ON [dbo].[Category]([InIDCategory]) ON [PRIMARY]GOsetuserGOEXEC sp_bindefault N'[dbo].[Age]', N'[test].[Age]'GOsetuserGOALTER TABLE [dbo].[Category] ADD CONSTRAINT [FK_Category_InfoHotels] FOREIGN KEY ( [IDHotel] ) REFERENCES [dbo].[InfoHotels] ( [IDHotel] ) ON DELETE CASCADE ON UPDATE CASCADE GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO/****** Object: View dbo.MaxIDClient Script Date: 10.03.2006 10:54:02 ******/CREATE VIEW dbo.MaxIDClientASSELECT nameclientFROM dbo.ClientsWHERE (idclient > 5) OR (idclient < 0)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO/****** Object: Stored Procedure dbo.prGetAllHotelsInfo Script Date: 10.03.2006 10:54:02 ******/--Ïðîöåäóðà âîçâðàùàåò îáùóþ èíôîðìàöèþ îáî âåõ îòåëÿõ CREATE PROCEDURE prGetAllHotelsInfo AS SELECT HotelName,LevelHotel,Resort FROM Hotels RETURN 0GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO/****** Object: Stored Procedure dbo.prGetAllRoomsInCategory Script Date: 10.03.2006 10:54:02 ******/--Ïðîöåäóðà âîçâðàùàåò âñå íîìåðà äëÿ îäíîé êîíêðåòíîé êàòåãîðèèCREATE PROCEDURE prGetAllRoomsInCategory @HotelID INT, @CategID INT AS SELECT InIDRoom FROM Room WHERE IDHotel=@HotelID AND InIDCategory=@CategIDRETURN 0GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO/****** Object: Stored Procedure dbo.prGetOneHotelInfo Script Date: 10.03.2006 10:54:02 ******/--Ïðîöåäóðà âîçâðàùàåò Èíôîðìàöèþ îá îäíîì îòåëå CREATE PROCEDURE prGetOneHotelInfo @HotelID INT AS SELECT NameCategory, ShortNameCat, MainSeats FROM Category WHERE IDHotel = @HotelID RETURN 0GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO/****** Object: Stored Procedure dbo.spTestOb Script Date: 10.03.2006 10:54:02 ******/CREATE PROCEDURE spTestOb ASIF EXISTS (SELECT name FROM sysobjects WHERE name = 'Room') SELECT type FROM sysobjects WHERE name = 'Room'RETURN 0GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO/****** Object: Stored Procedure slog.CursorTest Script Date: 10.03.2006 10:54:02 ******/setuser N'slog'GO--- Work with cursorsCREATE PROCEDURE [slog].[CursorTest] ASDECLARE cur_GetBigCity SCROLL CURSOR FOR SELECT * FROM Cities WHERE Population > 1000FOR UPDATEOPEN cur_GetBigCityIF @@Cursor_Rows = 0 BEGIN CLOSE cur_GetBigCity DEALLOCATE cur_GetBigCity PRINT 'Â êóðñîðå íåò çàïèñåé' RETURN 0ENDWHILE @@FETCH_STATUS = 0 FETCH NEXT FROM cur_GetBigCityCLOSE cur_GetBigCityDEALLOCATE cur_GetBigCityRETURN 0GOsetuserGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOexec sp_addextendedproperty N'Build', 194, N'user', N'dbo', N'table', N'Room'GOexec sp_addextendedproperty N'MajorVer', 8, N'user', N'dbo', N'table', N'Room'GOexec sp_addextendedproperty N'MinorVer', 0, N'user', N'dbo', N'table', N'Room'GOexec sp_addextendedproperty N'MS_Description', N'Êîä ìåñòà ãëîáàëüíûé', N'user', N'dbo', N'table', N'Seat', N'column', N'IDSeat'GOexec sp_addextendedproperty N'MS_Description', N'Êîä ìåñòà â íîìåðå', N'user', N'dbo', N'table', N'Seat', N'column', N'NumSeat'GOexec sp_addextendedproperty N'MS_Description', N'Óíèêàëüíûé êëþ÷ êàòåãîðèè â ÁÄ', N'user', N'dbo', N'table', N'Category', N'column', N'InIDCategory'GOexec sp_addextendedproperty N'MS_Description', N'Êëþ÷ êàòåãîðèè â êîíêðåòíîì îòåëå', N'user', N'dbo', N'table', N'Category', N'column', N'OutIDCategory'GO |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-10 : 03:11:24
|
I have tried running the first few statements of your script and did not encounter any of the error you posted.----------------------------------'KH' |
|
|
hornet
Yak Posting Veteran
96 Posts |
Posted - 2006-03-10 : 06:45:22
|
Have you done this in QueryAnalizer?And what database have you selected in toolbar comobobox?Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-10 : 08:22:33
|
yes. run in Query Analyser under master----------------------------------'KH' |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-10 : 09:42:17
|
There are some "foreign" characters in here, so will need to be run in Unicode - that's fine for Query Analyser, but if you are editing it in some other editor that could introduce problems [if its not Unicode aware]The script uses "GO" as the Batch Separator; make sure that this is how your Query Analyser is set up:Tools : Options : [Connections] : "Batch separator"which should be set to "GO" (without the quotes)Kristen |
|
|
hornet
Yak Posting Veteran
96 Posts |
Posted - 2006-03-13 : 02:59:09
|
Thanks a lot! |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-13 : 03:12:02
|
quote: Originally posted by hornet Thanks a lot!
were you able to get your script to run successfully?-ec |
|
|
|
|
|
|
|