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 […]
Tag: SQL Server 2014
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 […]
Five Undocumented SQL Server 2014 Database Mail Procedures
Five Undocumented SQL Server 2014 Database Mail Procedures SQL Server 2014 supports the following useful undocumented database mail stored procedures: sp_RunMailQuery sp_SendMailMessage sp_sysmail_activate sysmail_verify_account_sp sysmail_verify_profile_sp Note. These stored procedures exist in the msdb database, not in master. sp_RunMailQuery The sp_RunMailQuery database mail stored procedure is used to run the mail query. This stored procedure is used in the sp_send_dbmail stored […]
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 […]
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 […]
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 […]
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 […]
Troubleshooting problems with locking in SQL Server 2014 (Part 1)
Troubleshooting problems with locking in SQL Server 2014 (Part 1) If you have problems with SQL Server 2014 locking, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2014 service pack. Because many locking bugs were fixed in SQL Server 2014 service packs, you should install the latest SQL Server service pack. At the time […]
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 […]
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 = […]