Microsoft sql 2005, 2008 stored procedure into temp table
Date created: 2010-02-12 (it is going to snow a 2nd time in baton rouge in less than 3 months)
AND YES THE SAINTS WON THE SUPER BOWL for 2009 FOOTBALL SEASON ...
Christopher,Matthew and I went to the SuperBowl for the NFC championship (we beat Brett Favre and the Vikings)
ANYWAYS HERE IS THE THOUGHTS ON A #TEMP TABLE OR ##TEMPTABLE and inserting a stored procedure resutls into the temp table:
I found a website (see below) which put me most of the way...BUT
****I needed to use parameters in the stored procedure.****
And OPENQUERY does not allow for this to happen:
reference:
The next thing is to define LOCALSERVER. It may look like a keyword in the example, but it is in fact only a name. This is how you do it:
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servernameTo create a linked server, you must have the permission ALTER ANY SERVER, or be a member of any of the fixed server roles sysadmin or setupadmin.
OPENQUERY opens a new connection to SQL Server. This has some implications:
- The procedure that you call with OPENQUERY cannot refer temp tables created in the current connection.
- The new connection has its own default database (defined with sp_addlinkedserver, default is master), so all object specification must include a database name.
- If you have an open transaction and are holding locks when you call OPENQUERY, the called procedure can not access what you lock. That is, if you are not careful you will block yourself.
- Connecting is not for free, so there is a performance penalty.
--
SO I found a way to work the system and also not have to make the Table definition so rigid and redefine it inside another stored procedure (and of course take the chance it may break)!!!
YES, You can dynamically create the table definition returned from the stored procedure by
using the OPENQUERY statement with bogus varaiables (as long as you *NO RESULT SET* returns the
same number of fields as a good dataset).
Once the table is created....You can use exec stored procedure into the temporary table all day long.
----------------------------------------------------------------
AND to NOTE (as indicated above) you must enable Data Access
--
EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE
----------------------------------------------------------------
CODE:
--
declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime
set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()
--build temp table (based on bogus variable values) --because we just want the table definition and
--since openquery does not allow variable definitions...I am going to use bogus vars to get the table defintion
select * into #tempCoAttendanceRpt20100211 FROM OPENQUERY(DBASESERVER, 'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')
set @locCompanyId = '7753231'
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
set @locCompanyId = '9872231'
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211
Thanks for the information which originally was provided...
YES, FINALLY I DO NOT HAVE TO CREATE ALL THESE BOGUS (Strict) table defintions when using data from
another stored procedure or database...and YES you can use parameters too.
SEARCH references:
sql 2005 stored procedure into temp table
openquery with stored procedure and variables 2005
openquery with variables
execute stored procedure into temp table
I also posted this solution on: