Insert stored procedure result set into Temp table and query temp table

Question

I have the following stored proc which is trying to:

Execute system stored procedure (sp_monitorconfig) and put the result set into a temp table.
SELECT FROM this temp table and add 2 custom columns (SOURCESERVER & CollectionTime)
This final result set will be ingested into Logstash via jdbc job.

I'm currently using SAP ASE 16 (sybase) and am getting an incorrect syntax error at keyword 'exec'. I'm not sure if I have to prefix the stored proc or what, but I'm currently stumped and any help is appreciated.

USE db
GO
    CREATE PROCEDURE sp_active_con_ratio.sql AS
    DECLARE @servername varchar(32) DECLARE @collecttime DATETIME DECLARE @procparam varchar(32)
select
    @servername = @@servername
select
    @collecttime = getdate()
select
    @procparam = 'number of user connections' CREATE TABLE #TempUserConnections
    (
        TempName varchar(35),
        FreeConnections int,
        ActiveConnections int,
        PercentActive char(6),
        MaxUsed int,
        Reuse_cnt int,
        Instance_Name varchar(30) NULL
    )
INSERT INTO
    #TempUserConnections (TempName, FreeConnections, ActiveConnections, PercentActive, MaxUsed, Reuse_cnt, Instance_Name)
    exec sp_monitorconfig @procparam  **ERROR HERE**
SELECT
    @servername AS 'SOURCESERVER',
    FreeConnections,
    ActiveConnections,
    PercentActive,
    MaxUsed,
    @collecttime AS 'CollectionTime'
FROM
    #TempUserConnections
    DROP TABLE #TempUserConnections
    RETURN
GO

Thanks!

You don't tell us *what* that error is. Also `sp_` is a reserved prefix by Microsoft and should **not** be used for User Procedures. — Thom A, Aug 15 '20 at 19:14
@Larnu Thanks for the comment. Sorry, error is just "Incorrect syntax near the keyword exec." Also, I altered the db name and sp name because they both included company info :) — basement, Aug 15 '20 at 19:22
[Check if this help you](https://stackoverflow.com/questions/166080/how-can-i-get-data-from-a-stored-procedure-into-a-temp-table) — Ronen Ariely, Aug 15 '20 at 22:04
@basement this code is running without error on my environment !. — Vahid Farahmandian, Aug 16 '20 at 06:47
`insert / exec` is not supported in Sybase ASE; while you can [use a proxy table to insert proc output into a table](https://stackoverflow.com/a/5570045/7366100) this is a bit convoluted; your best bet would be to extract the desired queries from the source of `sp_monitorconfig` and build your own/custom sql to do what you want — markp-fuso, Aug 16 '20 at 15:15
@markp-fuso Thanks for the comment, recreating the query was my initial thought- using sp_helptext on sp_monitorconfig was what I did originally however I could not decipher how it was querying 'max available USER connections'. Full code here: http://www.sqlbrowser.com/wikiHTML/sqlbhtml/sybsystemprocs/sp_monitorconfig.html — basement, Aug 16 '20 at 16:24
@basement - thanks for the link/reminder ... I'd forgotten about that optional input parameter that allows storing values in a pre-defined table; see my answer (below) for more details — markp-fuso, Aug 16 '20 at 17:29

markp-fuso · Accepted Answer · 2023-05-12T22:33:31.513

I'd forgotten that sp_monitorconfig has an optional input parameter (@result_tbl_name) that allows the operator to designate a table into which the results should be inserted.

From the documentation on sp_monitorconfig, Example #8 ...

First create the table to hold the results; while the table name can vary you'll want to keep the column names/datatypes as defined:

create table sample_table
(Name            varchar(35),
 Config_val      int,
 System_val      int,
 Total_val       int,
 Num_free        int,
 Num_active      int,
 Pct_act         char(6),
 Max_Used        int,
 Reuse_cnt       int,
 Date            varchar(30),
 Instance_Name   varchar(35))

To capture a few metrics:

exec sp_monitorconfig "locks",            sample_table
exec sp_monitorconfig "number of alarms", sample_table

Display metrics:

-- select * from sample_table

exec sp_autoformat sample_data
go

sp_autoformat sample_table
go

 Name             Config_val System_val Total_val Num_free Num_active Pct_act Max_Used Reuse_cnt Date                Instance_Name
 ---------------- ---------- ---------- --------- -------- ---------- ------- -------- --------- ------------------- -------------
 number of locks       10000        942     10000     9717        283   2.83       308         0 Aug 16 2020 12:26PM              
 number of alarms        400          0       400      386         14   3.50        14         0 Aug 16 2020 12:26PM

This is exactly what I was looking for. Thank you so much! – basement Aug 16 '20 at 17:46 — basement, Aug 16 '20 at 17:46

score -1 · Answer 2 · answered Aug 16 '20 at 14:13

You could do something like this;

USE db
GO
CREATE PROCEDURE usp_active_con_ratio.sql AS
BEGIN

    DECLARE @servername varchar(32) = (select @@servername)
    DECLARE @collecttime DATETIME   = (select getdate())
    DECLARE @procparam varchar(32)  = (select 'number of user connections')

    
    CREATE TABLE #TempUserConnections
    (
        TempName varchar(35),
        FreeConnections int,
        ActiveConnections int,
        PercentActive char(6),
        MaxUsed int,
        Reuse_cnt int,
        Instance_Name varchar(30) NULL
    )

    INSERT INTO #TempUserConnections 
    (
        TempName, 
        FreeConnections, 
        ActiveConnections, 
        PercentActive, 
        MaxUsed, 
        Reuse_cnt, 
        Instance_Name
    )

    -- Add the semi-colon to terminate the statement
    EXEC sp_monitorconfig @procparam;

    SELECT
        @servername AS 'SOURCESERVER',
        FreeConnections,
        ActiveConnections,
        PercentActive,
        MaxUsed,
        @collecttime AS 'CollectionTime'
    FROM
        #TempUserConnections
        DROP TABLE #TempUserConnections
END

GO

As @larnu mentioned you should not use the prefix sp, a better prefix in my opinion is usp_.

Ensure the stored procedure you're calling (sp_monitorconfig) has a RETURN

Insert stored procedure result set into Temp table and query temp table

2 Answers2