Category Archives: SQL

CRM 2011 Generic SQL error

Symptom: You receive a “Generic SQL error” exception i Microsoft Dynamics CRM 2011.

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail:
<OrganizationServiceFault>
<ErrorCode>-2147204784</ErrorCode>
<Message>Generic SQL error.</Message>

Solution: In our case the problem was caused by insufficient disk-space on the SQL Server where the CRM databases resides. More space solved the problem.

Lync 2010 Response group SQL query

A query that returns call details about calls made to response groups. It was not easy to find documentation of this, but here’s my first successfull attempt. The query is work-in-progress as I’m still trying to figure out how to connect the SessionDetails to the AgentGroups table in rgsConfig database.

use LcsCDR;

select
--SessionDetails users
User1.UserUri as User1Uri, User2.UserUri as User2Uri, StartedByUser.UserUri as StartedByUser,

--VoipDetails
FromPhone.PhoneUri as FromPhoneUri, ConnectedPhone.PhoneUri as ConnectedPhoneUri,

--SessionDetails stats
SessionDetails.ResponseCode, SessionDetails.ResponseTime, SessionDetails.SessionEndTime,
DateDiff(ss,SessionDetails.ResponseTime, SessionDetails.SessionEndTime) as 'Call Duration',

--Client Versions
Client1Version.ClientType as User1ClientType, Client2Version.ClientType as User2ClientType

from SessionDetails
--VoipDetails
join VoipDetails on SessionDetails.SessionIdTime = VoipDetails.SessionIdTime and SessionDetails.SessionIdSeq = VoipDetails.SessionIdSeq
left outer join Phones as FromPhone on FromPhone.PhoneId = VoipDetails.FromNumberId
left outer join Phones as ConnectedPhone on ConnectedPhone.PhoneId = VoipDetails.ConnectedNumberId

--Users
left outer join Users as User1 on User1.UserId = SessionDetails.User1Id
left outer join Users as User2 on User2.UserId = SessionDetails.User2Id
left outer join Users as StartedByUser on StartedByUser.UserId = SessionDetails.SessionStartedById

--Used to filter response group service calls only
left outer join ClientVersions as Client1Version on Client1Version.VersionId = SessionDetails.User1ClientVerId
left outer join ClientVersions as Client2Version on Client2Version.VersionId = SessionDetails.User2ClientVerId

where

--Filter response group service calls only
Client1Version.ClientType = 1024 or Client2Version.ClientType = 1024 --'RTCC/4.0.0.0 Response_Group_Service'

A Microsoft Lync 2010 custom SQL query

This query can be used for example when designing a custom SSRS report against Lync 2010 databases. It will show the current state of users in response groups.

SELECT Groups.Name, Agents.DisplayName, [State] FROM rgsdyn.dbo.AgentGroupSignInStates AS States JOIN rgsconfig.dbo.Agents AS Agents ON Agents.ID = States.AgentId JOIN rgsconfig.dbo.AgentGroups AS Groups ON Groups.ID = States.GroupId ORDER BY GroupId, States.State DESC