Monday, December 11, 2006

sp_msforeachdb

To iterate a command over all databases on a server, the undocumented sp_msforeachdb stored procedure fits the bill nicely. For example, I was given the task to set all databases on a particular server to a bulk-logged recovery model; this was easily accomplished with a single command:

master..sp_msforeachdb 'exec sp_dboption ''?'', ''select into/bulkcopy'', ''on'''

Note that this procedure executes dynamic SQL, replacing the ? with the name of the database as it iterates.

Monday, December 4, 2006

ADODB.Recordset to ADO.NET DataTable

It turns out that an OleDbDataAdapter can be used to fill an ADO.NET DataTable object from an ADODB.Recordset object:

Imports System.Data.OleDb

...

Public Function RecordSetToDataTable(ByVal objRS As ADODB.RecordSet) As DataTable
    Dim objDA As New OleDbDataAdapter()
    Dim objDT As New DataTable()
    objDA.Fill(objDT, objRS)
    Return objDT
End Function