Hello guys,

As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.

DECLARE @duration INT=20;

WITH cte

AS (SELECT TOP 1...

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format

Is it on the same machine where the SQL instance installed against which you are running the query ?

I think the problem must be with the access to network path

Database=\\192.168.1.20\upload\data.xls

No it returns

(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)

You can, but I wouldn't recommend it:

declare @str as table( val varchar(20))

insert @str select '12-FLA-2-02'

insert @str select '12-FLA-2-12'

declare @update varchar(20)='11111'

select * from @str

update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select *...

You can use REPLACE function as well.

declare @str as table( val varchar(20))

insert @str

select '12-FLA-2-02'

declare @update varchar(20)='11111'

select * from @str

update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select * from @str

The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.

See if this helps.

DECLARE @duration INT=70

SELECT TOP 1 time_sched_start_dt,

...

In what format are the dates stored in the table ? Can you please post some sample data ?

Try setting up as "Outprocess"

To connect to Microsoft Access, SQL Server must start the Microsoft Access Database Engine. Unlike most other providers, Microsoft Access is not a light-weight provider, but...

Is the output for RunningTotal right based on the sample data you have provided ? If not then what it should be ?

What is the value of physical memory allocated to sql instance set to ?I think not enough memory(VAS) is available for the linked server.

How big is the Excel data ?

Is AWE enabled for your server ?

Is your SQL Server also a 32 bit ?If yes then it looks like the linked server has no memory available to pull the data.

How much memory does your server...

Not exactly a tally table as mister.magoo mentioned but somewhat equivalent.

create table #t(person varchar(100),id1 int,id2 int,id3 int)

insert #t

select 'Person1', 10, 15, 5 union

select 'Person2', 14,14,0 union

select 'Person3', 20,21,1

select person,id1,id2,isnull(T.number,0)+id1 id3

from...