Do you have the ability to insert the text block into a table/column?

Books online describes how to incrementally pull a large text block into a variable for working.  Do a...

Can you post the text of the stored proc?  Or at least the signature?

Let me answer the questions as presented.

> are there any alternatives that would be worth thinking about?

Yes, but without a more specific problem domain, theory is about all we can offer...

create table TblRoutes (

 GID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,

   Node1 NUMERIC           NOT NULL,

   Node2 NUMERIC               NULL)

insert into TblRoutes (Node1, Node2) VALUES(...)

SELECT * FROM TblRoutes WHERE Node1 =...

An additional question is whether the transportation system is constrained to a hierarchical structure, a network structure, or a directed graph?  If a directed graph, does the graph cycle?  Each...

How big is "a very large xml packet" in your world?  Is it larger than the parameter supports?  For example, are you trying to pass more than 8000 characters using...

There are several ways to accomplish what you are trying to do.  Triggers are one option.  Another option is to set up the tables as described in BOL.  Do a...

Can you procide a little more information please?  It would be helpful if you could provide the actual query, as well as sample table structures.

Given the little information in your...

Here is a query that will return the list of tables and the Sp that reference them:

SELECT DISTINCT TABLE_NAME

      , object_name(id)

  from syscomments,

       INFORMATION_SCHEMA.TABLES

 where text like '%' + TABLE_NAME...

Hello,

This is where I admit to being less than perfect. 

I routinely encounter this situation when I explicitly open transactions with a 'BEGIN TRANSACTION'...

Here is an alternate way to achieve the goal.  I am sure it can be reduced to a smaller chunk of code, but this will get you started.

DECLARE @tmpTable TABLE...

To be honest, I'm not sure how a single stored proc could technically trap all errors, as the call to the stored proc would mask some errors.

I generally implement a...

Peter: Your method works perfectly as long as I am logged in as SA.  When I embed the process in the actual stored proc running as a non-priveleged account I...

All excellent suggestions.  Unfortunately, they are all interactive.  The procedure is intended to function ina lights out environment.  I am seeking a solution that I can embed in the stored...

Thanks Phill,

You have eliminated one of the variables for me. i.e. the size of the file. I'll focus my attention on the data tomorrow morning. Thanks for...