Command Line Script to Open Ports for SQL Server

 

 

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022 
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo =========  Misc Applications  ==============

@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
@echo Enabling port for MAX License Service
netsh firewall set portopening TCP 8080 "ExactRMService"

Dynamics GP 2015 Upgrade System Requirements

If you are upgrading to Dynamics GP 2015 make sure that you are at least on SQL Server 2008 R2 and running Windows Server 2012.
Vince Stefanetti

How to Clear Stuck Users in ExactMAX 5

 

Always use caution when editing live tables in MAX.

On occasion a hard closure of MAX or a dropped connection can cause MAX to retain the user in the ERMUD table which is in the EXACTMAX master database. You can query the ERMUD table and review the details to see which users are stuck.

MachineName    ExactRMUser CompanyName   AppID
STEF277301-02 MANAGER     MAX Sample DB 8
STEF277301-02 MANAGER     MAX Sample DB 512

MAX Also has an associated App ID.

You can query the table :

SELECT * FROM EXACTMAX..ERMUD

You can delete stuck users by executing:

DELETE FROM ERMUD WHERE ExactRMUser = ‘Type the Login Name’ and CompanyName = ‘Type the Company Name’

 

 

As well you can use this handy Script to check to see who is in what module:

-- Get the Logged in Users and the Core Modules they are in

/* 10/2/2014 - Authored: Vincent Stefanetti */

/* Notes: System Manager = 512, Advanced Shipping = None, SOP = 1, Warranty = 2048, BOM = 4, ECO = None, Feature Opton = None, Advanced Shipping = None,

INV = 8, MPS = 128, MRP = 16, PUR = 2, pim = 8192, labor tracking = 16384, SFC = 64, Costing = 256, FIM = 32, EDI = 32768, TnxLoad = 1024

Archive = None, User Designed Fields = None

*/

USE ExactMAX -- OR enter your DB name here

select SQ.hostname as sql_hostname, MX.MachineName, MX.ExactRMUser, MX.CompanyName, MX.Module

from [master].[sys].[sysprocesses] AS sq

LEFT OUTER JOIN

(SELECT MachineName, ExactRMUser, CompanyName,

CASE

WHEN AppID = '512' THEN 'System Manager'

WHEN AppID = '1' THEN 'Sales Order Processing'

WHEN AppID = '2' THEN 'Purchasing'

WHEN AppID = '4' THEN 'Bills of Materials'

WHEN AppID = '8' THEN 'Inventory Control'

WHEN AppID = '16' THEN 'MRP'

WHEN AppID = '32' THEN 'Financial Integration'

WHEN AppID = '64' THEN 'Shop Floor Control'

WHEN AppID = '128' THEN 'Master Scheduling'

WHEN AppID = '256' THEN 'Costing'

WHEN AppID = '1024' THEN 'Transaction Load'

WHEN AppID = '2048' THEN 'Warranty Tracking'

WHEN AppID = '8192' THEN 'Physical Inventory'

WHEN AppID = '16384' THEN 'Labor Tracking'

WHEN AppID = '32768' THEN 'Physical Inventory'

else ''

END AS 'Module'

FROM ERMUD) AS MX

ON sq.hostname = mx.MachineName

where hostname <> ''

group by SQ.hostname, MX.MachineName, MX.ExactRMUser, MX.CompanyName, MX.Module

Vince Stefanetti

Senior Consultant

ID:12248195

Exact Software North America - Exact MAX

Setting Up SQL Database Email with a Google Mail Account

 

If you need to use a Google Account for setting up email notifications on SQLServer make sure you use the 2 step security method.

1. Login to the Google Account
2. Go to Account Settings for the Account – Upper Right when you log in.
3. Select Sign-in and Security

 

4. Then – 2 step

5. Then perform the actions needed to do the verification by phone or text.

6. Next, Add a name for the app “SQL Database Email” etc.

7. Grab the password

8. Configure the DB email account and place the password there for the account authentication (see below)

Hope this helps!

Collation Sequence

 

This defines how SQL Server will sort Case and Accents ( glyphs to determine pronunciation) and Kana (Japanese syllabic writing). Capitalization and the Diacritical ( Dia - through and krinein “to separate”) marks are included in sort considerations by SQL Server. Selecting the collation sequence on setup provide the Instance Collation however the collation can also be changed at the DB and Column level as well.

Key Words:

Collate > https://www.google.com/?gws_rd=ssl#q=define+collate and the derivation meaning “brought together”.

Code Page > https://en.wikipedia.org/wiki/Code_page

Vince Stefanetti

What does the Double Period Mean in T-SQL Script?

When a double period is used, as in:

 

ExactMAXSampleDB..Part_Master

 

This means the schema is inferred to be the default schema of the current login.

 

In most cases this will be .dbo. So instead of having to provide it one could use the shortcut of the “..” or more correctly the default for that user which is more right due to the fact that the author of the script may not know which default is in use but may know the table and databases name that may well be standard as determined by the software app deployed.

 

Vince Stefanetti

Senior Consultant

                                              ID:12248195