Problems accessing the /reports website in Dynamics 365 VMs

Hi

Today I had an issue trying to access the classic reports folder for SSRS in a Dynamics 365 for Finance and Operations VM hosted in Azure. When connecting to the site we used http://[VMname]/reports and all we get is a login prompt.

This one was quite easy… Just fire up the Reporting Services Configuration Manager, goto Web Portal URL and click Apply to configure it

And that is it!

Happy weekend

/Johan

Copy database from a OneBox environment to another OneBox Environment

This document is a check-list I have compiled on moving a database from one OneBox environment to another. Microsoft does not have a document for this scenario so I have compiled information from multiple blog posts.

1. Create a Backup copy of the Source database

2. Move the Database to the destination environment and restore it there

3. Stop the AX Services

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service
    (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI]
    computers only)

4. Rename the OLD database to AxDB_ORIG_TodaysDate and rename the newly restored database to AxDB.

5. Start the services that was stopped in Step 3 above

6. Sync the Database using either Visual Studio or this command from an elevated command prompt:

K:<br />
cd K:\AosService\WebRoot\bi<br />
Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "K:\AosService\PackagesLocalDirectory" -metadatadir K:\AosService\PackagesLocalDirectory -sqluser axdbadmin –sqlserver localhost -sqldatabase AxDB -setupmode sync -syncmode fullall -isazuresql false -sqlpwd <sql password> >log.txt 2>&1

Note: K is the Service Volume

Verify that there are no errors in log.txt

7. I the environment is running Retail you will need to run the Retail Reprovisioning tool in this document.

8. Reset the Financial Reporting Database according to this document.

Links
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/reset-financial-reporting-datamart-after-restore
http://blog.johanpersson.nu/?p=2684

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column when syncing Retail Data to Channel Database

One of our customer are going to use the AX 2012 R3 Retail solution and the setup of the Async Server – Client in the TEST environment was setup and tested. The application consultants started importing the product data to AX and suddenly the sync stopped with this error:

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.connectionString: Data Source=XXXXXX;Initial Catalog=ClientChannelDB;Integrated Security=True;Connect Timeout=60;Application Name=”Commerce Data Exchange Async Client” —> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. —> System.InvalidOperationException: String or binary data would be truncated.

After having a look online I found that there is no sync of the schema between the AX database and the Channel Database (!!!). This meant that when we imported data to AX everything worked but when we did the sync jobs it failed. The reason being that some field types did not match.

After talking to MS support I got a script to try to figure out which fields were wrong

select t1.table_name, t1.column_name, t1.character_maximum_length, t2.character_maximum_length
from MicrosoftDynamicsAX.INFORMATION_SCHEMA.columns t1
inner join RetailHoustonStore.INFORMATION_SCHEMA.columns t2
on t1.table_name = t2.table_name
and t1.column_name = t2.column_name
and t1.character_maximum_length != t2.character_maximum_length order by TABLE_NAME

this query finds all of the fields which are not matched between the AX database and the Channel Database. With some help we found that the issue probably was the field called RETAILVARIANTID which was present in INVENTDIMCOMBINATION and INVENTITEMBARCODE

We ran these SQL queries:

ALTER TABLE [ClientChannelDB].ax.INVENTDIMCOMBINATION ALTER COLUMN RETAILVARIANTID nvarchar(25) NOT NULL;
ALTER TABLE [ClientChannelDB].ax.INVENTITEMBARCODE ALTER COLUMN RETAILVARIANTID nvarchar(25) NOT NULL;

And there was also a view called INVENTDIMCOMBINATION which we scripted out to a query windows, deleted and recreated again. This solved the issue.

Bryta sig in i en SQL Server

Härom dagen bytte jag domän på en SQL server och missade att byta lösenord på SA innan jag gjorde det. Detta betydde att jag jag inte kunde logga in. Här kommer lite information om hur jag gjorde för att ta mig in.

För att göra en Password Recovery på SQL Servern gjorde jag följande

  1. Starta SQL Configuration Manager
  2. Stoppa tjänsten SQL Server
  3. Öppna Properties för SQL Server tjänsten
  4. Gå till fliken Advanced
  5. Ändra Startup Parameters till –m;-dC:\Program Files\Microsoft SQL…
  6. Starta SQL tjänsten igen
  7. Starta en Kommando Prompt
  8. Kör SQLCMD
  9. Kör följande SQL kommandon för att skapa ett nytt SA konto och ge det rättigheterna sysadmin:
       1: CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy

       2: go

       3: sp_addsrvrolemember ‘recovery’, ‘sysadmin’

       4: go

  10. Gå tillbaka till SQL Configuration Manager stäng SQL tjänsten
  11. Öppna Properties för SQL Server tjänsten
  12. Gå till fliken Advanced
  13. Ändra Startup Parameters tillbaka till -dC:\Program Files\Microsoft SQL…
  14. Starta SQL tjänsten igen
  15. Starta upp SQL Server Management Studio och ändra lösenordet på sa kontot

Jag i det här läget att server var i Windows Authentication mode så jag var tvungen att ställa om den till mixed mode.

  1. Starta Regedit
  2. Ändra HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode till 2 för mixed mode

 

 

Länkar

http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

http://www.sqlservercentral.com/Forums/Topic421197-149-1.aspx#bm505642

Flytta Business Contact manager till en ny server

Här kommer en liten lista på saker man behöver tänka på när man skall flytta Microsoft Business Contact Manager till en ny SQL Server

  • SQL instansen måste heta MSSMLBIZ
  • SQL instansen måste vara inställd att lyssna på port
  • SQL Browser måste vara startad
  • Se till att Windows Brandvägg är öppen på rätt portar
  • SQL Servern måste vara inställd för remote connections

Länkar

http://support.microsoft.com/kb/901164
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
http://support.microsoft.com/kb/823938
http://msdn.microsoft.com/en-us/library/ms177440.aspx
http://social.technet.microsoft.com/Forums/en/outlook/thread/fbc8e91b-93bc-4c49-888f-7785b2a2680a
http://support.microsoft.com/default.aspx?scid=kb;EN-US;953745
http://social.technet.microsoft.com/Forums/en/outlook/thread/fbc8e91b-93bc-4c49-888f-7785b2a2680a
http://wintivity.wigital.net/blog/2008/07/26/sql-database-remote-install-business-contact-manager-office-accounting-point-of-sale-domain/

Finding out version and edition of a Microsoft SQL Server

The other day at a customers I needed to find out the version and edition of an installed SQL server… Here is the solution:

Connect to an instance of SQL Server by using the following sqlcmd command:

sqlcmd -S ServerInstance

Where Server is the name of the computer and Instance is the name of the instance you want to check. If you have used the default named instance during setup, specify the instance as “SQLExpress”.

To identify the server name and instance name, type the following command:

select @@servername
go

When connected, type the following two commands:

select @@version
go

Alternatively, you can type the following two commands:

select serverproperty(‘edition’)
go

The sqlcmd tool displays the version information. If the last line of information includes “Express Edition,” the instance to which you are connecting is running SQL Server Express.

Thanks Anders…

Links

http://msdn.microsoft.com/en-us/library/ms165662(SQL.90).aspx

Reports not working in Microsoft CRM

When you try to open the Reports folder in Microsoft CRM you get the error:

Error An error has occurred. For more information, contact your system administrator.

This happens sometimes if you have separated CRM, SQL and reporting Services

This hapaned on a clientcomputer with Windows 2000. The solution was to enable Integrated Windows Authentication in Internet Explorer under “Internet Options – Advanced – Security Section”

Links

http://microsoftdynamicscrm.blogspot.com/2007/11/error-message-when-you-try-to-access.html
http://www.microsoft.com/downloads/details.aspx?FamilyID=51bf9f20-bd00-4759-8378-b38eefda7b99&DisplayLang=en

GrundläggandeMaintenance Tasks på SQL Server för icke-DB-Admins

Basic install

– Database Components
– Integration Service
– Management tools

Maintenance Plans

Maintenance plan (Kräver Integration Services)

Skapa Backupjobb (Scheduleras dagligen innan så att den är klar innan bandbackup)

Dra in ett Database backup block

  • Välj Databaser
  • Välj Typ
  • Välj Destination

Dra in ett Maintenance Cleanup Task block

  • Välj Backup Files
  • Välj mapp
  • Ställ in “Delete files based on…” till 3 dagar

Dra in ett “History Cleanup Task” block

  • Välj “Local Server Connection”
  • Välj “Backup and Restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance Plan History”
  • Ställ in tid (ex 4 veckor)

Koppla ihop blocken enligt: Backup – Maintenance Cleanup – History Cleanup

Skapa Indexering och Verifiering (Scheduleras en gång i veckan)

Dra in “Rebuild Index Task” block

  • Välj Databaser
  • Välj “Reorganize pages…”

Dra in “Update Statistics Task” block

  • Välj Databaser
  • Välj “All existing statistics”

Dra in “History Cleanup Task” block

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)

Koppla ihop blocken enligt: Rebuild – Update Stats – History Cleanup

Check Database Integrity (Scheduleras en gång i veckan efter Indexering och verifiering)

Dra in “Check Database Integrity task” block

  • Välj databaser
  • Välj Include indexes

Dra in “History Cleanup Task”

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)

Problems with Managemnet Studio on x64 SQL 2005 server

I had a problem today with editing maintenance plans in Management Studio 2005 on a server with Windows Server 2003 x64.

The error I get is:

Cannot show the editor for this task.
Additional information:
Exception from HRESULT:  0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

The solution I found was this:

%windir%syswow64regsvr32 “%ProgramFiles(x86)%Microsoft SQL Server90dtsbinndts.dll” 

Source:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=609326&SiteID=1

SQL Server Tips and Tricks for non-DBAs

I have collected some Good-To-Know-Things here and will add more as time goes by…

Cleaning up backups in SQL 2005

In SQL pre 2005 you could set up a backup job to delete backupfiles older than X number of days (see picture). This feature has been removed in SQL 2005.
 

If you want to do the same thing in SQL 2005 you do the following:

  1. Goto the maintenance plan that does the backup
  2. Rightclick and select Modify
  3. Drag and drop a Maintenance Cleanup Task and connect it after the last task
  4. Edit the task
  5. Specify the Path to the backup files, the extension och the files and the number of days you want to keep the backups
  6. Click OK to save the job

Adding more memory to a SQL Server

  1. Start Management Studio
  2. Open properties for the server
  3. Go to memory and change minimum and maximum memory
  4. Reboot the server

Change the default path for databases and logs

1. In management studio, right click on the server and select properties.
2. Goto database Settings
3. Change Database and Log paths