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

Improve this question
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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 at 21:55

1 Answer 1

Reset to default
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'

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .