Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
- How to find privilege changes with SQL Server Default Trace
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
How to create a temporary Stored Procedure using TempDB
29 September,2015 by Tom Collins
Question: I have to execute a stored procedure for troubleshooting – but permanent stored procedures are disallowed. Is there a way to create a temporary stored procedure - ?
Answer: Yes, you can create a temporary stored procedure in SQL Server , using tempdb. There are two options. A stored procedure available only to your session and a stored procedure available to more than your session
Option 1 – Stored Procedure available only for your session
CREATE PROC #myStoredProcedure
If another session attempts to execute the stored procedure , the user will see a “cannot find the stored procedure” message.
Option 2 – Stored Procedure available to more than your session
CREATE PROC ##ourStoredProcedure
Restrictions on compiling permanent stored procedures can be a problem – but often there a good security reasons . Speak to the DBA about other workarounds.
Read More on Stored Procedures
SQL Server - Grant execute on all stored procedures - SQL Server ...
SQL Server - Last time a stored procedure was executed - SQL ...
Xp_cmdshell – the most dangerous extended stored procedure
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |