2

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

7
  • 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. datapigtechnologies.com/blog/index.php/… – Frank Thomas Oct 24 '13 at 16:10
  • @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 Oct 24 '13 at 16:45
  • 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 Oct 24 '13 at 16:49
  • 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 Oct 24 '13 at 20:34
  • 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 Oct 24 '13 at 21:55
0

Try adding single quotes around the new field name, interval_30.

(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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.