Wednesday, February 4, 2015
Wednesday, January 28, 2015
How to use macros and Scheduled Tasks to automatically start the program and to perform other tasks in Microsoft Dynamics GP
1. To create and to run a macro that automatically generates reports or that performs other tasks, follow these steps:a. Start Microsoft Dynamics GP. Do not log on.
b. In the Welcome to Microsoft Dynamics GP dialog box, press ALT+F8 to start recording the logon macro. Name the macro, and then save it to the installation folder for Microsoft Dynamics GP.
Note If the macro is not in the installation folder, you will receive the following error message:
Cannot open macro
c. In the Server box, click a server.
Note It is important to select the appropriate server when you create the macro, even if the correct server was selected when you started Microsoft Dynamics GP.
d. In the User ID field, enter the appropriate information for the user who will generate the report or who will generate other tasks.
Notes? It is important to do this step when you create the macro, even if the correct user ID is displayed when you start Microsoft Dynamics GP.
? To make sure that the desired user ID is recorded within the macro, we recommended that you delete any existing user ID from the field, move to the Password field, return to the User ID field, and then enter the desired ID.
e. In the Password field, type the password for the user who is selected in step 1d.
f. Click OK. The Company Login dialog box opens.
g. From the Company list, click the company for which you want to log on, and then click OK.
Note It is important to select the appropriate company when you create the macro, even if the correct company is selected when the Company Login window opens.
h. Complete all the steps that are required to process the reports or other tasks within this company.
i. On the File menu, click Exit to close Microsoft Dynamics GP.
2. Use a text editor, such as Notepad, to add the following line as the second line of the macro.
Logging file 'macro.log'
Note This line prevents any message that is displayed by the macro from being displayed. Therefore, such a message cannot prevent a logon to Microsoft Dynamics GP. An example of such a message is the total run time of the macro. All messages that are generated by the macro are written to the Macro.log file.
3. To start Microsoft Dynamics GP by using the macro, create a batch file. To do this, follow these steps:a. Create a file that has a .bat file name extension. For example, create a file that is named DynamicsGP.bat.
b. Use a text editor, such as Notepad, to open the batch file.
c. Paste the following line into the .bat file.
dynamics dynamics.set XXX.mac cd c:\Dynamics GP installation path
Note Replace XXX.mac with the name of the macro file that you assigned in Step 1b. Replace Dynamics GP installation path with the installation path of Microsoft Dynamics GP on this computer. For example, type the following path:
Program Files\Microsoft Dynamics\GP
d. Save the changes, and then close the batch file.
b. In the Welcome to Microsoft Dynamics GP dialog box, press ALT+F8 to start recording the logon macro. Name the macro, and then save it to the installation folder for Microsoft Dynamics GP.
Note If the macro is not in the installation folder, you will receive the following error message:
Cannot open macro
c. In the Server box, click a server.
Note It is important to select the appropriate server when you create the macro, even if the correct server was selected when you started Microsoft Dynamics GP.
d. In the User ID field, enter the appropriate information for the user who will generate the report or who will generate other tasks.
Notes? It is important to do this step when you create the macro, even if the correct user ID is displayed when you start Microsoft Dynamics GP.
? To make sure that the desired user ID is recorded within the macro, we recommended that you delete any existing user ID from the field, move to the Password field, return to the User ID field, and then enter the desired ID.
e. In the Password field, type the password for the user who is selected in step 1d.
f. Click OK. The Company Login dialog box opens.
g. From the Company list, click the company for which you want to log on, and then click OK.
Note It is important to select the appropriate company when you create the macro, even if the correct company is selected when the Company Login window opens.
h. Complete all the steps that are required to process the reports or other tasks within this company.
i. On the File menu, click Exit to close Microsoft Dynamics GP.
2. Use a text editor, such as Notepad, to add the following line as the second line of the macro.
Logging file 'macro.log'
Note This line prevents any message that is displayed by the macro from being displayed. Therefore, such a message cannot prevent a logon to Microsoft Dynamics GP. An example of such a message is the total run time of the macro. All messages that are generated by the macro are written to the Macro.log file.
3. To start Microsoft Dynamics GP by using the macro, create a batch file. To do this, follow these steps:a. Create a file that has a .bat file name extension. For example, create a file that is named DynamicsGP.bat.
b. Use a text editor, such as Notepad, to open the batch file.
c. Paste the following line into the .bat file.
dynamics dynamics.set XXX.mac cd c:\Dynamics GP installation path
Note Replace XXX.mac with the name of the macro file that you assigned in Step 1b. Replace Dynamics GP installation path with the installation path of Microsoft Dynamics GP on this computer. For example, type the following path:
Program Files\Microsoft Dynamics\GP
d. Save the changes, and then close the batch file.
Transfer SQL login regardless version
/************************************************************************************************
*
* Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.
* Script is compatiable with SQL 7 / SQL 2000 / SQL 2005. Need to save the results
* to a text file and run within a new query window.
*
* Created Date: Inital - 01/13/2006
*
* Revsions: 01/15/2006 - Made some formatting changes to the output text.
* 01/16/2006 - Made syntax change to account for Binary version of SQL Server.
* 01/16/2006 - Change version SQL version check because of syntax differences between
* SQL 2000 and 2005 with @@version.
* 04/10/2007 - Made change to deal with user names that begin with numbers instead of characters.
* 11/14/2008 - Made syntax change to update for SQL 2008
*
************************************************************************************************/
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
create procedure seeMigrateSQLLogins @login_name sysname = NULL
as
declare
@name char(50),
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@Is_Policy bit,
@Is_Exp bit,
@type char(1),
@Pol char(3),
@Exp char(3)
set nocount on
create table #logins (
[name] nvarchar(128) NOT NULL,
[sid] varbinary(85) NOT NULL,
[type] char(1) NOT NULL,
[is_policy_checked] bit default 0,
[is_expiration_checked] bit default 0,
[password_hash] varbinary(256) )
insert #logins (name, sid, type)
select name, sid, type from sys.server_principals where
(type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITY\SYSTEM'
update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
from #logins a, sys.sql_Logins b
where a.sid = b.sid
set nocount off
IF (@login_name IS NULL) --Not a single user, get the list
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name <> 'sa'
ELSE
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name = @login_name
OPEN seelogin_curs
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF @type = 'S'
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END
IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
PRINT @tmpstr
PRINT ''
END
Else
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
PRINT @tmpstr
PRINT ''
END
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
drop table #logins
GO
declare
@version2005 char(5)
declare
@version2008 char(5)
--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)
if @version2005 = '9.00'
Begin
exec seeMigrateSQLLogins
End
Else if @version2008 = '10.0'
Begin
exec seeMigrateSQLLogins
End
Else
begin
exec sp_help_revlogin
End
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
/* End Script */
GP-GP performance
A , SQL SP
https://mbs.microsoft.com/customersource/support/documentation/systemrequirements
B , GP SP
https://mbs.microsoft.com/customersource/support/downloads/servicepacks
C, Posting table for posting slow performance
If you experience performance issues when you post, run the following SELECT statement on the PJOURNAL table. Run the statement against all the company databases.
SELECT * FROM PJOURNAL
If rows are returned, we recommend that you clear the contents of the table by running the following statement against all the company databases.
DELETE PJOURNAL
D, Enable delete Pjournal Job
INTRODUCTION
This article describes how to use Microsoft SQL Server Enterprise Manager to enable the Delete PJournal job that is created during the installation of Microsoft Dynamics GP or of Microsoft Business Solutions - Great Plains. When you enable the Delete PJournal job, the speed of the posting process in the program increases.
MORE INFORMATION
To enable the Delete PJournal job in Enterprise Manager, follow these steps: 1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the instance of SQL Server for Microsoft Dynamics GP or for Microsoft Business Solutions - Great Plains.
3. Expand Management, expand SQL Server Agent, and then click Jobs.
4. Right-click Remove Posted PJournals for your company database, and then click Properties.
5. Click to select the Enabled check box, click Apply, and then click OK.
Note By default, this job is specified to run every 30 minutes. You can change the frequency and the time of the job execution. To do this, follow these steps:1. Right-click Remove Posted PJournals for your company database, and then click Properties.
2. Click the Schedules tab.
3. Click Edit.
E, Performance issues that occur when you open windows.
1. Open the user preferences. To do this, follow the appropriate step:• In Microsoft Dynamics GP 10.0, click Microsoft Dynamics GP, and then click User Preferences.
• In Microsoft Dynamics GP 9.0 and in earlier versions, click Tools, point to Setup, and then click User Preferences.
2. Click AutoComplete.
3. Click to clear the Show AutoComplete Suggestions check box, and then click OK.
4. In Windows Explorer, delete the AutoCmpl.dat file and the AutoCmpl.idx file. These files are in the following folders: • In Microsoft Dynamics GP 10.0, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Microsoft Dynamics GP\dbname\
• In Microsoft Dynamics GP 9.0 and in earlier versions, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Great Plains\dbname\
5. Repeat step 1 through step 5 for each user.
F, Performance issues that occur when you log on to Microsoft Dynamics GP
Performance issues that occur when you log on to Microsoft Dynamics GP
1. Make sure that you have Microsoft Dynamics GP 9.0 Service Pack 2 or a later version installed. Logon issues were caused by an issue in which the Menu Master table (SY07110) became too large. To obtain the latest service pack for Microsoft Dynamics GP, visit the following Microsoft Web site:
https://mbs.microsoft.com/customersource/support/downloads/servicepacks/microsoftdynamicsgp90_mspreleases.htm?printpage=false
2. The location of the modified Reports.dic file and the modified Forms.dic file may affect the logon performance. If the modified dictionaries are on a network share, copy the dictionaries to the local Microsoft Dynamics GP folder, and then try to log on. To do this, follow these steps:a. To verify the location of the modified dictionaries, locate the Microsoft Dynamics GP folder. By default, this folder is in the following location:
C:\Program Files\Microsoft Dynamics\GP
b. Right-click the Dynamics.set file, and then open the Dynamics.set file in a text editor such as Notepad or WordPad.
c. Locate the path of the Reports.dic file and of the Forms.dic file. If the path is located on a network share, change the path to a local path.
3. Certain SmartList reminders may cause logon issues, depending on the homepage role for that user. To verify the reminders for a user, use the appropriate step:• In Microsoft Dynamics GP 9.0, click View, and then click Reminders. At the bottom, click Change Reminder Preferences. Remove any reminders in the Custom Reminders section at the bottom of the window.
• In Microsoft Dynamics GP 10.0, click Microsoft Dynamics GP, and then click Reminders. Click Change Reminder Preferences, and then remove the reminders in the Custom Reminder section at the bottom of the window.
G, Virus Scanner , exclude SQl database files and GP files and location
H, Sources of additional troubleshooting information
The System Information tool
Run the System Information tool to obtain the hardware specifications of the following computers: • The computer that is running SQL Server
• The client workstations
• The computer that is running Terminal Server
To obtain this information, follow these steps on each computer:1. Click Start, click Run, type msinfo32, and then click OK.
2. Click File, and then click Save to save this information to a file.
Trace logs
Create a Dexsql.log file and a SQL trace when you reproduce the performance issue. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
850996 How to create a Dexsql.log file for Microsoft Dynamics GP
857246 How to create a SQL Trace with Profiler on Microsoft SQL Server 2000
To enable additional tracing, use SQL Query Analyzer to run the following script against the master database.
DBCC Traceon (1204,3605 -1)
Note This trace flag captures more information about deadlocks in the SQL Server error logs. To view the SQL Server error logs, start SQL Enterprise Manager. Click Management, and then click SQL Server Logs
H, SQLDIAG diagnostic tool
TechKnowledge Content
The information in this document applies to:
- Great Plains on Microsoft SQL Server
SUMMARY
This article describes the SQLDIAG tool and what it is used for.
MORE INFORMATION
The SQLDIAG tool gathers and stores diagnostic information into \MSSQL7\LOG\SQLDIAG.txt (Microsoft SQL Server 7.0) or \Program Files\Microsoft SQL Server\MSSQL\LOG\SQLDIAG.txt (Microsoft SQL Server 2000). This output file includes error logs, output from sp_configure and additional version information. Sqldiag must be run on the server itself from an MS-DOS window.
The syntax for a DEFAULT instance of SQL Server is as follows:
sqldiag -U sa -P password
For example, if the Login ID is sa and the password is access, you would type the following at the DOS prompt:
sqldiag -U sa -P access
If you have a named instance of SQL Server, the following syntax is as follows:
sqldiag -U sa -P password -i Instance Name
For example, if the SQL Server instance was named SERVER\SQL2000, you would type the following at the DOS prompt:
sqldiag -U sa -P access -i SQL2000
You should now be able to locate the sqldiag.txt and view the diagnostic information. If the SQL Server is running, sqldiag gathers the following items:
- Text of all error logs
- Registry information
- dll version information
- Output from: sp_configure, sp_who, sp_lock, sp_helpdb_, xp_msver, sp_helpextendedproc, sysprocesses
- Input buffer SPIDs/deadlock information
- Server Diagnostics Report (processor list, memory report, services report, environment report, system report)
https://mbs.microsoft.com/customersource/support/documentation/systemrequirements
B , GP SP
https://mbs.microsoft.com/customersource/support/downloads/servicepacks
C, Posting table for posting slow performance
If you experience performance issues when you post, run the following SELECT statement on the PJOURNAL table. Run the statement against all the company databases.
SELECT * FROM PJOURNAL
If rows are returned, we recommend that you clear the contents of the table by running the following statement against all the company databases.
DELETE PJOURNAL
D, Enable delete Pjournal Job
INTRODUCTION
This article describes how to use Microsoft SQL Server Enterprise Manager to enable the Delete PJournal job that is created during the installation of Microsoft Dynamics GP or of Microsoft Business Solutions - Great Plains. When you enable the Delete PJournal job, the speed of the posting process in the program increases.
MORE INFORMATION
To enable the Delete PJournal job in Enterprise Manager, follow these steps: 1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the instance of SQL Server for Microsoft Dynamics GP or for Microsoft Business Solutions - Great Plains.
3. Expand Management, expand SQL Server Agent, and then click Jobs.
4. Right-click Remove Posted PJournals for your company database, and then click Properties.
5. Click to select the Enabled check box, click Apply, and then click OK.
Note By default, this job is specified to run every 30 minutes. You can change the frequency and the time of the job execution. To do this, follow these steps:1. Right-click Remove Posted PJournals for your company database, and then click Properties.
2. Click the Schedules tab.
3. Click Edit.
E, Performance issues that occur when you open windows.
1. Open the user preferences. To do this, follow the appropriate step:• In Microsoft Dynamics GP 10.0, click Microsoft Dynamics GP, and then click User Preferences.
• In Microsoft Dynamics GP 9.0 and in earlier versions, click Tools, point to Setup, and then click User Preferences.
2. Click AutoComplete.
3. Click to clear the Show AutoComplete Suggestions check box, and then click OK.
4. In Windows Explorer, delete the AutoCmpl.dat file and the AutoCmpl.idx file. These files are in the following folders: • In Microsoft Dynamics GP 10.0, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Microsoft Dynamics GP\dbname\
• In Microsoft Dynamics GP 9.0 and in earlier versions, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Great Plains\dbname\
5. Repeat step 1 through step 5 for each user.
F, Performance issues that occur when you log on to Microsoft Dynamics GP
Performance issues that occur when you log on to Microsoft Dynamics GP
1. Make sure that you have Microsoft Dynamics GP 9.0 Service Pack 2 or a later version installed. Logon issues were caused by an issue in which the Menu Master table (SY07110) became too large. To obtain the latest service pack for Microsoft Dynamics GP, visit the following Microsoft Web site:
https://mbs.microsoft.com/customersource/support/downloads/servicepacks/microsoftdynamicsgp90_mspreleases.htm?printpage=false
2. The location of the modified Reports.dic file and the modified Forms.dic file may affect the logon performance. If the modified dictionaries are on a network share, copy the dictionaries to the local Microsoft Dynamics GP folder, and then try to log on. To do this, follow these steps:a. To verify the location of the modified dictionaries, locate the Microsoft Dynamics GP folder. By default, this folder is in the following location:
C:\Program Files\Microsoft Dynamics\GP
b. Right-click the Dynamics.set file, and then open the Dynamics.set file in a text editor such as Notepad or WordPad.
c. Locate the path of the Reports.dic file and of the Forms.dic file. If the path is located on a network share, change the path to a local path.
3. Certain SmartList reminders may cause logon issues, depending on the homepage role for that user. To verify the reminders for a user, use the appropriate step:• In Microsoft Dynamics GP 9.0, click View, and then click Reminders. At the bottom, click Change Reminder Preferences. Remove any reminders in the Custom Reminders section at the bottom of the window.
• In Microsoft Dynamics GP 10.0, click Microsoft Dynamics GP, and then click Reminders. Click Change Reminder Preferences, and then remove the reminders in the Custom Reminder section at the bottom of the window.
G, Virus Scanner , exclude SQl database files and GP files and location
H, Sources of additional troubleshooting information
The System Information tool
Run the System Information tool to obtain the hardware specifications of the following computers: • The computer that is running SQL Server
• The client workstations
• The computer that is running Terminal Server
To obtain this information, follow these steps on each computer:1. Click Start, click Run, type msinfo32, and then click OK.
2. Click File, and then click Save to save this information to a file.
Trace logs
Create a Dexsql.log file and a SQL trace when you reproduce the performance issue. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
850996 How to create a Dexsql.log file for Microsoft Dynamics GP
857246 How to create a SQL Trace with Profiler on Microsoft SQL Server 2000
To enable additional tracing, use SQL Query Analyzer to run the following script against the master database.
DBCC Traceon (1204,3605 -1)
Note This trace flag captures more information about deadlocks in the SQL Server error logs. To view the SQL Server error logs, start SQL Enterprise Manager. Click Management, and then click SQL Server Logs
H, SQLDIAG diagnostic tool
TechKnowledge Content
The information in this document applies to:
- Great Plains on Microsoft SQL Server
SUMMARY
This article describes the SQLDIAG tool and what it is used for.
MORE INFORMATION
The SQLDIAG tool gathers and stores diagnostic information into \MSSQL7\LOG\SQLDIAG.txt (Microsoft SQL Server 7.0) or \Program Files\Microsoft SQL Server\MSSQL\LOG\SQLDIAG.txt (Microsoft SQL Server 2000). This output file includes error logs, output from sp_configure and additional version information. Sqldiag must be run on the server itself from an MS-DOS window.
The syntax for a DEFAULT instance of SQL Server is as follows:
sqldiag -U sa -P password
For example, if the Login ID is sa and the password is access, you would type the following at the DOS prompt:
sqldiag -U sa -P access
If you have a named instance of SQL Server, the following syntax is as follows:
sqldiag -U sa -P password -i Instance Name
For example, if the SQL Server instance was named SERVER\SQL2000, you would type the following at the DOS prompt:
sqldiag -U sa -P access -i SQL2000
You should now be able to locate the sqldiag.txt and view the diagnostic information. If the SQL Server is running, sqldiag gathers the following items:
- Text of all error logs
- Registry information
- dll version information
- Output from: sp_configure, sp_who, sp_lock, sp_helpdb_, xp_msver, sp_helpextendedproc, sysprocesses
- Input buffer SPIDs/deadlock information
- Server Diagnostics Report (processor list, memory report, services report, environment report, system report)
GP application path in registry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Great Plains\8.0\Install
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Business Solutions\Great Plains\1033\Inst01\SETUP\Apppath
Report Writer-Modify 12 check 12 lines default.
Report writer, Tools>Section>>Addtional Header >> Change 12 lines
GP-Delete User from Backend
Use dynamics
delete ACTIVITY where USERID = 'XXXX'
delete SY01400 where USERID = 'XXXX'
delete SY02000 where USERID = 'XXXX'
delete SY60100 where USERID = 'XXXX'
delete sy10500 where USERID = 'XXXX'
then manually delete SQL security login
In GP10 security table was integrated in view sy10000
delete ACTIVITY where USERID = 'XXXX'
delete SY01400 where USERID = 'XXXX'
delete SY02000 where USERID = 'XXXX'
delete SY60100 where USERID = 'XXXX'
delete sy10500 where USERID = 'XXXX'
then manually delete SQL security login
In GP10 security table was integrated in view sy10000
Econnect account permission and how to change account
The user account that is specified in the eConnect 10 for Microsoft Dynamics GP COM+ component must be a member of the DYNGRP role for the DYNAMICS database and for the company databases in SQL Server. This user account must be a member of the DYNGRP role to have the Execute permission for the required eConnect SQL stored procedures.
To determine which user account is specified in the eConnect 10 for Microsoft Dynamics GP COM+ component, follow these steps: 1. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.
2. In Component Services, expand Component Services, expand Computers, expand My Computer, and then expand COM+ Applications.
3. Right-click eConnect 10 for Microsoft Dynamics GP, and then click Properties.
4. Click the Identity tab.
The user account that is configured under This user is the user account that connects to SQL Server.
To determine which user account is specified in the eConnect 10 for Microsoft Dynamics GP COM+ component, follow these steps: 1. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.
2. In Component Services, expand Component Services, expand Computers, expand My Computer, and then expand COM+ Applications.
3. Right-click eConnect 10 for Microsoft Dynamics GP, and then click Properties.
4. Click the Identity tab.
The user account that is configured under This user is the user account that connects to SQL Server.
Gp-How to change decimal point from more to less
----- Change Open transactions decdimal point ----
1. In Query Analyzer, run the script below:
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'DECPLCUR'
order by o.name
** This script will search the tables that have the DECPLCUR (Currency Decimal Places) as a column name.
2. List down the tables with Inventory (IV), Invoicing (IVC), Sales Order Processing (SOP) or Purchase Order Processing (POP) prefixes. We need to update these associated tables with the correct currency decimal places.
3. Update TABLENAME set DECPLCUR = '0' where ITEMNMBR = 'xxx'
(where xxx is the Item number of the item we need to increase)
Currency Decimal Place field in SQL Query Analyzer:
1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places
Example:
If you wanted to have 5 Decimal Places, the Update statement is:
Update IV10001 set DECPLCUR = '6' where ITEMNMBR = 'plug in item number'
---- Change UoM
4. Create a new Unit of Measure schedule in Great Plains with the new currency decimals of the item. This will have to have the same Base unit and Equivalencies as the OLD unit of measure schedule because you have Work transactions that are using them.
5. Run the script below so you will know what table to update to change your item's Unit of Measure Schedule in the back end.
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'UOMSCHDL'
order by o.name
6. Update TABLENAME set UOMSCHDL = 'plug in new Unit of Measure Schedule' where ITEMNMBR = 'plug in item number'
Example:
Since you wanted to have 5 quantity Decimal Places, the Update statement for a new unit of measure:
Update TABLENAME set UOMSCHDL = 'plug in new Unit of Measure Schedule' where ITEMNMBR = 'plug in item number'
7. The steps below may be taken care of with the Check Links file maintenance procedure (when run against IV, SOP and POP), but just to make sure we can also run the following steps:
In Query Analyzer, run the script below:
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'DECPLQTY'
order by o.name
** This script will search the tables that have the DECPLQTY (Quantity Decimal Places) as a column name.
8. List down the tables with Inventory (IV), Invoicing (IVC), Sales Order Processing (SOP) or Purchase Order Processing (POP) prefixes. We need to update these associated tables with the correct currency decimal places.
9. Update TABLENAME set DECPLQTY = '0' where ITEMNMBR = 'xxx'
(where xxx is the Item number of the item we need to increase)
Currency Decimal Place field in SQL Query Analyzer:
1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places
Example:
If you wanted to have 5 Decimal Places, the Update statement is:
Update IV10001 set DECPLQTY = '6' where ITEMNMBR = 'plug in item number'
10. Run Check Links on the Inventory, Sales and Purchasing Series to make sure that the transactions are updated with the correct Decimal Places.
1. In Query Analyzer, run the script below:
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'DECPLCUR'
order by o.name
** This script will search the tables that have the DECPLCUR (Currency Decimal Places) as a column name.
2. List down the tables with Inventory (IV), Invoicing (IVC), Sales Order Processing (SOP) or Purchase Order Processing (POP) prefixes. We need to update these associated tables with the correct currency decimal places.
3. Update TABLENAME set DECPLCUR = '0' where ITEMNMBR = 'xxx'
(where xxx is the Item number of the item we need to increase)
Currency Decimal Place field in SQL Query Analyzer:
1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places
Example:
If you wanted to have 5 Decimal Places, the Update statement is:
Update IV10001 set DECPLCUR = '6' where ITEMNMBR = 'plug in item number'
---- Change UoM
4. Create a new Unit of Measure schedule in Great Plains with the new currency decimals of the item. This will have to have the same Base unit and Equivalencies as the OLD unit of measure schedule because you have Work transactions that are using them.
5. Run the script below so you will know what table to update to change your item's Unit of Measure Schedule in the back end.
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'UOMSCHDL'
order by o.name
6. Update TABLENAME set UOMSCHDL = 'plug in new Unit of Measure Schedule' where ITEMNMBR = 'plug in item number'
Example:
Since you wanted to have 5 quantity Decimal Places, the Update statement for a new unit of measure:
Update TABLENAME set UOMSCHDL = 'plug in new Unit of Measure Schedule' where ITEMNMBR = 'plug in item number'
7. The steps below may be taken care of with the Check Links file maintenance procedure (when run against IV, SOP and POP), but just to make sure we can also run the following steps:
In Query Analyzer, run the script below:
Select * from sysobjects o, syscolumns c where o.id = c.id and o.type = 'U'
and c.name = 'DECPLQTY'
order by o.name
** This script will search the tables that have the DECPLQTY (Quantity Decimal Places) as a column name.
8. List down the tables with Inventory (IV), Invoicing (IVC), Sales Order Processing (SOP) or Purchase Order Processing (POP) prefixes. We need to update these associated tables with the correct currency decimal places.
9. Update TABLENAME set DECPLQTY = '0' where ITEMNMBR = 'xxx'
(where xxx is the Item number of the item we need to increase)
Currency Decimal Place field in SQL Query Analyzer:
1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places
Example:
If you wanted to have 5 Decimal Places, the Update statement is:
Update IV10001 set DECPLQTY = '6' where ITEMNMBR = 'plug in item number'
10. Run Check Links on the Inventory, Sales and Purchasing Series to make sure that the transactions are updated with the correct Decimal Places.
GP-How to troubleshooting version
1. A copy of the GP_LoginErrors.log file. This file was not attached if it was intended to attach.
2. Script results of the following:
sp_helpdb
select * from DB_Upgrade
select * from DB_DU000020
select CMPANYID, INTERID, CMPNYNAM from DYNAMICS..SY01500
SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber
AND a.PRODID = b.PRODID
JOI
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15
3. A copy of the DYNAMICS.set file from the SQL Server. This file can be found by default in the path:
C:\Program Files\Microsoft Dynamics\GP\
2. Script results of the following:
sp_helpdb
select * from DB_Upgrade
select * from DB_DU000020
select CMPANYID, INTERID, CMPNYNAM from DYNAMICS..SY01500
SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber
AND a.PRODID = b.PRODID
JOI
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15
3. A copy of the DYNAMICS.set file from the SQL Server. This file can be found by default in the path:
C:\Program Files\Microsoft Dynamics\GP\
IM upload sequence
the solution was to open up the IM.ini file found in the C:/program files/dynamics/Integration Manager, change the following to "false":
UseOptimizedFiltering=False
UseOptimizedFiltering=False
SQL- How to find transactions that are posted to Inventory but not to General Ledger in Microsoft Dynamics GP
To find the transactions, run a script in Microsoft SQL Server 2005 Management Studio or in SQL Query Analyzer.
select * from IV30100 where TRXSORCE
NOT IN (select ORTRXSRC from GL20000) and BCHSOURC = 'IV_Trxent'
Click Query, and then click Execute.
The script in Method 2 can be used to look in the "Year-to-Date Transaction Open" table (GL20000). The "Year-to-Date Transaction Open" table (GL20000) contains open posted transactions in Financials and in General Ledger. If you want to look in the Transaction Work table (GL10000) or in the Account Transaction History table (GL30000), change GL20000 to GL10000 or to GL30000 in the script.
If you want to look for inventory transfers, change "BCHSOURC = 'IV_Trxent'" to "BCHSOURC='IV_Trans'." "BCHSOURC='IV_Trans'" indicates that the transactions originate from the Inventory Transfer Entry window and that the transactions are batch posted.
If you change "BCHSOURC = 'IV_Trxent'" to "BCHSOURC='IV_Trans'," the complete script resembles the following script.
select * from IV30100 where TRXSORCE
NOT IN (select ORTRXSRC from GL20000) and BCHSOURC=’IV_Trans’
SQL-How to identify datatable size.
SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc
NAV- Create/Configure New NAV application services
Create a second service by running the sc command at the command prompt. When you run this command, you create a second service that appears in the Services snap-in in Control Panel.
----------------------------------
To create a second service
----------------------------------
#1, In Control Panel, click Administrative Tools, and then click Services to open the Services snap-in.
#2, In the list of services, right-click Microsoft Dynamics NAV Server, and then click Stop.
#3, Navigate to the Microsoft Dynamics NAV Service directory. This directory is typically located at C:\Program Files\Microsoft Dynamics NAV\60\.
#4, Copy the service directory and paste it into the same directory. Rename the copy from Copy of Service to Service2.
#5, At the command prompt, type the sc command to create a new service. Copy the following command and paste it at the command prompt. Replace <computername> with the actual computer name.
Copy Code
sc \\<computername> create MicrosoftDynamicsNAVServer$NAV2 binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service2\Microsoft.Dynamics.Nav.Server.exe $NAV2" DisplayName= "Microsoft Dynamics NAV Server Instance 2" start= auto type= own depend= NetTcpPortSharing
Note
You can also specify a login account for the service as part of the sc command using the obj parameter, such as:
Copy Code
obj= "NT Authority\NetworkService"
If the command succeeds, then you receive the following message:
[SC] CreateService SUCCESS
#6, In the Services snap-in, you see your second service, Microsoft Dynamics NAV Server Instance 2, in the list.
#7, Double-click the new service to open a Properties dialog box for the new service, and then click the Log On tab.
#8, The default setting is for the service to log on with the Local System Account. Setup configures the original Microsoft Dynamics NAV Server service to use the Network Service account. This walkthrough also uses the Network Service account for the second service. For improved security, you should use a dedicated domain user account. For information on how to configure a Microsoft Dynamics NAV Server service to use a domain user account, see Walkthrough: Installing the Three Tiers on Three Computers.
Click This account on the Log On tab.
Type NT Authority\NetworkService in the first text box.
Clear the entries in the Password and Confirm password fields.
Click OK to exit the Properties dialog box for the new service.
#9, Right-click Microsoft Dynamics NAV Server Instance 2, and then click Start to verify that the service is configured correctly.
Right-click Microsoft Dynamics NAV Server Instance 2 again, and then click Stop.
--------------------------------------
Configuring a Second Service
--------------------------------------
To create a name for the server instance
#1, Use Notepad or another text editor and open the CustomSettings.config file in the Service2 directory. The default path for this file is C:\Program Files\Microsoft Dynamics NAV\60\Service2\Custom.Settings.config.
#2, Locate the following line:
<add key="ServerInstance" value="DynamicsNAV" />
Replace DynamicsNAV with DynamicsNAV2, which is the name for your new server instance.
#3, Save your changes, and then close the editor.
To activate port sharing
#1, Return to the Services snap-in.
#2, Right-click Net.Tcp Port Sharing Service, and then click Properties.
#3, On the General tab, set Startup type to Manual.
#4, Click OK to close the Properties window.
#5, Right-click Net.Tcp Port Sharing, and then click Start.
You have now activated port sharing over the TCP/IP protocol.
**** Note
You can also specify a separate port number for each Microsoft Dynamics NAV Server service. You then do not need to implement TCP port sharing.
It is not possible to run the Microsoft Dynamics NAV Server service with port sharing if the service is running on a domain user account. If you need to run two Microsoft Dynamics NAV Server services on the same computer, then you must run the services with the Network Service account.
Recreate the Original Microsoft Dynamics NAV Server Service with TCP Port Sharing
To reconfigure the original Microsoft Dynamics NAV Server service to use TCP port sharing, you must use the sc command to delete the service and then use it again to recreate the service with the proper configuration.
To recreate the original Microsoft Dynamics NAV Server service
At the command prompt, type the following command to delete the Microsoft Dynamics NAV Server service:
Copy Code
sc delete MicrosoftDynamicsNavServer
Note
If you attempt to delete a service while it is running, then the service is marked for deletion but is not deleted until you restart the computer.
At the command prompt, recreate the original Microsoft Dynamics NAV Server service with the option for TCP port sharing:
Copy Code
sc \\<computername> create MicrosoftDynamicsNAVServer binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service\Microsoft.Dynamics.Nav.Server.exe" DisplayName= "Microsoft Dynamics NAV Server" start= auto type= own depend= NetTcpPortSharing
If the service has been created successfully, then you receive the following message:
Copy Code
[SC] CreateService SUCCESS
If the Services snap-in is still open, then close it and reopen it to update the information in the snap-in. Otherwise, reopen the Services snap-in.
Double-click Microsoft Dynamics NAV Server to open the Properties dialog box for the service, and then click the Log On tab.
Click This account on the Log On tab.
Type NT Authority\NetworkService in the first text box.
Clear the entries in both the Password and Confirm password fields.
Click OK to exit the Properties dialog box for the new service.
In the Services snap-in, right-click Microsoft Dynamics NAV Server, and then click Start.
Right-click Microsoft Dynamics NAV Server Instance 2, and then click Start.
You now have two instances of the Microsoft Dynamics NAV Server service running on the same computer.
Testing the Second Service
Simon wants to test his second service by connecting to it from the RoleTailored client and by making a change to the data in one database.
To connect the RoleTailored client to a second service.
Open the RoleTailored client that you have installed on a separate computer.
In the Role Center menu bar, click Microsoft Dynamics NAV, and then click Select Server.
In Server Name, add the number 2 to the DynamicsNAV value so that it is DynamicsNAV2.
Click Connect.
Select CRONUS International Ltd. from the Companies list.
Unless you have already installed additional databases, the demo database is the only one available and is automatically selected.
Click Connect, and then start working with your database.
Next Steps
Simon is satisfied that the two Microsoft Dynamics NAV Server services are working correctly. The next steps that he could take are:
Setting up two SQL Server databases on the computer running SQL Server.
Configuring the two Microsoft Dynamics NAV Server services to connect to different databases.
----------------------------------
To create a second service
----------------------------------
#1, In Control Panel, click Administrative Tools, and then click Services to open the Services snap-in.
#2, In the list of services, right-click Microsoft Dynamics NAV Server, and then click Stop.
#3, Navigate to the Microsoft Dynamics NAV Service directory. This directory is typically located at C:\Program Files\Microsoft Dynamics NAV\60\.
#4, Copy the service directory and paste it into the same directory. Rename the copy from Copy of Service to Service2.
#5, At the command prompt, type the sc command to create a new service. Copy the following command and paste it at the command prompt. Replace <computername> with the actual computer name.
Copy Code
sc \\<computername> create MicrosoftDynamicsNAVServer$NAV2 binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service2\Microsoft.Dynamics.Nav.Server.exe $NAV2" DisplayName= "Microsoft Dynamics NAV Server Instance 2" start= auto type= own depend= NetTcpPortSharing
Note
You can also specify a login account for the service as part of the sc command using the obj parameter, such as:
Copy Code
obj= "NT Authority\NetworkService"
If the command succeeds, then you receive the following message:
[SC] CreateService SUCCESS
#6, In the Services snap-in, you see your second service, Microsoft Dynamics NAV Server Instance 2, in the list.
#7, Double-click the new service to open a Properties dialog box for the new service, and then click the Log On tab.
#8, The default setting is for the service to log on with the Local System Account. Setup configures the original Microsoft Dynamics NAV Server service to use the Network Service account. This walkthrough also uses the Network Service account for the second service. For improved security, you should use a dedicated domain user account. For information on how to configure a Microsoft Dynamics NAV Server service to use a domain user account, see Walkthrough: Installing the Three Tiers on Three Computers.
Click This account on the Log On tab.
Type NT Authority\NetworkService in the first text box.
Clear the entries in the Password and Confirm password fields.
Click OK to exit the Properties dialog box for the new service.
#9, Right-click Microsoft Dynamics NAV Server Instance 2, and then click Start to verify that the service is configured correctly.
Right-click Microsoft Dynamics NAV Server Instance 2 again, and then click Stop.
--------------------------------------
Configuring a Second Service
--------------------------------------
To create a name for the server instance
#1, Use Notepad or another text editor and open the CustomSettings.config file in the Service2 directory. The default path for this file is C:\Program Files\Microsoft Dynamics NAV\60\Service2\Custom.Settings.config.
#2, Locate the following line:
<add key="ServerInstance" value="DynamicsNAV" />
Replace DynamicsNAV with DynamicsNAV2, which is the name for your new server instance.
#3, Save your changes, and then close the editor.
To activate port sharing
#1, Return to the Services snap-in.
#2, Right-click Net.Tcp Port Sharing Service, and then click Properties.
#3, On the General tab, set Startup type to Manual.
#4, Click OK to close the Properties window.
#5, Right-click Net.Tcp Port Sharing, and then click Start.
You have now activated port sharing over the TCP/IP protocol.
**** Note
You can also specify a separate port number for each Microsoft Dynamics NAV Server service. You then do not need to implement TCP port sharing.
It is not possible to run the Microsoft Dynamics NAV Server service with port sharing if the service is running on a domain user account. If you need to run two Microsoft Dynamics NAV Server services on the same computer, then you must run the services with the Network Service account.
Recreate the Original Microsoft Dynamics NAV Server Service with TCP Port Sharing
To reconfigure the original Microsoft Dynamics NAV Server service to use TCP port sharing, you must use the sc command to delete the service and then use it again to recreate the service with the proper configuration.
To recreate the original Microsoft Dynamics NAV Server service
At the command prompt, type the following command to delete the Microsoft Dynamics NAV Server service:
Copy Code
sc delete MicrosoftDynamicsNavServer
Note
If you attempt to delete a service while it is running, then the service is marked for deletion but is not deleted until you restart the computer.
At the command prompt, recreate the original Microsoft Dynamics NAV Server service with the option for TCP port sharing:
Copy Code
sc \\<computername> create MicrosoftDynamicsNAVServer binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service\Microsoft.Dynamics.Nav.Server.exe" DisplayName= "Microsoft Dynamics NAV Server" start= auto type= own depend= NetTcpPortSharing
If the service has been created successfully, then you receive the following message:
Copy Code
[SC] CreateService SUCCESS
If the Services snap-in is still open, then close it and reopen it to update the information in the snap-in. Otherwise, reopen the Services snap-in.
Double-click Microsoft Dynamics NAV Server to open the Properties dialog box for the service, and then click the Log On tab.
Click This account on the Log On tab.
Type NT Authority\NetworkService in the first text box.
Clear the entries in both the Password and Confirm password fields.
Click OK to exit the Properties dialog box for the new service.
In the Services snap-in, right-click Microsoft Dynamics NAV Server, and then click Start.
Right-click Microsoft Dynamics NAV Server Instance 2, and then click Start.
You now have two instances of the Microsoft Dynamics NAV Server service running on the same computer.
Testing the Second Service
Simon wants to test his second service by connecting to it from the RoleTailored client and by making a change to the data in one database.
To connect the RoleTailored client to a second service.
Open the RoleTailored client that you have installed on a separate computer.
In the Role Center menu bar, click Microsoft Dynamics NAV, and then click Select Server.
In Server Name, add the number 2 to the DynamicsNAV value so that it is DynamicsNAV2.
Click Connect.
Select CRONUS International Ltd. from the Companies list.
Unless you have already installed additional databases, the demo database is the only one available and is automatically selected.
Click Connect, and then start working with your database.
Next Steps
Simon is satisfied that the two Microsoft Dynamics NAV Server services are working correctly. The next steps that he could take are:
Setting up two SQL Server databases on the computer running SQL Server.
Configuring the two Microsoft Dynamics NAV Server services to connect to different databases.
SQL- How to get Extender table script (or other tables started with ***)
SELECT '/* ' + RTRIM(TABLE_NAME) + '*/ SELECT ''' + RTRIM(TABLE_NAME) + ''' AS Extender_Table, * FROM ' + RTRIM(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'EXT%'
how to get retrieving dynamics GP user idle time.
SELECT CASE WHEN S.session_id is null THEN 'Missing DEX_SESSION' WHEN A.USERID <> P.loginame or P.loginame is null THEN 'Phantom' ELSE '' END MismatchOnUserID, CASE WHEN datediff ( mi, P.last_batch, getdate() ) > 90 THEN 'Idle ' + str ( datediff ( mi, P.last_batch, getdate() ) ) ELSE '' END AS Working , A.USERID , A.CMPNYNAM , INTERID , LOGINDAT + LOGINTIM LoginDatestamp , SQLSESID , P.login_time , P.last_batch , datediff ( mi, P.last_batch, getdate() ) SinceLastAction , S.session_id , S.sqlsvr_spid , P.spid , P.status , P.net_address , P.dbid , P.hostname , P.loginameFROM DYNAMICS..ACTIVITY A LEFT JOIN DYNAMICS..SY01400 U on A.USERID = U.USERID LEFT JOIN DYNAMICS..SY01500 C on A.CMPNYNAM = C.CMPNYNAM LEFT JOIN tempdb..DEX_SESSION S on A.SQLSESID = S.session_id LEFT JOIN master..sysprocesses P on S.sqlsvr_spid = P.spid and ecid = 0 LEFT JOIN master..sysdatabases D on P.dbid = D.dbid
SQL-finding column in tables
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
SQL-ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS database referencing databases that no longer exist on the SQL Server.
/* ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS
database referencing databases that no longer exist on the SQL Server.
Requirements:
Company database you wish to have cleaned out of the tables in the DYNAMICS
database must be removed from the SQL server before running this script.
Ensure that all your databases have been restored or they will be erased
from the DYNAMICS database.
*/
set nocount on
/* Remove all references in the company master (SY01500) for databases that
Do not exist on the SQL Server */
delete DYNAMICS..SY01500 where INTERID not in
(select name from master..sysdatabases)
/* Clear out all tables in DYNAMICS database that have a CMPANYID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @CMPANYID char(150)
declare CMPANYID_Cleanup CURSOR for
select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'
+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'CMPANYID'
and o.name <> 'SY01500' order by o.name
OPEN CMPANYID_Cleanup
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
while (@@FETCH_STATUS <>-1)
begin
exec (@CMPANYID)
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
end
DEALLOCATE CMPANYID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a companyID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @companyID char(150)
declare companyID_Cleanup CURSOR for
select 'delete ' + o.name + ' where companyID not in (0,-32767)'
+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'companyID'
and o.name <> 'SY01500'
set nocount on
OPEN companyID_Cleanup
FETCH NEXT from companyID_Cleanup into @companyID
while (@@FETCH_STATUS <>-1)
begin
exec (@companyID)
FETCH NEXT from companyID_Cleanup into @companyID
end
DEALLOCATE companyID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a db_name field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @db_name char(150)
declare db_name_Cleanup CURSOR for
select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'' and db_name <> ''''
and db_name not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'db_name'
set nocount on
OPEN db_name_Cleanup
FETCH NEXT from db_name_Cleanup into @db_name
while (@@FETCH_STATUS <>-1)
begin
exec (@db_name)
FETCH NEXT from db_name_Cleanup into @db_name
end
DEALLOCATE db_name_Cleanup
GO
set nocount on
/* Clear out all tables in DYNAMICS database that have a dbname field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @dbname char(150)
declare dbname_Cleanup CURSOR for
select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''
and DBNAME not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'DBNAME'
set nocount on
OPEN dbname_Cleanup
FETCH NEXT from dbname_Cleanup into @dbname
while (@@FETCH_STATUS <>-1)
begin
exec (@dbname)
FETCH NEXT from dbname_Cleanup into @dbname
end
DEALLOCATE dbname_Cleanup
GO
set nocount on
/* Remove all stranded references from the other Business Alerts table that
no longer exist in the SY40500 */
delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
GO
database referencing databases that no longer exist on the SQL Server.
Requirements:
Company database you wish to have cleaned out of the tables in the DYNAMICS
database must be removed from the SQL server before running this script.
Ensure that all your databases have been restored or they will be erased
from the DYNAMICS database.
*/
set nocount on
/* Remove all references in the company master (SY01500) for databases that
Do not exist on the SQL Server */
delete DYNAMICS..SY01500 where INTERID not in
(select name from master..sysdatabases)
/* Clear out all tables in DYNAMICS database that have a CMPANYID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @CMPANYID char(150)
declare CMPANYID_Cleanup CURSOR for
select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'
+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'CMPANYID'
and o.name <> 'SY01500' order by o.name
OPEN CMPANYID_Cleanup
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
while (@@FETCH_STATUS <>-1)
begin
exec (@CMPANYID)
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
end
DEALLOCATE CMPANYID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a companyID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @companyID char(150)
declare companyID_Cleanup CURSOR for
select 'delete ' + o.name + ' where companyID not in (0,-32767)'
+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'companyID'
and o.name <> 'SY01500'
set nocount on
OPEN companyID_Cleanup
FETCH NEXT from companyID_Cleanup into @companyID
while (@@FETCH_STATUS <>-1)
begin
exec (@companyID)
FETCH NEXT from companyID_Cleanup into @companyID
end
DEALLOCATE companyID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a db_name field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @db_name char(150)
declare db_name_Cleanup CURSOR for
select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'' and db_name <> ''''
and db_name not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'db_name'
set nocount on
OPEN db_name_Cleanup
FETCH NEXT from db_name_Cleanup into @db_name
while (@@FETCH_STATUS <>-1)
begin
exec (@db_name)
FETCH NEXT from db_name_Cleanup into @db_name
end
DEALLOCATE db_name_Cleanup
GO
set nocount on
/* Clear out all tables in DYNAMICS database that have a dbname field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @dbname char(150)
declare dbname_Cleanup CURSOR for
select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''
and DBNAME not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'DBNAME'
set nocount on
OPEN dbname_Cleanup
FETCH NEXT from dbname_Cleanup into @dbname
while (@@FETCH_STATUS <>-1)
begin
exec (@dbname)
FETCH NEXT from dbname_Cleanup into @dbname
end
DEALLOCATE dbname_Cleanup
GO
set nocount on
/* Remove all stranded references from the other Business Alerts table that
no longer exist in the SY40500 */
delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
GO
The beginning balance -- One sided entry KB 865712
SYMPTOMS
The beginning balance for the current year does not balance in the general ledger in Microsoft Dynamics GP or in Microsoft Business Solutions - Great Plains.
CAUSE
This problem may occur if the ending balance for the previous year does not balance.
RESOLUTION
To resolve this problem, follow these steps.
#1 make sure that you have a complete backup copy of the database that you can restore if a problem occurs. Additionally, make sure that all other users exit the company
#2 Open the Fiscal Periods Setup window.
#3 In the Fiscal Periods Setup window, open a period in the previous fiscal year, and then click OK.
#4 In the General Ledger Setup window, click to select the Posting to History check box. To do this, use the appropriate step:
In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Setup, point to Financial, and then click General Ledger.
In Microsoft Dynamics GP 9.0 or in earlier versions, point to Setup on the Tools menu, point to Financial, and then click General Ledger.
In the Maintain History area, click to clear the Accounts check box.
Click to clear the Transactions check box.
#5 Open the Multicurrency Setup window. In the Maintain History area, click to clear the General Ledger Account check box.
#6 Open the Account Maintenance window.Create a dummy account number in the Account Maintenance window. For example, type 999-999-99 in the Account box.
In the Posting Type area, click Balance Sheet. This step makes sure that the Retained Earnings account will not be updated when you close the year.
In the Category list, click Nonfinancial Accounts. This step makes sure that the account will not be included in the financial statements. The Nonfinancial Accounts category will keep this account off the Quick Financial statements. However, the account will appear in other General Ledger reports. Therefore, we recommend that you use a number sequence such as 9999-999-99 for the dummy account number so that you can exclude this account from most statements.
Click Save.
#7 On the Transactions menu, point to Financial, and then click General.
Enter a transaction, and then post the transaction to the account that is out of balance and to the dummy account that you created in step 9.
In the Transaction Date field, type a date for the transaction that is in the historical year.
Use the appropriate step:
If you use Microsoft SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
If you use SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
If you use SQL Server Desktop Engine (also known as MSDE 2000), start Support Administrator Console. To do this, click Start, point to Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.
Run the following statement against the company database.
select ACTINDX from GL00100 where ACTNUMBR_1 = XXX
Notes
The XXX placeholder represents the first three numbers of the account that you created in step 9.
Make sure that the account that you created in step 9 is the only account that is returned when you run this query.
To delete the transaction from the history, run the following statement against the company database.
delete GL20000 where ACTINDX = XXX
Note The XXX placeholder is the number that is returned from the query that you run in step 17.
To clear the balance information on the dummy account, you must run the Reconcile utility on the oldest open year. To do this, use one of the following methods:
In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Financial, and then click Reconcile.
In Microsoft Dynamics GP 9.0 or in earlier versions of Microsoft Dynamics GP, point to Utilities on the Tools menu, point to Financial, and then click Reconcile.
The transaction is now a one-sided transaction, and the balance should be correct.
To delete the dummy account, follow these steps:
On the Cards menu, point to Financial, and then click Account.
In the Account field, type the dummy account number.
Click Delete.
If you want to maintain history, follow these steps:
In the Maintain History area in the General Ledger Setup window, click to select the Accounts check box, and then click to select the Transactions check box.
In the Maintain History area in the Multicurrency Setup window, click to select the Accounts check box, and then click to select the Transactions check box.
Windows OS command list
Program Name Run Command
About Windows winver
Add a Device devicepairingwizard
Add Hardware Wizard hdwwiz
Advanced User Accounts netplwiz
Authorization Manager azman
Backup and Restore sdclt
Bluetooth File Transfer fsquirt
Calculator calc
Certificates certmgr
Change Computer Performance Settings systempropertiesperformance
Change Data Execution Prevention Settings systempropertiesdataexecutionprevention
Change Printer Settings printui
Character Map charmap
ClearType Tuner cttune
Color Management colorcpl
Command Prompt cmd
Component Services comexp
Component Services dcomcnfg
Computer Management compmgmt
Computer Management compmgmtlauncher
Connect to a Network Projector netproj
Connect to a Projector displayswitch
Control Panel control
Create A Shared Folder Wizard shrpubw
Create a System Repair Disc recdisc
Credential Backup and Restore Wizard credwiz
Data Execution Prevention systempropertiesdataexecutionprevention
Default Location locationnotifications
Device Manager devmgmt
Device Pairing Wizard devicepairingwizard
Diagnostics Troubleshooting Wizard msdt
Digitizer Calibration Tool tabcal
DirectX Diagnostic Tool dxdiag
Disk Cleanup cleanmgr
Disk Defragmenter dfrgui
Disk Management diskmgmt
Display dpiscaling
Display Color Calibration dccw
Display Switch displayswitch
DPAPI Key Migration Wizard dpapimig
Driver Verifier Manager verifier
Ease of Access Center utilman
EFS REKEY Wizard rekeywiz
Encrypting File System Wizard rekeywiz
Event Viewer eventvwr
Fax Cover Page Editor fxscover
File Signature Verification sigverif
Font Viewer fontview3
Getting Started gettingstarted
IExpress Wizard iexpress
Import to Windows Contacts wabmig1
Install or Uninstall Display Languages lusrmgr
Internet Explorer iexplore1
iSCSI Initiator Configuration Tool iscsicpl
iSCSI Initiator Properties iscsicpl
Language Pack Installer lpksetup
Local Group Policy Editor gpedit
Local Security Policy secpol
Local Users and Groups lusrmgr
Location Activity locationnotifications
Magnifier magnify
Malicious Software Removal Tool mrt
Manage Your File Encryption Certificates rekeywiz
Math Input Panel mip1
Microsoft Management Console mmc
Microsoft Support Diagnostic Tool msdt
NAP Client Configuration napclcfg
Narrator narrator
New Scan Wizard wiaacmgr
Notepad notepad
ODBC Data Source Administrator odbcad32
ODBC Driver Configuration odbcconf
On-Screen Keyboard osk
Paint mspaint
Performance Monitor perfmon
Performance Options systempropertiesperformance
Phone Dialer dialer
Presentation Settings presentationsettings
Print Management printmanagement
Printer Migration printbrmui
Printer User Interface printui
Private Character Editor eudcedit
Problem Steps Recorder psr
Protected Content Migration dpapimig
Registry Editor regedit
regedt324
Remote Access Phonebook rasphone
Remote Desktop Connection mstsc
Resource Monitor resmon
perfmon /res
Resultant Set of Policy rsop
Securing the Windows Account Database syskey
Services services
Set Program Access and Computer Defaults computerdefaults
Share Creation Wizard shrpubw
Shared Folders fsmgmt
Snipping Tool snippingtool
Sound Recorder soundrecorder
SQL Server Client Network Utility cliconfg
Sticky Notes stikynot
Stored User Names and Passwords credwiz
Sync Center mobsync
System Configuration msconfig
System Configuration Editor sysedit5
System Information msinfo32
System Properties (Advanced Tab) systempropertiesadvanced
System Properties (Computer Name Tab) systempropertiescomputername
System Properties (Hardware Tab) systempropertieshardware
System Properties (Remote Tab) systempropertiesremote
System Properties (System Protection Tab) systempropertiesprotection
System Restore rstrui
Tablet PC Input Panel tabtip1
Task Manager taskmgr
Task Scheduler taskschd
Trusted Platform Module (TPM) Management tpm
User Account Control Settings useraccountcontrolsettings
Utility Manager utilman
Version Reporter Applet winver
Volume Mixer sndvol
Windows Activation Client slui
Windows Anytime Upgrade Results windowsanytimeupgraderesults
Windows Contacts wab1
Windows Disc Image Burning Tool isoburn
Windows DVD Maker dvdmaker1
Windows Easy Transfer migwiz1
Windows Explorer explorer
Windows Fax and Scan wfs
Windows Features optionalfeatures
Windows Firewall with Advanced Security wf
Windows Help and Support winhlp32
Windows Journal journal1
Windows Media Player dvdplay2
wmplayer1
Windows Memory Diagnostic Scheduler mdsched
Windows Mobility Center mblctr
Windows Picture Acquisition Wizard wiaacmgr
Windows PowerShell powershell1
Windows PowerShell ISE powershell_ise1
Windows Remote Assistance msra
Windows Repair Disc recdisc
Windows Script Host wscript
Windows Update wuapp
Windows Update Standalone Installer wusa
WMI Management wmimgmt
WMI Tester wbemtest
WordPad write
XPS Viewer xpsrchvw
About Windows winver
Add a Device devicepairingwizard
Add Hardware Wizard hdwwiz
Advanced User Accounts netplwiz
Authorization Manager azman
Backup and Restore sdclt
Bluetooth File Transfer fsquirt
Calculator calc
Certificates certmgr
Change Computer Performance Settings systempropertiesperformance
Change Data Execution Prevention Settings systempropertiesdataexecutionprevention
Change Printer Settings printui
Character Map charmap
ClearType Tuner cttune
Color Management colorcpl
Command Prompt cmd
Component Services comexp
Component Services dcomcnfg
Computer Management compmgmt
Computer Management compmgmtlauncher
Connect to a Network Projector netproj
Connect to a Projector displayswitch
Control Panel control
Create A Shared Folder Wizard shrpubw
Create a System Repair Disc recdisc
Credential Backup and Restore Wizard credwiz
Data Execution Prevention systempropertiesdataexecutionprevention
Default Location locationnotifications
Device Manager devmgmt
Device Pairing Wizard devicepairingwizard
Diagnostics Troubleshooting Wizard msdt
Digitizer Calibration Tool tabcal
DirectX Diagnostic Tool dxdiag
Disk Cleanup cleanmgr
Disk Defragmenter dfrgui
Disk Management diskmgmt
Display dpiscaling
Display Color Calibration dccw
Display Switch displayswitch
DPAPI Key Migration Wizard dpapimig
Driver Verifier Manager verifier
Ease of Access Center utilman
EFS REKEY Wizard rekeywiz
Encrypting File System Wizard rekeywiz
Event Viewer eventvwr
Fax Cover Page Editor fxscover
File Signature Verification sigverif
Font Viewer fontview3
Getting Started gettingstarted
IExpress Wizard iexpress
Import to Windows Contacts wabmig1
Install or Uninstall Display Languages lusrmgr
Internet Explorer iexplore1
iSCSI Initiator Configuration Tool iscsicpl
iSCSI Initiator Properties iscsicpl
Language Pack Installer lpksetup
Local Group Policy Editor gpedit
Local Security Policy secpol
Local Users and Groups lusrmgr
Location Activity locationnotifications
Magnifier magnify
Malicious Software Removal Tool mrt
Manage Your File Encryption Certificates rekeywiz
Math Input Panel mip1
Microsoft Management Console mmc
Microsoft Support Diagnostic Tool msdt
NAP Client Configuration napclcfg
Narrator narrator
New Scan Wizard wiaacmgr
Notepad notepad
ODBC Data Source Administrator odbcad32
ODBC Driver Configuration odbcconf
On-Screen Keyboard osk
Paint mspaint
Performance Monitor perfmon
Performance Options systempropertiesperformance
Phone Dialer dialer
Presentation Settings presentationsettings
Print Management printmanagement
Printer Migration printbrmui
Printer User Interface printui
Private Character Editor eudcedit
Problem Steps Recorder psr
Protected Content Migration dpapimig
Registry Editor regedit
regedt324
Remote Access Phonebook rasphone
Remote Desktop Connection mstsc
Resource Monitor resmon
perfmon /res
Resultant Set of Policy rsop
Securing the Windows Account Database syskey
Services services
Set Program Access and Computer Defaults computerdefaults
Share Creation Wizard shrpubw
Shared Folders fsmgmt
Snipping Tool snippingtool
Sound Recorder soundrecorder
SQL Server Client Network Utility cliconfg
Sticky Notes stikynot
Stored User Names and Passwords credwiz
Sync Center mobsync
System Configuration msconfig
System Configuration Editor sysedit5
System Information msinfo32
System Properties (Advanced Tab) systempropertiesadvanced
System Properties (Computer Name Tab) systempropertiescomputername
System Properties (Hardware Tab) systempropertieshardware
System Properties (Remote Tab) systempropertiesremote
System Properties (System Protection Tab) systempropertiesprotection
System Restore rstrui
Tablet PC Input Panel tabtip1
Task Manager taskmgr
Task Scheduler taskschd
Trusted Platform Module (TPM) Management tpm
User Account Control Settings useraccountcontrolsettings
Utility Manager utilman
Version Reporter Applet winver
Volume Mixer sndvol
Windows Activation Client slui
Windows Anytime Upgrade Results windowsanytimeupgraderesults
Windows Contacts wab1
Windows Disc Image Burning Tool isoburn
Windows DVD Maker dvdmaker1
Windows Easy Transfer migwiz1
Windows Explorer explorer
Windows Fax and Scan wfs
Windows Features optionalfeatures
Windows Firewall with Advanced Security wf
Windows Help and Support winhlp32
Windows Journal journal1
Windows Media Player dvdplay2
wmplayer1
Windows Memory Diagnostic Scheduler mdsched
Windows Mobility Center mblctr
Windows Picture Acquisition Wizard wiaacmgr
Windows PowerShell powershell1
Windows PowerShell ISE powershell_ise1
Windows Remote Assistance msra
Windows Repair Disc recdisc
Windows Script Host wscript
Windows Update wuapp
Windows Update Standalone Installer wusa
WMI Management wmimgmt
WMI Tester wbemtest
WordPad write
XPS Viewer xpsrchvw
Undo bank reconciliatin
******************************
SQL queries:
1) Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):
select * from CM20500 where CHEKBKID = ‘XXXXX’
order by RECONUM
2) Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):
update CM20200 set Recond = 0 where RECONUM = ‘##.#####’
update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’
update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’ update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’
3) Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):
delete CM20500 where RECONUM = ‘##.#####’
4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):
update CM00100 set Last_Reconciled_Date = ‘2010-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’
update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’
**********************************
SQL queries:
1) Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):
select * from CM20500 where CHEKBKID = ‘XXXXX’
order by RECONUM
2) Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):
update CM20200 set Recond = 0 where RECONUM = ‘##.#####’
update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’
update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’ update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’
3) Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):
delete CM20500 where RECONUM = ‘##.#####’
4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):
update CM00100 set Last_Reconciled_Date = ‘2010-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’
update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’
**********************************
SQL-Search String '******' from all of tables which start from 'POP'
EXEC csp_SearchString 'Customer1', 'POP'
Use below to search customer1 from all tables
EXEC csp_SearchString 'Customer1', ''
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].csp_SearchString') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].csp_SearchString
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC csp_SearchString( @SearchStr nvarchar(100), @TableName_LikeString varchar(100) ='', @IsSelectString smallint =0 )
AS
BEGIN-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##CSTmp0001') IS NOT NULL
BEGIN
DROP TABLE ##CSTmp0001
END
CREATE TABLE ##CSTmp0001 (ColumnName nvarchar(370), ColumnValue nvarchar(max) , TblName nvarchar(370) DEFAULT '', ColName nvarchar(370) DEFAULT '', selectString varchar(max) DEFAULT '', DEX_ROW_ID INT identity(1,1) )
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
IF ISNULL(@TableName_LikeString,'') =''
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped'
) = 0
)
END
ELSE
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE ( @TableName_LikeString+'%' )
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped'
) = 0
)
END
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text' )
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO ##CSTmp0001(ColumnName, ColumnValue, TblName,ColName )
EXEC
(
-- 'SELECT TOP 1 ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName + ')
'SELECT TOP 1 ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName +') , ''' + @TableName + ''' , ''' + @ColumnName + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
UPDATE ##CSTmp0001
SET selectString ='SELECT * FROM '+TblName +' WHERE '+ColName +' = ''' + LTRIM(RTRIM(ColumnValue ))+' '''
IF ISNULL(@IsSelectString,0) =0
BEGIN
SELECT ColumnName, ColumnValue,selectString FROM ##CSTmp0001
END
ELSE
BEGIN
Declare @TotalCounts as int ,@i as int
SET @i =1
SET @TotalCounts =ISNULL( ( SELECT COUNT(*) FROM ##CSTmp0001 ),0)
WHILE @i <=@TotalCounts
BEGIN
--SELECT ColumnName, ColumnValue,selectString FROM ##CSTmp0001 WHERE DEX_ROW_ID =@i
declare @sql as nvarchar(max) ,@ParmDefinition as nvarchar(2024) ,@SqlConQuery as nvarchar(3000)
SET @ParmDefinition =N' @S as varchar(10) , @dot as varchar(10) '
Set @sql = ISNULL( ( SELECT REPLACE( selectString , '*' , 'TableColumn = '''+ColumnName+' '' , * ' ) FROM ##CSTmp0001 WHERE DEX_ROW_ID =@i ), '' )
EXECUTE sp_executesql @sql, @ParmDefinition, @S='' , @dot =','
--select @sql
SET @i = @i+1
END
END
END------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**
Declare @StartTime as DateTime
SET @StartTime =getdate()
EXEC csp_SearchString '09282010-AP40', 'POP'
SELECT DATEDIFF( minute, @StartTime, getdate()) as 'Execute Minutes'
***/
Use below to search customer1 from all tables
EXEC csp_SearchString 'Customer1', ''
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].csp_SearchString') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].csp_SearchString
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC csp_SearchString( @SearchStr nvarchar(100), @TableName_LikeString varchar(100) ='', @IsSelectString smallint =0 )
AS
BEGIN-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##CSTmp0001') IS NOT NULL
BEGIN
DROP TABLE ##CSTmp0001
END
CREATE TABLE ##CSTmp0001 (ColumnName nvarchar(370), ColumnValue nvarchar(max) , TblName nvarchar(370) DEFAULT '', ColName nvarchar(370) DEFAULT '', selectString varchar(max) DEFAULT '', DEX_ROW_ID INT identity(1,1) )
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
IF ISNULL(@TableName_LikeString,'') =''
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped'
) = 0
)
END
ELSE
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE ( @TableName_LikeString+'%' )
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped'
) = 0
)
END
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text' )
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO ##CSTmp0001(ColumnName, ColumnValue, TblName,ColName )
EXEC
(
-- 'SELECT TOP 1 ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName + ')
'SELECT TOP 1 ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName +') , ''' + @TableName + ''' , ''' + @ColumnName + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
UPDATE ##CSTmp0001
SET selectString ='SELECT * FROM '+TblName +' WHERE '+ColName +' = ''' + LTRIM(RTRIM(ColumnValue ))+' '''
IF ISNULL(@IsSelectString,0) =0
BEGIN
SELECT ColumnName, ColumnValue,selectString FROM ##CSTmp0001
END
ELSE
BEGIN
Declare @TotalCounts as int ,@i as int
SET @i =1
SET @TotalCounts =ISNULL( ( SELECT COUNT(*) FROM ##CSTmp0001 ),0)
WHILE @i <=@TotalCounts
BEGIN
--SELECT ColumnName, ColumnValue,selectString FROM ##CSTmp0001 WHERE DEX_ROW_ID =@i
declare @sql as nvarchar(max) ,@ParmDefinition as nvarchar(2024) ,@SqlConQuery as nvarchar(3000)
SET @ParmDefinition =N' @S as varchar(10) , @dot as varchar(10) '
Set @sql = ISNULL( ( SELECT REPLACE( selectString , '*' , 'TableColumn = '''+ColumnName+' '' , * ' ) FROM ##CSTmp0001 WHERE DEX_ROW_ID =@i ), '' )
EXECUTE sp_executesql @sql, @ParmDefinition, @S='' , @dot =','
--select @sql
SET @i = @i+1
END
END
END------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**
Declare @StartTime as DateTime
SET @StartTime =getdate()
EXEC csp_SearchString '09282010-AP40', 'POP'
SELECT DATEDIFF( minute, @StartTime, getdate()) as 'Execute Minutes'
***/
SQL- Assign blank default home page role to all users.
USE DYNAMICS
set nocount on
declare @Userid char(15)
declare cDispHP cursor for
select A.USERID
from SY01400 A left join SY08000 B on A.USERID = B.USERID
where B.USERID is null or B.DISPHP <> 0
open cDispHP
while 1 = 1
begin
fetch next from cDispHP into @Userid
if @@FETCH_STATUS <> 0 begin
close cDispHP
deallocate cDispHP
break
end
if exists (select DISPHP from DYNAMICS.dbo.SY08000 where USERID = @Userid)
begin
print 'adjusting ' + @Userid
update DYNAMICS.dbo.SY08000
set DISPHP = 0
where USERID = @Userid
end
else begin
print 'adding ' + @Userid
insert DYNAMICS.dbo.SY08000 ( USERID, DISPHP, REFRSHHP, User_Role )
values ( @Userid, 0, 0, 121 )
end
end /* while */
set nocount off
set nocount on
declare @Userid char(15)
declare cDispHP cursor for
select A.USERID
from SY01400 A left join SY08000 B on A.USERID = B.USERID
where B.USERID is null or B.DISPHP <> 0
open cDispHP
while 1 = 1
begin
fetch next from cDispHP into @Userid
if @@FETCH_STATUS <> 0 begin
close cDispHP
deallocate cDispHP
break
end
if exists (select DISPHP from DYNAMICS.dbo.SY08000 where USERID = @Userid)
begin
print 'adjusting ' + @Userid
update DYNAMICS.dbo.SY08000
set DISPHP = 0
where USERID = @Userid
end
else begin
print 'adding ' + @Userid
insert DYNAMICS.dbo.SY08000 ( USERID, DISPHP, REFRSHHP, User_Role )
values ( @Userid, 0, 0, 121 )
end
end /* while */
set nocount off
Line item Description wrap up to more lines.
Customer request a report modification to show all of line item descriptions. below is working solution.
Make three Calculated field CM_DESCIPTION_LINE_1,2,3
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 1)
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 2)
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 2)
Make three Calculated field CM_DESCIPTION_LINE_1,2,3
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 1)
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 2)
FUNCTION_SCRIPT(RW_ParseString SOP_LINE_WORK.Item.Description 50 2)
Check Remittance Undefined Symbol Error When Upgrading to Microsoft Dynamics GP 2013
Customer upgraded from GP 8 to GP2013R2, with customized 'Remittance Report'. Error shows
Instead of re-implement customization, do below.
#1, Export modified report to *.package file through customization maintenance window.
#2, Open it using notepad
#3, Ctrl+H, replace PM_Payment_WORK with pmRemittanceTemp
#4, Replace key from 7 to 3
#5, Import the *.package file back and overwrite the customized remittance report.
Instead of re-implement customization, do below.
#1, Export modified report to *.package file through customization maintenance window.
#2, Open it using notepad
#3, Ctrl+H, replace PM_Payment_WORK with pmRemittanceTemp
#4, Replace key from 7 to 3
#5, Import the *.package file back and overwrite the customized remittance report.
Subscribe to:
Posts (Atom)