DELETE FROM OPENQUERY

Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like : SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Can a similar method be used but for DELETE? Answer: To use DELETE , it's a similar process to the SELECT but using the DELETE syntax. Taking the SELECT example above - you'll do the following. Note:this example will delete ALL the rows in the linked server table . : DELETE FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Read more on OPENQUERY,Linked Servers and AS400 SQL Server – Datetime date range , AS400... Read more →

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 →