cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Turn Stored Procedure Output into View or Table

Hi,

I need help storing the output of a stored procedure into a view or a table. I'm referring specifically to [dbo].[ReportLocationConsumptions]  under Programmability. 

The code to execute the stored procedure is the following:

 

USE [FNMScompliance]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[ReportLocationConsumptions]

SELECT 'Return Value' = @return_value

GO

 

Thanks!

(1) Solution

Hi Sabrina,

You can always insert the data returned from a stored procedure into a table.

See the following code as an example:

IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL   DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable 
(
      RowIndex                         INT
    , LicenseName                      NVARCHAR(256)
    , LicenseType                      NVARCHAR(256)
    , CostPerRight                     MONEY
    , Publisher                        NVARCHAR(64)
    , CoveredProducts                  NVARCHAR(MAX)
    , LicenseVersion                   NVARCHAR(60)
    , LicenseEdition                   NVARCHAR(60)
    , LocationHierarchicalName         NVARCHAR(MAX)
    , Purchased                        NVARCHAR(256)
    , Consumed                         NVARCHAR(256)
    , LicenseCost                      MONEY
    , ComplianceCost                   MONEY
    , ShortfallAvailability            INT
    , Level1                           NVARCHAR(256)
    , Level2                           NVARCHAR(256)
    , Level3                           NVARCHAR(256)
    , Level4                           NVARCHAR(256)
    , PurchasedLocally                 INT
    , ConsumedLocally                  INT
    , LocalShortfallAvailability       INT
    , LocalCompliance                  MONEY
    , UsedLocally                      INT
    , RolledUpPurchased                INT
    , RolledUpConsumed                 INT
    , RolledUpUsed                     INT
    , AssignedLocally                  INT
    , LocationTreeLevel                INT
    , CoveredProductsAndEdition        NVARCHAR(MAX)
    , LocationPath                     NVARCHAR(256)
    , Path                             NVARCHAR(256)
    , CoveredInstallations             NVARCHAR(MAX)
)

INSERT INTO #ReturnTable 
EXEC [dbo].[ReportLocationConsumptions]

SELECT * FROM #ReturnTable 

Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.

T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.

You can call the function by simply doing a select:

SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)

Please note:

The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.


I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'

 

View solution in original post

(12) Replies

Hi Sabrina,

You can always insert the data returned from a stored procedure into a table.

See the following code as an example:

IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL   DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable 
(
      RowIndex                         INT
    , LicenseName                      NVARCHAR(256)
    , LicenseType                      NVARCHAR(256)
    , CostPerRight                     MONEY
    , Publisher                        NVARCHAR(64)
    , CoveredProducts                  NVARCHAR(MAX)
    , LicenseVersion                   NVARCHAR(60)
    , LicenseEdition                   NVARCHAR(60)
    , LocationHierarchicalName         NVARCHAR(MAX)
    , Purchased                        NVARCHAR(256)
    , Consumed                         NVARCHAR(256)
    , LicenseCost                      MONEY
    , ComplianceCost                   MONEY
    , ShortfallAvailability            INT
    , Level1                           NVARCHAR(256)
    , Level2                           NVARCHAR(256)
    , Level3                           NVARCHAR(256)
    , Level4                           NVARCHAR(256)
    , PurchasedLocally                 INT
    , ConsumedLocally                  INT
    , LocalShortfallAvailability       INT
    , LocalCompliance                  MONEY
    , UsedLocally                      INT
    , RolledUpPurchased                INT
    , RolledUpConsumed                 INT
    , RolledUpUsed                     INT
    , AssignedLocally                  INT
    , LocationTreeLevel                INT
    , CoveredProductsAndEdition        NVARCHAR(MAX)
    , LocationPath                     NVARCHAR(256)
    , Path                             NVARCHAR(256)
    , CoveredInstallations             NVARCHAR(MAX)
)

INSERT INTO #ReturnTable 
EXEC [dbo].[ReportLocationConsumptions]

SELECT * FROM #ReturnTable 

Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.

T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.

You can call the function by simply doing a select:

SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)

Please note:

The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.


I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'

 

Thank you for the answer and explanation why. I was able to create the tableau and bring it into Tableau. 

Erwin,

Thank you for your help on this. 

How would one go about adding domains to this code. 

Thanks

Hi Sabrina,

Not sure what you mean by 'adding domains' in this context?

The procedure will break down license consumption data by location.

There is a table that stores Windows domain names (named '[ComplianceDomain']), but these Windows domains are linked to Windows users  (stored in the [ComplianceUser] table), and not related to licenses.

Hi Erwin,

I was thinking domains could perhaps give some clues as to what the location may be for some of the 'Null' locations. Please advise.

Thanks

 

Hi Sabrina,

The ‘location’ as well as other group properties (‘corporate unit’, ‘cost center’) cannot be retrieved automatically by an inventory source like the Flexera agent. These group properties must be imported from another data source, like from a CMDB (Computer Management Data Base).

Usually, these properties are updated in an asset. Updates done to these properties on an asset will by default synchronize to the inventory that is linked to the asset.

FNMS has an optional setting named ‘Syncronize device locations with site subnets’ that allows creating up updating the location of your computers.

Picture1.png

Using this feature requires that site and subnet information is available in FNMS. If this information is maintained in Active Directory (AD), it will be imported from AD if you are using the standard AD integration.   

Thank you Erwin, will try your suggestion above. Just to confirm, domains have nothing to do with License Compliance so there is no way to tie them in, correct?

Hi Sabrina,

That is correct - Windows domains are not relevant for license compliance.

Under the assumption that your organization is using country specific Windows domains, you could use the domain of computers (inventories) or the domain of users assigned to computers (assigned user, calculated user) for setting the correct location of the inventories. That is not a common request though.

'Group properties' - location, organizational unit and cost center - of inventories are not relevant for the global license balance, too.

However:

  • You might be interested in how the license balance per license breaks down by location (as an example). On the 'Group assignment' tab of a license, you can see how purchase orders and consumption are broken down per 'group' (for example, per location).
  • Using the 'Restrictions' tab on a license, you can restrict where the license can be consumed (for example, by configuring certain locations). This is called 'scoping'.

Hi Erwin, 

You mentioned that we could use the domain of the users assigned for setting the correct location. Is there a guide for this? I know for some of our Null location users, we do have domains for some of them. 

Thanks 

Hi Sabrina,

The following SQL joins FNMS inventories with users and user domains and returns inventories without any location, together with the user assigned to the inventory and with the Windows domain of that user.

;WITH cteCompplianceComputers(ComplianceComputerID, ComputerName, UserID) AS
(
    SELECT
         cc.ComplianceComputerID
       , cc.ComputerName
       , ISNULL(cc.AssignedUserID, cc.CalculatedUserID) AS UserID
    FROM [ComplianceComputer] cc
    WHERE cc.LocationID IS NULL
)
SELECT
      ComplianceComputerID
    , ComputerName
    , cu.UserName
    , cd.FlatName         AS WindowsDomainName
FROM cteCompplianceComputers ccc
JOIN [ComplianceUser]        cu   ON cu.ComplianceUserID = ccc.UserID
JOIN [ComplianceDomain]      cd   ON cd.ComplianceDomainID = cu.ComplianceDomainID

If you are able to link Windows domains to FNMS locations, you can use this as the basis for building an UPDATE statement that sets the 'LocationID' property for Inventories in the [ComplianceComputer] table.

Before updating the location of your inventories, make sure that the output of the SELECT statement above correctly reflects the locations that you want to assign to your inventories, please.

Hi Erwin,

I will use the domain method as an temporary solution to get some locations derived from the blank locations for licenses.

Is there a way to convert the code you provided on 7/2 into a view so it's always updated as opposed to the one time table it created?

Thanks

Hi Sabrina,

You cannot convert the output of a stored procedure into a SQL view.

However, you can simply convert the output of the Table Valued Function (TVF) that I provided on 7/2 into a view. Just use:

CREATE VIEW View_ReportlocationconsumptionsMSTVF AS
(
    SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)
)