Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Team Foundation Server (TFS) -The underlying connection was closed

image
Having build and upgraded Team Foundation server from 2010 to 2013, SQL Sever and Sharpoint Foundation 2013, running on VMWare ESX,  the team found very frequent and random like connection dropped problems, most frequent being:
  • connection that was expected to be kept alive was closed
  • The underlying connection was closed

This happened trying to check things in, or other source tracking related activities against Team Foundation Server.

The hunt was on the the cause, a week of investigation (on and off), heading off looking at the IIS settings, checking settings for WCF, looking for possible max connection limits, working pools and recycling logs etc.

Also investigated a post regarding network interface card (NIC) off loading and to switch it off on virtual boxes (I’m not convinced by that).

Solution

For others in a similar position, I present our solution, the static IP address that was assigned to the server had accidentally had a a printer assigned to it too. So whenever the user came in to work and put the printer on (not every day), we would experience these problems, for a bit, until the printer went off again. All due to the duplicate IP address.

How to make GP company login text boxes longer

In the blog post series by David Musgrave, he shows how to customise the GP company login boxes, to make them long enough to see very long company names in full. David uses Dexterity in the third part of the series. However in the latest he attempts to do the same using an addin, albeit he “cheats” by passing in the SanScript. Note this is not a supported method.
By using the Continuum COM library to allow .NET code in the Dynamics GP Visual Studio addin to pass SanScript through to the GP we CAN do it, see how I did it!

The original article can be found here:

image

Customising the Company Login window series Part 4 - Visual Studio Toolsby David Musgrave

So David had a problem getting it working, I had Visual Studio open on my Addin project, so I gave it a go.

Make a reference to the Continuum

This is a COM object in, so go to your project reference and search for “contin” and then add the reference. Ensure you marry the version number with the version of GP you are using.

image

Store the script in a project resource

Under the project settings, you can store resources. This is a good place to put your scripts as it deals with line feeds and keeps them in an intact form for future editing.

Go to the resources tab of the project properties, create a new resource and paste in the script provided by David. We are going to use the .NET code to run the script, so we don’t need to put it in a procedure, only take the fragment that does the deed. Name the resource LoginResizeScript

image

Add event handler

Now when the company login window is opened we want to execute the script to resize the text boxes on that window. So to me it seems like a good plan to add and event handler to the company switch form. Now go to your GPAddin.vb and add and event handler like this for the company switch form.

Sub Initialize() Implements IDexterityAddIn.Initialize
'You should add code to only execute once in lifetime
AddHandler Microsoft.Dexterity.Applications.DynamicsDictionary.OpenSwitchCompanyFormProcedure._Instance.InvokeAfterOriginal, AddressOf DoLoginResize

End Sub

Deal with the namespaces

Important as the project reference messes you up!

Imports DynamicsGP = Microsoft.Dexterity.Applications.Dynamics

Execute the script

So next the script needs to run in response to the event firing on the Switch Company event handler. Add the following to your GPAddin.vb .

The SanScript is now held in a project resource we can reference by My.Resources.Resources.LoginResizeScript so we push this into the ExecuteSanscript method.

Public Sub DoLoginResize(sender As Object, e As EventArgs)
Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String = Nothing
Dim CompilerError As Integer
' Execute SanScript in Dynamics GP
CompilerError = CompilerApp.ExecuteSanscript(My.Resources.Resources.LoginResizeScript, CompilerMessage)
If CompilerError <> 0 Then
Windows.Forms.MessageBox.Show(CompilerMessage)
End If
End Sub

Resulting resized controls – it works!

image

And hey it works from VB.NET too! I love Visual Studio and Dynamics GP!

“The Installation of this package failed” Sharepoint 2013 Server 2012 R2

On attempting to install team foundation server I encountered the following error trying to install sharepoint 2013 foundation.

image

This was after the installation had failed and on trying to run the installer again.

To clear this dialog box and run the installer again I used filemon to see where the extracted installer was running from and deleted that directory, in my case:

c:\program Files (x86)\MSECache\SharePoint2010

image

The original problem was the pre-requisites could not be downloaded by the installer, due to IE protection mode.

Change to indexdefragmentation script

My take on Muthukkumaran Kaliyamoorhty’s script, deals with schemas for indexes and tables.

Ran from stored job to rebuild or reorganize and update stats depending on if they need it or not.

Build history table:

USE [msdb]
GO

/****** Object: Table [dbo].[dba_defrag_maintenance_history] Script Date: 05/13/2014 10:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[dba_defrag_maintenance_history](
[db_name] [sysname] NOT NULL,
[table_name] [sysname] NOT NULL,
[index_name] [sysname] NOT NULL,
[frag] [float] NULL,
[page] [int] NULL,
[action_taken] [varchar](35) NULL,
[date] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[dba_defrag_maintenance_history] ADD DEFAULT (getdate()) FOR [date]
GO

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[indexdefragmentation] Script Date: 05/13/2014 09:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary: Remove the Index Fragmentation to improve the query performance
Contact: Muthukkumaran Kaliyamoorhty SQL DBA
Description: This Sproc will take the fragmentation details and do four kinds of work.
1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
3. Check the fragmentation between 15% to 29% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the three conditions is false
ChangeLog:
Date Coder Description
2011-11-23 Muthukkumaran Kaliyamoorhty created

*************************All the SQL keywords should be written in upper case*************************

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schema_name SYSNAME
)

EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schema_name)
SELECT
T.Name AS obj,
I.[name] AS ind,
f.avg_fragmentation_in_percent,
f.page_count,
SCHEMA_NAME(T.Schema_ID) AS table_schema
FROM sys.tables T WITH (NOLOCK)
LEFT OUTER JOIN sys.indexes I
ON T.object_id = I.object_id
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) F
On F.object_id=i.object_id
WHERE T.type = '
'U''
ORDER BY T.Name
'

)

SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag

TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

WHILE (@min_id<=@max_id)
BEGIN

SELECT
@tab_name=table_name,
@schema_name=schema_name,
@ind_name=index_name ,
@frag=frag ,
@pages=pages
FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
BEGIN
IF (@frag>=30 AND @pages>1000)
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',GETDATE())
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',GETDATE())
END CATCH
END

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
ELSE
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',GETDATE())
END
END
ELSE
BEGIN

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',GETDATE())
END

SET @min_id=@min_id+1
END
DROP TABLE #tempfrag

INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history

END