The good news is that I've got an answer. The bad news is that the answer is that it doesn't work. As I suspected, and as per Microsoft, the error...

Update after some testing:

It works fine when I call the sp from a different db on the same server. The problem is when the db where the sp lives is...

I'm the original poster and I can't even figure out why the query *should* work, let alone whether it does or not. I'm pretty certain Jack didn't think it was...

Sorry, I meant the CATCH never gets triggered.

@@ERROR does come back as 50000, but none of the other error info is available. I can trap for @@ERROR right after the...

Hmmm. not certain what's wrong with the test data. I can run your query against it and get the expected result.

Be that as it may, however, your code highlights the...

I"m looking at that right now. It looks like it might work in this case. I'd like to be able to do it in a query, too, since I have...

Ah ha, OUTER APPLY....

I've never use that. I barely know it exists. Can you give me any good links to help figuring out how it works? I can sort of...

Does this give you what you need?

--------------------------

--DDL

--------------------------

CREATE SCHEMA [SAMPLE]

GO

IPCode is PK

CREATE TABLE [sample].127.0.0.1(

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--IPCode is FK

CREATE TABLE [sample].[AddressProfile](

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--IPCode is FK

CREATE...

Just one other change to keep db_sendmail happy:

ALTER PROCEDURE dbo.SendWXData

AS

BEGIN

SET NOCOUNT ON;

DECLARE @xml XML;

--explicitly declare the var to be passed to db_sendmail

DECLARE @Output NVARCHAR(MAX)

SELECT @xml = (SELECT * FROM...

I'm happy to do it via the backup and restore method, and that seems to be the approach I see discusse most often. Thanks!