CODEWITHSUNDEEP
sqldatabase
Lefteris Gkinis
Lefteris Gkinis

Reputation: 1259

Create new Database in SQL Server 2008 R2

I use the following code to create a new database in SQL Server 2008 R2, program language is VB.NET: 

Dim WEBconnString As String = "Data Source='NewName'; LCID=1032; Case Sensitive = FALSE; SSCE:Database Password=NewDBpass; Encrypt = TRUE;"
Dim engine As New SqlCeEngine(WEBconnString)
engine.CreateDatabase()

Additionally:

I open a web server connection with the following instruction: 

ServerConn.ConnectionString = "Provider=SQLOLEDB; Data Source=" & WebServerName & _
                         "; Database=master" & _
                         "; Uid=" & TempUserName & _
                         "; Pwd=" & TempUserPass
ServerConn.Open()

And I also use a data base connection with the following instruction:

SQLConn.ConnectionString = "server=" & WebServer & "; Uid=" & AdminName & "; Pwd=" & AdminPassword & "; database=master"
SQLConn.Open()

In order to be able to use my instruction. I have already create a temporary database in my ISP SQL Server, and I'm using for login name and password the credentials from this database.

For the first time use; it works fine, means that somewhere creates a database

Now I'm trying to see this database and I can't found anything

I'm running the same code again because it seams to me that the database not created, and at the engine instruction gives me an error "The database exist"

And the question is: Where it is opened the new database?

Please give me any solution I may need in VB.NET

Upvotes: 1

Views: 2535

Answers (3)

Lefteris Gkinis
Lefteris Gkinis

Reputation: 1259

Here is a ready to use sub for creating a procudere: 

Public Shared Sub CreateSP(ByVal usp As String, ByVal sender As Object, ByVal e As System.EventArgs)
        Dim RetValue As Boolean = False

        Try
            DBConnection("SQLConn", "SQLUSER", "master", False, sender, e)
            Select Case SQLConn.State
                Case ConnectionState.Open

                    MASQLComm = New SqlCommand("CREATE PROCEDURE [dbo].[" & usp & "]" & _
                      NewLine & " ( " & _
                      NewLine & "@DBName varchar(50) ) AS " & NewLine & _
                       "BEGIN_TRY: " & NewLine & " SET QUOTED_IDENTIFIER ON;" & NewLine & " SET NOCOUNT ON; " & _
                       NewLine & "DECLARE " & _
                       NewLine & "@ErrMsg nvarchar(4000), " & _
                       NewLine & "@CreateSdb nvarchar(4000)" & _
                       NewLine & "SET @CreateSdb =('CREATE DATABASE ' + @DBName )" & _
                       NewLine & "BEGIN_CATCH:" & NewLine & "SELECT ERROR_MESSAGE() as ErrorMessage;" & _
                       NewLine & "SELECT @ErrMsg = ERROR_MESSAGE()" & _
                       NewLine & "EXEC sp_executesql @CreateSdb" & _
                       NewLine & "RAISERROR (@ErrMsg,2,1)" & _
                       NewLine & "Return 0" & NewLine & "END_CATCH:" & NewLine & "Return 1" & NewLine & "END_TRY: ", SQLConn)
                    If MASQLComm.Connection.State = ConnectionState.Open Then
                        MASQLComm.ExecuteNonQuery()
                    Else
                        MASQLComm.Connection.Open()
                        MASQLComm.ExecuteNonQuery()
                    End If
            End Select
            Success = True
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Class 'DB_Access' CreateSP ", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Success = False
        End Try
        MASQLComm.Connection.Close()

    End Sub

And here is a code for use to run the above procedure:

Public Shared Sub RunSP(ByVal DrivePath As String, ByVal DBname As String, ByVal sender As Object, ByVal e As System.EventArgs)
        Try

             DBConnection("SQLConn", "SQLUSER", "master", False, sender, e)
            MASQLComm = New SqlCommand("EXEC dbo." & procName & " '" & DBname & " '", SQLConn)
        MASQLComm.ExecuteNonQuery()
            MASQLComm.ExecuteNonQuery()
            MASQLComm = New SqlCommand("ALTER DATABASE " & DBname & " SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE", SQLConn)
            MASQLComm.ExecuteNonQuery()
            Success = True
        Catch qx As SqlException
            MessageBox.Show(qx.Message, "Class 'NainClass' Run Stored Procdure", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            Success = False
        End Try

        MASQLComm.Connection.Close()
    End Sub

And here is a code for SQLconn connectionstring:

 If Not CBool(SQLConn.State) Then
                                SQLConn.ConnectionString = "server=" & ServNameKeyValue & "; Uid=" & cUserName & ";  Pwd=" & cUserPass & "; database=" & DBname
                                SQLConn.Open()
                            Else
                                SQLConn.Close()
                                SQLConn.ConnectionString = "server=" & ServNameKeyValue & "; Uid=" & cUserName & ";  Pwd=" & cUserPass & "; database=" & DBname
                                SQLConn.Open()
                            End If

Upvotes: 0

Chris
Chris

Reputation: 8647

1) It seems you are mixing SqlServerCompact (Local datatase which can be used in ASP.NET since version 4.0) and SqlServer. SqlCeEngine is part of System.Data.SqlServerCe namespace. So you create a SqCompact file and the engine.CreateDatabase() method raises an exception the second time. The connection string seems correct (for a SqlServerCompact file). If you don't specify the full path in your connection string (just set the database name like here), the database will be created where the app.exe is executed (=|DirectoryPath|). You will have to look for a file with .sdf extension.

2) I don't know what type is ServerConn but since I see "Provider=SQLOLEDB;" in your connection string, I guess your are using OLEDB class. You should use instead the managed SqlServer class (System.Data.SqlServer namespace). So you should use SqlConnection , SqlCommand, ..., objects. If you already use them, then check your connectionstring since the provider is wrong. At any rate, you can't access to the file created first in 1) in both case.

3) If your goal is to create a SqlServer Datatase, unfortunatly, where is no SqlServerEngine class like in SqlServerCe.

To create a database, some possible ways:

  • At design time with Sql Server Management studio
  • By executing scrits (via Sql Server Management studio, Or via .Net code, ...)
  • Using System.Data.SqlServer.Smo class (.Net)

Upvotes: 2

gh9
gh9

Reputation: 10693

It looks like you are opening the Server connection the the master database. The master database is where the sql server engine keeps all the important information and meta data about all databases on that server.

I need to replace database=master to database=WHATEVER DATABASE NAME YOU CREATED. All else fails go to the sql server and run this

use master;
select * from sysdatabases

That will give you the name of every database that master is 'monitoring'. Find the one you tried to create and replace the database=master with database=the one you found

Upvotes: 0

Related Questions