Tuesday, July 5, 2016

General Utility - Queries

Get SSIS Version of Deployed SSIS Packages
use msdb;select * from dbo.sysssispackages


SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
-------------------------------------------------------------------------------------------
SQL Query to retrieve Schema name and Display name - CRM 2011
---------------------------------------------------------------------------------------
select en.Name, en.LogicalName, displayname.ObjectColumnName, displayname.Label, IsCustomEntity from
entityview en
inner join LocalizedLabelLogicalView displayname
on en.EntityId = displayname.ObjectId
and displayname.ObjectColumnName = 'LocalizedName'
where IsCustomEntity = 1
and name like '%customerstatus%'
 -- remove this if you want to show the system entities as well
--and add the isActivity = 0 if you want to filter out the Activity entity
--if you want to remove like post album, filter, etc
--and en.Name not like '%msdyn%'
order by label

--------------------------------------------------------------------------



delete from Vrp_PartyReferralDetails_CONTACT where documentnumberreference not in(1000177777,2368903759)
select CustomerNumber,PartyCustomerNumber,DocumentNumberReference,IsNew,ContactId from Vrp_PartyReferralDetails_CONTACT

select * from Vrp_PartyReferralDetails_CONTACT where IsNew=1
and DocumentNumberReference is not null
-- and DocumentNumberReference!=0

select  PartyCustomerNumber, * from Vrp_PartyReferralDetails_COMPANY


update Vrp_PartyReferralDetails_CONTACT set status=1 where

select * from Vrp_PartyReferralDetails_CONTACT A inner join
Vrp_PartyReferralDetails_CONTACT B ON A.DocumentNumberReference=B.DocumentNumberReference
------------------------------------------------------------------------------------------------------------------------------
select top(NumberOfNew)* from
--update B set ContactId=A.ContactId from
(select DocumentNumberReference,count(1) as NumberOfNew from  Vrp_PartyReferralDetails_CONTACT group by DocumentNumberReference,IsNew having IsNew=1
and DocumentNumberReference is not null and DocumentNumberReference!=0) A
 inner join Vrp_PartyReferralDetails_CONTACT B
on A.DocumentNumberReference=B.DocumentNumberReference


select * from  Vrp_PartyReferralDetails_CONTACT where IsNew=1
---------------------------------------------------------------------------------------------------
-- USE THIS TASK JUST AFTER CHECK CONTACT AND COMPANY
-----------------------------------------------------------------------------------------------------
----CONTACT
------------------------
update t set t.IsNew = 0
--select *
from (
    select *, row_number() over(partition by PartyCustomerNumber order by (select CustomerNumber)) row_num
    from Vrp_PartyReferralDetails_CONTACT where IsNew=1 and PartyCustomerNumber is not null and PartyCustomerNumber!=0
) t
where row_num > 1
---------------------------------------------------------------
update t set t.IsNew = 0
--select *
from (
    select *, row_number() over(partition by DocumentNumberReference order by (select CustomerNumber)) row_num
    from Vrp_PartyReferralDetails_CONTACT where IsNew=1 and DocumentNumberReference is not null and DocumentNumberReference!=0
) t
where row_num > 1
--------------------------------------------------------------
--COMPANY
-------------------------------------------------------------
update t set t.IsNew = 0
--select *
from (
    select *, row_number() over(partition by PartyCustomerNumber order by (select CustomerNumber)) row_num
    from Vrp_PartyReferralDetails_COMPANY where IsNew=1 and PartyCustomerNumber is not null and PartyCustomerNumber!=0
) t
where row_num > 1
----------------------------------------------------------------
-- Use this Task after Inserting Contact
--select B.DocumentNumberReference, IsNew,A.ContactId from
update B set ContactId=A.ContactId from
(select distinct DocumentNumberReference, IsNew as IsNewContact,contactId from  Vrp_PartyReferralDetails_CONTACT where IsNew=1
and DocumentNumberReference is not null and DocumentNumberReference!=0) A
 inner join Vrp_PartyReferralDetails_CONTACT B
on A.DocumentNumberReference=B.DocumentNumberReference and B.IsNew=0

update B set ContactId=A.ContactId from
(select distinct PartyCustomerNumber, IsNew as IsNewContact,contactId from  Vrp_PartyReferralDetails_CONTACT where IsNew=1
and PartyCustomerNumber is not null and PartyCustomerNumber!=0) A
 inner join Vrp_PartyReferralDetails_CONTACT B
on A.PartyCustomerNumber=B.PartyCustomerNumber and B.IsNew=0




-- Use this Task after Inserting Company
--update B set AccountId=A.AccountID from
select B.PartyCustomerNumber, IsNew,A.AccountId from
(select distinct DocumentNumberReference, IsNew as IsNewContact,AccountId from Vrp_PartyReferralDetails_COMPANY where IsNew=1
and PartyCustomerNumber is not null and PartyCustomerNumber!=0) A
 inner join Vrp_PartyReferralDetails_COMPANY B
on A.PartyCustomerNumber=B.PartyCustomerNumber and B.IsNew=0



----- To Export Multiple SSIS Package------------------
select foldername as FolderName,
[name] as PackageName
into #PackageNames
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld
on pkg.folderid = fld.folderid
order by FolderName, PackageName

select 'dtutil /SQL '
+ case
when len(FolderName) = 0 then ''
else FolderName + '\'
end
+ PackageName
+ ' /COPY FILE;C:\OutputFolder\'
+ case
when len(FolderName) = 0 then ''
else FolderName + '\'
end
+ PackageName
+ '.dtsx'
+ '/ QUIET' --suppresses prompt to delete if an older file with same name exists
from #PackageNames

drop table #PackageNames 
CRM Activity

select ActivityTypeCode,count(1) as noofActivities from ActivityPointerBase APB
 INNER JOIN EntityView EV ON APB.ActivityTypeCode=EV.ObjectTypeCode
 where APB.StateCode=0
 and EV.name like 'vrp_%'
  group by ActivityTypeCode
  order by noofActivities desc

------------------------------------------------------------------------------
Query to get Column names for CRM entity
-----------------------------------------------------------------------------
Select  * from  [MetadataSchema].[Attribute] a  inner join [MetadataSchema].[Entity] e on  e.EntityId=a.EntityId where e.Name='entity name'