SSIS and DBMail could definitely do this. Not so sure about how to do it in SQL 2000. Been too long since I used that, and I never...

The first thing I noticed was join statements that use UDFs. Is that actually necessary?

Since I can't tell what the UDFs do, I can't tell if they are needed...

Create a linked server. SQL 2005 doesn't like linking to SQL Server with OpenRowset.

Your compile time is killing it! Wow!

Definitely try the sub-procs route on this one. I use that all the time and it is pretty efficient.

This is what I mean by a recursive solution:

create table #T (

ID int identity,

Col1 int,

Col2 int)

insert into #T (Col1, Col2)

select 1, 1 union all

select 2, 2 union all

select 3, 3...

At the very least, post the results from each (2000 and 2005) and let us know what's wrong with the ones from 2005.

The only thing I can think of is set up an intermediate server with 32-bit. Maybe an instance on the same server. That might work, but it seems...

I guess I don't see the problem. Assign an integer value to each level and go from there.

An ID field will probably suffice for that.

Another thing to keep in mind, in a system like this, is that Texarkana (for example) is one city in two states. They have a street called, appropriately, "State...

I guess I'm not clear on what you're asking for.

You can't change the behavior of Try...Catch. Any raiserror with a severity between 11 and 19 will cause the code...

You can use a recursive Common Table Expression to select the hierarchy. Books Online/MSDN has a great example of how to do that. Is that what you're asking...

You could do something like this:

declare @Columns varchar(max)

select @columns = coalesce(@columns + ',' + name, name)

from sys.columns

where object_id = object_id('orders')

and name != 'orderid'

select @columns

If you want indented formatting, with one...

Am I missing it, or did you not post the trigger code? That's what I'd start with.

Books Online and MSDN both have exact directions for partitioning a table.

Your drop command looks incorrect to me, but otherwise it looks like the temp table should work.

What about it isn't working? Are you getting an error message, or just...