Installing Logmein When a Console Session Isn’t Available

At my company, we use LogMeIn to provide remote support to our client base. It’s an incredible tool.

However, when installing LogMeIn, it doesn’t work to install thru an RDP session. What happens is that LMI binds to that session rather than to the console and once you logout, there’s nothing left to connect to and you get that black box error about Terminal Server problems.

Here’s the workaround – We’re going to install a VNC server on our target and the VNC viewer on the workstation we’re on. Then when we make the connection via VNC, we’ll be connecting to the console of the server rather than the RDP session we’re using to get there and will be able to install LogMeIn properly.

• Connect to a machine on the network. This should not be the target server we’re trying to install LMI on. It can be a workstation or another server. We’ll call this the host machine.
• RDP to the server you want to add to LogMeIn. We’ll call this the target server.
• Download TightVNC here – www.tightvnc.com
• On the target server, install TightVNC with both the server and viewer components. Make sure you set the passwords during the install process, otherwise it won’t work.

 

 

 

• On the host machine, install the viewer only.
• Once you have the viewer installed on the host, run the viewer client. Choose Listening Mode on the right side.

 

 

• The connection screen will disappear and will minimize to an icon in the system tray.
• On the target server, you should also have an icon in the system tray. Right click and choose Add Listening Viewer
• As a target, enter the host machine’s name or IP address.
• You should now have a window pop-up on the host machine with a view of the Console of the target server.
• You can now login and install LMI.
• After you’re done, close the VNC window. Stop the service on the host machine by right clicking on the icon in the system tray and choosing Close Listening daemon. Uninstall TightVNC from the host machine.
• Next, stop the service on the target server and uninstall TightVNC from that machine as well.

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

Error 1935 During NAV v5 Client Install

When installing NAV 5.1 on a workstation running Windows 7, I received the following error –

Error 1935.An error occurred during the installation of assembly component {98CB24AD-52FB-DB5F-A01F-C8B3B9A1E18E}.

HRESULT: 0x800736CC.

After some quick researching, this error relates to a missing assembly component.

To resolve the issue, install the Microsoft Visual C++ 2005 SP1 Redistributable pack. If you’re running on a 64-bit system, you’ll need to install both the 32-bit and 64-bit versions.

x86

x64

Thanks to KB953610 for pointing me in the right direction.

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