How to list Extended stored procedure for public

Extended stored procedures are used by developers to create routines. One of the main benefits of extended stored procedures is the ability to make them appear as regular stored procedures. i.e input parameters can be sent by the calling application , and result sets are returned. SQL Server is installed with a bunch of extended stored procedure with permissions granted to public. You’ll recognise quite a few including : sp_cursor, sp_cursor_open ,sp_executesql . There are about 80 of them installed with public permissions. Microsoft announced to extended stored procedures will be deleted in future versions , so if you’re a... Read more →

SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN. I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ... Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve. You... Read more →

How to check if a file exists with xp_fileexist

Question: How can I find if a file exists on the OS using the sql command language? I’m executing some stored procedures, requiring some checks on files . Answer: The extended stored procedure xp_fileexist will return some information about whether the file passed through the input parameter exists exec master.sys.xp_fileexist 'C:\file.txt' File Exists File is a Directory Parent Directory Exists 1 0 1 From a scripting perspective, you may just want to return one column with a value. An alternative way of executing sp_fileexist is to add an output parameter.This will return one column , which may be easier to... Read more →

Using NULL as an ordinary value in sql code - SQL Antipattern # 001

Using a NULL value represents an unknown or missing value in a table column. Based on this definition , it’s easy to find examples of code where NULL is used an ordinary value. This antipattern leads to further problems. Identifying and fixing this habit, leads to less ambiguity of the data. The first antipattern is when a NULL is used as an ordinary value. A typical example is : SELECT NULL + 1000 This will result in a NULL . This leads to an unknown value. An unknown value leads to deeper problems in our code, with all sorts of... Read more →

How to create a temporary Stored Procedure using TempDB

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.... Read more →