Find Column In SQL Using INFORMATION_SCHEMA

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPLast year I posted a script to find tables containing a particular column using sys objects. Steve Endow of Dynamics GP Land suggested using the INFORMATION_SCHEMA instead as he found it easier to use.

I’ve recently had reason to search for tables with a particular column in them, so I took a look at using a script using INFORMATION_SCHEMA.COLUMNS. However, when taking a detailed look at the results I found a few anomalies; the issue was that INFORMATION_SCHEMA.COLUMNS returns results for columns in not only tables, but also views. Which does make sense as both tables and views have columns. For what I was working on I needed a list of only tables.

I did a little exploring of the INFORMATION_SCHEMA and determined that I could join to INFORMATION_SCHEMA.TABLES and filter on TABLE_TYPE <> ‘VIEW’ to get a result set of only tables:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
	['Tables'].TABLE_SCHEMA AS 'Schema'
	,['Tables'].TABLE_NAME AS 'Table'
FROM
	INFORMATION_SCHEMA.COLUMNS AS ['Columms']
INNER JOIN
	INFORMATION_SCHEMA.TABLES AS ['Tables']
		ON
			['Tables'].TABLE_CATALOG = ['Columms'].TABLE_CATALOG
		AND
			['Tables'].TABLE_SCHEMA = ['Columms'].TABLE_SCHEMA
		AND
			['Tables'].TABLE_NAME = ['Columms'].TABLE_NAME
		AND
			['Tables'].TABLE_TYPE <> 'VIEW'
WHERE
	COLUMN_NAME = @ColumnToFind
ORDER BY
	'Schema'
	,'Table'

In the original posts script I was using the sys objects directly, but was filtering out the views by joining to sys.tables which contains only tables. Both the original script and the above one return exactly the same result set.

So, what’s the difference?

INFORMATION_SCHEMA, or System Information Schema Views to give the full name, is one of several methods SQL Server provides to get an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes may have been made to the underlying system tables. The information schema views included in SQL Server originally complied with the ISO standard definition for the INFORMATION_SCHEMA, but appear to have diverged from the standard as new standards have been introduced.

The metadata returned by INFORMATION_SCHEMA, comes from the sys objects. So by using the former you are getting information from the latter, but in a way which should be future proofed against database changes.

● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

Deploy SQL View to All Databases

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPI have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

  • Delete any existing view with the same name (this allows for an easy redeployment).
  • Create the view.
  • Grant the SELECT permission to DYNGRP.
  • The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

    The large highlighted section is where you please the content of the view which is to be deployed.
    Continue reading → Deploy SQL View to All Databases

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

    SQL View to Return Budgets By Month

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

    However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

    With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

    The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

    Continue reading → SQL View to Return Budgets By Month

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    Stored Procedure To Get Next Purchase Receipt Number

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPThis stored procedure can be executed to generate the next sequential purchase receipt number which can be used for both receivings transactions (Shipment and Shipment/Invoice) and invoices; the generated invoice was then added to the integration file which was then submitted to eConnect. I’ve written this stored procedure at least three times for different integrations, so thought it best to post it here so I don’t write it again.

    -- drop stored proc if it exists
    IF OBJECT_ID (N'usp_AZRCRV_GetNextPOPReceiptNumber', N'P') IS NOT NULL
        DROP PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber
    GO
    -- create stored proc
    CREATE PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber AS
    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
    */
    BEGIN
    	DECLARE @return_value INT
    	DECLARE @I_vInc_Dec TINYINT = 1
    	DECLARE @O_vPOPRCTNM AS VARCHAR(17)
    	DECLARE @O_iErrorState INT
    
    	exec @return_value = taGetPurchReceiptNextNumber  @I_vInc_Dec, @O_vPOPRCTNM = @O_vPOPRCTNM OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
    	SELECT @O_vPOPRCTNM
    END
    GO
    
    -- grant execute permission on stored proc to DYNGRP
    GRANT EXECUTE ON usp_AZRCRV_GetNextPOPReceiptNumber TO DYNGRP
    GO
    
    -- execute stored proc
    EXEC usp_AZRCRV_GetNextPOPReceiptNumber
    GO

    The stored proc calls a Microsoft Dynamics GP stored procedure which actually does the work, so we are still getting the receipt number using standard functionality.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

    Microsoft Dynamics GP 2016 R2 Financial Dashboard Error

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPWhen reviewing some dashboards with a client a while ago we encountered an error launching the Financial Dashboard:

    Microsoft Excel - We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Microsoft Excel

    We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Continue reading → Microsoft Dynamics GP 2016 R2 Financial Dashboard Error

    ● Categories: Dynamics, Excel Reports, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    “Service ‘GP OData Service (GPODataService) failed to start. Verify that you have sufficient privilages to start the system services.”

    ● Ian Grieve ●  ● 2 Comments   ● 

    Microsoft Dynamics GPWhen working on the Hands On With Microsoft Dynamics GP 2016 R2 series of posts, I installed the OData Service. While doing so, I received an error:

    Service 'GP OData Service' (GPODataService) failed to start. Verify that you have sufficient privileges to start the system services.

    Microsoft Dynamics GP OData Service

    Service 'GP OData Service' (GPODataService) failed to start. Verify that you have sufficient privileges to start the system services.

    This type of error is not atypical when working with new service accounts, which I was on the series of posts as it was a brand new virtual machine I was working on. Fortunately, with experience of working with Microsoft Dynamics GP for nearly 14 years, I have a little experience of resolving this type of error.

    In fact, I have blogged about it a couple of times. The first was back in March 2013; the instructions are towards the bottom of the post.

    ● Categories: Dynamics, GP, GP OData Service, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    MS Connect Suggestion: Allow Workflow Steps to be Copied

    ● Ian Grieve ●  ● 2 Comments   ● 

    Microsoft Dynamics GPI am on a bit of a kick with suggestions for improving Workflow 2.0 at the moment and have another MS Connect suggestion for you to vote for.

    The Workflow Maintenance window currently has the facility to copy an entire workflow process (including between companies), but does not have the facility to copy a workflow step; many times when I am creating a workflow process with, or for, a client we are creating many steps which only vary in the approver and part of the condition (for example a different site or segment in the account, and it would reduce the effort and time needed if the workflow step could be copied and amended, rather than created from scratch each time.

    You can vote for this suggestion here.

    ● Categories: Dynamics, GP, Microsoft, Microsoft Connect, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script To Return Functional Currencies For All Companies Without a Cursor

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

    This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    CREATE TABLE #FunctionalCurrencies(
    	INTERID VARCHAR(5)
    	,FUNLCURR VARCHAR(20)
    )
    GO
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
    					SELECT 
    						CHAR(13) 
    							+ 'SELECT 
    								''' + INTERID + '''
    								,FUNLCURR
    							FROM
    								' + INTERID + '.dbo.MC40000
    							WHERE
    								LEN(FUNLCURR) = 0'
    					FROM
    						DYNAMICS.dbo.SY01500
    					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #FunctionalCurrencies
    	EXEC sys.sp_executesql @SQL
    GO
    
    SELECT * FROM #FunctionalCurrencies
    GO
    
    DROP TABLE #FunctionalCurrencies
    GO
    ● Categories: Dynamics, GP, Microsoft, Multicurrency ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script Linking Purchase Orders to Purchase Requisitions

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

    It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

    Continue reading → SQL Script Linking Purchase Orders to Purchase Requisitions

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

    You Cannot Print An Unauthorized Purchase Order

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThe below error message was reported to me by a user performing UAT (User Acceptance Testing) on an implementation of Purchase Order Processing and Workflow 2.0:

    Microsoft Dynamics GP - You cannot print an unauthorized purchase order

    Microsoft Dynamics GP

    You cannot print an unauthorized purchase order

    This error was somewhat surprising as it was produced when trying to print a purchase order (PO) which had been converted from a purchase requisition (PR). There was a workflow process on the PR, which had been final approved and the PO created, but there was not a workflow on the PO.

    I double checked to make sure that the old Purchase Order Enhancements (POE) Approvals functionality was disabled (it was) as the customer had previously been using this.

    It was when I was checking for POE Approvals, that I realised that, as part of the project, we had enabled the PO Commitments part of POE.

    The warning message was being produced as the PO line had exceeded the commitment budget value. Loading the PO, clicking the save button, accepting the override of the commitment budget value, reloading the PO and clicking the Print button successfully printed the PO.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●