SQL Server 2005 32-bit vs 64-bit

[wbodger]

I have a COM object (a wrapper for SN/Key creation) that works in win2k3 32-bit with 32-bit sql 2k5, but I cannot get it to work in win2k3 64-bit with sql2k5 64-bit. It also works in win2k3 64-bit with sql2k 32-bit. Did MS make some kind of change in the 64-bit version of SQL 2k5 so that one MUST use CLR rather than calling sp_OACreate or anything else along those lines?

Thanks,
wb

 

[gmmastros]

The sp_OA* procedures are disabled by default. Have you tried enabling them?

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures',1;
RECONFIGURE;

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I did enable them

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
[\code]

unless I missed something. There was also this kb article:
[URL unfurl="true"]http://support.microsoft.com/kb/932872[/URL]

wb

 

[gmmastros]

wb,

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:

Option Explicit

Public Function TestThis() As String
    TestThis = "Giddy Up"
End Function

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:

Option Explicit

Public Sub Main()
    
    Dim O As Test32Bit.TestClass
    Dim FSO As Scripting.FileSystemObject
    
    Set O = New Test32Bit.TestClass
        
    Set FSO = New Scripting.FileSystemObject
    FSO.GetStandardStream(StdOut).WriteLine O.TestThis
    Set FSO = Nothing
    
    Set O = Nothing

End Sub

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:

xp_cmdshell 'C:\Data\Development\Utilities\Test32Bit\Test32BitExe'

In the results window, I got this:

[tt][blue]
output
--------------------
Giddy Up
NULL

(2 row(s) affected)
[/blue][/tt]

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.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Hmm... Thank you very much!

 

[wbodger]

A question. If the dll that I am wrapping is a 32-bit dll, can I wrap that in a 64-bit com object?

 

[gmmastros]

I don't know. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom