• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stored Procedure call issue

T

tkriegel

New Member
I have a very simple data connection that calls a stored procedure from a database where we store custom tables and SP's. For example purposes, we'll call this custom_db. This stored procedure pulls data from our main erp database, we'll call that main.db. I set up the connection in my usual manner and my sql command is {call sp_daily_production}. The data pulls as it should. However, as soon as I try to refresh the data, Excel generates an error 'invalid object name'. When I go to verify the connection string, it's gone and greyed out. I've tried every which way of creating the connection - they all work on the first fetch, but the connection string isn't saved. I did find something on the never helpful MS site, that hints that the issue is one database SP is calling data from a different database. It says recordsets are inadvertently overwriting portions of the metadata (whatever all that means). This is an old issue though, and the bug within Excel had a fix a few years ago. I'm in Office 2016 Pro, so this shouldn't be a problem. I have many dynamic workbooks with various connections, and have never had an issue. Any thoughts are greatly appreciated!
 
I should also note that I have tried using the full database path name, {call custom_db.dbo.sp_daily_production} - same result, data pulls and won't refresh
 
tkriegel
Based Your: Any thoughts are greatly appreciated!
Reread from

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
especially How to get the Best Results at Chandoo.org
After that, You'll remember - what would You need to do next.
 
vletm - I'm not sure what you mean. I cannot upload a file, trying to troubleshoot a connection issue when there's no access to the databases would be pointless. I feel that I have described my problem in detail. When calling a stored procedure that pulls data from a second database, the connection string is not saved. Simply looking for anyone else that may have had this issue and how they combated it.
 
tkriegel
With a sample file,
it would be smoother to figure exact
what do You mean?
what would You like to get?
as well as
someway test Your challenge.
 
I simply don't understand how a sample file will benefit you in any way. I'm connecting to a local database, which pulls data from another local database. If I send you the file, not only would I be disclosing private data, you would simply see a file with the connection sting data missing and greyed out. I thought this group of experienced users may have heard of this issue in the past.
 
Never mind - we figured it out. The person that wrote the stored procedure did not use the full path in her select statement, only in the where clause. I put the full path in for every field and Excel is now successfully refreshing on open.
 
You must log in or register to reply here.
Back
Top