Close

T-SQL

T-SQL

SQL Server 2016 Cluster – Partially Running – CEIP

SQL Server 2016 Cluster – Partially Running [half red/half green] – came across below issue, and upon further investigation l noticed the nodes has CEIP versions of Analysis services as well as Agent. Simply removing these roles, returned the server roles status to running/green   Click here for reference link on msdn.microsoft forum Click here […]

Read More

SQL Server Error – SSMS GUI – Mismatch in column datatype and tessellation scheme

When below error occurs, remember to change the Tesselation scheme, in this case from Geometry grid to Geography auto [if the case]   Example code:   SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF   GO CREATE SPATIAL INDEX [SpatialIndex-Vegetation1] ON [dbo].[VEGETATION1] […]

Read More

CLR – Check if enabled, enable it.. etc – SQL Server

“The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio:”  ….  Microsoft Essential commands: –check SELECT * FROM sys.configurations WHERE […]

Read More

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

On a server running SQL 2014 SSIS with SSISDB configured, one of the SQL Agent jobs configured to run using a proxy account kept failing with the error below. Message Unable to start execution of step 1 (reason: JobOwner domain\domainuser doesn‘t have permissions to use proxy 1 for subsystem SSIS). The step failed. Solution that […]

Read More

Transferring logins and passwords between separate SQL server Instances

Transferring logins and passwords between separate SQL Server instances eg SVR1\DEV to SVR1\DEV2 can be done by scripting out the encrypted passwords using the stored procedures sp_hexadecimal and sp_help_revlogin which can be found by following this detailed link by clicking here.

Read More

Microsoft SQL Server Merge Replication Client Status Information

Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool. SELECT TOP 1000 [subscriber_server] ,[db_name] ,SubStatus = CASE [status] WHEN 0 THEN ‘Inactive’ WHEN 1 THEN ‘Active’ WHEN 2 THEN ‘Deleted’ ELSE ‘N/A’ END […]

Read More