Re: inserting SP output into table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 04/28/04

Next message: joe: "Re: how does trigger works?"
Previous message: Offeral: "Re: Suppressing External Output"
In reply to: CG: "inserting SP output into table"
Next in thread: Don Peterson: "Re: inserting SP output into table"
Messages sorted by: [ date ] [ thread ]

Date: Wed, 28 Apr 2004 14:17:25 -0500

If you have already have the table, you can do:

INSERT tbl EXEC usp ;

If you do not have the table, you can use a passthrough query with the local
server as a loopback. Note that this is not a recommended approach & can
dampen the efficiency to a great extent.

EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;

If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:

SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;

-- 
Anith

Next message: joe: "Re: how does trigger works?"
Previous message: Offeral: "Re: Suppressing External Output"
In reply to: CG: "inserting SP output into table"
Next in thread: Don Peterson: "Re: inserting SP output into table"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: SQL Server stored procedure issue calling another stored procedure
... If you have #temp tables used in your stored procedure, ... SELECT * INTO tbl ... 'SET FMTONLY OFF; EXEC usp'); ... a temp table. ...
(microsoft.public.sqlserver.programming)
Re: ??
... EXEC sp_serveroption 'Server', 'Data Access', TRUE ... SELECT * INTO tbl ... 'SET FMTONLY OFF; EXEC usp'); ... > I created a store procedure in a database that is located ...
(microsoft.public.sqlserver.server)
Re: Insert/into from Stored Procedure
... If you have #temp tables used in your stored procedure, ... SELECT * INTO tbl ... 'SET FMTONLY OFF; EXEC usp'); ... I want to put the Select into a stored procedure that I can call from my ...
(microsoft.public.sqlserver.programming)
Re: Stored Procedure as subquery
... EXEC sp_serveroption 'Server', 'Data Access', TRUE ... If you have #temp tables used in your stored procedure, ... 'SET FMTONLY OFF; EXEC usp'); ...
(microsoft.public.sqlserver.programming)