How to read and parse the Microsoft SQL Server extended event log file xel file

Question: I'm trying to read the Microsoft SQL Server Extended Event Log File - xel file for the SQL Server Extended Events system_health extended event. Just opening it up in Notepad - produces human unreadable output . How can I read the Extended Event Log File output file , using t0-sql? Answer: Microsoft have provided various methods and SQL functions which can read the Extended Event Log File file . There are multiple options. It is possible to list out the records within the Log file - this option will return a recordset, as xml output. You may want to... Read more →

How to import JSON file into a SQL recordset with OPENJSON

Question: I have a JSON file loaded daily onto a drive . I need to set up a process to read the JSON file and translate into a SQL recordset. Eventually - it will be added to a SQL table. Is it possible to read a simple JSON file using T_SQL ? Answer: It is possible to read a json file using T-SQL.There are a number of different methods. By using the OPENROWSET functionality , ISJSON and OPENJSON function you can quickly read the file , check if the JSON is valid and then unpack the JSON into a SQL... Read more →

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 →