Setting up SQL Notifications and Alerts

When running a SQL server, it’s handy to let it tell you how things are going, rather than having to go look at the logs every so often and see if anything’s broken.

Here are the steps to do this. In this case, I have a large number of clients. To standardize, I setup a single GMail account to use for the outgoing email notifications for all the servers I manage. If you have an internal mail server or are using something other than GMail, you’re a big kid now - plug your settings in.

Tasks:

• Setup your mail account
• Setup Database mail
• Setup Operators
• Setup Alerts
• Maintenance Plan Failure notification
• Testing / Debugging
• Resources

Note: These settings are Instance specific, so you'll need to do this for EACH instance.

 

Setup your Mail Account

Setup your GMail account. Verify that you can send / receive emails.

Setup Database Mail

Setup Mail Profile

• SQL Mgt Studio
• Management Folder / Database Mail / Right Click / Configure
• Next / Setup up DB Mail / Next (If prompted to enable Database Mail, choose Yes)
• Profile Name: Alert Notifications
• Add new SMTP Account
• Account name: {Descriptive name of your email user} 
• Email address: {yourmail@server.com}
• Display Name: SQL Alert – {Server \ Instance name} IE: JumboCorp \ CounterPoint
• Reply email: {yourmail@server.com}
• Email Server: smtp.gmail.com
• Port: 587
• Require secure connections: checked
• Enter the basic authentication username / password. Note that for GMail you’ll use your full email address
• Click Next
• Check Public Profile / Set Default Profile to Yes
• Click Next
• Accept defaults for system parameters / Next
• Review / Finish / Close

To test, right click Database Mail, choose Send Test E-Mail.

Setup Operators

• Right Click Operators under SQL Server Agent / New Operator
• Name: {Descriptive name for destination email address} 
• E-mail name: {destinationemail@server.com}
• Save

Setup additional operators if other users wish to be notified of SQL Alerts

 

Setup Alerts

Enable Alerts in the SQL Agent

• Right click SQL Server Agent / Properties / Alert System

• Enable mail profile
• Mail system: Database Mail
• Mail Profile: Accelerando Notification
• Ok
• Restart the SQL Server Agent service
  • (If notifications don't go out after a SQL Agent restart, you may also need to restart the SQL Service)

Setup Alerts - General Alerts

Run the this script against the Master database.

 

Enabling Alert Notification

After the alerts are created, you'll need to activate notification for each of them (one at a time)

• Right click the alert / Properties
• Response Tab / Notify operators: checked
• Check email for Accelerando SQL Alerts
• Click Options Tab / Include alert error in: Email (checked)
• Delay between responses: 180 (3 hours)
• Ok

 

Large Log File Alert

Additional alerts - these cannot be scripted because they depend on specific databases. You'll need to create an alert for each of the databases you wish to monitor.

• Right click Alerts under SQL Server Agent / New Alert
• Alert name: {Servername / Instance Name - DBNAME} DB - Large Log File Alert
• Type: SQL Server performance condition alert
• Object: SQL Server Databases
• Counter: Log File(s) Size (KB)
• Instance: Choose the DB to monitor
• Alert if counter: rises above
• Value: {Insert reasonable number here. For large databases 50000000 would be 50GB)
• Enable the email alert notifications as in the section above

Maintenance Plan Success / Failure notification

Open the maintenance plan to which you'd like to add notifications. You can add notifications to just the backup task or to multiple tasks. To add a notification, use the following steps:

• Grab the Notify Operator Task and drag it into the maintenance plan.
• From the step on which you'd like to receive the notification, grab an available component output and connect it to the Notify Operator Task. Right click on the component output and choose whether you'd like the notification to happen on success or failure.
• Edit the Notify Operator Task and check any operators that should be alerted in this notification
• In the Subject line, enter {Server\Instance} - {Action} Failed / Succeeded
  • For example: JumboCorp\DynamicsGP - Transaction Log backup failed
• In the notification message, repeat the above line.
• Repeat the steps above for any other points in the plan that you'd like notification for.
• NOTE: If you want to receive both a success and failure notification, you'll need to add two separate Notify Operator Tasks

Testing / Debugging

Test Alert Level Notifications

Run the following script against the Master database. It should trigger an alert notification and email response.

RAISERROR('This is a test', 17, 1) WITH LOG

Checking Sent Mail status

Run the following script against the Master database. It will return a list of the emails that have been sent and show the status of each of the emails

SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_event_log

If sent emails fail, test by installing an email client on that machine with the same configuration as above.

Incoming mail server: pop.gmail.com
Port: 995
Require SSL: Checked

You can download Windows Live Mail here -
http://explore.live.com/windows-live-essentials-other-programs?T1=t2

Only install the email client (live mail)

Resources

SQL Server 2008 : Database Mail - Using SQL Server Agent Mail
http://mscerts.programming4.us/sql_server/sql%20server%202008%20%20%20database%20mail%20-%20using%20sql%20server%20agent%20mail.aspx

Setup SQL Server 2008 Maintenance Plan Email Notifications
http://808techblog.com/2009/07/setup-sql-server-2008-maintena.html

SQL Server Alerts: Soup to Nut
http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

SQL Script - General Alerts

Requirements

DBMail activated
Run against Master DB

 

Script

USE [msdb]
GO

/****** Object:  Alert [Alert Level 17 - Insufficient Resources]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 17 - Insufficient Resources',
        @message_id=0,
        @severity=17,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 18 - Nonfatal Internal Error]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 18 - Nonfatal Internal Error',
        @message_id=0,
        @severity=18,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 19 - Fatal Error in Resource]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 19 - Fatal Error in Resource',
        @message_id=0,
        @severity=19,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 20 - Fatal Error in Current Process]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 20 - Fatal Error in Current Process',
        @message_id=0,
        @severity=20,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 21 - Fatal Error in Database Processes]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 21 - Fatal Error in Database Processes',
        @message_id=0,
        @severity=21,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 22 - Fatal Error: Table Integrity Suspect]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 22 - Fatal Error: Table Integrity Suspect',
        @message_id=0,
        @severity=22,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 23 - Fatal Error: Database Integrity Suspect]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 23 - Fatal Error: Database Integrity Suspect',
        @message_id=0,
        @severity=23,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 24 - Fatal Error: Hardware Error]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 24 - Fatal Error: Hardware Error',
        @message_id=0,
        @severity=24,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 25 - Fatal Error]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 25 - Fatal Error',
        @message_id=0,
        @severity=25,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

CRM: Comparing Time Worked with Time Billed on Resolved Cases

This article applies to CRM 4.0

We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.

On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.

Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.

select
    i.accountidname,
    i.title,
    ir.actualend,
    max(timespent) as BillableTime,
    isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime

from filteredactivitypointer a
    join filteredincident i on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1

I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.

You can also then build on this and figure out your top accounts using a script like this -

SELECT
    Accountidname as AccountName,
    COUNT(accountidname) AS Cases,
    SUM(BillableTime) AS TimeBilled,
    SUM(TotalActivityTime) as TimeWorked,
    SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC

Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.

Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.

Troubleshooting CRM Performance

Once in a while, the SQL box hosting CRM at the office begins to bog down. Here are a few scripts and procedures that I use to help troubleshoot what can be causing the slowdowns.

First of all – start with the basics. Does the server have adequate disk space. How badly fragmented are the drives?

NOTE: The built-in Windows Defrag won’t touch files larger than 2 GB so you may have to defrag by moving the large files onto another volume, defragging, then bringing them back.

Find a SQL process with high CPU utilization

Here’s a general SQL script that will show you the process causing high CPU usage. You can then use the SPID to kill the offending process. Insert the name of your organization database into the script as noted.

USE Master
GO

DECLARE @DATABASE_ID INT
SET @DATABASE_ID = DB_ID(‘organizationname_MSCRM');

SELECT ST.TEXT,
SP.*
FROM DBO.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.DBID = @DATABASE_ID
ORDER BY CPU DESC
GO

Once you know which process is stuck, you can kill it using the following command. Insert the correct SPID you gathered from the script above. NOTE: KILLING A RUNNING PROCESS IS NORMALLY A BAD IDEA. Only use this to kill a process that’s hung.

Kill spid

Cleaning up completed workflows

As workflows complete, their records remain in the AsyncOperationsBase table. Over time, this table can grow to enormous size. To resolve the problem, run the following script against your organization_MSCRM database, replacing with your orgname where noted. This is a one-time running script. If you want it to run on a recurring basis, you could set it up as a SQL job.

NOTE: There is sometimes business value in being able to see completed workflows. Make sure you really want them gone before running the script.

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin     
begin tran     
insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)    
       Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end     
        if (@continue = 1)        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId            
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
            delete @DeletedAsyncRowsTable     
end      
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

The above script was taken from Microsoft KB968520

Finding and removing waiting workflows

Here’s a script to list open workflow operations on the sql server. These are Workflows that are stuck in a waiting state. Too many and the server starts bogging down. I start getting worried at 50,000 and then at 100,000 we usually see the server start to croak.

Run this against your organization_mscrm database.

Select DISTINCT(asyncoperation.Name ), COUNT(asyncoperation.Name ) AS NUM
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
Group BY asyncoperation.Name
ORDER BY NUM DESC

An example might be a workflow that no longer exists, but has live versions of itself stuck in a waiting state. To remove these orphaned workflows, use the following steps. You can get the workflow name from the script above. Insert into the marked locations below.

PLEASE NOTE: MS DOES NOT SUPPORT editing data directly in the database. You do this at your own risk, and for the sake of all that’s holy, backup your database first.

delete from workflowwaitsubscriptionbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from workflowlogbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1

Async service on multi-core

KB Article 2489162 on Customersource / Partnersource has a good tip on improving CRM Async performance on multi-core servers. Apparently the Async service doesn’t automatically take advantage of multi-core processors. Login to CS/PS and search for article 2489162 to find the article titled How to improve Microsoft CRM Async Performance on multi-core servers

Returning Waiting Workflows in CRM

Our CRM system seems to have slowed over time. One of the reasons is an increase in the number of workflows that are in a waiting state.

I have a SQL script that I ran against the company_MSCRM database to return a list of all the workflow names that were in the wait state, exported the list to Excel and subtotaled it to get a unique count for the number of each workflow. Sort by count and you get a good list of where you need to start optimizing workflows to hit and stop rather than wait for something to happen.

In our case, the number 1 culprit was a workflow that waits for the regarding attribute to be set to an account rather than a case. As a service organization, we try to push all of our service work thru cases. Instead of doing a check and then existing, the workflow ‘waits’ for the regarding to equal an account. This means that every open activity in CRM has a live workflow waiting for the regarding to equal an account.

Here’s the SQL script I used to return the waiting workflow names.

Select asyncoperation.Name
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
ORDER BY asyncoperation.Name

This code is based on an article by JonSCRM on MSDN.

Renaming a SQL Server

We use virtual machines in-house to test different software configurations. Some of these servers run SQL and a part of that process is renaming the servers. SQL doesn’t like this because it stores its own name internally. Sometimes stored procedures fail, scripts may not work right and SQL Agent jobs may freak out. However, there’s a process for renaming that works pretty well.

On the SQL Server, you can retrieve the internally stored name by running the query:

SELECT @@SERVERNAME AS 'Server Name'

If the instance of SQL you’re working with is the default instance, you can change the servername using the following script:

sp_dropserver OLD_NAME

GO

sp_addserver NEW_NAME, local

GO

If the instance you’re working with is not the default instance, use the script:

sp_dropserver 'OLD_NAME\instancename'
GO
sp_addserver 'OLD_NAME\instancename', local
GO

Note that you can’t change the instance name using this process. 

The information here was pulled from Microsoft Technet at http://technet.microsoft.com/en-us/library/ms143799.aspx

SQLPass 2011 Notes

01/22/2011 – SQLPass training at UofL in louisville Kentucky

Live notes from sessions

 

Session 1 - SQL Query performance optimization

Use Set Statistics ON to get feedback on the efficiency of your statements. Provides results, execution plan, and message that shows efficiency of the statement.

Thumb rule - whenever possible, do not use functions on select statements, far more 'busy'

BAD:

SELECT ModifiedDate

FROM SALES.Customer c

WHERE YEAR(ModifiedDate) = 2005

GOOD

SELECT ModifiedDate

FROM Sales.Customer c

WHERE ModifiedDate >= '01/01/2005' AND ModifiedDate < '01/01/2006'

Parameter Sniffing

Local variables are expensive – compiler has to make assumptions about values when creating the plan and account for possibilities.

Instead, use stored procedures with parameters. Compilation uses the actual values when compiling.

NOTE: stored proc execution plan compiles for the values present when compiled. If you rerun with new values, this requires a new plan and may be more expensive.

Action: Check out Cross Apply action.

Better Data Vis with Reporting Svcs 2008 Rw

Presenter Arie Jones arie.jones@pti.net

www.programmersedge.com

www.sqlsherpa.com

www.twitter.com/programmersedge

Check out MongoDB video on YouTube

AJ’s 10 second rule – Data vis needs to be understandable within 10 seconds of viewing the report

Image Indicators

SQL 2008 R2 includes indicators as part of the available tool elements.

When setting ranges for kpi’s, start is >=, end is <

KPI’s include all the conditiional formatting capabilities of other report elements

Custom images can be imported and used in reports (company logo)

Data Bars

Show a bar-chart style representation of values

To show multiple styles of representation based on user preference, add multiple elements and use visibility property and show / hide based on a report parameter. Can even store user preference in db and pull back into reports using a 2nd dataset for the report.

Sparklines

Show trending over time

Can use tooltips for datapoints to display the values at specific points.

Switching between sparkline types maintains tooltips, conditional expressions, etc.

Spatial Data

Includes mappings by state, by county, checkbox for bing map linking

Metadata is included in the maps so the geolocation is not needed

Running in Windows7, you have to run dev environment as Administrator

New Features

Pagination improvements

Named Excel Worksheet Tabs

Text rotation up to 270 deg

Cache refresh plans

New Web Svc endpoints

Azure data retrieval

<fill additional feature list>

 

Database Design Considerations

• Gather requirements
• Initial design
  • Tables, Columns, Relationships, Constraints
• Normal Forms <investigate>
• Atomicity – break data down until it cannot be split further
  • Store data at the level you want to use it to report
• To be in 1NF, some uniqueness needs to be on a column in the table that has meaning
• FName,Lname,Children not 1NF – Custnum,FName,LName good
• Investiage Boyce-Codd normal Forms

Real World Analytics

• Ranking Data
• Recursive expressions
• Rows to columns / crosstab or pivot
• Use DateDiff() to programmable start / end dates rather than using functions – functions cannot make use of table indexes and prompt full scans rather than seeks
• Check on Anchor queries, recursive CTE
• Mark Wills article Dynamic Pivot Procedure for SQL Server – http://e-e.com/A_653.html

Red Gate SQL Monitor

Presenter Louis Davidson – drsql@hotmail.com

• http://monitor.red-gate.com
• Does not offer remote agent pushing to collection server, CS has to pull from remote servers

Effective SQL: Understanding Order of Operation

Presenter Kevin C. Cross – Elliott Tool Technologies LTD

• PEMDAS
• Parenthesis
• Exponents
• Multiplication and Division
• Addition and Subtraction
• Additional
• Unary Operations
• Modulo
• Type Conversions / Collation
• Bitwise and logical operations

BAD:

SELECT *

FROM Products

WHERE CATEGORY = 1 OR CATEGORY = 2

AND PRICE <=40

The price <=40 is tied to the second Category, not as a second where clause

GOOD:

SELECT *

FROM PRODUCTS

WHERE (CATEGORY = 1 OR CATEGORY = 2)

AND PRICE <= 40

• To avoid integer division where 1/3 = 0, use mathematics in the query to cast one of the values to a float. This avoids the expensive use of dropping a formula on top of the column.

BAD: Select cast(column1) / column2

Good: SELECT column1 * 1.00 / column2

• Like data type conversions, collation will be applied to string before comparison (EXPENSIVE)
• Bitwise ops are equal to arithmetic (after multiplation / division)
• When joining, start with the table that contains the most data you’re using – use the least selective table
• Select precedence
• from
• where
• group by
• having
• select
• distinct
• union all
• order by
• top

Removing a Company from GP

If a company was deleted by detaching or deleting the DB from SQL, there will be orphaned records in the DYNAMICS database.

In order to remove the company, use the following script -

/* ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS

database referencing databases that no longer exist on the SQL Server.

Requirements:

Company database you wish to have cleaned out of the tables in the DYNAMICS

database must be removed from the SQL server before running this script.

Ensure that all your databases have been restored or they will be erased

from the DYNAMICS database.

*/

set nocount on

/* Remove all references in the company master (SY01500) for databases that

Do not exist on the SQL Server */

delete DYNAMICS..SY01500 where INTERID not in

(select name from master..sysdatabases)

/* Clear out all tables in DYNAMICS database that have a CMPANYID field

that no longer matches any Company ID's in the SY01500 */

USE DYNAMICS

declare @CMPANYID char(150)

declare CMPANYID_Cleanup CURSOR for

select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'

+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'CMPANYID'

and o.name <> 'SY01500' order by o.name

OPEN CMPANYID_Cleanup

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

while (@@FETCH_STATUS <>-1)

begin

exec (@CMPANYID)

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

end

DEALLOCATE CMPANYID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a companyID field

that no longer matches any Company ID's in the SY01500 */

USE DYNAMICS

declare @companyID char(150)

declare companyID_Cleanup CURSOR for

select 'delete ' + o.name + ' where companyID not in (0,-32767)'

+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'companyID'

and o.name <> 'SY01500'

set nocount on

OPEN companyID_Cleanup

FETCH NEXT from companyID_Cleanup into @companyID

while (@@FETCH_STATUS <>-1)

begin

exec (@companyID)

FETCH NEXT from companyID_Cleanup into @companyID

end

DEALLOCATE companyID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a db_name field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @db_name char(150)

declare db_name_Cleanup CURSOR for

select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'' and db_name <> ''''

and db_name not in (select INTERID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'db_name'

set nocount on

OPEN db_name_Cleanup

FETCH NEXT from db_name_Cleanup into @db_name

while (@@FETCH_STATUS <>-1)

begin

exec (@db_name)

FETCH NEXT from db_name_Cleanup into @db_name

end

DEALLOCATE db_name_Cleanup

GO

set nocount on

/* Clear out all tables in DYNAMICS database that have a dbname field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @dbname char(150)

declare dbname_Cleanup CURSOR for

select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''

and DBNAME not in (select INTERID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'DBNAME'

set nocount on

OPEN dbname_Cleanup

FETCH NEXT from dbname_Cleanup into @dbname

while (@@FETCH_STATUS <>-1)

begin

exec (@dbname)

FETCH NEXT from dbname_Cleanup into @dbname

end

DEALLOCATE dbname_Cleanup

GO

set nocount on

/* Remove all stranded references from the other Business Alerts table that

no longer exist in the SY40500 */

delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

GO

Resolving Contract Line Errors

Steps to correct contract line allotments

Dynamics CRM 4 does not provide a way to revise case resolution allotments after the case has been resolved. Reactivating the case and reclosing it will double-dip allotments from the contract lines, leaving them incorrect.
This must be resolved behind the scenes, using SQL scripts.
NOTE: This is not a supported resolution as it directly modifies data rather than relying on the CRM interface. Always make sure and backup a SQL database before modifying data directly.
This article applies specifically to Dynamics CRM v4.0

Get the Case Resolution Activity ID

Use this procedure to get the activity id related to the closed case.
Open the case. Click the History menu item on the left. The top activity should be the case resolution activity. Open it, then click CTRL+N to open the activity in a new browser window.
The Activity ID is the long string between the {} characters

http://server:5555/company/cs/cases/closecase.aspx?id={68C018E8-5D08-E011-B563-001A4B4D64BE}&_CreateFromType=112&_CreateFromId=%7b616C59EA-2DE8-DF11-BA3B-001A4B4D64BE%7d

Get the Contract Line ID

Click through the case to the contract line and use the same procedure as above to get the contract line id.

Double-Check Your Work

Before you start updating all willy-nilly, make sure you have the correct records.
Using SQL Management Studio, connect to the CRM Company database. Use the following scripts to return the records you’re looking to update. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

SELECT *
FROM ContractDetailBase
WHERE ContractDetailId = '<CONTRACT LINE ID>'

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '<RESOLUTION ID>'

Sample:

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '81A0A2C9-DE1F-DF11-9063-001A4B4D64BE'

Verify that you’re looking at the correct record and note the current values. In the ContractDetailBase table, we’re specifically looking at the AllotmentsUsed and AllotmentsRemaining fields. For the IncidentResolutionBase table, we’re changing the TimeSpent field.

Update Your Values

If everything matches up and we’re sure we have the right records, use the scripts below to update the correct fields. Replace the <NEW VALUE> tag with the number you wish to use. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

UPDATE IncidentResolutionBase
SET TimeSpent = '<NEW VALUE>'
Where ActivityId = '<CONTRACT LINE ID>’

UPDATE ContractDetailBase
SET AllotmentsUsed = '<NEW VALUE>', AllotmentsRemaining = '<NEW VALUE>'
WHERE ContractDetailId = '<RESOLUTION ID>'

You’ll first adjust the Resolution Activity to the correct value. Make a note of how much it as changed and then adjust the Allotments Used on the Contract Line accordingly. Then, use the same number and adjust the Allotments Remaining as well.
For example, we closed a case and resolved it for 30 minutes, when actually it should have been resolved for 45 minutes. The Contract Line is for a total of 600 minutes (10 hours) and after we closed this case, 4 hours (240) had already been used up.
Old Values

Case Resolution: TimeSpent = 30
Contract Line: Allotments Used = 240
Contract Line: AllotmentsRemaining = 360

Corrected Values

Case Resolution: TimeSpent = 45
Contract Line: Allotments Used = 255
Contract Line: AllotmentsRemaining = 345

GP: Retrieve List of Company Names & IDs

Here’s a script to return you a quick list of the company ids (which are also the database name) and the company names for the companies defined in the DYNAMICS database

SELECT INTERID,CMPNYNAM FROM SY01500
ORDER BY CMPNYNAM

GP: Reset the System Password

Working as a GP Consultant, I occasionally run into a situation where a client doesn’t remember their System Password for GP. You know, the one that let’s you add users, change company information, the admin level tasks in GP.

The only supported solution is to contact MBS Support, who will walk you thru a process to gather information to send them.

There’s an easier way. Run the following script in SQL to reset the system password to blank. You can then go back into Setup and set it to whatever you want.

update DYNAMICS.dbo.SY02400
set PASSWORD = 0x00202020202020202020202020202020
WHERE DMYPWDID = 1

GP: Error setting User Access to Company

In Dynamics GP, administrators have the ability to control what companies users can access. This also controls what users see when the click the drop-down company field during login.

The menu instructions for this post are for GP 9.0. Newer versions provide the same functionality, although menu layout may be slightly different.

To change User Access for a user, click Tools / Setup / System / User Access. Clicking a username will display all of the available databases on the right and you can click the checkbox to allow or disallow access to specific companies for that user.

Every once in a while, especially have migrating to a new server or merging companies, you’ll get an error message “The user could not be added to one or more database”

A typical reason for this is that on the SQL side of the installation, the GP user already exists as a SQL user on the database, but GP doesn’t know about it. When you click the checkbox, GP tries to add that user to the database users. Since the object already exists, SQL kicks back an error that GP doesn’t know how to deal with.

To resolve the error, open SQL Management Studio and connect to your GP SQL instance. Then, run the following script, replacing <dbname> with the Database name of the company you’re trying to add the user to and replacing <username> with the user’s SQL login id. Our example is for company “Jim’s Bike Shop” that has a database name of JBS and the user id being jfallon.

USE <dbname>
DROP USER <userid>

So for our example, the code would be

USE JBS
DROP USER jfallon

Go back into GP and you should now be able to add the user to the company.