Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

SQL Create Database and Tables Neccessary code??????

This may be a C# question but I'm sure SQL EE will know as well.

I need to know what actual code is needed to create a new database on a new install computer.

I can save the MS SQL scripts easy enough, but the don't code well in C#....

Q. What SQL syntax is really neccessary for a new Database install including permissions?

Here is some of the questionable code possibly not needed in C#?

 " BEGIN CREATE DATABASE [Accounting] ;"+
                    " EXEC dbo.sp_dbcmptlevel @dbname=N'Accounting', @new_cmptlevel=80 ;"+
                    " ALTER DATABASE [Accounting] SET ANSI_NULL_DEFAULT OFF ;"+
                    " ALTER DATABASE [Accounting] SET ANSI_NULLS OFF ;"+
                    " ALTER DATABASE [Accounting] SET ANSI_PADDING OFF ;"+
                    " ALTER DATABASE [Accounting] SET ANSI_WARNINGS OFF ;"+
                    " ALTER DATABASE [Accounting] SET ARITHABORT OFF ;"+
                    " ALTER DATABASE [Accounting] SET AUTO_CLOSE ON ;"+
                    " ALTER DATABASE [Accounting] SET AUTO_CREATE_STATISTICS ON ;"+
                    " ALTER DATABASE [Accounting] SET AUTO_SHRINK OFF ;"+
                    " ALTER DATABASE [Accounting] SET AUTO_UPDATE_STATISTICS ON ;"+
                    " ALTER DATABASE [Accounting] SET CURSOR_CLOSE_ON_COMMIT OFF ;"+
                    " ALTER DATABASE [Accounting] SET CURSOR_DEFAULT  GLOBAL ;"+
                    " ALTER DATABASE [Accounting] SET CONCAT_NULL_YIELDS_NULL OFF ;"+
                    " ALTER DATABASE [Accounting] SET NUMERIC_ROUNDABORT OFF ;"+
                    " ALTER DATABASE [Accounting] SET QUOTED_IDENTIFIER OFF ;"+
                    " ALTER DATABASE [Accounting] SET RECURSIVE_TRIGGERS OFF ;"+
                    " ALTER DATABASE [Accounting] SET  READ_WRITE ;"+
                    " ALTER DATABASE [Accounting] SET RECOVERY SIMPLE ;"+
                    " ALTER DATABASE [Accounting] SET  MULTI_USER ;"+
                    " ALTER DATABASE [Accounting] SET TORN_PAGE_DETECTION ON ;"+
                    " if ( ((@@microsoftversion / power(2, 24) = 8) and "+
                    " (@@microsoftversion & 0xffff >= 760)) or (@@microsoftversion / power(2, 24) >= 9) ) ;"+
                    " begin exec dbo.sp_dboption @dbname =  N'Accounting', @optname = 'db chaining', @optvalue = 'OFF' end ;"+
                    " USE [Accounting] ;"+
                    @" IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'BUILTIN\Administrators') "+
                    @" EXEC dbo.sp_grantdbaccess @loginame = N'BUILTIN\Administrators', @name_in_db = N'BUILTIN\Administrators' ;"+
                    " SET ANSI_NULLS ON ; "+
                    " SET QUOTED_IDENTIFIER ON ;"+

                    " IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reports]') "+
                    " AND OBJECTPROPERTY(id, N'IsUserTable') = 1) ;"+
                    " BEGIN CREATE TABLE [dbo].[Reports]([ID] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NULL, "+
                    " [Priority] [varchar](100) NULL, [User] [varchar](50) NULL, [Name] [varchar](50) NULL, [Middle] "+
                    " [varchar](50) NULL, [Last] [varchar](50) NULL, [Address] [varchar](100) NULL, [Zip] [int] NULL, "+
                    " ON [PRIMARY] END ;"+
                    " SET ANSI_NULLS ON ; "+
                    " SET QUOTED_IDENTIFIER ON ;"+
                    " SET ANSI_NULLS ON ;"+
                    " SET QUOTED_IDENTIFIER ON ;"+
                 
0
kvnsdr
Asked:
kvnsdr
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
                   @" IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'BUILTIN\Administrators') "+
                    @" EXEC dbo.sp_grantdbaccess @loginame = N'BUILTIN\Administrators', @name_in_db = N'BUILTIN\Administrators'

here '@' is not needed
Also u need to check the grant access statement ... whether to grant this user or any oither particular user
0
 
imran_fastCommented:
also check for the missing paranthesis

[varchar](50) NULL, [Last] [varchar](50) NULL, [Address] [varchar](100) NULL, [Zip] [int] NULL,<---------

[varchar](50) NULL, [Last] [varchar](50) NULL, [Address] [varchar](100) NULL, [Zip] [int] NULL)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now