Scott, I was beginning to approach this from a different angle.  When creating the linked server, I'll do:

sp_addlinkedserver

   @server = 'stored-procedure-server-name'

  ,@srvproduct=N''

  ,@datasrc = [real-server-name]

  ,@provider='SQLOLEDB'

 ...

Scott, that looks really promising.

Is there a programatic interface to that functionality?

I was going to start one, but I wanted to respond to this first so you didn't think I was leaving you haning.  Maybe we'll have better luck on the...

Thanks, Aaron.  Those ideas may well prove useful at some point in this project.  However, my question is really:

Is there a way to create a stored procedure that references a...

I'm old.

Thanks, Aaron.

That would work.  I was thinking of something WITHOUT dynamic SQL (should have mentioned that in the original post), but if no options present themselves, I'll try that.

Try:

Not Exists (SELECT * From tblCapRec Where t1.CaseNumber = tblCapRec.CaseNumber)

NOT EXISTS evaluates to a boolean (true/false).  WHERE NOT EXISTS (Select * FROM tblCapRec)

will still only be true if that table is empty.  So, no, it won't work.

<code snipped>...INNER JOIN tblAccurint_Above1500 as t2 ON t1.CaseNumber = t2.acctno WHERE NOT EXISTS ( Select * FROM tblCapRec WHERE ...)

I don't know what your criteria are for inclusion/exclusion, so I can't...

"IF NOT EXISTS(SELECT * from tblCapRec)" is only true if tblCapRec is empty.  If your intent is to add rows

to tblCapRec that aren't already there, you'll have to add a...

Jon,

It should still work (just tried it).  Don't know what to tell you.

In your first post, your error message has a '(' after the Between and before the Cast.  Was...

There should be no problem using CAST with BETWEEN.  I just tried it successfully in QA.

However, your query looks a little odd:

WHERE #form.pStreetNumber# BETWEEN Cast(AddrPrimaryLowNo As Integer) AND Cast(AddrPrimaryHighNo As Integer)...

What is the name of the utility?

Thanks.

Regarding your requirement to route data to specific LFSs, you should be able to do this with merge replication's dynamic filtering.  Take a look at the Books Online 'dynamic filtering'...

Do you know what the performance differences are between invoking a DTS package from T-SQL, and using SP_EXECUTESQL BULK INSERT...?

Also, are DTS packages re-entrant, since there will be multiple threads...