SQL-DMO Frequently Asked Questions (FAQ)FAQ's:
FAQ: How do I set the default backup directory using SQL-DMO? The default backup directory is used when you do not specify a full path for example when using: backup database pubs to disk = 'pubs.bak' By default the backup directory for a default instance (using a default SQL Server 2000 installation) points to
"C:\Program Files\Microsoft SQL Server\MSSQL\Backup" This is how you can set the backup directory using SQL-DMO ' ********************************************************************************
' @file BackupDirectory.bas
' @description Setting the Backup Directory using SQL-DMO
' @copyright Copyright © SQLDev.Net 1991-2003 All rights reserved.
' @author GertD@SQLDev.Net
'
' @version @date @change
' 1 2003-03-23 project creation
' ********************************************************************************
' ********************************************************************************
' Add reference to Microsoft SQLDMO Object Library
' Located in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.dll
' ********************************************************************************
Option Explicit
Public Sub main()
On Error GoTo errHandler
Dim oSQLServer As SQLDMO.SQLServer2
Set oSQLServer = New SQLDMO.SQLServer2
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)"
Dim oRegistry As SQLDMO.Registry2
Set oRegistry = oSQLServer.Registry
' Current directory
Debug.Print oRegistry.BackupDirectory
oRegistry.BackupDirectory = "Z:\SQL\BACKUPS"
oSQLServer.DisConnect
Set oSQLServer = Nothing
Exit Sub
errHandler:
Debug.Print Err.Number & " " & Err.Description
Resume Next
End Sub Note: SQL-DMO does not verify if the directory actually exists!
FAQ: How do I use User Defined Functions in SQL-DMO Since User Defined Functions (UDF) were only introduced in SQL Server 2000, only the SQL-DMO version that ships with SQL Server 2000 supports UDF's. However in order not to break existing applications that were using SQL-DMO, interface version is used to exposed new functionality like User Defined Functions. Interface version mains that for certain objects there are now two versions, for example Database and Database2, where the Database object is identical to the version in 7.0 and Database2 is exposing the 7.0 and new 2000 functionality. This implies that you need to use the Database2 object instead of the Database object, in order to user user defined functions. This is a small example in Visual Basic 6.0 ' ********************************************************************************
' @file UserDefinedFunctions.bas
' @description Using User Defined Functions in SQL-DMO
' @copyright Copyright © SQLDev.Net 1991-2002 All rights reserved.
' @author GertD@SQLDev.Net
'
' @version @date @change
' 1 2002-02-17 project creation
' ********************************************************************************
' ********************************************************************************
' Add reference to Microsoft SQLDMO Object Library
' Located in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.dll
' ********************************************************************************
Option Explicit
Public Sub main()
On Error GoTo errHandler
' In order to use SQL Server 2000 specific functionality
' you need to use the version2 interfaces
'
Dim oSQLServer As SQLDMO.SQLServer2
Set oSQLServer = New SQLDMO.SQLServer2
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)\dev"
' Only Database2 exposes the UserDefinedFunctions collection
'
Dim oDatabase As SQLDMO.Database2
Set oDatabase = oSQLServer.Databases("master")
Dim oUserDefinedFunction As SQLDMO.UserDefinedFunction
For Each oUserDefinedFunction In oDatabase.UserDefinedFunctions
Debug.Print oUserDefinedFunction.Name & " type: " & _
UDFType(oUserDefinedFunction.Type)
Next
Set oUserDefinedFunction = Nothing
Set oDatabase = Nothing
oSQLServer.DisConnect
Set oSQLServer = Nothing
Exit Sub
errHandler:
Debug.Print "Error " & Err.Number & " " & Err.Description
End Sub
Public Function UDFType(t As SQLDMO.SQLDMO_UDF_TYPE) As String
If t = SQLDMOUDF_Inline Then
UDFType = "inline"
ElseIf t = SQLDMOUDF_Scalar Then
UDFType = "scalar"
ElseIf t = SQLDMOUDF_Table Then
UDFType = "table"
Else
UDFType = "unknown"
End If
End Function
Note: This trick is based on the fact that you can always get the older version from the higher version interface because the older version object is contained within the higher version object.
FAQ: How can I use spaces inside file name? When you are backup to or restoring from locations which have embedded spaces in the file path, you can get the following error: -2147218303 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
The way to workaround this issue is to place the file path between square brackets like "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]" instead of using "C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak"
Example:
' ********************************************************************************
' @file BackRest.bas
' @description Backup & Restore with long file names (with spaces)
' @copyright Copyright © SQLDev.Net 1991-2003 All rights reserved.
' @author GertD@SQLDev.Net
'
' @version @date @change
' 1 2003-03-23 project creation
' ********************************************************************************
' ********************************************************************************
' Add reference to Microsoft SQLDMO Object Library
' Located in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.dll
' ********************************************************************************
Private Sub BackupPubs()
On Error GoTo errHandler
Dim oSQLServer As New SQLDMO.SQLServer
Dim oBackup As New SQLDMO.Backup
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)"
oBackup.Database = "pubs"
' This does not work
' oBackup.Files = "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]"
oBackup.Files = "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]"
oBackup.SQLBackup oSQLServer
Set oBackup = Nothing
oSQLServer.DisConnect
Set oSQLServer = Nothing
Exit Sub errHandler:
Debug.Print Err.Number & " " & Err.Description
End Sub Private Sub RestorePubs()
On Error GoTo errHandler
Dim oSQLServer As New SQLDMO.SQLServer
Dim oRestore As New SQLDMO.Restore
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)"
oRestore.Database = "pubs"
' This does not work
' oRestore.Files = "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]"
oRestore.Files = "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]"
oRestore.SQLRestore oSQLServer
Set oRestore = Nothing
oSQLServer.DisConnect
Set oSQLServer = Nothing
Exit Sub errHandler:
Debug.Print Err.Number & " " & Err.Description
End Sub *** | 
