How can I retrieve teh TEXT field alone from this? I need to get the Server Manaufacturer from thsi query:ĮXEC sys.xp_readerrorlog 0, 1, N'Manufacturer' Wednesday, J12:45:48 PM - Clifton S Dunaway INSERT INTO #temp(LogDate, ProcessInfo, TextData)ĮXEC sp_readerrorlog 0, 1, N'Manufacturer' If the statistics are boring, you’ve got the wrong numbers.Ĭlifton, the only way to do that would be to load the data to a temp table and then pull the data you need.ĬREATE TABLE #temp ( LogDate datetime, ProcessInfo varchar(100), TextData varchar(max) ) This script will show all but logon issues:ĭROP TABLE IF EXISTS #SQLErrorLog -assuming SQL Server 2016+.otherwise use the typical if object exists drop We had a recovery of one of our biggest databases (> 100 TB) and logs were filled with logon issues due to thousands of connections not being able to connect. It's more flexible if you put everything in a temp table. This allows you to query the data much easier. How I can get this Result In SQL 2008 R2, SQL 20014, SQL 2016 Sir I want to Know Deleted record from specific Table with specific date period Tuesday, Decem11:32:41 AM - Mehwish Mushtaq Inamdar Reference: Add APPLY to Your TSQL Tool Belt from our JCG partner John Sonmez at the Making the Complex Simple.Tuesday, Decem11:56:25 AM - Greg Robidoux Any time you are trying to join against a table function (this is actually what CROSS APPLY was created for.). You can change it to a CROSS APPLY that does the filter in the CROSS APPLY statement. A query that has a large set of data it is joining against and then filtering out.Anywhere you are using a Common Table Expression (CTE) could possibly be rewritten as a CROSS APPLY.A query where you are doing a sub-query, but need more than one value from the sub-query.(Example: one column in the first table tells you how many rows in the 2nd table to get) A query where the result set you want to JOIN against is in some way related to the data in the first set.Here are some general guidelines of times when you might want to use CROSS APPLY: There isn’t a good solid rule you can use to identify when you should use a CROSS APPLY but having the knowledge of CROSS APPLY and how it works can help you when you are trying to tune queries and you are having a difficult time constructing one. If you first try to rewrite the sub-select as a JOIN and find that you can’t, you may be able to write it as a CROSS APPLY. If you wanted to use other columns from the sub-select, you would have to do another sub-select for each of these columns. The other instance in which CROSS APPLY will be useful is when you are doing a sub-select that has more than one value you would like to use in your final query.įor example if you were sub-selecting from an Order Details table to match up order ids that had a Quantity greater than 5, that sub-select would need to return exactly one column in order for you to use it in your where clause. So CROSS APPLY is useful whenever you have some data that you would want to be able to join against, but are forced to do some kind of sub-query instead because the data you are trying to join is not going to map well against a single key. Using CROSS APPLY it is simple though: SELECT * There are a few ways to do it without using CROSS APPLY, but none of them are really very easy or perform very well. Take a minute and think about how you would write that query. What if I said give me the three most recent orders for each customer? Remember how I said it can do more than a simple join? Joins are restricted to only joining two sets of data that could be queries independently of each other. If both of those queries have no results, then you know the results from each query are the same since EXCEPT will show any results that are in the top query but not in the bottom one. Just run this exact query again swapping the SQL above the EXCEPT with the SQL below and make sure it has no results as well. We can prove these results sets are exactly the same by using EXCEPT to make sure there are no rows in one set that aren’t in the other and then flipping it, like so: SELECT *
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |