pjsz


Hello. I have been trying all morning to get this CreateDatabase function to work. I have searched others peoples issues and have found none like mine. It is having trouble with the FileGroup sql. The inner exception is "Syntax error near 'FileGroup'. If i set the SQLExecutionMode to CaptureSql then the function has no exceptions. However no database is created and I do not know how to view the SQL that would have been executed to check on the error. If I do not manually try to build the files and just to a simple "Create()" with the database object , then the function works and files are created in the default location.

Here is the function:

Code Snippet

Public Shared Function CreateDatabase(ByVal serverName As String, ByVal databaseName As String, _
Optional ByVal filePath As String = "D:\INS Data Version 6.0\", _
Optional ByRef errmsg As String = "") As Boolean


'Base file name for db files
Dim fileName As String
fileName = filePath & "\" & databaseName
fileName = fileName.Replace("\\", "\")

'Our objects we need
Dim srvr As Server
Dim db As Database
Dim dbFG As FileGroup
Dim df1 As DataFile
Dim lf1 As LogFile

'Connect server, exiting if failure
srvr = GetServer(serverName)
If srvr Is Nothing Then
errmsg = "Can't connect to server, " & serverName
Return False
End If

'srvr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql

'Set up database how we want it, Smo is picky about order of things setup
db = New Database(srvr, databaseName)
dbFG = New FileGroup(db, "Primary")

'Add file group to db
db.FileGroups.Add(dbFG)

'Now add primary db file to file group
df1 = New DataFile(dbFG, databaseName & "_Data")
dbFG.Files.Add(df1)
df1.FileName = fileName & ".mdf" 'Set file location

'Add log file
lf1 = New LogFile(db, databaseName & "_Log")
db.LogFiles.Add(lf1)
lf1.FileName = fileName & ".ldf"

'Create db
Try
db.Create()
Catch ex As Microsoft.SqlServer.Management.Smo.FailedOperationException
errmsg = "Could Not Create Database: " & ex.Message.ToString
Exit Function
End Try

'Success
Return True

End Function


Thanks in advance for any help.








Re: SMO - Create failed for Database (please help)

AVanWieren


You might want to verify that the path and filename are correct. The structure looks correct, but the address of the file has caused me problems in the past with \ and what not. This is the first thing I would look at would be the path and file and verify that everything going in is single slashes and is correct. I ended up using a stringbuilder to construct the path and then converted it to string when I passed it in and this did the trick, using an @ did not work and double slashes do not work. I personally have found this to be tricky. You could also try and use a simple path such as c:\temp\ and see if that works.

Hope this helps.





Re: SMO - Create failed for Database (please help)

Allen White

I can't see anything wrong in your code to help you fix the problem (maybe the slashes, I don't know), so I'm pasting code I use in demos to show how to create a database where you want it using SMO:

Dim srv As Server
Dim srvConn As ServerConnection

srv = New Server("SQLHASP1\SQLDEV01")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim strDBName As String
strDBName = "NewDatabase"

Dim dbNewDB As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbNewDB = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbNewDB, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbNewDB.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "C:\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.KB 'Define the Growth Type
dbFile.Growth = 100.0 * 1024.0 'Define the Growth Percent
'dbFile.MaxSize = 1000.0 * 1024.0 'Define the Max database size

dbLogFile = New LogFile(dbNewDB, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbNewDB.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "C:\MSSQL\Logs\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent

dbNewDB.Create() 'Create the database

Hope this helps.