How to get results from an SP into a table.
-
It worked with no parameters, but since sp_spaceused has multiple active result sets, only the first one was inserted into TempSomeTable.
SELECT *INTO TEMPSomeTable
FROM OPENROWSET ('SQLOLEDB','Server=server_name,9999;TRUSTED_CONNECTION=YES; Initial Catalog=database1', 'exec cattsp.dbo.sp_spaceused') x
When trying to pass a parameter to to the procedure, I cannot get it to work. I tried using 'table_name' and [table_name] and neither syntax worked.
-
Ed Wagner (6/11/2013)
When trying to pass a parameter to to the procedure, I cannot get it to work. I tried using 'table_name' and [table_name] and neither syntax worked.Ok I did build a solution.
First: IT IS NOT ELEGANT.
Second: Still have to build a general solution.
Third: Am in a meeting in 3 minutes. Hope to publish a more comprehensive (full) solution later on.
Fourth: tried this on 2008R2
The solution:
Build a procedure with no parameters, which executes the actual query.
(Including the parameters). (some extras needed).
(this procedure can be dynamically generated, so parameters can be resolved during this generation, have not tried this yet).
Use the OPENROWSET to query that procedure.
This did work with the sp_space_used 'table_name1'
Have to work this out further.
All thanks for your inputs, maybe this will be solved in SQL-server in the future.
ben
-
When you get your full solution done, please post it if you don't mind. I, for one, am interesting in seeing how you did it.
-
I got something similar to work, LOL at the quotes needed, plus it doesn't seem like "Initial Catalog" is any help, but I put it in there anyways:
SELECT * INTO
#TMP1
FROM
OPENROWSET ('SQLOLEDB','Server=MYSERVER\MYINSTANCE;TRUSTED_CONNECTION=YES;Initial Catalog=mydatabase',
'exec (''SET FMTONLY OFF;USE mydatabase;EXEC sp_spaceused ''''mytable'''''')') x
-
ben.brugman (6/10/2013)
The output from a stored procedure is often a resultset which is or is similar to a table.Output from a stored procedure MIGHT be a resultset. What do you do with procedures that don't return one resultset?
Example:
CREATE PROCEDURE FunnyOutputAS
SELECT name FROM sys.databases;
SELECT name, schema_id FROM sys.tables;
SELECT OBJECT_NAME(object_id), name, type_desc FROM sys.indexes;
GO
EXEC FunnyOutput
INTO ???
-
Would be interested to see too. I'm looking at a CLR solution. I've never developed CLR before, but would be a good way to learn.
-
Right - using this approach, if the procedure has multiple active result sets (I think this is called MARS) only the first one is inserted into the output table. That's one of the hidden challenges in working through this problem.
-
patrickmcginnis59 10839 (6/11/2013)
I got something similar to work, LOL at the quotes needed, plus it doesn't seem like "Initial Catalog" is any help, but I put it in there anyways:SELECT * INTO
#TMP1
FROM
OPENROWSET ('SQLOLEDB','Server=MYSERVER\MYINSTANCE;TRUSTED_CONNECTION=YES;Initial Catalog=mydatabase',
'exec (''SET FMTONLY OFF;USE mydatabase;EXEC sp_spaceused ''''mytable'''''')') x
Excellent.
Indeed I also had the impression that the initial catalog did/does not work.
I played around with the FMTONLY and with USE, but did not get it to work.
(I did not keep all my trail efforts, zo can not see what I did wrong to miss this one).
Thanks again,
I'll try to finish my effort and show it here, but this is a far shorter way than I came up with.
Problem, also for my solution is that SET FMTONLY is not effective any more in 2012 and this and my solution probably will not work anymore.
Thanks,
Ben
-
ben.brugman (6/11/2013)
patrickmcginnis59 10839 (6/11/2013)
I got something similar to work, LOL at the quotes needed, plus it doesn't seem like "Initial Catalog" is any help, but I put it in there anyways:SELECT * INTO
#TMP1
FROM
OPENROWSET ('SQLOLEDB','Server=MYSERVER\MYINSTANCE;TRUSTED_CONNECTION=YES;Initial Catalog=mydatabase',
'exec (''SET FMTONLY OFF;USE mydatabase;EXEC sp_spaceused ''''mytable'''''')') x
Excellent.
Indeed I also had the impression that the initial catalog did/does not work.
I played around with the FMTONLY and with USE, but did not get it to work.
(I did not keep all my trail efforts, zo can not see what I did wrong to miss this one).
Thanks again,
I'll try to finish my effort and show it here, but this is a far shorter way than I came up with.
Problem, also for my solution is that SET FMTONLY is not effective any more in 2012 and this and my solution probably will not work anymore.
Thanks,
Ben
http://msdn.microsoft.com/en-us/library/ms143729.aspx
"The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined."
The version of SQL server (page) that this was on was 2012, so it might only be depreciated right now, I don't have a 2012 available with adhoc queries so can't tell for sure.
On the other hand, when I want to get jobs like these done, often I'll just call SQL from some vb.net or sqlcmd scripts, and this way I can do what I want with whatever gets returned 🙂
-
Hello all,
Now that SSC-Enthusiastic has given a short solution to the problem, my solution does not have much value anymore. But I promissed to come up with a more complete solution, but because of the other solution did not work any further on this. (The stored procedure could be made more dynamic, so that it can handel all kinds of code).
The Key is the FMTONLY switch. (As with SSC-Enthusiastics solution).
This code is split up in more parts which makes it larger, but this was the first code with the space used and the table name I got to actually run.
To anwser some of the questions.
Yes this is limited to the first result set only.
And probably has a lot of other limitations as well.
But this and enthusiastics code makes it possible to get meta data from stored procedures within SSMS, which can be helpfull for some situations.
The code below is cut and pasted from a file, haven't tested this after cutting and pasting, (am not on a SQL-server now).
All thanks for your time, input and effort,
ben brugman
CREATE PROCEDURE sp_Tesf_OpenRowSet
AS BEGIN
--
-- code was taken from :
-- http://www.sommarskog.se/share_data.html
--
DECLARE @fmtonlyon int
SELECT @fmtonlyon = 0
IF 1 = 0 SELECT @fmtonlyon = 1
SET FMTONLY OFF
EXEC database_name1.dbo.sp_spaceused 'table_name1'
IF @fmtonlyon = 1 SET FMTONLY ON
END -- Create sp_Tesf_OpenRowSet
GO
SELECT *
into TEMPSomeTable
FROM OPENROWSET ('SQLOLEDB','Server=InstanceName1\R2;TRUSTED_CONNECTION=YES; Initial Catalog=database_name1',
'exec database_name1.dbo.sp_Tesf_OpenRowSet') x -- Working code
select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TEMPSOMETABLE'
SELECT * FROM tempsometable
DROP TABLE tempsometable
-
Here's another option you might consider:
-- First we need to set up some sample data.
-- I'm using the Tally table just as an example--any table or
-- tables would work for demonstration purposes. I'm also
-- assuming that the itvf 'DelimitedSplit8K' exists. If not,
-- the code for both is easily found with a search of SQL Central.
USE LocalTestDB -- DB #1 (Your DBs will be different of course)
GO
SELECT TOP(100) * INTO dbo.TestTable1 FROM dbo.Tally
SELECT TOP(100) * INTO dbo.TestTable2 FROM dbo.Tally
SELECT TOP(100) * INTO dbo.TestTable3 FROM dbo.Tally
USE Test -- DB #2
GO
SELECT TOP(100) * INTO dbo.TestTable1 FROM dbo.Tally
SELECT TOP(100) * INTO dbo.TestTable2 FROM dbo.Tally
SELECT TOP(100) * INTO dbo.TestTable3 FROM dbo.Tally
-- These tables are the ones that will have 'space-used' run
-- against. You could re-run this block of code with different
-- TOP values to see how the data changes.
Next we need a table to hold the results. This only needs to be done once.
IF OBJECT_ID('dbo.SpaceUsed') IS NULL
BEGIN
CREATE TABLE [dbo].[SpaceUsed](
[RowNum] [INT] IDENTITY(1,1) NOT NULL,
[TableName] [VARCHAR](50) NULL,
[Rows] [INT] NULL,
[Reserved] [VARCHAR](50) NULL,
[Data] [VARCHAR](50) NULL,
[Index_size] [VARCHAR](50) NULL,
[Unused] [VARCHAR](50) NULL,
[StatsDate] [DATETIME] NULL,
PRIMARY KEY CLUSTERED
(
[RowNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[SpaceUsed] ADD CONSTRAINT [DF_SpaceUsed_StatsDate] DEFAULT (GETDATE()) FOR [StatsDate]
END
Now we can run the actual script:
-- Declare some variables
DECLARE
@strInput NVARCHAR(4000)
,@dbname SYSNAME
,@schemaname SYSNAME
,@tablename SYSNAME
,@strSQL NVARCHAR(MAX)
-- Define the tables that you want to include in the space used statistics
-- by creating a delimited string array
SET @strInput = N'Test.dbo.TestTable1|Test.dbo.TestTable2|Test.dbo.TestTable3|LocalTestDB.dbo.TestTable1|LocalTestDB.dbo.TestTable2|LocalTestDB.dbo.TestTable3'
SET @strSQL = N''
-- Now parse the source tables into a single dynamic query
;WITH
cteSource (dbname, schemaname, tablename)
AS
(SELECT
PARSENAME(dsk.Item,3) AS dbname
,PARSENAME(dsk.Item,2) AS schemaname
,PARSENAME(dsk.Item,1) AS tablename
FROM
dbo.DelimitedSplit8K(@strInput,'|') AS dsk
)
SELECT
@strSQL = @strSQL +
N'USE ['+dbname+']'+CHAR(13)+CHAR(10)+
'INSERT INTO LocalTestDB.dbo.SpaceUsed
(TableName, Rows, Reserved, Data, Index_size, Unused)
EXEC sp_spaceused '''+dbname+'.'+schemaname+'.'+tablename+''' '+
CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
FROM
cteSource
-- This lets you examine the dynamically generated code in the Messages window
PRINT @strSql
-- Execute
EXEC(@strSQL)
NOTE: This script will only work as is if the two databases are on the same server (and
each has the proper permissions of course). For the script to work between servers they
would have to be set up first as linked servers so they could both access the stats
collection table.
-
Steven Willis (6/12/2013)
Here's another option you might consider:Thanks for your contribution Steven, but or I do not get your anwser, or you are missing the point.
The question was:
How to get results from an SP into a table?
The sp_space_used table was just an example of a Stored procedure. So I am not after the information produced by this particular stored procedure, but after the information what meta data and what data is produced by a stored procedure. Most specifically what meta data is produced by a stored procedure. The method reseached in the thread was to be used on any stored procedure. (Or at least on most stored procedures).
Thanks,
ben brugman
-
Hi Ben,
Another solution might be CLR.
command.CommandText = @"EXEC @StoredProcedure @Variable";command.Parameters.AddWithValue("@StoredProcedure", sStoredProcedure);
command.Parameters.AddWithValue("@Variable", sVariable);
conn.Open();
SqlDataReader rdr = command.ExecuteReader();
Once the data is in the datareader like this, I plan to create a table using some logic (e.g. tbl + name of sp + incremental integer), use the amount of columns in the result to get the amount of columns required for the new table, then insert all the data into the table.
I've never done CLR, or C# for that matter but seems relatively simple.
I've successfully managed to build the CLR stored procedure which reads the data back into the management studio. Just need to do the table logic and write statement etc. Also it needs to deal with a variable amount of parameters, but I'm sure that's possible.
-
ben.brugman (6/12/2013)
Steven Willis (6/12/2013)
Here's another option you might consider:Thanks for your contribution Steven, but or I do not get your anwser, or you are missing the point.
The question was:
How to get results from an SP into a table?
The sp_space_used table was just an example of a Stored procedure. So I am not after the information produced by this particular stored procedure, but after the information what meta data and what data is produced by a stored procedure. Most specifically what meta data is produced by a stored procedure. The method reseached in the thread was to be used on any stored procedure. (Or at least on most stored procedures).
Thanks,
ben brugman
Yes, I think I understood that this was just an example. I guess the question I didn't ask was whether this is something you set up for a long-run or is it ad hoc everytime? The method I posted is likely most efficient when the target table (where the stored procedure output will go) is set up first as in my example. If these are always just ad hoc insertions and the target schema is always changing then getting that table created so it can receive the output is really the only stumbling block--but not that hard to deal with.
The actual significant part of my post is this:
;WITH
cteSource (dbname, schemaname, tablename)
AS
(SELECT
PARSENAME(dsk.Item,3) AS dbname -- these parameters would of course change with
,PARSENAME(dsk.Item,2) AS schemaname -- each procedure's requirements
,PARSENAME(dsk.Item,1) AS tablename
FROM
dbo.DelimitedSplit8K(@strInput,'|') AS dsk
)
SELECT -- the target table and the stored procedure would be whatever you need them to be
@strSQL = @strSQL +
N'USE ['+dbname+']'+CHAR(13)+CHAR(10)+
'INSERT INTO LocalTestDB.dbo.SpaceUsed
(TableName, Rows, Reserved, Data, Index_size, Unused)
EXEC sp_spaceused '''+dbname+'.'+schemaname+'.'+tablename+''' '+
CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
FROM
cteSource
The code doesn't change...it consumes the name of your source tables as you enter them (though they would all have to have the same structure). I used a delimited string then split it into a table. You could start with such a table and skip that step. Either way the dynamic SQL treats the db/tablenames as variables. So for some other sproc instead of the parameters being the db and table names it might be categoryID and invoiceID or whatever. Above I highlighted the target table and the stored procedure being processed...those could easily be made into variables as well.
If the target and source tables and the parameters for various procedures are going to be changing frequently, I don't think it would be hard to make this code even more generic. I used your original example since 'sp_spaceused' is on everyone's server and the tally table and DelimitedSplit8K function is so readily available that the example could be set up to run on just about anyone's machine. I get a bit irritated when scripts are posted and related data or functions are missing or not worth the hassle. So I try to be as considerate as possible and use references that I can either post easily or are otherwise common to most users.
Sorry if I created any confusion. If this isn't going to work for your application this time, perhaps others may benefit from it. Good luck with your project!
-
Steven Willis (6/12/2013)
We still have some confusion.
Or I do not understand you, or you do not understand me.
I want the result of a stored procedure, where I want both the meta data and the data, the meta data being the most important part. When calling the stored procedure or using the stored procedure it is unknown what the fields are. I want to pick up the fields (and the types) dynamically.
So there is a stored procedure st_X, of which I do not know what the output is. The stored procedure can have parameters. Use a script to get the result table of this Stored procedure st_X.
I still think your solution needs to know the output format before you make the call.
If I misunderstand, sorry.
Ben
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply