Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

wbodger (Programmer) (OP)
4 Oct 11 18:18
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 (Programmer)
4 Oct 11 18:41
The sp_OA* procedures are disabled by default.  Have you tried enabling them?

CODE

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 (Programmer) (OP)
4 Oct 11 18:44
I did enable them

[code]
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:
http://support.microsoft.com/kb/932872

wb
 
gmmastros (Programmer)
4 Oct 11 19:33
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:

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:

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:

CODE

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

In the results window, I got this:


output
--------------------
Giddy Up
NULL

(2 row(s) affected)


You can capture the output of sp_cmdshell like this:

CODE

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 (Programmer) (OP)
4 Oct 11 19:42
Hmm... Thank you very much!
wbodger (Programmer) (OP)
4 Oct 11 20:16
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 (Programmer)
4 Oct 11 21:26
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close