Setup:
1. I have a SQL Server 2008 db (called VantageTrans) with a linked server (a Progress db called VANTAGE - the back end of our financial system).
2. I have a stored procedure (spUpdateTblForFile) on VantageTrans (the SQL Server db) that pulls data out of the tables in VANTAGE (the linked server) and dumps the manipulated data into tables in VantageTrans.

Issue:
The stored procedure runs fine on when run directly on the server db (VantageTrans) but will not run when called from VBA. I have tried calling it from Access (.accdb and .adp) and from Excel using the ADODB.Connection / ADODB.Command functions but it just hangs. Initially it produced a timeout error but I set the connection string and command timeouts to 0 and that removed the error.

I can run stored procedures successfully from VBA that only use the VantageTrans data tables so I am confident that the VBA code is correct.

PLEASE can anyone suggest what the issue with the linked server might be? I wondered if it might be a permissions thing when trying to connect to the linked server but I don't know how I can check his out.

All help would be gratefully appreciated! :-)

What are the security settings for the definition of this linked server? When you say "Progress", do you mean "Postgress"?

My first guess would be that the Progress server is using Windows Authentication and that there is a Windows security issues on the SQL Server that is preventing it from authenticating to the Progress server. You could test this by running the VBA script on the SQL Server (use CSCRIPT.EXE if you must), if I'm correct then the VBA script will run on the machine running your SQL Server.

If the VBA script runs on the machine running your SQL Server, come back and we can work through your options.

-PatP

rdjabarov: No, I mean a Progress OpenEdge database.

Pat Phelan: I have run the VBA as script on the database and it runs perfectly but it will still not run from Excel (even running it from the same physical machine as the SQL Server).

I have put the username & password directly into the connection string so I know the code is connecting as the correct user. This user AD account has permissions on the SQL Server and I have set up the linked server with that account mapped to a valid account on the linked server (under "Local server login to remote server login mappings" on the Linked Server properties.

Thanks for your help with this. What can I try next?

Update: I have successfully run the VBScript from command line on the server that the SQL Server is on but it fails when I run it from the command line on a remote machine.

Based on your latest post, the problem appears to be with proxying authentication.

Ask the Windows Adminsitrator for the machine running SQL Server to review the Service Principal Name configuration on that machine for the Windows Service Account that SQL Server uses to authenticate to Windows. It should help the admin to know that your VBA script works using local authentication, but fails using proxied authentication.

-PatP