How to execute a SQL batch multiple times with GO

Question: I'd like to execute a batch sql statement through SQL Server management studio multiple times and avoid using a loop statement or other similar constructs . I'm doing some testing requiring multiple returns of recordsets and would like a simple approach - for ad hoc queries Answer: Looking at the SQL Server documentation for the sql server utility GO - indicates the syntax is GO [count]. This means the queries triggered as part of the batch executed will execute the amount of times listed after GO. By default it's 1 , but if you specify a positive integer -... Read more →

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 →