Thanks Bob. I had seen that before, different error message and plus I don't have access to the SQL 2000 machine, so I didn't fully go down that path. However i did find a solution for those interested:
This is what I did:
1. Create ODBC connection to SOMESERVER.COM, call it dsnSOME
2. Create a Linked Server to dsnSOM:
- Linked server: "SOMESERVER" (I don't think it matters)
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Product Name: dsnSOME
- Data source: dsnSOME
And that's it. Now when I call the stored procedure I call:
[SOMESERVER].database.owner.storedprocedure
And it works!
It seems that for whatever reason, when trying to run a stored procedure on SQL 64 bit to a SQL 32 bit linked server directly, it gives this weird message about Encryption. However going through the ODBC and it works great.