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

Reports in Microsoft CRM not working

I just set up a Microsoft CRM server for a customer. After one of our developers had set up the Biztalk connection (and changed some settings in IIS to work with his web service 🙁 ) the reports in MS CRM did not work anymore.

When I tried to connect directly to the ReportingServices virtual directory I got the message:

Unable to generate a temporary class (result=1). error CS2001: Source file ‘C:WINDOWSTEMP9a1mhqzb.0.cs’ could not be found error CS2008: No inputs specified

I tried to look at the permissions on C:WindowsTEMP but they seemed OK. Just for troubleshooting purposes I added Everyone Full Control to the folder.

The error message changed to:

“The report server is not responding. Verify that the report server is running and can be accessed from this computer.”

I checked the SQL Reporting Services Service but it was running. Then I got a tip on a MS SQL tool called RS Config Tool (rsconfigtool.exe) which is installed with MS SQL 2005. When I ran this gave me an error in “Web Services Identity” which ment that the ASP.NET services was not running as the correct user. It seemed the the developer had changed it to IWAM_Computername instead of NETWORK SERVICE.

To change this I went into IIS Manager under Application Pools. Righitclick on Default Application Pool and ReportServer, goto the tab Identity and change it to NETWORK SERVICE

Links:

Error CS2001 & CS2008
Change the account that ASP.NET runs under

Installation av Microsoft CRM Server 3.0

Installation av Microsoft CRM Server 3.0

(Detta är en editerad version av postningen den 22/5 2006)

Förutsättning: Microsoft CRM installeras i detta fall på samma server som Microsoft SQL. Allt installeras på en Windows Server 2003 SP2.

Installera IIS med följande komponenter:
 – ASP.NET
 – WWW Publishing Service
 – SMTP Service

Installera SQL Server 2005 (Jag installerades SP0. Har inte kunna testa med SP1 och SP2.)
 – SQL Server Database Service
 – Reporting Services (kan installeras av CRM om du använder SQL 2000)
 – Windows Authentication Mode
 – Finnish Swedish

Skapa Användare mm i AD
 – Skapa OU Microsoft CRM
 – Skapa Användare CRMAdmin (Domain Admin under installationen)
 – CRM Mail – Vanligt konto med mailbox (Mailrouter till Exchange)
 – Skapa DNS Pekare CRM till servern
 – Verifiera Full-Text Searching i SQL Enterprise Manager
 – Starta Indexing Service och sätt automatisk
 – Starta SQL Agent och sätt automatisk

Installera Microsoft CRM som CRM Admin

CD Key finns på CDKit. om du inte hunnit få detta kan du använda en utvärderingsnyckel (se nedan). Denna kan bytas mot den riktiga nyckeln inom 90 dagar.

  Create New Website
 – Ange Exchange Server (endast vid mailintegration)
 
 – Koppla upp http://localhost:5555

Installera Microsoft CRM Rollup 1

Edit: Jag installerade SQL SP1 i efterhand och det verkar fungera

OBS Plocka inte bort den website som läggs upp av installation och ändra inte port OBS

Post Install Tasks

Microsoft CRM

Verifiera att AD grupperna (PrivUserGroup, SQL Access Group, Reporting Group och UserGroup) har skapats.

Lägg Microsoft CRM Service, IIS Service Account och SQL Server Service Account konton i “Pre-Windows 2000 Compatible” säkerhetsgruppen.

Registrera CRM installationen inom 30 dagar

Skapa ett nytt adminkonto i MS CRM. Kryssa i restricted access för detta konto. Kryssa ur Restricted access för CRMAdmin

Reporting Services

(Om CRM körs på samma server som MS SQL 2005) Byt Application Pool på den virtuella katalogen reports till Default Application Pool.

Kopiera mscrm.css, mscrmx.css, mscrmp.css, mscrmxp.css från wwwrootCRMReportsrsstyles på MSCRM Installationsskivan till c:ProgramFilesMicrosoftSQL ServerMSSQLReportingServicesReportServerstyles

Ändra rättigheter på ovanstående filer: Lägg till UserGroup Read

Verifiera Reporting Services genom att öppna Workplace, under My Work, klicka Reports och välj User Summary

Gå till Microsoft KB Artikel om Backup
Microsoft CRM Implementation Guide – Planning the Backup and Restoration of the Microsoft CRM System

Update Rollup 1 for Microsoft Dynamics CRM 3.0 is available
Update Rollup 1 for Microsoft Dynamics CRM 3.0 (KB922815)

Tidbits on MS CRM

Microsoft CRM 3.0: 90-Day Trial Versions