Sunday, December 29, 2013

An error occurred while applying security information to

While attempting to move files from a hard drive I recovered I kept getting the following error on a specific set of files.

An error occurred while applying security information to
{G:\Folder}
Failed to enumerate objects in the container. Access is denied.

In order to fix it did the following.

-Open a Command Prompt "As Administrator"

-Run the following commands:

takeown /f G:\folder/r /d y
icacls G:\folder /grant administrators:F /T

Tuesday, November 19, 2013

IE11 __dopostback undefined

After attempting to access my web app in IE11 I noticed all the links were no longer working.  Looking through the JavaScript debugger I saw the error "__dopostback undefined" when attempting to click a link.  Solution turned out to be installing .NET 4.5 on the server.

http://www.microsoft.com/en-us/download/details.aspx?id=30653

Tuesday, November 5, 2013

Repeat Column Headers for Tablix in SSRS 2008

If you are having trouble getting the column headings to repeat on every page for a table/tablix in SSRS 2008 try this. Open the file in a text editor and look for the Tablixrowheirarchy->tablixMembers->TablixMember node. Then add the following elements.

<keepwithgroup>After</keepwithgroup>
<repeatonnewpage>true</repeatonnewpage>
<keeptogether>true</keeptogether>


Monday, November 4, 2013

SQL - Generate Scripts to Drop Tables/Views

You can use the commands below to create SQL commands which will drop tables/views.  the were clause can be used to limit which tables/views are included in the statement.

Drop Tables

SELECT 'DROP TABLE [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'CRM%'
AND TABLE_TYPE = 'BASE TABLE'



Drop Views

SELECT 'DROP VIEW [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOT LIKE 'CRM%'

SQL - Add Header Row to SSIS Generated CSV Files

After generating a flat file in SSIS I needed to update that file with the column headers as the first row.  In order to do this I used a SQL stored procedure.

STEP 1: Run the code below to create the stored procedure.

USE MIGRATION_DB

IF OBJECT_ID('GenerateBCPforSSIS') IS NOT NULL
DROP PROC GenerateBCPforSSIS
GO

CREATE PROCEDURE GenerateBCPforSSIS
(
@db_name varchar(1000),
@table_name varchar(1000),
@file_name varchar(1000)
) AS

Declare @Headers varchar(MAX),@HeadersRaw varchar(MAX),@sql varchar(MAX), @header_file varchar(MAX), @filename_short varchar(MAX)

--Generate column names as a recordset
Select @Headers = IsNull(@Headers + ',', '') + '""' + Column_Name + '""'
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

--Create a dummy file to have header data
select @header_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@header_file+'" -c -C RAW -t, -S localhost -T'
print @sql

set @sql = 'type "'+@file_name+'" >> "'+@header_file+'"'
print @sql

set @sql = 'del "'+@file_name+'"'
print @sql

set @filename_short = reverse(substring(reverse(@file_name),1,charindex('\',reverse(@file_name)) -1 ) )

set @sql = 'rename "'+@header_file+'" '+'"'+@filename_short+'"'
print @sql

GO




STEP 2: Run the following command which will produce sql commands for all the views and tables in your database.  You can add where clauses to the statements to limit which views/tables commands are created for.  Note: Make sure to update the MIGRATION_DB string to the name of your database

USE MIGRATION_DB

SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.views

UNION

SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.tables



STEP 3: Copy the output from the command in step 2 and paste it into a new SQL query window.


STEP 4: Copy the output from step 3 and past it into a batch file.


STEP 5: Run the batch file, it will open your SSIS csv file and insert a header row with the column names. 


Notes:



  • The CVS file output from SSIS must match the names of your Views/Tables

  • If you change, add, or remove any of the columns in your table/view you will have to run steps 2-5 again.

SQL - Remove Special Characters

Removes all special characters from an input string.

USE MIGRATION_DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID (N'dbo.RemoveSpecialChars', N'FN') IS NOT NULL
DROP FUNCTION dbo.RemoveSpecialChars;
GO
CREATE FUNCTION dbo.RemoveSpecialChars
( @InputString VARCHAR(max)
)
RETURNS VARCHAR(8000) BEGIN
IF @InputString IS NULL RETURN NULL

DECLARE @OutputString VARCHAR(max)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @c INT
SET @c = ASCII(SUBSTRING(@InputString, @p, 1))
IF @c BETWEEN 48 AND 57
OR @c BETWEEN 65 AND 90
OR @c BETWEEN 97 AND 122
--OR @c = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END IF LEN(@OutputString) = 0 RETURN NULL
RETURN @OutputString
END
GO

SQL - Remove Non-Alpha Characters

This function will remove all non-alphanumeric characters.
USE MIGRATION_DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID (N'dbo.RemoveNonAlphaCharacters', N'FN') IS NOT NULL
DROP FUNCTION dbo.RemoveNonAlphaCharacters;
GO
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(max))
Returns VarChar(max)
AS
BEGIN

DECLARE @KeepValues as varchar(50) = '%[^a-z]%'
WHILE PatIndex(@KeepValues, @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

RETURN @Temp
END
GO

SQL - Remove Line Breaks From Text

This function will attempt to remove line breaks from text in SQL.
USE MIGRATION_DB
GO
IF OBJECT_ID (N'dbo.RemoveLineBreak', N'FN') IS NOT NULL
DROP FUNCTION dbo.RemoveLineBreak;
GO
CREATE FUNCTION dbo.RemoveLineBreak
(@TEXT VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @fixedTEXT VARCHAR(MAX) = @TEXT

SET @fixedTEXT = REPLACE(REPLACE(REPLACE(REPLACE(@TEXT, CHAR(10), ''), CHAR(13), ''), '\r', ' '), '\n', ' ')

RETURN @fixedTEXT
END
GO

SQL - Get Nth Word

Need to find the Nth word in a sentence, try this function.
USE MIGRATION_DB
GO
IF OBJECT_ID (N'dbo.GetNthWord', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetNthWord;
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetNthWord
(
@string varchar(max),
@n int
)
RETURNS varchar(max) AS
BEGIN
DECLARE
@xml xml,
@s nvarchar(max),
@wd varchar(100)

set @xml = '<root><record>' + REPLACE(@string, ' ','</record><record>') +
'</record></root>';

With rs(id, word) AS
(
select ROW_NUMBER ()over( order by (select 1))id , * from(
select
t.value('.','varchar(150)') as [items]
from @xml.nodes('//root/record') as a(t)) data
where len([items]) > 0
)

select @wd=word from rs where id=@n
return(@wd)
END

Friday, October 25, 2013

Wake Up IIS Websites

One of the biggest issues I have found with using authentication methods like Kerberos Constrained Delegation is that the first person who hits the site in the morning gets a 500 error until the website application pools have been rebuilt after their nightly recycle.  Recently I discovered a great module for IIS that can help prevent this.

http://www.iis.net/downloads/microsoft/application-initialization

"IIS Application Initialization for IIS 7.5 enables website administrators to improve the responsiveness of their Web sites by loading the Web applications before the first request arrives. By proactively loading and initializing all the dependencies such as database connections, compilation of ASP.NET code, and loading of modules, IT Professionals can ensure their Web sites are responsive at all times even if their Web sites use a custom request pipeline or if the Application Pool is recycled. While an application is being initialized, IIS can also be configured to return an alternate response such as static content as a placeholder or "splash page" until an application has completed its initialization tasks."

Also you will probably want a UI that allows you to do the configuration, here you go:
http://blogs.msdn.com/b/amol/archive/2013/01/25/application-initialization-ui-for-iis-7-5.aspx


Additional Resources:
http://blogs.iis.net/wadeh/archive/2012/05/01/application-initialization-part-2.aspx
http://dynamics.co.il/crm-iis-site-wake/

Thursday, October 24, 2013

Get Times for CRM Import Jobs

This script will give you the times for CRM import jobs that have been run on your system.

--Shows all import records and the amount of time they took
SELECT [Name], [StartedOn], [CompletedOn],
DATEDIFF(MINUTE, [StartedOn], [CompletedOn]) AS [Diff In Minutes]
FROM [Default_MSCRM].[dbo].[AsyncOperationBase]
WHERE OperationType IN (3,4,5) --OperationType 3,4,5 are the ones which relate to imports


--Shows total number of minutes for all imports
SELECT SUM(DATEDIFF(MINUTE, [StartedOn], [CompletedOn])) AS [Total Diff In Minutes]
FROM [Default_MSCRM].[dbo].[AsyncOperationBase]
WHERE OperationType IN (3,4,5)


--Shows total number of hours for all imports
SELECT SUM(DATEDIFF(MINUTE, [StartedOn], [CompletedOn])) /60 AS [Total Diff In Hours]
FROM [Default_MSCRM].[dbo].[AsyncOperationBase]
WHERE OperationType IN (3,4,5)

Tuesday, October 22, 2013

SSIS Speed: DefaultBufferMaxRows

SSIS can sometimes take a long time to load.  Try messing witht he DefaultBufferMaxRows setting in the package.  In a first attempt on one table changing the setting from 10,000 (the default) to 1,000 saved me about 15 minutes off my run time.

Thanks to Jamie Thomson for posting this info: http://consultingblogs.emc.com/jamiethomson/archive/2007/12/18/SSIS_3A00_-A-performance-tuning-success-story.aspx

Tuesday, September 24, 2013

Reset Default Org for CRM Users

The script below will change the default org for users.  This can be helpful if you have disabled the original default org and users are now getting the crm error that the organization has been disabled.  Just change the @NewOrgName value to the value of your database name without the _MSCRM suffix.

USE MSCRM_Config
DECLARE @NewOrgName VARCHAR(64)
SET @NewOrgName = 'DefenseReadyUM'

UPDATE [MSCRM_CONFIG].[dbo].[SystemUser] SET [DefaultOrganizationId] =
(SELECT [Id] FROM [MSCRM_CONFIG].[dbo].[Organization] WHERE [DatabaseName] = @NewOrgName + '_MSCRM')
WHERE EXISTS (SELECT [Id] FROM [MSCRM_CONFIG].[dbo].[Organization] WHERE [Id] = [MSCRM_CONFIG].[dbo].[SystemUser].[DefaultOrganizationId])

Friday, August 23, 2013

CRM 2011 Performance Settings

REGISTRY

Disable Loopback check
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
           New DWORD Value: DisableLoopbackCheck = 1 (Decimal)

CRM Settings
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
          New DWORD Value: OLEDBTimeout = 86400 (Decimal)
          New DWORD Value: ExtendedTimeout = 1000000 (Decimal)
          New DWORD Value: NormalTimeout = 300000 (Decimal)
          New DWORD Value: AsyncRemoveCompletedWorkflows = 1 (Decimal)
          New DWORD Value: AsyncRemoveCompletedJobs = 1 (Decimal)

TCP/IP Settings
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
          New DWORD Value: MaxUserPort = 65534 (Decimal)
          New DWORD Value: TcpTimedWaitDelay = 260 (Decimal)

SQL

Max Degree of Parallelism and Other SQL Settings
exec sp_configure 'show adv', 1;
RECONFIGURE WITH OVERRIDE;
--reduces locking by ensureing queries are only run against a single processor
exec sp_configure 'max degree', 1;
RECONFIGURE WITH OVERRIDE;
--reduces ASYNC_NETWORK_IO locks that occur 
--when large queries hog all the memory on a machine
--and the machine can no longer access the IO.
--The value of this setting should be modified based 
--upon the amount of ram on your machine.
exec sp_configure 'max server memory', 8192;
RECONFIGURE WITH OVERRIDE;
--Enable SQL CLR which can increase timezone conversion
--on advanced find
exec sp_configure 'clr enabled', '1';
RECONFIGURE WITH OVERRIDE;
exec sp_configure;

Read Snapshot Isolation

How to check to see if it’s already turned on:
SELECT name, is_read_committed_snapshot_on FROM sys.databases

How to turn it on for a specific database:
DECLARE @DBNAME VARCHAR(100);
SET @DBNAME = 'Default_MSCRM'; --Update this based upon your database name

DECLARE @query varchar(max);
SET @query = 'ALTER DATABASE {DATABASE} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' +
'ALTER DATABASE {DATABASE} SET ALLOW_SNAPSHOT_ISOLATION ON;' +
'ALTER DATABASE {DATABASE} SET READ_COMMITTED_SNAPSHOT ON;' +
'ALTER DATABASE {DATABASE} SET MULTI_USER;'
SET @query = REPLACE(@query, '{DATABASE}', @DBNAME);
exec(@query);


SqlCommandTimeout

USE MSCRM_CONFIG
UPDATE DeploymentProperties
SET IntColumn = 9000
WHERE ColumnName = 'SqlCommandTimeout'


IIS


WCF/SOAP Compression

Open a command promp 'As Administrator' and run the following command:
%SYSTEMROOT%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpCompression /+"dynamicTypes.[mimeType='application/soap%u002bxml; charset=utf-8',enabled='true']" /commit:apphost


JSON Compression

Open a command promp 'As Administrator' and run the following command:
%SYSTEMROOT%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpCompression /+"dynamicTypes.[mimeType='application/json; charset=utf-8',enabled='true']" /commit:apphost



http://blogs.msdn.com/b/crminthefield/archive/2011/12/29/enable-wcf-compression-to-improve-crm-2011-network-performance.aspx

Static Content Caching

This will help ensure that some static content files used by CRM will also be cached.  It ensures that the Response Header is not set to * but instead to Accept-Encoding.


  • Open IIS Manager
  • Click on Microsoft Dynamics CRM Website
  • Click on Configuration Editor
  • Navigate to system.web/caching/outputCache
  • Switch omitVaryStart to True and click the Apply button.

image

http://blogs.msdn.com/b/crminthefield/archive/2014/12/19/static-content-not-cached-properly-in-dynamics-crm-due-to-vary-header.aspx

Kerberos and Windows Auth

Update setting to reduce 401 responses when using Kerberos and Windows Auth at the same time. This will not hurt anything if they are only using windows auth.

- Run a command prompt As Administrator
- At the command prompt, type the following commands, and then press ENTER:

cd %SystemRoot%\System32\inetsrv
appcmd set config /section:windowsAuthentication /authPersistNonNTLM:true

http://support.microsoft.com/kb/954873





Wednesday, July 24, 2013

Get Sketchup Working on Windows Surface Pro

After installing Sketchup every time I clicked on the "Start Using Sketchup" button located on the Welcome window I would get an error that closed the application.  Apparently the default drivers included on the Surface Pro were causing the problem.  After updating the drivers from the link below Sketchup started functioning perfectly.

https://downloadcenter.intel.com/Detail_Desc.aspx?agr=Y&DwnldID=22842&lang=eng&OSVersion=Windows%208%2C%2064-bit*&DownloadType=Drivers


Tuesday, May 21, 2013

SQL Statements to Get Basic Info About Tables

Here are two scripts I found that will help you generate basic info about your database.  This data can be useful when beginning the level of effort for a data migration.
NUMBER OF COLUMNS PER TABLE
SELECT TABLE_NAME, COUNT(*) AS COLUMN_COUNT
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME

image


RECORD COUNT FOR TABLE
SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC



image

FOREIGN KEY CONSTRAINTS (Relationships)
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY TABLE_NAME

image

Thursday, May 2, 2013

Check Out the DefenseReady Daily Standup App for Windows 8

DefenseReady Daily Standup is a Windows 8 app that provides military commanders and leadership with real-time insights into PERSTEMPO and strength levels throughout their organizations. Inspired by SOCOM’s Preservation of the Force and Families Task Force and designed exclusively for use with the DefenseReady platform, the app is optimized for touch-screen control via mobile devices using Windows 8. With a few easy swipes of the mobile device, military leaders can get detailed visibility into data about their forces that previously took significant man-hours and staff coordination to retrieve and compile. DefenseReady Daily Standup can work both online and offline and will sync with real time data when a DefenseReady server is available.

View in the Windows Store

Screen shot 1

Screen shot 2

Screen shot 3

Screen shot 4

Screen shot 5

Screen shot 6

Friday, March 22, 2013

Fragmented indexes were detected in the Microsoft Dynamics CRM database

Re-index your CRM Org database if you receive this error.

USE Org_MSCRM
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO 

Thursday, February 21, 2013

TimeGem – Free Tile Layout Software

As a true techie I always look to find computer solutions that can help me save time and money.  I decided to tackle putting down tile in my basement and wondered if there was any software that could help me plan the layout.  I was pleasantly surprised to find TileGem, a freeware app.  After taking measurements I added in the walls, chose the tile, and set the grout size.  Once the room was set up it was easy to drag around the tile grid to find the best way to completely the layout.

http://www.tilegem.com

image

Monday, February 4, 2013

Update Thumbprint in Web.Config After Updating ADFS 2.0 Certificate

Recently I had to replace an expired certificate on my ADFS 2.0 machine.  I followed the instruction on the TechNet wiki found here.

http://social.technet.microsoft.com/wiki/contents/articles/2554.ad-fs-2-0-how-to-replace-the-ssl-service-communications-token-signing-and-token-decrypting-certificates.aspx

The instructions were great but there is one more step that you need to complete before your website will connect correctly.

Once you have the thumbprint of the certificate you are using for ADFS 2.0 you must then update the web.config of each website that is utilizing ADFS for authentication.  Be careful when copying the thumbprint from the certificate properties window.  Make sure to remove all the spaces between the data before pasting it into the thumbprint property of the web.config.

image

Install/Update Certificate for Remote Desktop Gateway

 

To import a certificate to the RD Gateway server in the (Local Computer)/Personal Store


  1. Open the Certificates snap-in console. If you have not already added the Certificates snap-in console, you can do so by doing the following:

    1. Click Start, click Run, type mmc, and then click OK.
    2. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue..
    3. On the File menu, click Add/Remove Snap-in.
    4. In the Add or Remove Snap-ins dialog box, in the Available snap-ins list, click Certificates, and then click Add.
    5. In the Certificates snap-in dialog box, click Computer account, and then click Next.
    6. In the Select Computer dialog box, click Local computer: (the computer this console is running on), and then click Finish.
    7. In the Add or Remove Snap-ins dialog box, click OK.
  2. In the Certificates snap-in console, in the console tree, expand Certificates (Local Computer), and then click Personal.

  3. Right-click the Personal folder, point to All Tasks, and then click Import.

  4. On the Welcome to the Certificate Import Wizard page, click Next.

  5. On the File to Import page, in the File name box, specify the name of the certificate that you want to import, and then click Next.

  6. If the Password page appears, if you specified a password for the private key associated with the certificate earlier, type the password, and then click Next.

  7. On the Certificate Store page, accept the default option, and then click Next.

  8. On the Completing the Certificate Import Wizard page, confirm that the correct certificate has been selected.

  9. Click Finish.

  10. After the certificate import has successfully completed, a message appears confirming that the import was successful. Click OK.

  11. With Certificates selected in the console tree, in the details pane, verify that the correct certificate appears in the list of certificates on the RD Gateway server. The certificate must be under the Personal store of the local computer.

To import a certificate to be used by the RD Gateway server


  1. SERVE 2008 R2: Open RD Gateway Manager. To open RD Gateway Manager, click Start, point to Administrative Tools, point to Remote Desktop Services, and then click RD Gateway Manager.

    SERVER 2008: Open TS Gateway Manager.  To open TS Gateway Manager, click Start, point to Administrative Tools, point to Terminal Services, and then click TS Gateway Manager.

  2. In the RD Gateway Manager console tree, right-click the local RD Gateway server, and then click Properties.

  3. On the SSL Certificate tab, click Select an existing certificate for SSL encryption (recommended), and then click Browse Certificates.

  4. In the Install Certificate dialog box, click the certificate that you want to use, and then click Install.

  5. Click OK to close the Properties dialog box for the RD Gateway Manager server.

  6. If this is the first time that you have mapped the RD Gateway Manager certificate, after the certificate mapping is completed, you can verify that the mapping was successful by viewing the RD Gateway Server Status area in RD Gateway Manager. Under Configuration Status and Configuration Tasks, the warning stating that a server certificate is not yet installed or selected and the View or modify certificate properties hyperlink are no longer displayed.

Friday, February 1, 2013

Page Not Found 404 After Install or Uninstalling CRM 2011 UR

After uninstalling an update rollup and rebooting the machine CRM starting coming back with a 404 Page Not Found error when trying to access the site.

The trace log showed the following error:
Crm Exception: Message: Invalid license. PidGen.dll cannot be loaded from this path C:\Program Files\Microsoft Dynamics CRM\Server\bin\PidGen.dll, ErrorCode: –2147167677

This machine was set to NOT automatically install windows updates.  To solve my issue run windows update and get all the current updates other than any new CRM Update Rollups you do not wish to install.  After the windows update and another reboot everything was working again. 

Thursday, January 31, 2013

Deletion State Codes Fields Preventing Solution Import


If you have upgraded a CRM database from 4.0. to 2011 you may have some leftover fields that end in dsc.  These fields can later on come back to haunt you by showing up as missing components when import a solution into 2011. The fields will always end in “dsc” as you can see in the image below.
image

John Hoven has written a SQL script which will remove these attributes from the database.  After removing them re-export the solution and attempt the import again.
-- Delete the custom DSC attributes from the database.
 
DELETE a 
--select A.LogicalName  
FROM   metadataschema.attribute a 
      
INNER JOIN metadataschema.entity e 
        
ON e.entityid = a.entityid 
WHERE  a.attributeof IS NOT NULL 
      
AND a.attributetypeid = '00000000-0000-0000-00AA-110000000019' 
      
AND a.logicalname LIKE '%dsc' 
      
AND a.iscustomfield = 1 
-- Delete the dsc attribute map columns
DELETE am 
--select AM.*  
FROM   dependencybase db 
      
INNER JOIN dependencynodebase dnb 
        
ON db.dependentcomponentnodeid = dnb.dependencynodeid 
      
INNER JOIN attributemapbase am 
        
ON am.attributemapid = dnb.objectid 
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid 
                                     
FROM   dependencynodebase dnb 
                                            
LEFT JOIN metadataschema.attribute 
                                                      
a 
                                              
ON dnb.objectid = a.attributeid 
                                     
WHERE  dnb.componenttype = 2 
                                            
AND a.logicalname IS NULL) 
-- delete attribute map required dependencies on dsc columns 
DELETE db 
FROM   dependencybase db 
      
INNER JOIN dependencynodebase dnb 
        
ON db.dependentcomponentnodeid = dnb.dependencynodeid 
WHERE  db.dependentcomponentnodeid IN (SELECT dnb.dependencynodeid 
                                      
FROM   dependencynodebase dnb 
                                             
LEFT JOIN metadataschema.attribute 
                                                       
a 
                                               
ON dnb.objectid = a.attributeid 
                                      
WHERE  dnb.componenttype = 2 
                                             
AND a.logicalname IS NULL) 
-- Delete the attribute dependencies on dsc columns 
DELETE db 
FROM   dependencybase db 
      
INNER JOIN dependencynodebase dnb 
        
ON db.dependentcomponentnodeid = dnb.dependencynodeid 
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid 
                                     
FROM   dependencynodebase dnb 
                                            
LEFT JOIN metadataschema.attribute 
                                                       a 
                                              
ON dnb.objectid = a.attributeid 
                                     
WHERE  dnb.componenttype = 2 
                                            
AND a.logicalname IS NULL) 
-- Delete the dependency nodes 
DELETE dnb 
-- select A.LogicalName  
FROM   dependencynodebase dnb 
      
LEFT JOIN metadataschema.attribute a 
        
ON dnb.objectid = a.attributeid 
WHERE  dnb.componenttype = 2 
      
AND a.logicalname IS NULL