VB.Net sql error 26 - error locating server/instance specified

  • Thread starter Thread starter AbanoubZak
  • Start date Start date
A

AbanoubZak

Guest
Hello,

I have an application that create DB then Tables inside Then some login Users

Public Function CreateDB()
MessageBox.Show("CreateDB")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
USE [master]

/****** Object: Database [AZFTS] Script Date: 02/15/2020 00:15:14 ******/
CREATE DATABASE [AZFTS] ON PRIMARY
( NAME = N'AZFTS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\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.SQLEXPRESS\MSSQL\DATA\AZFTS_log.ldf' , SIZE = 15040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

ALTER DATABASE [AZFTS] SET COMPATIBILITY_LEVEL = 100

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AZFTSACC].[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
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=master;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)

Dim sCmd = New SqlCommand(script, 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

Public Function CreateDBT()
MessageBox.Show("CreateDBT")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
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
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"

Dim conn = New SqlConnection(connStr)

Dim sCmd = New SqlCommand(script, 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

but the Login user give me Error "error 26 - error locating server/instance specified" and i use the same code and conection string except for the DB i change this time from Master to "AZFTS" as it's my DB that will use to create login user in

Public Function adduser2()
MessageBox.Show("adduser2")
Dim username As String = Environment.UserName.ToString
Dim Domain As String = Environment.MachineName.ToString
Dim domainuser As String = "" & Domain & "\" & username & ""
Dim databaseName As String = "AZFTS"
Dim login As String = "AZFTSUSER"
Dim password As String = "123456789"
Dim passwordsa As String = "123456789123"
Dim script =
<SQL>
CREATE LOGIN [<%= login %>] WITH PASSWORD=N'<%= password %>', DEFAULT_DATABASE=[<%= databaseName %>]
ALTER LOGIN [<%= domainuser %>] DISABLE
EXEC sp_password NULL, '<%= passwordsa %>', 'sa'
USE [<%= databaseName %>]
CREATE USER [<%= login %>] FOR LOGIN [<%= login %>]
ALTER USER [<%= login %>] WITH DEFAULT_SCHEMA=[dbo]
EXECUTE sp_addrolemember db_datareader, 'AZFTSUSER'
EXECUTE sp_addrolemember db_datawriter, 'AZFTSUSER'
EXECUTE sp_addrolemember db_backupoperator, 'AZFTSUSER'
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)

Dim sCmd = New SqlCommand(script, 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


Any one have idea why first two have no problem but stop with number 3 which is "adduser2"

Continue reading...
 
Back
Top