Getting results of stored proc into temporary table

I have a need to get the results of a stored procedure into a temporary
table. The results of the query will be larger than 8000 characters so I
can't use a variable. Following is what is being done in the stored
procedure (findorphans):
==========
PRINT 't1'
SELECT mco_itn,chg_dt,chg_in FROM t1 LEFT JOIN t2 ON t2.mco_itn=t1.mco_itn
WHERE t2.mco_itn IS NULL
PRINT 't3'
SELECT mco_itn,chg_dt,chg_in FROM t3 LEFT JOIN t4 ON t4.mco_itn=t3.mco_itn
WHERE t4.mco_itn IS NULL
==========
I tried the following:
==========
CREATE TABLE ##msgtext (msgtext text)
INSERT INTO ##msgtext exec test..mxw_findorphans
...
DROP TABLE ##msgtext
==========
I end up getting an error as follows:
==========
t1
Server: Msg 213, Level 16, State 7, Procedure findorphans, Line 2
Insert Error: Column name or number of supplied values does not match table
definition
==========
Ultimately I am going to send the results in an email. I had it working
fine but the messages were getting cutoff due to the 8000 character limit.
In the documentation for xp_sendmail they had an example of sending a
message > 8000 which is where I am going with this. I think the problem may
be that line 2 returns 0 rows and for some reason is causing the problem.
Can anyone point me at what I am doing wrong?
TIA
MarkMark,
your temp table need to mirror exactly the columns the stored procedures
will return, so looking at it, your temp table should contain columns
mco_itn,chg_dt,chg_in
I'm also not sure if you can insert multiple resultsets into a temp table.
so you may have to make the queries in the stored procedure into a UNION.
Have you considered a user-defined function which can return a TABLE ? Look
them up in BOL.
Hope that helps.
Damien
"Mark Butler" wrote:

> I have a need to get the results of a stored procedure into a temporary
> table. The results of the query will be larger than 8000 characters so I
> can't use a variable. Following is what is being done in the stored
> procedure (findorphans):
> ==========
> PRINT 't1'
> SELECT mco_itn,chg_dt,chg_in FROM t1 LEFT JOIN t2 ON t2.mco_itn=t1.mco_itn
> WHERE t2.mco_itn IS NULL
> PRINT 't3'
> SELECT mco_itn,chg_dt,chg_in FROM t3 LEFT JOIN t4 ON t4.mco_itn=t3.mco_itn
> WHERE t4.mco_itn IS NULL
> ==========
> I tried the following:
> ==========
> CREATE TABLE ##msgtext (msgtext text)
> INSERT INTO ##msgtext exec test..mxw_findorphans
> ....
> DROP TABLE ##msgtext
> ==========
> I end up getting an error as follows:
> ==========
> t1
> Server: Msg 213, Level 16, State 7, Procedure findorphans, Line 2
> Insert Error: Column name or number of supplied values does not match tabl
e
> definition
> ==========
> Ultimately I am going to send the results in an email. I had it working
> fine but the messages were getting cutoff due to the 8000 character limit.
> In the documentation for xp_sendmail they had an example of sending a
> message > 8000 which is where I am going with this. I think the problem m
ay
> be that line 2 returns 0 rows and for some reason is causing the problem.
> Can anyone point me at what I am doing wrong?
> TIA
> Mark
>
>|||Looks like you can have multiple resultsets from a procedure:
CREATE PROC usp_test
AS
SELECT 1
SELECT 2
GO
DROP TABLE #test
CREATE TABLE #test ( value1 INT )
GO
INSERT INTO #test
EXEC usp_test
SELECT *
FROM #test
DROP PROC usp_test
GO
"Damien" wrote:
> Mark,
> your temp table need to mirror exactly the columns the stored procedures
> will return, so looking at it, your temp table should contain columns
> mco_itn,chg_dt,chg_in
> I'm also not sure if you can insert multiple resultsets into a temp table.
> so you may have to make the queries in the stored procedure into a UNION.
> Have you considered a user-defined function which can return a TABLE ? Lo
ok
> them up in BOL.
> Hope that helps.
>
> Damien
> "Mark Butler" wrote:
>|||When I send the output from the stored procedure directly into xp_sendmail
it is a text based message formated like the following:
=========
t1
mco_itn chg_dt chg_in
-- -- --
t3
mco_itn chg_dt chg_in
-- -- --
=========
I was expecting that to be true the way I was doing it but as you pointed
out that must be false.
Is there another or better way?
Do I need to CAST and concatinate the fields so they are one?
Is there a way to send the results to a text file that I can then attatch to
the email?
Thanx for any suggestions.
Mark
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:7F9C8D7E-06CF-4272-A690-34C86EC517D3@.microsoft.com...
> Mark,
> your temp table need to mirror exactly the columns the stored procedures
> will return, so looking at it, your temp table should contain columns
> mco_itn,chg_dt,chg_in
> I'm also not sure if you can insert multiple resultsets into a temp table.
> so you may have to make the queries in the stored procedure into a UNION.
> Have you considered a user-defined function which can return a TABLE ?
> Look
> them up in BOL.
> Hope that helps.
>
> Damien
> "Mark Butler" wrote:
>