Dynamically create table when executing a stored procedure
-
Hi all,
How can i create a temporary table on executing a procedure.
ex:
exec sp_who
how to dinamically create temporary tabe on executing the above procedure
from
killer
-
Do you mean you want to store the resultset from the procedure in a temporary table? If so, it can't be dynamically created. You can however create it 'manually' and store the results there.
CREATE TABLE #who ( define all columns that exist in the output of sp_who )INSERT INTO #who EXEC sp_who
-
Thanx Chris,
This is not what i need i dont want to define the columns that exist in the output
Is there anycommand that we can create a table without defining the columns.
from
Killer
-
I don't say it can't be done... but it would take so much work that I think it would be simpler to create a table with dummy col names (col001, col002...). I assume here that this is for general knowledge, do you have an actual situation in mind?
-
The code snippet below is what I use to sort the results of sp_who2. This is what Chris suggested. It is specific to sp_who2.
If you need to work with the results of a stored procedure known at design time, this should work. The only maintenance required is changing the table definition if the output of the stored procedure changes.
Are you wanting a more generic solution that will store the results of some procedure that is not known at design time (maybe passed as a parameter)?
------------------------------
DECLARE @Field as varchar(15)
set @Field = 'DBName'
DROP TABLE #SPWHO
CREATE TABLE #SPWHO
(SPID int,
Status varchar(100),
Login varchar(100),
HostName varchar(100),
BlkBy varchar(100),
DBName varchar(100),
Command varchar(100),
CPUTime int,
DiskIO int,
LastBatch varchar(50),
ProgramName varchar(100),
SPID2 int)
INSERT INTO #SPWHO
EXEC master.dbo.sp_who2
SELECT * FROM #SPWHO
ORDER BY DBName
------------------------------
-
UPDATE: My previous post included some stray code from an experiment. There is no need for the lines
DECLARE @Field as varchar(15)
set @Field = 'DBName'
-
This seems like the most evil idea... but it would work :
Select * into NewTable from Openrowset (linkedserverinfo, 'Exec procedure...')
-
Hi ,
Thanx Johnson for ur effort but i mentioned before dont define the table structure before.
Hi Remi,'
But openrowset doesnt work.
from
killer
-
I'll try to make it work tomorrow.. but I know it's possible.
-
Remi,
One attribute of Openrowset requires the connection to return columns. This may be the problem when executing sp_who or sp_who2 depending on the provider used.
Raj,
The code was an example if you could predefine the table. The question was are you looking for a solution specific to sp_who or something more generic that will handle procedures identified at run time?
-
Here's something that I just tested:
I created a linked server on my machine :
exec sp_addlinkedserver @server = 'TestLinkServer',
@srvproduct = 'SQLServer OLEDB Provider',
@provider = 'SQLOLEDB',
@datasrc = 'CASSANDRA'
Then did the following:
Select *
into #NewTable
from Openquery(TestLinkServer, 'Exec sp_who')
select * from #NewTable
drop table #NewTable
worked like a charm....
**ASCII stupid question, get a stupid ANSI !!!** -
Yup, I was trying to make it work without the self-linked server but looks like there's no way around... unless we ask Noeld
-
sure we can ask noeld (not that there's anything wrong with that) - but wasn't he on the other post that had the exact same question...????
will go and check!
**ASCII stupid question, get a stupid ANSI !!!** -
Don't think so. That post is somewhat incomplete at the moment.
-
Remi,
Yes you can use Openrowset:
Select *
into #T1
from Openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVER','exec sp_who') dt
select * from #T1
drop table #T1
-- You need to have MSDTC ON and the server set to allow remote server connections but other than that it should be OK
* Noel
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply