Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Question Unanswered: unable to run stored procedure using data from linked server via VBA

    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! :-)

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What are the security settings for the definition of this linked server? When you say "Progress", do you mean "Postgress"?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2009
    Posts
    7
    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?

  5. #5
    Join Date
    Aug 2009
    Posts
    7
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •