Tag: SQL Server 2014

SQL Server

Three Undocumented SQL Server 2014 XML Stored Procedures

Three Undocumented SQL Server 2014 XML Stored Procedures SQL Server 2014 supports the following three undocumented XML system stored procedures: sp_syscollector_validate_xml sp_xml_schema_rowset sp_xml_schema_rowset2 sp_syscollector_validate_xml The sp_syscollector_validate_xml system stored procedure is used to validate the XML collector. Syntax sp_syscollector_validate_xml [ @collector_type_uid = ] ‘collector_type_uid’, [ @name = ] ‘name’, [ @parameters = ] ‘parameters’ Arguments [ @collector_type_uid = ] ‘collector_type_uid’ Is […]

SQL Server

Four Undocumented SQL Server 2014 Agent Procedures

Four Undocumented SQL Server 2014 Agent Procedures SQL Server 2014 supports the following useful undocumented SQL Server Agent stored procedures: sp_MSgetalertinfo sp_readerrorlog sp_sqlagent_is_srvrolemember sp_sqlagent_get_startup_info Two of these stored procedures exist in the master database (sp_MSgetalertinfo and sp_readerrorlog), other exist in the msdb database. sp_MSgetalertinfo The sp_MSgetalertinfo stored procedure is used to get information about alerts from Windows registry. Syntax sp_MSgetalertinfo […]

SQL Server

Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures

Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures SQL Server 2014 supports the following useful undocumented database maintenance plan stored procedures: sp_clear_dbmaintplan_by_db sp_maintplan_close_logentry sp_maintplan_delete_log sp_maintplan_delete_plan sp_maintplan_delete_subplan sp_maintplan_open_logentry sp_maintplan_start sp_maintplan_subplans_by_job sp_maintplan_update_subplan_tsx Note. These stored procedures exist in the msdb database, not in master. sp_clear_dbmaintplan_by_db The sp_clear_dbmaintplan_by_db database maintenance plan stored procedure is used to delete the maintenance plans, the […]

SQL Server

Undocumented SQL Server 2014 extended stored procedures (Part 1)

Undocumented SQL Server 2014 extended stored procedures (Part 1) In this article, you can find the description of some useful undocumented SQL Server 2014 extended stored procedures. sp_MSgetversion This extended stored procedure can be used to get the current version of Microsoft SQL Server. Syntax: EXECUTE sp_MSgetversion For example, to get the current SQL Server version, you can run: EXEC […]

SQL Server

Troubleshooting SQL Server 2014 System Stored Procedures

Troubleshooting SQL Server 2014 System Stored Procedures If you have problems with SQL Server 2014 system stored procedures, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2014 service pack. Because many system stored procedures bugs were fixed in SQL Server 2014 service packs, you should install the latest SQL Server service pack. At the […]

SQL Server

Troubleshooting problems with locking in SQL Server 2014 (Part 2)

Troubleshooting problems with locking in SQL Server 2014 (Part 2) If you have problems with SQL Server 2014 locking, review this troubleshooting checklist to find potential solutions. 1. Encapsulate transactions within SQL Server stored procedures. This can reduce network traffic, because your client will send to server only the stored procedure name (perhaps with some parameters), and reduce the amount […]

SQL Server

Troubleshooting Upgrading to SQL Server 2014

Troubleshooting Upgrading to SQL Server 2014 Should you have problems with upgrading to SQL Server 2014, review this troubleshooting checklist to find potential solutions. 1. Check the hardware requirements. To upgrade to SQL Server 2014, you should have the following hardware: – Intel or compatible platform with processor speed: 1.0 GHz minimum for a 32-bit x86 implementation and a 1.4 […]

SQL Server

Understanding Service Broker to perform asynchronous operations (Part-2)

Understanding Service Broker to perform asynchronous operations – (Part-2)   Author: Basit A. Farooq   Creating queues Next, you create the queue using the CREATE QUEUE statement. You must specify queue_name as a fully-qualified name unless you are creating it in the current database using your default schema.   The syntax is:   CREATE QUEUE [ WITH [ STATUS = […]