excel sql server error: Invalid object name

I access a stored procedure on a Microsoft SQL Server 2005 database within excel using

Select * 
from  [dbo].[fn_ph_by15minbyQueue] ('2013-09-01','2013-09-30','LocationCode')

in the data connection, which works fine.
I needed an additional column, so I altered the statement to

select *, 
(case right([Interval],2)
    when '15' then left([interval],len([Interval])-2)+'00'
    when '45' then left([interval],len([Interval])-2)+'30'
    else [interval]
    end) as interval_30
from [dbo].[fn_ph_by15minbyQueue] ('2013-09-01','2013-09-30','LocationCode')

which works correctly within SQL Server, but when I enter that code into the Command Text, Excel reports:

Invalid object name 'dbo.fn_ph_by15minbyQueue'

What am I doing wrong?

Connection info:
enter image description here
Connection String:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=BusinessUsers;Data Source=qadbs4784;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False

SeanC

Posted 2013-10-24T16:00:38.363

Reputation: 3 439

do you really need to rename the column at all? if not, just use select * as you had been. also when you edited your function, did you set 'no count on'? if so, remove it. http://datapigtechnologies.com/blog/index.php/running-advanced-sql-stored-procedures-from-excel/

– Frank Thomas – 2013-10-24T16:10:14.693

@FrankThomas, can I set no count from an excel data connection? The naming of the column is only for readability, but removing it has no effect on the response from excel – SeanC – 2013-10-24T16:45:10.077

no, its part of the function/procedure script. since you added a column to the output, you would have had to edit the function. – Frank Thomas – 2013-10-24T16:49:45.557

the function works correctly in SQL Server, but if I put it into an excel data connection, I get the error. The manipulation is actually taking a time field, and rounding down to the nearest half hour – SeanC – 2013-10-24T20:34:22.390

yes, I know. having 'no count on' has no negative impact on the server end, but it prevents excel from processing the result. also, we may be getting hung up on terminology, but a connection and a command are two different things, and you cannot put a command in a connection. a connection says 'connect to server x using database y, here are my credentials' whereas a command says 'give me this data and use this connection to do it'. could you please post the function, and describe how you specify it in excel. pictures are worth a thousand words. – Frank Thomas – 2013-10-24T21:55:25.603

see if this provides any insight: http://stackoverflow.com/questions/14868798/vba-ado-invalid-object-name-error-no-error-sql-server-management-studio

– Frank Thomas – 2013-10-24T22:28:52.573

@FrankThomas, added code. I do not have access to view or edit the function itself, but I added the code and connection information – SeanC – 2013-10-28T12:30:30.167

excel sql server error: Invalid object name

Answers