A 64 bit SQL Server can only call 64 bit DLLs.
Here is a quick enumeration of some possible solutions, each with their own warts/flaws, in no particular order:
- You could try this, but I can't vouch for it. It seems like a hack, and I wouldn't try it unless I was desperate.
- Talk to the vendor and see if there is a 64 bit version of the DLL. (Just in case: You (probably) need a 64 bit x86 version. A 64 bit Itanium version will not do.)
- Recompile the DLL as a 64 bit DLL.
- Rewrite the DLL using straight Transact SQL. (I've done this, as part of a retrofit/modernization project, but only with some extremely simplistic DLLs that probably didn't need to be extended procedures in the first place.)
- Rewrite the DLL using .Net and the SQLCLR. (This is the best thing to do, long-term. IMO.)
- Find a 32 bit server, install the DLL there and call it from the 64 bit server via a Linked Server.
- Find a way to use the DLL via XP_COMMANDSHELL or a job step, or run it through some other mechanism.
This is a common problem with legacy stuff. For example, (until recently) there were only 32 bit versions of the Fox Pro ODBC drivers, which are just DLLs.
