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.

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


xtype values in sysobjects

This is a list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:

• C - CHECK constraint
• D - Default or DEFAULT constraint
• F - FOREIGN KEY constraint
• L - Log
• P - Stored procedure
• PK - PRIMARY KEY constraint
• RF - Replication filter stored procedure
• S - System table
• TR - Trigger
• U - User table
• UQ - UNIQUE constraint
• V - View
• X - Extended stored procedure

Paste method of Worksheet class failed

I ran across this automation exception message sporadically in a project I'm working on where I'm trying to place a large string on the Clipboard and paste it into an Excel 2002 spreadsheet (object) using Interop in VB.NET 2005. It probably happened one time out of every five attempts to paste.

Previously, my code read:

'objWorksheet is an Excel.Worksheet automation object
Dim strClipboard As New System.Text.StringBuilder("<some humongous string>")
objWorksheet.Range("A1").Select()
System.Windows.Forms.Clipboard.SetDataObject(strClipboard.ToString())
objWorksheet.Paste()
objWorksheet.Range("A1").Select()

The exception occurred at the objWorksheet.Paste() line.

I found a Microsoft forum post here that suggested checking the Application.Ready flag before pasting, since Excel is a funny animal and needs to be in "edit mode" before it will accept a Paste command via automation. (Side note: Application.Ready only exists in Excel 2002 edition and later.)

My code now reads:

'objWorksheet is an Excel.Worksheet automation object
Dim strClipboard As New System.Text.StringBuilder("<some humongous string>")
objWorksheet.Range("A1").Select()
System.Windows.Forms.Clipboard.SetDataObject(strClipboard.ToString())
'The following is a workaround to fix a problem where Paste() is called before Excel is ready for editing
While Not objExcel.Ready
    System.Threading.Thread.Sleep(10)
End While
objWorksheet.Paste()
objWorksheet.Range("A1").Select()

No more problems!

SQL Server: DBCC CHECKIDENT

This neat little SQL Server statement allows you to view/edit properties of the identity value of a table.

Syntax: DBCC CHECKIDENT ('table_name' [ , { NORESEED { RESEED [ , new_reseed_value ] } } ])[ WITH NO_INFOMSGS ]

For example, if I want to reset the identity value of the table FOOBAR to 55, I run the following SQL statement:

DBCC CHECKIDENT('FOOBAR', RESEED, 55).

If FOOBAR already has rows, the next row's identity field will take on the value 56. If FOOBAR is an empty table, its first row's identity field will take on the value 55.

Neat-o.

Purpose Statement

The whole point of this blog is so I don't have to attempt to remember programming tips and tricks I find. Hopefully I can help other developers at the same time. This is merely intended to be extra brain space.

Yippee.

So, I'm a blogger. How sad.