I was able to test this on my computer. I haven't tried doing anything with sp_oa in a very long time, which is my I wasn't aware of this. I tested with Vista 64bit and SQL2008R2 64 bit. I created a dummy dll in VB6 to create a 32 bit com component. Sure enough, I got the same error message mentioned in the knowledge base article.
The next thing I tried was creating a 32 exe that called by 32bit dll, and then used xp_cmdshell from 64bit SQL on a 64 operating system, and it worked.
I'm not sure if this approach will work for you, but it may be something to consider.
To do this:
1. I created a 32 bit dll. I created a new ActiveX dll project in VB6. The project name was Test32Bit. I create a class module called TestClass. In the class module, I have this code:
Public Function TestThis() As String
TestThis = "Giddy Up"
2. I then created an executable. I created a new EXE project in VB6. I named it Test32BitExe. I created a module with the following code:
Public Sub Main()
Dim O As Test32Bit.TestClass
Dim FSO As Scripting.FileSystemObject
Set O = New Test32Bit.TestClass
Set FSO = New Scripting.FileSystemObject
Set FSO = Nothing
Set O = Nothing
Of course, I added a reference to the 32bit dll I created earlier, and another reference to "Microsoft Scripting Runtime".
3. Finally, I moved over to SQL Server Management Studio, and executed this:
In the results window, I got this:
(2 row(s) affected)
You can capture the output of sp_cmdshell like this:
Create Table #Output(Data VarChar(max))
Insert Into #Output(Data)
Exec xp_cmdshell 'C:\Data\Development\Utilities\Test32Bit\Test32BitExe'
Select * From #Output
Obviously working with an executable is different from working with a COM component, so this method may not work for you. You mimic function parameters with command line arguments, and you can mimic multiple outputs by using string formatting or even XML.
Basically, you can work around this problem by making a 32 exe as a wrapper for your COM component.
I hope this helps.
Microsoft SQL Server MVP
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom