| Author: Biju 26 Jul 2008 | Member Level: Gold | Rating:  Points: 6 |
Users running SQL Server Profiler against ADO, OLE DB, ODBC, and DB-Library applications may notice the use of system stored procedures that are not covered in the Transact-SQL Reference. These stored procedures are used by the Microsoft OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the DB-Library dynamic-link library (DLL) to implement the functionality of a database API. These stored procedures are simply the mechanism the provider or drivers use to communicate user requests to SQL Server. They are intended only for the internal use of the OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the DB-Library DLL. Calling them explicitly from a SQL Server application is not supported.
The complete functionality from these stored procedures is made available to SQL Server applications through the API functions they support. For example, the cursor functionality of the sp_cursor system stored procedures is made available to OLE DB applications through the OLE DB API cursor properties and methods, to ODBC applications through the ODBC cursor attributes and functions, and to DB-Library applications through the DB-Library Cursor Library.
These system stored procedures support the cursor functionality of ADO, OLE DB, ODBC, and the DB-Library Cursor Library:
sp_cursor sp_cursorclose sp_cursorexecute
sp_cursorfetch sp_cursoropen sp_cursoroption
sp_cursorprepare sp_cursorunprepare
These system stored procedures support the prepare/execute model of executing Transact-SQL statements in ADO, OLE DB, and ODBC:
sp_execute sp_prepare sp_unprepare
The sp_createorphan and sp_droporphans stored procedures are used for ODBC ntext, text, and image processing.
The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction.
The sp_sdidebug stored procedure is used by SQL Server for debugging Transact-SQL statements.
|
| Author: vasanthiraajan 26 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
hi BIJU,
Thanks for Quick Reply..
Thank U Very Much..
Regards,
Vasu.
|
| Author: Bunty 27 Jul 2008 | Member Level: Diamond | Rating: Points: 4 |
Hi,
System Stored Procedures:
The system stored procedures have names prefixed with sp_ .These primarily support various administrative tasks that help to manage SQL Server.The system databases,which store system stored procedures,are master and msdb databases.
Thanks and Regards
S.S.Bajoria
Thanks & Regards
S.S.Bajoria
|
| Author: Ratheesh 30 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
Most of the business logic was written in TSQL and front end developed in vb, asp and vb.Net and there were no proper documentation available. In most of the situation we had to do an impact analysis for the data base changes. Adding one parameter in one stored procedure will impact many other stored procedure since these procedures are called from other procedures. Most of the time calling state ment was embedded in the strings. To identify the impacted procedure sys_depends (which is system stored procedure used to identify the dependency) is not reliable since most of the time it's failed to give reliable result. But we found that sql server stored the entire database informations in the system tables. With system table literally you can query anything related to your database.
SQL server database comes with many sotred procedures which will aid you in managing the sql server objects.This article will give you a brief introduction on various system stored procedures and explain how you can write sql queries instead of using the system procedures.
Advantage
Writting your own SQL Query will give you more control over the result .If you know your system tables you can manage any situation. You don't have to remember hundreds of stored procedures to get your work done. I listed few examples here, you can just explore the system tables to get more information.
In a sql server database you can find the system stored procedure under the master database. Normally the names begins with sp_<procedure name> ( it is a good practice not to begin your stored procedure with sp_ ).
|