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;

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

AXProdNEW_model:
public

You can identify the AOS account by going to the services on the application server (start->
services.msc) and check the Log On tab:

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:

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.

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 line, Worksheet, 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.

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.........................!!!

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