Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree (2022-04-13)

SQLServerCentral Newsletter for April 13, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree]( - [How to create a table using SQL Server Management Studio]( - [ICYMI: Dealing with Database Data and Metadata in Flyway Developments]( - [From the SQL Server Central Blogs - Book Review – Snowflake Security]( - [From the SQL Server Central Blogs - Creating a Generic SSRS Report]( Question of the Day - [Cross Platform Migration]( The Voice of the DBA  Daily Coping Tip Eat some healthy food. Try something new today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. When to Use Zero I'm not great at building charts and graphs. I can build a basic chart, but I often depend on the tooling I use to size, scale, etc. appropriately for whatever I'm graphing. That, or I just use a basic graph that starts from zero and has some sort of linear scale. There are plenty of misleading charts, especially used by the media that want to show some particular aspect of data that suits the story they are reporting. Many of these misleading charts often don't start at zero, and they end up scaling in a way that can confused people. You wouldn't think it was hard to decide whether or not to scale a graph of data from zero or not, but it can be complicated, as [this post shows](. There is a flowchart to help you decide when to include zero, or even when to have an inset chart that better explains the data. As many of us know, it can be easy to misinterpret data, especially when someone else is deciding what to show. In this case, the post talks about some examples of what the data doesn't graph well because the scale is too large and the range of data too small. Or when the scale distorts the relative size of two values in a graph. In all of the examples, it makes perfect sense why you do or do not include zero on your axis, but I don't know that I know when I should or shouldn't do this. Part of the problem for me is that people often glance at visualizations and charts without spending enough time to really study them. That's part of the idea of a visual, in that we can get information quickly from a picture, as opposed to a chart of numbers. However, it is still easy to look at a broken scale or inset chart and not spend the time to comprehend that one value isn't twice another, but that the chart is zoomed in because the scale changed. To me, a lot of misunderstanding gets cleared up when we discuss the chart and the data in a group. Often one person will realize when others are not reading the scale when drawing their conclusions and remind others that Canada isn't twice as large as Brazil. That works if people speak up and if others listen. That isn't always the case, especially when the boss is making a mistake. The other issue is that many of us might look at a chart by ourselves. and we are unlikely to tell ourselves we are misreading the values. There isn't a good way to ensure people read a scale and factor that into any decision they make regarding the chart. I do always like to include some data with a visual, that way I can see raw numbers on the same report, or by drilling in. The combination works well for me, but I'm a geeky, numbers person. I like seeing data, which is why I like working with databases. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to ScriptDOM]( [Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree]( Diligentdba 46159 from SQLServerCentral In this level of the Stairway to ScriptDOM, we examine the way the tool parses scripts and creates tokens from the text. [External Article]( [How to create a table using SQL Server Management Studio]( Additional Articles from MSSQLTips.com In this article we walk through things you should know to create new tables in SQL Server using the SQL Server Management Studio interface. [External Article]( [ICYMI: Dealing with Database Data and Metadata in Flyway Developments]( Additional Articles from Redgate In case you missed it! Before you get very far with database development you need to be clear about your strategy for handling data. In this article I'll explain some of these issues in general terms, and then demonstrate how you can navigate these problems easily with Flyway. [Blog Post]( From the SQL Server Central Blogs - [Book Review – Snowflake Security]( Koen Verbeeck from Koen Verbeeck When I was working with Snowflake on a project, it seemed security (assigning roles to users, assigning permissions etc.) is not as straight forward as it is in SQL... [Blog Post]( From the SQL Server Central Blogs - [Creating a Generic SSRS Report]( Tim Mitchell from Tim Mitchell Creating useful reports is part art and part science. On one end of the spectrum, you have visually appealing and highly customized reports and dashboards that are truly works...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Cross Platform Migration If I needed to migrate an Oracle database to SQL Server, which tool is best suited for this? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Removing Text From All Rows In a database, I have some data that describes pieces and parts for a custom built PC. I have a sample of the data in this CTE: WITH ctePC AS ( SELECT expression FROM ( VALUES ('\1\cpu') , ('\1\ram') , ('\1\ssd') , ('\1\gpu') , ('\1\cooler') , ('\1\fan')) a (expression) ) Each part has a hierarchy value separated by backslashes and then the name of the part. If I wanted to get the name of each of the parts, which code should I use to complete the query? Answer: replace(ctePC.expression, '\1\', '') Explanation: In this case, we want to remove the '\1\' text from the string, as this isn't part of the name. The REPLACE function will remove this code. The RIGHT function could work in this case if all the parts had three characters in them, but that isn't the case. Ref: REPLACE - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Measuring Ad-hoc workload on a per database level]( - Ok so I need to see how much workload on a server (per database) is ad-hoc. Gut feel its high for one db in particular . is the following a valid way to determine this or is there a better approach ? SELECT Convert(INT,Sum ( CASE a.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) * […] SQL Server 2016 - Development and T-SQL [Where Can I Download Install Media for SQL Server 2016?]( - This seems like a simple thing but I've been unsuccessful in my search thus far and figured I'd check if anyone here knows right off where I should go for this; Where can I download install media for SQL Server 2016? I've got an existing installation at work I need to add reporting services to […] [syntax error get data from linked server]( - This query runs but doesn't get any info. I think it's something in the syntax, but can't find the issue. If I query a table using Linked Server I can see the data I need. DECLARE @SQL VARCHAR(MAX) = '' ; SELECT @SQL += REPLACE(REPLACE(' RAISERROR("-----------------------------------",0,0) WITH NOWAIT; RAISERROR("Processing <>",0,0) WITH NOWAIT; INSERT INTO BC_Data_Capture_Staging […] [Extracting the clientapp for the victim of a deadlock report]( - Given a deadlock report, I need to extract the clientapp that was the victim process. The query I've written works and is below: DECLARE @path [nvarchar](MAX) SELECT TOP 1 @path = REPLACE([path] + '\system_health*.xel', '\\', '\') FROM [sys].[dm_os_server_diagnostics_log_configurations] WHERE [is_enabled] = 1 DECLARE @data TABLE ([deadlock] , [exec_time] [datetime]) INSERT INTO @data SELECT CONVERT(XML, [event_data]).query('/event/data/value/child::*') […] Administration - SQL Server 2014 [SQL Server Management 2014 Windows 10]( - Hey guys I have an installation for SQL Server Management 2014 (SQLEXPRADV_x64_ENU.exe is the full name) which I used for Windows 8.1. I installed Windows 10 recently and tried to use the same installation but the rules fail where I don't have Microsoft .NET Framework 3.5 Service Pack 1. I can't find an installation for […] Development - SQL Server 2014 [I need to convert column warehouse to Wh1 and Wh2 with the corresponding on hand]( - I need to convert column warehouse to Wh1 and Wh2 with the corresponding on hand  select DATENAME(dw,ompRequestedShipDate) as day , omlPartID as Part , sum(omlOrderQuantity) as ordered , case when imbWarehouseID ='' then 'WH1' else imbWarehouseID end as warehouse , cast(imbQuantityOnHand as INT) as onhand from SalesOrders left outer join SalesOrderLines on omlSalesOrderID=ompSalesOrderID left […] SQL Server 2019 - Administration [Bringing Data from SQL Server to AWS automatically]( - We have a requirement where we want to bring data present in SQL Server to AWS - SQL Server (i.e. AWS RDS). At present the data is present in on-prem SQL Server and on 1st of every month, the data is then transferred from SQL server to excel sheet. The excel sheet contains 7 different […] [Windows 11 Pro]( - Hi everyone I just got a new machine. It is running Windows 11 Pro. I am trying to install SS2019 but I am coming across issues. I am getting below error: "Oops... A required file could not be downloaded. This could mean the version of the installer is no longer supported. Please download again from […] SQL Server 2019 - Development [Performance related issue after using left join to bring new fields to the query]( -  i have stored procedure which was running fantastic. When ran with parameters it gives results in 25 seconds. I needed to bring new fields to the query used left join SELECT AccountNumber , payment_date ,Account_date FROM CORE.AccountData(NOlock) WHERE Prop_TYPE NOT LIKE '%Rond%' AND (CASE WHEN CAST([CreatedDate] AS Date) < CAST([ProposalDate] AS Date) THEN CAST([CreatedDate] […] [Create constraint using index]( - Is there a way in T-SQL to use an existing index as source for a constraint? Goal: Have a constraint with an included column In Oracle it is possible with "using index" CREATE TABLE MYTEST ( ID number(1,0) not null ,ID2 number(1,0) not null ) ; CREATE INDEX IX_ID2 ON MYTEST (ID2,ID); ALTER TABLE MYTEST […] [Trying to re-id a database table and getting error on the first select keyword]( - I have a small table ( < 1000 lines). I deleted the first entry ID 1 as it was invalid. I know it is not necessary to Re-ID it, however I want the first entry to be ID 1 not ID 2. I tried the following script; CREATE TABLE Codelines_backup AS SELECT ID, Rail_Road, NCS_Codeline, […] SQL Server 2008 - General [How to get the Parameters of a Parameterized Query from dm_exec_sql_text]( - SELECT sqltext.TEXT FROM sys.dm_exec_query_stats AS CP CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext I ran the said sql statement and get some sql scripts and the following is one of them (@P1 nvarchar(10), @P2 nvarchar(20)) select * from purchline where purchid = @p1 and vendorcode= @p2 I tried to join with dm_exec_query_plan to get the values for […] Amazon AWS and other cloud vendors [AWS S3 with video editing?]( - I'm looking for a solution where I can add the cloud storage as a shared network drive or folder on my PC and then directly edit heavy videos from the cloud via my connection. I have a 10 Gigabit internet connection and all the hardware to support that amount of load. However it seems like […] General Cloud Computing Questions [Aws reporting question]( - Looking for an idea / workflow to automate reporting for system manager nodes . I know I can get a list of nodes and also get a list of running ec2 instances but what I want is to get a list of the running instances rhat are NOT showing up as managed nodes (so ec2 […] Azure Machine Learning [performance management / other gotchas]( - I'm moving into a project where ML will be a component, primarily it will be as a DBA although I do have some DEV (SSIS / Tsql) experience. I'm wondering are there any good resources yet on how to admin MS ML on SQL Server , I'm finding a lot of info on how to […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -