Manipulate a SQL recordset row with ROW_NUMBER,CTE and CASE

Question: How can I write a query to return a recordset and do something to a specific row? I understand this is a presentation issue - because the obvious answer is to return the recordset and then do some manipulation at the presentation layer, but the current situation requires me o run the query at the sqlcmd line and then create an output file. For example: The main column to focus is on the GeoExposure column , although at a relational level 5000 would appear on every row , I only want 5000 to appear just on the FIRST row.... Read more →

How to find computed columns on a SQL Server table

Question: I'm having a problem with an index rebuild job , which is failing on tables with computed columns. I've read ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF which details a solution for situations of computed columns, xml data type and LOB data types. How can I find the computed columns on sql tables? Answer: These are two ways in which you can identify the computed columns. One method is through the sys.columns and the other through sys.computed_columns . sys.computed_columns has the benefit of not requiring you to use the "is_computed = 1" predicate. Method 1 -... Read more →

Use cases for NULLIF

Question: I understand the theory behind NULLIF – based on the MS documentation “Returns a null value if the two specified expressions are equal”. But I’ve rarely had the opportunity to apply this function in real word scenarios . Can you expand on some real world scenarios for NULLIF ? Answer: You are right – it’s not a widely used function , I think mainly because the more widely used expression CASE is used Basically NULLIF(value1,value2) Is the equivalent to CASE WHEN value1 = value2 THEN NULL ELSE value1 END Using CASE gives you the advantage of utilising other expressions... Read more →

Msg 16955 – Could not create an acceptable cursor

Question: I’m getting an error when checking some new application installed with a SQL Server backend. How can I fix? The sql code and the associated error are below: declare @p1 int set @p1=0 declare @p3 int set @p3=98305 declare @p4 int set @p4=311300 declare @p5 int set @p5=0 exec sp_cursoropen @p1 output,N'SELECT GetDate()',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 Msg 16955, Level 16, State 2, Line 10 Could not create an acceptable cursor. Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1 [Batch Start Line 9] The cursor was not declared. Answer: This error is occurring... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →