--I had to edit for dupes...

--I make no promisses on efficiency

SELECT DISTINCT t1.nbr, t1.dt, t2.dt, DATEDIFF(d, t1.dt, t2.dt) + 1 timeSpan

FROM @table t1

INNER JOIN @table t2

ON t1.nbr = t2.nbr

AND DATEDIFF(d,t1.dt,t2.dt)...

obviously, that would return 2 records by def 5/4-5/6 and 5/5-5/7

if you want to omit the second record...

select *

from @table t1

inner join @table t2

on t1.nbr = t2.nbr and t1.dt +...

if you are adamant about sticking to the DOS prompt, you can pipe the results of an osql.exe query to a DOS script after running DTS #1.

create a connection to computer #2. Create an execute sql task object referencing the connection. EXEC xp_cmdshell '...\program.exe'

thank you for the reply..

I am using an adp. It doesn't prompt as an error. It's a special record lock message. I will try to reproduce it...

it's been a long day.. I was Reading it as D.O.S. Though that should have clued me in as well. I am assuming you are using DtsRun.exe. ...

--I haven't forgot about you...

--I'm pretty sure this is your expected result set

SELECT

P2.R_ID,

P2.Field1,

P2.Date1,

S2.Field2,

S2.Field3

FROM

(

SELECT

R_ID,

--P.Field1,

MIN(S1.startDate) minStartDate

FROM Table1 as P

LEFT JOIN Table2 as S1

ON P.Field1 = S1.Field1

AND S1.StartDate >= P.Date1

GROUP BY

R_ID

--,P.Field1

) P1

INNER JOIN Table1...

I am unsure what DOS prompt refers to...

There are connection properties on the Execute DTS Package Task. If you must run via a sp, then add another connection object.

just call the second dts from the first if that condition occurs.

IF (SELECT ISNULL(COUNT(*),0) FROM Table [WHERE ...]) = 0

BEGIN

RAISERROR()

END

Use the "on failure" work-flow to call the DTS

a few different ways to do this...

first would be creating an sp to execute the DTS. IF COUNT(*) > 0 DTS ELSE RAISEERROR

another way (inside the dts) is add...

"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /F "[.dts]"

you can pass variables like so...

/A[var name]="[value]"

did you set a primary key or index for your tables? how many records in each?

you may think about using the EXISTS statement.

p

inner join s1

on ...

where exists

(select min() from...

play with the text file (destination) object in dts or if you don't want to do that...

select 'columnname' columnname

union

select columnname

from MyTable

Select P.R_ID

, P.Field1

, P.Date1

, Min(S1.StartDate) As EarliestStartDate

, S2.Field2

, S2.Field3

from Table1 as P

Left join Table2 as S1

On P.Field1 = S1.Field1...

>= vs > ...

the only difference was the pattern 100100 and 101010 respectively.

not sure why you couldn't get it started. I cut and pasted the query.

thank you for the...