Thursday 7 September 2017

SQL SERVER REPAIR DATABASE FROM SUSPECT MODE

SQL SERVER REPAIR DATABASE FROM SUSPECT MODE

SQL Server database go into suspect mode. During that moment, you cannot work on the database as database go into suspect mode because primary file group get damaged and database fails to recover during the start of SQL Server.


SQL Server Suspect Database Reasons


  • Log files or data files have got damaged
  • Due to lack of disk space, suspect mode like issues crops up in SQL Server database
  • Due to improper shutdown of database Server, you mostly find SQL database in suspect mode
  • Inability of SQL to complete rollback operation or roll forward operation
  • How to Repair SQL Server Suspect Database


Trouble Shooting Steps:

1. Change the status of your database.
2. Set the database in 'Emergency' mode such as:

Query: ALTER DATABASE <DatabaseName> SET EMERGENCY


3. Check for any of the inconsistency by using Query: DBCC checkdb('DatabaseName')
4. Bring the database to MULTI USER mode

 Query:-ALTER DATABASE [DatabseName] SET MULTI_USER

5. If any error message even after using DBCC CHECKDB command, then quickly bring the database in SINGLE USER MODE by running the query mentioned below:

Query :-ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Friday 26 May 2017

Failed to create a session;confirm that the user has the proper privileges to log on to Microsoft Dynamics


Solution 1: Delete Partitions & Check User Info Table


UPDATE USERINFO
SET SID='S-1-5-21-916693446-2822637979-541647007-1001', NETWORKALIAS='administrator', NETWORKDOMAIN='contoso', NAME='Vijay'
WHERE ID = 'Admin'
— This will replace the AX Admin user to your user – you can modify this query to replace other
— users as well, or you can later add new users from the AX GUI.
If  setup a new environment for you and imported the demo data using the “Test Data Transfer Tool” you can run into this error upon sync.  
The reason you get this error is that there are 3 partitions:   Ext, Initial and PS and user Info was only given to the initial partition,you need to do to resolve this issue is:

a. First in SQL review the userinfo table and checkout how many times you see the Admin ID in the table:  select * from userinfo where id = ‘admin’


b. I’m guessing you only see the admin record one time and this would be the problem.  When you have multiple partitions you should see the record in there for as many partitions as you have.  So, in our instance there should be 3 admin records in the userinfo table.


c. In SQL I usually run this command:  delete PARTITIONS where PARTITIONKEY in (‘ext’, ‘ps’)


d. Next open the AX client and navigate to:  System Administration > Setup > Partitions.


e. You should only see one partition called “Initial”.  We need to recreate the two we delete via our SQL script.  So create two new partitions called:  Ext and PS as seen below.


Partitions


f. Go review the userinfo table in SQL you should see that two new records have been automatically added for the administrator account.


g. Now try to sync the database in AX and you should have success!



Solution 2: Run Business Logic


The second thing you can try which occasionally works is to run the business logic not using IL.  You can disable this by:



a. In the AX client navigate to:  File > Tools > Options > Development hyperlink.

b. Unmark “Execute Business Operations in CIL”.


c.  Close the AX app and restart your AOS.


d. Open AX and try to Sync.



Solution3. Run a Full CIL.


a. To do this restart your AOS 1st and then from the AX Development Environment go to:


i. Build > Generate Full CIL.


Generate Full CIL in AX 2012


b. Now try to Sync.




Solution 4: Delete your AUC Files.


NOTE:  The AUC file is a cache of objects from the AOT.


a. Close out of AX.


b. Navigate to one of the following:


i. Windows XP and Windows 2003


C:\Documents and Settings\%username%\Local Settings\Application Data


ii. Windows Vista, Windows 7 and Windows 2008


C:\Users\%username%\AppData\Local


c. Delete the file or files with the extension .auc.


d. Open AX and try to Sync.




Solution 5: Improper Privileges


The user actually doesn’t have the proper privileges in SQL to run a sync which means they aren’t in the db.reader and db.datawrite roles check your SQL User Mapping.



Grant the AX AOS account permissions over the databases as follows:
AXProdNEW:
db_datareader
db_datawriter
db_ddladmin
public
axpermissions1
AXProdNEW_model:
public
axpermissions2
You can identify the AOS account by going to the services on the application server (start->
services.msc) and check the Log On tab:
axservices1
If you are using NETWORK SERVICES as the service account you will need to grant permissions to the machine$ account – Use the following query to do it:

Monday 8 May 2017

Retail POS Opening Error Store Not Found

Getting Error While Open Retail POS after configuration of all POS AX setup :-

Retail POS Error:-




Event Viewer Error:-




Event Viewer Error:-

LSRetailPosis.POS.Program: LSRetailPosis.PosisException ---> System.Exception: Information about store: 000002 was not found.
   at LSRetailPosis.Settings.ApplicationSettings.LoadSettingsForTerminal(Terminal terminal)
   at LSRetailPosis.Settings.ApplicationSettings.InitializeSettings(String[] args)
   at LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[] args, Boolean owned, SplashScreenForm splashScreenForm)
   --- End of inner exception stack trace ---
   at LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[] args, Boolean owned, SplashScreenForm splashScreenForm)

   at LSRetailPosis.POS.Program.Main(String[] args).


Solution 

Check Application & Kernel Version and Update Retail Patch Binary Or Application Update, In my case I was battling with CU12 Update after binary update problem resolved.

Thursday 16 February 2017

Copy A Existing Legal Entity To Another New AX Instance Environment

Copy a Existing Legal Entity from one Environment to other Environment for instance Testing,Development,UAT etc.

Steps to Copy A Legal Entity To Another New Instance Environment
  • Create a legal entity to use as a template.
  • Set all configuration data for the source legal entity.
  • Create a definition group which can be used to export data.

To create a definition group, open your AX  Go to System Administration module->Common->Data export/import- >Definition groups->Click on New button On General tab of Create table definition group form, provide your Definition group and Description.


Options tab of above form check Include shared tables 

Don't check Include system tables check box, Include database log tables, Include cross-reference tables


To Export configuration data, include the following table groups: Reference, Parameter, Group, Framework, and Miscellaneous

To export master data, you must include the tables that are in the Main table group

Do not include the tables that are in the Transaction, Transaction header, Transaction lineWorksheet, Worksheet header, and Worksheet line table groups. 
These tables include transaction data. It can be difficult to create a duplicate environment for transaction data if the SaveDataPerCompany property of tables is set to No
For transaction date Microsoft SQL Server backup and restore mechanism is one of best option but a minor configuration changes can be made to illustrate specific Microsoft Dynamics AX features.

click on OK button to create definition group




 Now you are ready with Definition group to export data for a legal entity (Company). 
Go to System Administration module->Common->Data export/import->Export to
Select your Definition group from drop down and provide the file Name (and location). Remaining things are up to your choice. Clock on OK button. This will create a .dat file with your given name using the Microsoft Dynamics AX data export and import feature to export the legal entity to a .dat file.


create new legal entities, and then import the .dat file that you created into each entity individually. 
Go to System Administration module->Common->Data export/import->Import. Select your Definition groups from the drop down and provide file Name (and location). Remember if you have different environment create a same definition group in your new environment where you are support to import data for a legal entity. 
On Advance tab there are different options but you may go for Include shared tables.
Shared and per-company data is imported. When other legal entities are subsequently imported into the new legal entities, the shared data is merged.

Tuesday 31 January 2017

Error Offline Database Retail 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'

I have Face issue after deploy patch KB3031055 and windows update, Offline database not working \syncing and giving error as below

The type initializer for 'ExtentPlaceholderCreator' threw an exception.: System.TypeInitializationException: The type initializer for 'ExtentPlaceholderCreator' threw an exception. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.EntryPointNotFoundException: Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'.
   at Microsoft.SqlServer.Types.GLNativeMethods.SetClrFeatureSwitchMap(Int32 clrFeatureSwitchMap)
   at Microsoft.SqlServer.Types.SqlGeometry.IsValidExpensive()
   at Microsoft.SqlServer.Types.SqlGeometry.Construct(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)
   --- End of inner exception stack trace ---
   

Using Server 2012 R2 with SQLSERVER 2014.

Solution 

 adding the following lines to the pos.exe.config 
<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" />
      <bindingRedirect oldVersion="1.0.0.0-11.0.0.0" newVersion="10.0.0.0" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>
This forces the EntityFramework to use the version 10 of the SqlServer.Types.dll, which doesn't have the Geometry type apparently.


And works fine.........................!!!

Saturday 7 January 2017

AX 2012 Retail POS Default Operations List

In AX 2012 for Retail POS, you have the flexibility to add/change functionality behind buttons that the users can access from the Retail>>Setup>>POS>>Profile>Operations.
Operation ID
Operation name
Permission ID
Permission ID2
Check user access
User operation
Action lookup
100
Product sale
0
0
Yes
Yes
Retail product
101
Price check
0
0
Yes
Yes
None
102
Void product
1002
0
Yes
Yes
None
103
Product comment
0
0
Yes
Yes
None
104
Price override
0
0
No
Yes
None
105
Set quantity
0
0
Yes
Yes
None
106
Clear quantity
0
0
Yes
Yes
None
108
Product search
0
0
Yes
Yes
None
109
Return product
1017
0
Yes
Yes
None
110
Weigh product
0
0
No
No
None
112
Linked products add
0
0
No
No
None
113
Set dimensions
0
0
No
No
None
114
Return transaction
0
0
Yes
Yes
None
115
Show journal
1002
1002
Yes
Yes
None
117
Add loyalty card
0
0
Yes
Yes
None
121
Clear salesperson
0
0
Yes
Yes
None
122
Invoice comment
0
0
No
Yes
None
123
Change unit of measure
0
0
Yes
Yes
None
127
Override transaction tax
1018
0
Yes
Yes
None
128
Override transaction tax from list
1018
0
Yes
Yes
None
129
Override line product tax
1018
0
Yes
Yes
None
130
Override line product tax from list
1018
0
Yes
Yes
None
131
Packing slip
0
0
Yes
Yes
None
132
Deposit override
0
0
Yes
Yes
None
200
Pay cash
0
0
Yes
Yes
Payment method
201
Pay card
0
0
Yes
Yes
None
202
Pay customer account
0
0
Yes
Yes
Payment method
203
Pay currency
0
0
Yes
Yes
Payment method
204
Pay check
0
0
Yes
Yes
Payment method
206
Pay cash quick
0
0
Yes
Yes
Payment method
207
Pay loyalty
0
0
Yes
No
None
208
Pay corporate card
0
0
Yes
Yes
Payment method
210
Change back
0
0
Yes
No
None
211
Void payment
1002
0
Yes
Yes
None
212
Get fleet card information
0
0
No
No
None
213
Pay credit memo
0
0
Yes
Yes
Payment method
214
Pay gift card
0
0
Yes
Yes
Payment method
300
Line discount amount
0
0
Yes
Yes
None
301
Line discount percent
0
0
Yes
Yes
None
302
Total discount amount
0
0
Yes
Yes
None
303
Total discount percent
0
0
Yes
Yes
None
400
Popup menu
0
0
Yes
Yes
Menu
401
Sub menu
0
0
Yes
Yes
Menu
500
Void transaction
1001
0
Yes
Yes
None
501
Transaction comment
0
0
Yes
Yes
None
502
Salesperson
0
0
Yes
No
None
503
Suspend transaction
1011
0
Yes
Yes
None
504
Recall transaction
0
0
Yes
Yes
None
505
Recall unfinished transaction
0
0
No
No
None
506
Card swipe
0
0
No
No
None
508
Pharmacy prescription cancel
0
0
Yes
Yes
None
510
Pharmacy prescriptions
0
0
Yes
Yes
None
511
Issue credit memo
0
0
Yes
Yes
None
512
Issue gift card
0
0
Yes
Yes
None
513
Display total
0
0
Yes
Yes
None
515
Recall order
1022
0
Yes
Yes
None
516
Sales invoice
0
0
Yes
Yes
None
517
Income accounts
0
0
Yes
Yes
None
518
Expense accounts
0
0
Yes
Yes
None
519
Add to gift card
0
0
Yes
Yes
None
520
Gift card balance
0
0
Yes
Yes
None
600
Customer
0
0
Yes
Yes
None
602
Customer search
0
0
Yes
Yes
None
603
Customer clear
0
0
Yes
Yes
None
609
Customer transactions
0
0
Yes
Yes
None
610
Customer transactions report
0
0
No
Yes
None
611
Customer balance report
0
0
Yes
No
None
612
Customer add
0
0
Yes
Yes
None
614
Edit customer order
1021
0
Yes
Yes
None
620
Create customer order
1020
0
Yes
Yes
None
622
Search
0
0
Yes
Yes
None
623
Edit customer
0
0
Yes
Yes
None
700
Log on
0
0
No
No
None
701
Log off
0
0
No
Yes
None
702
Change user
0
0
Yes
No
None
703
Lock register
0
0
Yes
Yes
None
704
Log off force
0
0
No
No
None
800
Negative adjustment
1002
0
Yes
No
None
801
Inventory lookup
0
0
Yes
Yes
None
802
Stock count
0
0
Yes
Yes
None
803
Picking and receiving
0
0
Yes
Yes
None
900
Application exit
1002
0
Yes
No
None
906
Print tax free
0
0
Yes
No
None
907
Print previous slip
0
0
Yes
No
None
908
Print previous invoice
0
0
Yes
No
None
909
Upload printer logo
0
0
Yes
No
None
910
Restart computer
1002
0
Yes
No
None
911
Shut down computer
1002
0
Yes
No
None
912
Design mode enable
0
0
Yes
Yes
None
913
Design mode disable
0
0
Yes
Yes
None
914
Minimize pos window
0
0
Yes
Yes
None
915
Blank operation
0
0
Yes
Yes
None
916
Windows workflow foundation
0
0
Yes
Yes
None
917
Database connection status
0
0
Yes
Yes
None
918
Show blind closed shifts
1023
0
Yes
Yes
None
919
Extended log on
1002
0
Yes
Yes
None
920
Time clock
0
0
Yes
Yes
None
921
View time clock entries
0
0
Yes
Yes
None
922
View product details
0
0
Yes
Yes
None
1000
Open drawer
1012
0
Yes
Yes
None
1052
Tender declaration
1004
0
Yes
Yes
None
1053
Blind close shift
1023
0
Yes
Yes
None
1054
Suspend shift
0
0
Yes
Yes
None
1055
Close shift
0
0
Yes
Yes
None
1056
Print X
1003
0
Yes
Yes
None
1057
Reprint Z
1005
0
Yes
Yes
None
1100
Customer account deposit
0
0
Yes
Yes
None
1200
Declare start amount
0
0
Yes
Yes
None
1201
Float entry
1007
0
Yes
Yes
None
1210
Tender removal
0
0
Yes
Yes
None
1211
Safe drop
0
0
Yes
Yes
None
1212
Bank drop
0
0
Yes
Yes
None
1213
Search shipping address
0
0
Yes
Yes
None
1214
Add shipping address
0
0
Yes
Yes
None