All posts by Asish Punnose

List out all triggers in a SQL Server database

Execute the below query to get the list of triggers

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

 

Search a text in whole database in SQL Server

exec SearchText ‘SearchText’, 0

 

Create  PROCEDURE [dbo].[SearchText]
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM    Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE   Table_Type = ‘Base Table’
And Data_Type In (‘ntext’,’text’,’nvarchar’,’nchar’,’varchar’,’char’)

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN ‘If Exists(Select *
From   ReplaceTableName
Where  Convert(nVarChar(4000), [ReplaceColumnName])
= ”’ + @DataToFind + ”’
)
Set @DataExists = 1
Else
Set @DataExists = 0′
ELSE ‘If Exists(Select *
From   ReplaceTableName
Where  Convert(nVarChar(4000), [ReplaceColumnName])
Like ”%’ + @DataToFind + ‘%”
)
Set @DataExists = 1
Else
Set @DataExists = 0’
END,
@PARAMETERS = ‘@DataExists Bit OUTPUT’,
@i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
BEGIN
SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, ‘ReplaceTableName’, QUOTENAME(SchemaName) + ‘.’ + QUOTENAME(TableName)), ‘ReplaceColumnName’, ColumnName)
FROM    @Temp
WHERE   RowId = @i

PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

SET @i = @i + 1
END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1

Ports to be opened before installing Microsoft Dynamics CRM 2016

When you install Microsoft Dynamic CRM  with SQL Server you may come across this error

The SQL Server ‘SQLServerUsedforCRM’ is unavailable.

System.ComponentModel.Win32Exception (0x80004005): The network path was not found
at Microsoft.Crm.Setup.Common.Utility.NetworkUtility.GetJoinDomainName(String server)
at Microsoft.Crm.Setup.Server.CrmSqlDomainValidator.InternalCheck(IDictionary parameters)

 

On the CRM server unblock these ports  80,135,136,139,443,445,123,137,138,445,1025

On the SQL Server unblock these ports  80,135,139,445,1433,123,137,138,445,1025

 

Network ports for the Microsoft Dynamics CRM Web application

The following table lists the ports used for a server that is running a Full Server installation of Microsoft Dynamics CRM. Moreover, except for the Microsoft SQL Server role, and the Microsoft Dynamics CRM Connector for SQL Server Reporting Services server role, all server roles are installed on the same computer.

Protocol Port Description Explanation
TCP 80 HTTP Default Web application port. This port may be different as it can be changed during Microsoft Dynamics CRM Server Setup. For new Web sites, the default port number is 5555.
TCP 135 MSRPC RPC endpoint resolution.
TCP 139 NETBIOS-SSN NETBIOS session service.
TCP 443 HTTPS Default secure HTTP port. The port number may differ from the default port. This secure network transport must be manually configured. Although this port is not required to run Microsoft Dynamics CRM, we strongly recommend it. For information about how to configure HTTPS for Microsoft Dynamics CRM, see “Make Microsoft Dynamics CRM 4.0 client-to-server network communications more secure” in the Microsoft Dynamics CRM Installing GuideMake Microsoft Dynamics CRM client-to-server network communications more secure .
TCP 445 Microsoft-DS Active Directory directory service required for Active Directory access and authentication.
UDP 123 NTP Network Time Protocol.
UDP 137 NETBIOS-NS NETBIOS name service.
UDP 138 NETBIOS-dgm NETBIOS datagram service.
UDP 445 Microsoft-DS Active Directory service required for Active Directory access and authentication.
UDP 1025 Blackjack DCOM, used as an RPC listener.

Network ports that are used by the SQL Server that runs the Microsoft Dynamics CRM Connector for SQL Server Reporting Services server roles

The following table lists the ports that are used for a computer that is running SQL Server and has only SQL Server and the Microsoft Dynamics CRM Connector for SQL Server Reporting Services server roles installed.

Protocol Port Description Explanation
TCP 135 MSRPC RPC endpoint resolution.
TCP 139 NETBIOS-SSN NETBIOS session service.
TCP 445 Microsoft-DS Active Directory required for Active Directory access and authentication.
TCP 1433 ms-sql-s SQL Server sockets service. This port is required for access to SQL Server.This number may be different if you have configured your default instance of SQL Server to use a different port number or you are using a named instance.
UDP 123 NTP Network Time Protocol.
UDP 137 NETBIOS-NS NETBIOS name service.
UDP 138 NETBIOS-dgm NETBIOS datagram service.
UDP 445 Microsoft-DS Active Directory service required for Active Directory access and authentication.
UDP 1025 Blackjack DCOM, used as an RPC listener.

Resolved:- No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file.

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

Solution:-

Install Entity Framework using NuGet, right click on Reference and click on Manage Nuget Packages. Search and install   Entity Framework

PM> Install-Package EntityFramework

The connection string ‘Connect’ in the application’s configuration file does not contain the required providerName attribute.\

<connectionStrings>
    <clear/>
    <add name="ApplicationServices" connectionString="Data Source=PC-X;Initial Catalog=MYdb;Integrated Security=True"/>
  </connectionStrings>


 

Solution:-

 

You’re missing the following piece of code after the connectionString attribute (assuming that you’re using SQL):

providerName="System.Data.SqlClient"

 

Change to

<connectionStrings>
    <clear/>
    <add name="ApplicationServices" connectionString="Data Source=PC-X;Initial Catalog=MYdb;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Unable to install .NET Framework 3.5 on Windows Server 2012 R2

dotnet35-instalationfailed

Installation of one or more roles, role services, or features failed.
The source files could not be found. Try installing the roles, role services, or features again in a new Add Roles and Features Wizard session, and on the Confirmation page of the wizard, click “Specify an alternate source path” to specify a valid location of the source files that are required for the installation. The location must be accessible by the computer account of the destination server.

 

Solution:-

This behavior can also be caused by a system administrator who configures the computer to use Windows Server Update Services (WSUS) instead of the Microsoft Windows Update server for servicing. In this case, contact your system administrator and request that they enable the Specify settings for optional component installation and component repair Group Policy setting and configure the Alternate source file path value or select the Contact Windows Update directly to download repair content instead of Windows Server Update Services (WSUS) option.

To configure the Group Policy setting, follow these steps:

  1. Start the Local Group Policy Editor or Group Policy Management Console.Windows 8 and Windows Server 2012: Point to the upper-right corner of the screen, click Search, type group policy, click Settings, and then click Edit group policy.
    2915293Windows 8.1 and Windows Server 2012 R2: Point to the upper-right corner of the screen, click Search, type group policy, and then click Edit group policy.
    2915294
  2. Expand Computer Configuration, expand Administrative Templates, and then select System. The screen shot for this step is listed below.
    2796659
  3. Open the Specify settings for optional component installation and component repair Group Policy setting, and then select Enabled. The screen shot for this step is listed below.
    2796660
  4. If you want to specify an alternative source file, in the Alternate source file path box, specify a fully qualified path of a shared folder that contains the contents of the \sources\sxs folder from the installation media.
    Example of a shared folder path: \\server_name\share\Win8sxs

    Or, specify a WIM file. To specify a WIM file as an alternative source file location, add the prefix WIM: to the path, and then add the index of the image that you want to use in the WIM file as a suffix.

    Example of a WIM file path: WIM:\\server_name\share\install.wim:3
    Note In this example, 3 represents the index of the image in which the feature files are found.
  5. If it is applicable to do this, select the Contact Windows Update directly to download repair content instead of Windows Server Update Services (WSUS) check box.
  6. Tap or click OK.
  7. At an elevated command prompt, type the following command, and then press Enter to apply the policy immediately:
    gpupdate /force