VB.net create SQL DB from a script

[AbanoubZak]

Hello,

Lately i ask if i can create an SQL instance and Db, someone helped me with the following code

Dim databaseName As String = "MyDatabase"
Dim login As String = "Payne"
Dim password As String = "MyPassword"
Dim script =
<SQL>
CREATE DATABASE [<%= databaseName %>]
CREATE LOGIN [<%= login %>] WITH PASSWORD=N'<%= password %>', DEFAULT_DATABASE=[<%= databaseName %>]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<%= login %>]
USE [<%= databaseName %>]
CREATE USER [<%= login %>] FOR LOGIN [<%= login %>]
ALTER USER [<%= login %>] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_owner] ADD MEMBER [<%= login %>]
</SQL>.Value

she also advice me to script out the full DB and i did script it to a file and removed "Go",i was trying it throw the code

Public Function CreateDB()

Dim sqlFile As String = "D:\DB.sql"
Dim sqlText As String = File.ReadAllText(sqlFile)
Dim connStr As String = "Server=Machine\instancename;Database=master;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)

Dim sCmd = New SqlCommand(sqlText, conn)

Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function

the problem is this file has the DB including the tables and everything

when i used my code i understood that it load the whole script first before it execute it.

so it was giving me the error that the AZFTS is not exist (which is my DB Name) ths scrip is as follwing

USE [master]


/****** Object: Database [AZFTS] Script Date: 02/14/2020 14:38:15 ******/
CREATE DATABASE [AZFTS] ON PRIMARY
( NAME = N'AZFTS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.AZFTSDB\MSSQL\DATA\AZFTS.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AZFTS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.AZFTSDB\MSSQL\DATA\AZFTS_log.ldf' , SIZE = 15040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)


ALTER DATABASE [AZFTS] SET COMPATIBILITY_LEVEL = 100


IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AZFTS].[dbo].[sp_fulltext_database] @action = 'enable'
end


ALTER DATABASE [AZFTS] SET ANSI_NULL_DEFAULT OFF


ALTER DATABASE [AZFTS] SET ANSI_NULLS OFF


ALTER DATABASE [AZFTS] SET ANSI_PADDING OFF


ALTER DATABASE [AZFTS] SET ANSI_WARNINGS OFF


ALTER DATABASE [AZFTS] SET ARITHABORT OFF


ALTER DATABASE [AZFTS] SET AUTO_CLOSE ON


ALTER DATABASE [AZFTS] SET AUTO_CREATE_STATISTICS ON


ALTER DATABASE [AZFTS] SET AUTO_SHRINK OFF


ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS ON


ALTER DATABASE [AZFTS] SET CURSOR_CLOSE_ON_COMMIT OFF


ALTER DATABASE [AZFTS] SET CURSOR_DEFAULT GLOBAL


ALTER DATABASE [AZFTS] SET CONCAT_NULL_YIELDS_NULL OFF


ALTER DATABASE [AZFTS] SET NUMERIC_ROUNDABORT OFF


ALTER DATABASE [AZFTS] SET QUOTED_IDENTIFIER OFF


ALTER DATABASE [AZFTS] SET RECURSIVE_TRIGGERS OFF


ALTER DATABASE [AZFTS] SET DISABLE_BROKER


ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF


ALTER DATABASE [AZFTS] SET DATE_CORRELATION_OPTIMIZATION OFF


ALTER DATABASE [AZFTS] SET TRUSTWORTHY OFF


ALTER DATABASE [AZFTS] SET ALLOW_SNAPSHOT_ISOLATION OFF


ALTER DATABASE [AZFTS] SET PARAMETERIZATION SIMPLE


ALTER DATABASE [AZFTS] SET READ_COMMITTED_SNAPSHOT OFF


ALTER DATABASE [AZFTS] SET HONOR_BROKER_PRIORITY OFF


ALTER DATABASE [AZFTS] SET READ_WRITE


ALTER DATABASE [AZFTS] SET RECOVERY SIMPLE


ALTER DATABASE [AZFTS] SET MULTI_USER


ALTER DATABASE [AZFTS] SET PAGE_VERIFY CHECKSUM


ALTER DATABASE [AZFTS] SET DB_CHAINING OFF

USE [AZFTS]

/****** Object: Table [dbo].[ShiftStates] Script Date: 02/14/2020 14:31:56 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[ShiftStates](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EGPOpen] [money] NULL,
[USDOpen] [money] NULL,
[EUROpen] [money] NULL,
[GBPOpen] [money] NULL,
[EGPClose] [money] NULL,
[USDClose] [money] NULL,
[EURClose] [money] NULL,
[GBPClose] [money] NULL,
[UserName] [varchar](max) NULL,
[MachineName] [varchar](max) NULL,
[DateOn] [date] NULL,
[TimeOn] [time](7) NULL,
[DateOff] [date] NULL,
[TimeOff] [time](7) NULL,
[State] [varchar](max) NULL,
[EGP] AS ([egpclose]-[egpopen]),
[USD] AS ([USDClose]-[USDOpen]),
[EUR] AS ([EURClose]-[EUROpen]),
[GBP] AS ([GBPClose]-[GBPOpen]),
CONSTRAINT [PK_ShiftStates] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING OFF

/****** Object: Table [dbo].[Setting] Script Date: 02/14/2020 14:31:56 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

i tried to me make it 2 files first one have DB creation and 2nd have the Tables

the DB was created but then the 2nd file which have the tables gave me no access and i was using the windows integrated security not any custom user so any one have an idea.

2nd, would be much helpfull if some one told me how to Dim the scrip to code or a string maybe so i don't need a script file.

thank you.

Continue reading...