MR does not match GP
Are you using AA?
chances are, there is corrupted data in the AA tables for some transactions that is preventing them from being sent to the MR data warehouse.
Follow the steps here to resolve
http://support.microsoft.com/kb/2910626
You may need to add/update records in the following tables
aag30001,aag30002
using data from the aag30000 table and the output from the script
Follow all instructions in the scripts, and verify all records before adding or updating.
Wednesday, February 19, 2014
Tuesday, February 18, 2014
Dynamics GP - Change the Vendor Doc Number on Payables Transactions
You need to update the docnumber / vnddocnum in these tables
update PM20000 set DOCNUMBR = '584771' where VCHRNMBR = '002779'
update POP30300 set VNDDOCNM = '584771' where POPRCTNM = 'rct001575'
update PM00400 set DOCNUMBR = '584771' where DOCNUMBR = 'po001170'
this is a script i ended up using
--This script will identify all transactions with a specific Vendor Document Number, and switch them to a new document number.
declare @CurrVendDocNo varchar(30), @NewVendDocNo varchar(30)
select @CurrVendDocNo = 'po001170' -- Enter Current Vendor Doc Number to Search For
select @NewVendDocNo = '584771' -- Enter New Vendor Doc number to switch it to
select vendorid, pordnmbr,DOCNUMBR from pm20000 where DOCNUMBR = @CurrVendDocNo
select vendorid, POPRCTNM,VNDDOCNM from pop30300 where vnddocnm = @CurrVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @CurrVendDocNo
--Run the script up to here to view the records that will be affected by this change
update PM20000 set DOCNUMBR = @NewVendDocNo where DOCNUMBR = @CurrVendDocNo
update POP30300 set VNDDOCNM = @NewVendDocNo where VNDDOCNM = @CurrVendDocNo
update PM00400 set DOCNUMBR = @NewVendDocNo where DOCNUMBR = @CurrVendDocNo
select vendorid, pordnmbr,DOCNUMBR from pm20000 where DOCNUMBR = @NewVendDocNo
select vendorid, POPRCTNM,VNDDOCNM from pop30300 where vnddocnm = @NewVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @NewVendDocNo
update PM20000 set DOCNUMBR = '584771' where VCHRNMBR = '002779'
update POP30300 set VNDDOCNM = '584771' where POPRCTNM = 'rct001575'
update PM00400 set DOCNUMBR = '584771' where DOCNUMBR = 'po001170'
this is a script i ended up using
--This script will identify all transactions with a specific Vendor Document Number, and switch them to a new document number.
declare @CurrVendDocNo varchar(30), @NewVendDocNo varchar(30)
select @CurrVendDocNo = 'po001170' -- Enter Current Vendor Doc Number to Search For
select @NewVendDocNo = '584771' -- Enter New Vendor Doc number to switch it to
select vendorid, pordnmbr,DOCNUMBR from pm20000 where DOCNUMBR = @CurrVendDocNo
select vendorid, POPRCTNM,VNDDOCNM from pop30300 where vnddocnm = @CurrVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @CurrVendDocNo
--Run the script up to here to view the records that will be affected by this change
update PM20000 set DOCNUMBR = @NewVendDocNo where DOCNUMBR = @CurrVendDocNo
update POP30300 set VNDDOCNM = @NewVendDocNo where VNDDOCNM = @CurrVendDocNo
update PM00400 set DOCNUMBR = @NewVendDocNo where DOCNUMBR = @CurrVendDocNo
select vendorid, pordnmbr,DOCNUMBR from pm20000 where DOCNUMBR = @NewVendDocNo
select vendorid, POPRCTNM,VNDDOCNM from pop30300 where vnddocnm = @NewVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @NewVendDocNo
Monday, February 17, 2014
eOne Smartconnect - Excel Data sources cannot be found
Excel data sources are missing, unavailable, do not display in the drop down list, or are displayed, but smartconnect throws an error saying it cannot find a valid data source or driver.
This is because Smartconnect is using the 32 bit Microsoft Access Database Engine required for Excel data connections. (This is dependant on the original drivers used to create the original map. If the original map was created using 64-bit drivers, it will need 64 bit drivers on all clients. If the original map was created using 32-bit drivers, it will need 32-bit drivers on all clients)
These components are usually installed when you do a full install of office, however, if the client is using 64-bit Office, then it installs the 64-bit Access Database engine.
To resolve this issue, you must install the 32-bit Microsoft Access Database Engine, however, if you alrayd have 64-bit office installed, you will get an error saying you cannot install the 32-bit drivers because 64-bit drivers are already installed.
To get around this, run the install from a command line or batch file with the /passive flag
Example
C:\Accessdatabaseengine.exe /passive
will force the install even if 64-bit is already installed.
Access Database Engine Downloads
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Original Post
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
This is because Smartconnect is using the 32 bit Microsoft Access Database Engine required for Excel data connections. (This is dependant on the original drivers used to create the original map. If the original map was created using 64-bit drivers, it will need 64 bit drivers on all clients. If the original map was created using 32-bit drivers, it will need 32-bit drivers on all clients)
These components are usually installed when you do a full install of office, however, if the client is using 64-bit Office, then it installs the 64-bit Access Database engine.
To resolve this issue, you must install the 32-bit Microsoft Access Database Engine, however, if you alrayd have 64-bit office installed, you will get an error saying you cannot install the 32-bit drivers because 64-bit drivers are already installed.
To get around this, run the install from a command line or batch file with the /passive flag
Example
C:\Accessdatabaseengine.exe /passive
will force the install even if 64-bit is already installed.
Access Database Engine Downloads
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Original Post
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
Wednesday, February 12, 2014
Dynamics NAV - Installation checklist
1
MS SQL Installation
1.
Ensure the Server name is
accurate if it is a new server.
2.
Make sure the date and time is
set accurately.
3.
SQL Server Installation:
a)
Configure Windows Firewall: http://technet.microsoft.com/en-us/library/cc646023.aspx
i. Database Engine access requirements:
1.
Inbound rule for local port
1433
2.
Inbound
rule for SQL application C:\Program Files\Microsoft
SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe
ii. Analysis Services access requirements:
1.
For a default instance of SQL, create an inbound rule for port 2383. The execute the following
commands:
a.
netsh advfirewall firewall
add rule name="SQL Server Analysis Services (tcp-in) on 54321" dir=in
action=allow protocol=TCP localport=54321 profile=domain
b.
netsh advfirewall firewall
add rule name="SQL Server Analysis Services inbound on TCP 2383"
dir=in action=allow protocol=TCP localport=2383 profile=domain
2.
For a
default instance of SQL, create an inbound rule for port 2382. Verify that you can browse to the instance by trying to
connect to the instance using the syntax <servername>\<instancename>.
iii. Report Server access requirements:
1.
Inbound
rule for local port 80.
b)
Activate .Net 3.5 if the
machine is Server 2012 using Add Roles and Features from Server Manager. Server
2010 has .Net 4.0 installed but SQL searches for 3.5. (Recommended to add the
Application Server Role on the Server)
c)
Once all the prerequisite
checks are complete and all successful, you will be required to select SQL
Server Features to install. The following are the minimum features required for
NAV:
i. Database Engine Services.
ii. Management Tools – Basic
iii. Management Tools – Complete
iv. Analysis Services (Optional)
v. Reporting Services (Optional)
2
NAV Server Installation
1.
Ensure that Microsoft Outlook
and Excel 2010 or later versions are installed on the server.
2.
Ensure that the Microsoft
Search service is installed and activated.
3.
Ensure that the “SQL Server”, “SQL
Database Agent”, “SQL Server Browser”, “SQL Server Analysis Service” and SQL
Server Reporting Services” services startup type is set to automatic and are
running.
4.
Launch Setup file from
installation media.
5.
Select “Installation Option”
from the Dynamics NAV Setup screen.
a)
Note: Monitor the installation
ensure that SQL Express is not installed if SQL Standard is already installed
and will be the default database application. If SQL Standard is not installed
or recognized, the installation process will install SQL Express Server 2010.
6.
Select “Server” for the
installation option.
7.
Highlight all options top
install and the click on “Apply” to start the installation.
8.
Upon completion of the
installation, log into SQL Management Studio to confirm that the “Demo”
database was successfully created.
3
NAV Developer and Client
Installation
1. Launch the setup from installation media.
2. Select “Add or remove components”
3. Under Microsoft Dynamics NAV, select “Client” to install and then
select “Development Environment” and “Microsoft Office Excel Add-in” and then
click next.
4. In the “Specify Parameters” window, type “Demo” in the SQL Database
field, then click “Apply”.
5. Launch Dynamics NAV Administration Tool, select the DynamicsNAV71 instance,
and click on Edit” and update the database server field with the appropriate
name and then click on “Save”.
6. Restart the DynamicsNAV71 service by right clicking on the instance
and click “Restart”.
7. Once complete, launch Dynamics NAV client to determine if the
installation was successful. If the application launches successfully into the
demo company, the installation process is completed.
4
Additional resources:
b)
Basic Installation: http://blogs.msdn.com/b/nav_developer/archive/2008/11/05/basic-sql-overview-of-nav-specific-sql-features-for-application-consultants.aspx
c)
To rename a server that is running
SQL , you will need to perform the following steps:
vi. For a SQL server with a default instance:
1.
Rename the computer in Computer
Properties.
2.
Execute the following query:
sp_dropserver
<old_name>;
GO
sp_addserver <new_name>, local;
GO
3.
Execute the query to see if the
query was successful:
SELECT @@SERVERNAME AS 'Server Name';
4.
To view the instance name:
select @@servername + '\' + @@servicename
vii. For a server with a named instance:
1.
Rename the computer in Computer
Properties.
2.
Execute the following query:
sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO
3.
Execute the query to see if the
query was successful:
SELECT
@@SERVERNAME AS 'Server Name';
Tuesday, February 11, 2014
Dynamics GP - Report Writer Function - RW_PMAddrIDInfo
This will allow you to get address data from a vendor
User Defined function - System - RW_PMAddrIDInfo()
Working example:
rw_pmaddridinfo(POR_receipt_temp.vendorid "RETURN" 2)
will return the address line 1 of the current vendor in address id "RETURN"
User Defined function - System - RW_PMAddrIDInfo()
The parameters are:
in 'Vendor ID' IN_Vendor_ID;
in 'Address Code' IN_Address_Code;
in integer IN_Field;
{
IN_Field should use the following values:-
1 = Contact Name
2 = Address 1
3 = Address 2
4 = Address 3
5 = City
6 = State
7 = Zip
8 = Country
9 = Phone 1
10 = Phone 2
11 = Phone 3
12 = Fax
13 = Country Code
}
in 'Vendor ID' IN_Vendor_ID;
in 'Address Code' IN_Address_Code;
in integer IN_Field;
{
IN_Field should use the following values:-
1 = Contact Name
2 = Address 1
3 = Address 2
4 = Address 3
5 = City
6 = State
7 = Zip
8 = Country
9 = Phone 1
10 = Phone 2
11 = Phone 3
12 = Fax
13 = Country Code
}
Original Post
Working example:
rw_pmaddridinfo(POR_receipt_temp.vendorid "RETURN" 2)
will return the address line 1 of the current vendor in address id "RETURN"
Dynamics GP - Script to backup live company and restore into existing test company and update companyid. Copy live to test.
BACKUP DATABASE [YOURDB] TO DISK = N'C:\Temp\YOURDB_temp.bak' WITH NOFORMAT, INIT, NAME = N'YOURDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Use Master
Alter Database TEST
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST] FROM DISK = N'C:\Temp\YOURDB_temp.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
-- find the company id by using select * from dynamics.dbo.sy01500
update test.dbo.sy00100 set cmpanyid = 10
GO
Use Master
Alter Database TEST
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST] FROM DISK = N'C:\Temp\YOURDB_temp.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
-- find the company id by using select * from dynamics.dbo.sy01500
update test.dbo.sy00100 set cmpanyid = 10
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
declare @Statement varchar(850)
select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
from SY00100
exec (@Statement)
end
else begin
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off
endSaturday, February 1, 2014
Dynamics GP - IV00301 Lot Attribute Table Explanation
- If stock is increased,
- and the lot number does not exist, an entry is created in this table
- RCRDSTTUS is set to 0
- IUSCOUNT is set to 1
- and the lot number and the lot number already exists, the entry is updated
- IUSCOUNT = iuscount + 1
- If stock is decreased or transferred
- and the lot number does not exist, an entry is created in this table
- RCRDSTTUS is set to 2
- IUSCOUNT is set to 1
- and the lot number and the lot number already exists, the entry is updated
- IUSCOUNT = iuscount + 1
- IUSCOUNT represents the number of lots with the same name (it should never be negative)
Subscribe to:
Posts (Atom)