SQL Snippet: Generate Row Numbers

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

In thi spost, I am going to show how use ROW_NUMBER to generate a unique row number. There are three examples of code.

This first example, the simplest of the three, shows how to generate a unique number for each row in the recordset:

/*
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).
*/
SELECT
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

The second example, shows a row number can be assigned to the lines of each transaction (this is accomplished :

/*
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).
*/
SELECT
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(PARTITION BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

This final example, takes the first example and shows how we can select a range of row numbers (this is useful if you are selecting data to display on a page):

/*
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).
*/
WITH POTRX AS
	(SELECT
		PONUMBER
		,ORD
		,ITEMNMBR
		,ITEMDESC
		,ROW_NUMBER() OVER(ORDER BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
	FROM
		POP30110)
SELECT
	*
FROM
	POTRX
WHERE
	CUSTOM_ROW_ID BETWEEN 40 AND 59
GO
● Categories: Microsoft, SQL Server ● Tags: , ,  ● Permalink ● Shortlink ●

SQL Snippet: Generate GUID

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This fourth example is going to be, by far, the shortest pioece of SQL I post. It shows how to return new GUID:

/*
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).
*/
SELECT NEWID()

I discovered this function when looking for a way to generate new GUIDs for a large Workflow implementation for a client where we are insertin the workflow steps via SQL rather than through the UI. This is very much not the recommended way of creating a workflow process, but the approval requirements resulted in a very large number of workflow steps and tackling it in this way, saved us a large amount of time.

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

SQL Snippet: Split String By Delimiter

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This third example, shows how to use the new in SQL Server 2016 string_split command:

/*
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).
*/
SELECT
	value
	,ITEMNMBR
	,ITEMDESC
	,ITMCLSCD
FROM
	IV00101
 CROSS APPLY
	string_split(RTRIM(ITEMNMBR), '-')
WHERE
	value = 'SHP'

The example is part of the code I used when working on a client project a while ago; the client had a large number of Inventory Items and I needed to select a subset of the Items from the Inventory Master (IV00101).

When the clioent created their items they did so using a hyphen delimiter. Using the string_split command, I was able to separate out the segments of the Item Number and select only one of them in the WHERE clause.

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

SQL Snippet: Format Dates

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This second example, shows how to format a date in two of the most common formats I work with. Each example returns the date using the FORMAT command introduced in SQL Server 2012 and the more traditional method.

The first example, returns the date as day month year separated with /:

/*
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).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 103)
	,FORMAT(@DATE, 'dd/MM/yyyy')

The second returns the date in ISO 8601 format:

/*
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).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 126)
	,FORMAT(@DATE, 'yyyy-MM-dd')
● Categories: Microsoft, SQL Server ● Tags: , ,  ● Permalink ● Shortlink ●

SQL Snippet: Return Comma Delimited String

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This first example, shows how to return a comma delimited string of vlues from a select instead of the usual multiline recordset:

/*
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).
*/
DECLARE @DOCDATE DATETIME = '2017-04-12'
SELECT (STUFF((
        SELECT
			', ' + RTRIM(CNTRLNUM)
        FROM
			PM00400
        WHERE
			DOCDATE = @DOCDATE
        ORDER BY
			CNTRLNUM
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS ReturnString

The example above, is created against the Microsoft Dynamics GP sample database and returns a comma delimited list of vouchers for a particular date.

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

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 ●

Workflow Error Sending Email: “Execution Of User Code In The dotNET Framework Is Disabled”

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThis has come up twice fairly recently. In the first case it was following an upgrade to Microsoft Dynamics GP 2016 and workflow was being introduced for the first time, and in the second it was when a client had created a new test system:

Execution Of User Code In The dotNET Framework Is Disabled

Microsoft Dynamics GP

[Microsoft][SQL Server Native Client 11.0][SQL Server]Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Continue reading → Workflow Error Sending Email: “Execution Of User Code In The dotNET Framework Is Disabled”

● Categories: Dynamics, GP, Microsoft, SQL Server, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

How To Find Out What SQL Features Are Installed

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft SQL ServerNo day when you learn something new is a wasted day. I was onsite with a client recently to do some work with them on Microsoft Dynamics GP and got chatting to Den in their IT department.

It turns out that SQL Server has a report you can run which will show which features are installed.

The report is run from the SQL Server Installation Center which is available from the Windows Start menu. Click on Tools and then on Installed SQL Server features discovery report:

SQL Server Installation Center

Continue reading → How To Find Out What SQL Features Are Installed

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

How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In previous versions of Microsoft SQL Server, SQL Server Management Studio (SSMS) was always part of the standard install, but it seems this has changed with SQL Server 2016.

Instead SSMS is now available as a separate download. I’d recommend downloading the GA rather than any release candidate which might be available:

Download SQL Server Management Studio (SSMS)

Continue reading → How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio

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

How To Install Microsoft SQL Server 2016: SSIS Configuration For Named Instances

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In the last post, I installed SSIS, but if you are using a named Instance of SQL Server, there is a configuration step required.

To make the change, there is a file called MsDtsSrvr.ini which, for SQL Server 2016, is located in C:\Program Files\Microsoft SQL Server\130\DTS\Binn. The ServerName needs to be changed to include the full SQL Server Instance Name (as highlighted below):

MsDtsSrvr.ini file in Notepad

Once the full SQL Server Instance Name was added and the file saved, SSIS is ready to use.

Click to show/hide the How To Install Microsoft SQL Server 2016 Series Index

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