ADO Crash on successive calls to SQL Server

  • I have a strange issue occurring on a project. I'm using the same base code I've used for multiple projects without issue. Uses ADODB connections to run sql strings against the database to return a dataset. I then work with the data in Excel and then call another string to pull a different set.

    I started a new project about 2 weeks ago. Got everything up and running late last week and it was all fine up until last night. Now every time I try and make multiple Open calls in a row using automation, Excel crashes completely. The same code works fine on a different server. I can also do a single call without issue.

    Since it is a hard crash, I am really struggling to find out what is causing the issue. My guess is that it is on the server side, but as far as I know, nothing has changed. I checked the logs in SQL server and the Event viewer and didn't see anything that stuck out.

    Really look for help troubleshooting this. I am at a complete standstill and getting a lot of heat for the delay.

    Any suggestions on where to look or what to try?

  • Re: ADO Crash on successive calls to SQL Server

    I had a similar issue once where the code altered the users environment inputs (PROD/ACCT/SYST/INTE/UNIT) into the appropriate server name (DSA/DSAA/DSAS/DSAI/DSAU).

    The environment was entered byref to the code that altered it to the appropriate server name, so when it made the second call it went through the same code but never had to override say SYST to DSAS as it was already set to DSAS from the previous SQL call.

    I've no idea why it was failing, or why this fixed it, but all I did was pass in a temporary copy of the environment string (i.e SYST), which was overridden to the server name, but wouldn't overwrite the original environment string, so when the second call was made it passed SYST from the input variable into the temp variable which was passed to the code to run the SQL.

    EDIT: I should add that in theory changing the passing of the parameter to ByVal should have achieved the same result, but I hadn't written the SQL part of the VBA so didn't have access to alter that code directly, it was also an add-in referenced by a number of sheets so doing it this way avoided the regression testing impact that would have been inherent with changing the SQL VBA Code.

    And it worked.

    As I say no idea why, but if it sounds similar give it a bash.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!