VB6 support for SQL SMO and .Net 2.0

Question

We are trying to move from using SQL DMO to SMO in our COM+ based application, as we are dropping support for SQL Server 2000 and adding support for SQL Server 2008 in addition to SQL Server 2005.

I have been reading around on this, and found this particular quip on this microsoft forum:

"SMO is only supported in VB/C#.Net 2005. It requires the .Net 2.0 Framework, which isn't available in VB/VC 6."

Is it true? Googling in general and googling stackoverflow did not throw up and definitive answers.

Is it possible to implement SQL SMO using VB6?

Edit: I used a COM Wrapper to get around this... check out my answer below for some more details.

Answer 1

I haven't tried it, but you could probably write vb.net code to interact with SQL SMO, and then write a com wrapper for the vb.net code. Of course, you'll still need to .net framework to use it.

Instead, I would suggest that you migrate away from SQL DMO and SQL SMO. Anything you do with DMO or SMO can be done without it. Of course, it won't be easy, but googling for replacement code (using straight T-SQL instead of SQL DMO) will provide you the answers you need.

Answer 2

I don't know of a way you can get to SMO via VB6. I'd agree with G Mastros about doing a COM/Interop approach to implement .NET code directly.

An alternative to consider is that you could shell out to Powershell, executing a script that would do your .NET SMO work. You still have the pre-requisite of requiring the .NET framework (and Powershell obviously), but it would get the job done. Your script could take parameters for credentials, database name, backup type, etc.

I implement this a lot at clients who have SQL Express (no SQL Agent for backups, like MSDE). I hook up a scheduled task which invokes the script and manages their backups.

If helpful, here is a script -- largely stolen but I have modified it somewhat:

param (
  [string] $ServerName,
  [string] $DatabaseName,
  [string] $Backuptype,
  [string] $BackupPath,
  [int] $NumDays
)
Get-ChildItem $BackupPath | where {$_.LastWriteTime -le (Get-Date).AddDays(-$NumDays)} | remove-item
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory($BackupPath) | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$servername"
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"

if ($Backuptype -eq "FULL") 
{
$bck.Action = 'Database' 
$extenstion=".BAK" 
$text1="Full Backup"
}

if ($Backuptype -eq "TRAN") 
{
$bck.Action = 'Log' 
$bck.LogTruncation = 2
$extenstion=".TRN" 
$text1="Transactional Log Backup"
}

if ($Backuptype -eq "DIFF") 
{ 
$bck.Incremental = 1 
$extenstion=".DIFF"  
$text1="Differential Backup"
}

$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine($BackupPath, $DatabaseName+ "_"+ [DateTime]::Now.ToString("yyyy_MM_dd_HH_mm")+$extenstion)
$bck.Devices.Add($fil)
$bck.Database=$DatabaseName
$bck.SqlBackup($srv)
write-host $text1 of $Databasename done

It can do full, differential, and transactional backups and uniquely names each resulting file based on the date and time, deleting all files older than a certain number of days.

The syntax to call it is:

.\Backup.ps1 INSTANCENAME DATABASENAME FULL|TRAN|DIFF PATH DAYSTOKEEP

so...

.\Backup.ps1 SQLEXPRESS Northwind FULL C:\TempHold\Test 30
.\Backup.ps1 SQLEXPRESS Northwind TRAN C:\TempHold\Test 30
.\Backup.ps1 SQLEXPRESS Northwind DIFF C:\TempHold\Test 30

To Schedule in Task Scheduler, pass in:

powershell c:\temphold\test\backup.ps1 "SQLEXPRESS Northwind DIFF C:\TempHold\Test 30"

Answer 3

Okay I figured out how to do this.

The problem was that VB6 has no .Net 2.0 support and hence we cannot use SMO with VB6.

To get around that, I wrote a COM wrapper in C# which uses SMO and maps (mostly) one-to-one with the kind of functionality I want from from my VB app.

Basically, Create a C# project, add the SMO references as needed, add the following lines above the class declaration to make it COM visible:

[ComVisible(true)]

[GuidAttribute("{guid here}")]

[ClassInterface(ClassInterfaceType.AutoDual)] <--- Not recommended, but well...

In the project properties, in the "Build" section, make sure the "Register For COM Interop" box is checked. Compile and import into the VB6 app and you are in business!!

As is the case with trying to hammer any two different systems together, there will be some manipulations you will need to do with what you pass between the VB6 app and the C# Wrapper... but its not too hard.

Please comment if you need any more information/details.