Ytdyklly

I'm looking for alternatives in SQL Server for the below three functions which are currently performed using Oracle.

These are the Oracle features we need to use in SQL Server basically:

1. 
   DBMS_Application (Tracing option in Oracle).


2. 
   DBMS_Session (Used in Oracle).


3. Disable or enable DBMS_Output at database level.

We are moving from Oracle to SQL Server and we want to ensure the functionality equivalents are available and ready to use so I can start testing.

What would be the SQL Server functionality equivalent of these features we use with Oracle?

DBMS_APPLICATION_INFO

Note: You should test and pick which works best or which combinations of these work best for your needs. Test thoroughly and compare results while ensuring none of these cause overhead issues with the SQL Server instance. For example SQL Profiler is resource intense so use caution.

Based on the general description of the DBMS_APPLICATION_INFO package functionality per the Oracle docs, a few things with SQL Server will provide equivalent functionality. I'll list a few below for you to dig into with more detail and for a starting point, etc.

• 
  

  DBMS_APPLICATION_INFO

  
  
  

  Application developers can use the DBMS_APPLICATION_INFO package with
  Oracle Trace and the SQL trace facility to record names of executing
  modules or transactions in the database for later use when tracking
  the performance of various modules and debugging.

  
  
  

  Source

  
  

The SQL Server DBMS_APPLICATION_INFO equivelants. . .

• SQL Server Profiler


• Extended Events


• SQL Server Audit


• System Dynamic Management Views


• 
  

  sp_whoisactive

  
  
  
  
  
  • Automate capturing SQL Server activity with SP_WHOISACTIVE and SP_WHO2 via a SQL Agent job
  
  
  
  

DBMS_SESSION

Based on the general description of the DBMS_SESSION package functionality per the Oracle docs, use ALTER EVENT SESSION and ALTER ROLE or sp_addrolemember for providing equivalent functionality with SQL Server.

• 
  

  DBMS_SESSION

  
  
  

  This package provides access to SQL ALTER SESSION and SET ROLE
  statements, and other session information, from PL/SQL. You can use
  DBMS_SESSION to set preferences and security levels.

  
  
  

  Source

  
  

DBMS_OUTPUT

Based on the general description of the DBMS_OUTPUT package functionality per the Oracle docs, you can just use PRINT or SELECT to output the message and write the logic accordingly. Without getting into all the detail of the DBMS_OUTPUT Subprograms one by one, it seems that's the general and basic functionality it provides is controlling output and debug output of stored procedures and such.

• 
  

  DBMS_OUTPUT

  
  
  

  The DBMS_OUTPUT package enables you to send messages from stored
  procedures, packages, and triggers. The package is especially useful
  for displaying PL/SQL debugging information

  
  

Additionally and for example, according to a response on the "How to Print the Value of a Variable in SQL Server" post it is stated and sounds right to me too based on my past MSSQL usage. . .

• 
  

  I don't think there is a straight up SQL equivalent. I don't know of
  any direct capabilities for leaving a list for a trigger or another
  routine. Usually, I would just use a #temp table or function
  parameters to pass intermediate results.

  
  
  

  If you want to see intermediate results for edit purposes, the PRINT
  function works - e.g. in a stored procedure, it's a common debugging
  routine to insert print statements to show intermediate results in the
  edit environment.

  
  
  

  Source

  
  

Further Resources

• Serveroutput from a TSQL procedure