Tech News

In this post, we will explore how a Pod name is generated, Pod Name lifecycle, how it’s used inside a Pod to set the system hostname, and how the system hostname is used by SQL Server to set its server name metadata. Pod Naming in Deployments When deploying SQL Server in Kubernetes using a Deployment, the Pod created by the Deployment Controller will have a name with a structure of <DeploymentName>-<PodTemplateHash>-<PodID> for example, mssql-deployment-8cbdc8ddd-9n7jh. Let’s break that example Pod name down a bit more: mssql-deployment – this is the name of the Deployment specified at metatdata.name. This is stable for the lifecycle of the deployment 8cbdc8ddd – this is a hash of the Pod Template Spec in the Deployment object template.spec. Changing the Pod Template Spec changes this value and also triggers a rollout of the new Pod configuration. 9n7jh – this is a random string assigned to help identify the Pod uniquely. This changes with the lifecycle of the Pod itself. In a default Deployment configuration, the Pod’s name is used to system hostname inside the Pod. In a Deployment, when a Pod is deleted for whatever reason, Pod/Node failure, Pod administratively deleted, or an update to the Pod Template Spec triggering a rollout, the new Pod created will have a new Pod Name and a matching hostname inside the Pod. It is a new Pod after all. This can lead to an interesting scenario inside SQL Server since the Pod name can change. Let’s dig deeper… Server name metadata inside SQL Server running in a Pod To ensure SQL Server’s data has a lifecycle independent of the Pod’s lifecycle, in a basic configuration, a PersistentVolume is used for the instance directory /var/opt/mssql. The first time SQL Server starts up, it copies a set of system databases into the directory /var/opt/mssql. During the initial startup, the current hostname of the Pod is used to set SQL Server system metadata for the server name. Specifically @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers. In Listing 1, is an example Deployment for SQL Server. In this configuration, the hostname inside the Pod will match the current Pod Name. But what happens when the Pod name changes when a Pod is deleted, and new Pod is created with a new name? Let’s walk through that together in the next section. apiVersion: apps/v1 kind: Deployment metadata: name: mssql-deployment spec: replicas: 1 strategy: type: Recreate selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 containers: - name: mssql image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04' ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: pvc-nfs-instance Listing 1 – Example SQL Server Manifest using a Deployment Controller Examining Server Name Metadata When Deploying SQL Server in a Deployment Initial Deployment When the Deployment is created, a Pod is created. In the output below, you can see the name of the Pod is mssql-deployment-bb44b7bf7-nzkmt, and the hostname set inside the Pod is the same, mssql-deployment-bb44b7bf7-nzkmt kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-bb44b7bf7-nzkmt 1/1 Running 0 7s kubectl exec -it mssql-deployment-bb44b7bf7-nzkmt -- /bin/hostname mssql-deployment-bb44b7bf7-nzkmt Check Server Name Metadata Since this is the initial deployment of this SQL Server instance, system databases are copied into /var/opt/mssql, and the server name metadata is set. Let’s query SQL Server for @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers. In the output below you can see all three values match. sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-nzkmt Delete the Currently Running Pod Next, let’s delete a Pod and what happens to the Pod’s name, the Pod’s hostname, and the SQL Server server name metadata. kubectl delete pod mssql-deployment-bb44b7bf7-nzkmt pod "mssql-deployment-bb44b7bf7-nzkmt" deleted I’ve deleted the Pod, and since this is controller by a Deployment controller, it immediately creates a new Pod in its place. This Pod gets a new name. The existing databases and configuration are persisted in the attached PersistentVolume at /var/opt/mssql. These databases are all brought online. In this output below, you can see the new Pod name and hostname are both mssql-deployment-bb44b7bf7-6gm6v. kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-bb44b7bf7-6gm6v 1/1 Running 0 20s kubectl exec -it mssql-deployment-bb44b7bf7-6gm6v -- hostname mssql-deployment-bb44b7bf7-6gm6v What’s in a name? Now let’s query the server name metadata again. In the output below, you can see there are some inconsistencies. We saw above that Pod has a new name and hostname (mssql-deployment-bb44b7bf7-6gm6v), but this change isn’t updating all the server name metadata inside our Instance. The only place it is updated is SERVERPROPERTY('ServerName') the other values still have the initial Pod Name mssql-deployment-bb44b7bf7-nzkmt. sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-6gm6v mssql-deployment-bb44b7bf7-nzkmt Setting a Pod’s Hostname So what do we do about this? Having instability in the server name metadata can break Replication, mess up our server monitoring systems, and even break code. To get the Pod’s hostname to a persistent value, you need to set the template.pod.spec.hostname field in the Deployment. This sets the system hostname inside the Pod to this value. In the code below you, can see I’ve set the template.pod.spec.hostname to sql01. On the initial deployment of a SQL Instance, this is the value that is stored in the Instance server name metadata. If you already have a SQL Server up and running in Kubernetes and did not set the template.pod.spec.hostname value, the server name metadata will need to be updated using standard SQL Server methods with sp_dropserver and sp_addserver. But for demonstration purposes, I’m going to start over as if this is an initial deployment. And deploy the manifest in Listing 2 into my cluster. apiVersion: apps/v1 kind: Deployment metadata: name: mssql-deployment spec: replicas: 1 strategy: type: Recreate selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 hostname: sql01 containers: - name: mssql image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04' ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: pvc-nfs-instance Listing 2 – Example SQL Server Manifest using a Deployment Controller, setting the Pod’s hostname In the output, below the Pod Name is mssql-deployment-8cbdc8ddd-nv8j4, but inside the Pod, the hostname is sql01, and now all three values for our server name metadata match. If this Pod is deleted, the Pod gets a new name, the hostname inside the Pod will still be sql01, and the Pod server name metadata will still be set to sql01. kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-8cbdc8ddd-nv8j4 1/1 Running 0 43s kubectl exec -it mssql-deployment-8cbdc8ddd-nv8j4 -- hostname sql01 sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- sql01 sql01 sql01 Setting the hostname in the Pod Template Spec gives you the ability to persist the hostname and thus the server name metadata inside SQL Server. This is crucial for services and code that depend on a static hostname. A StatefulSet is a Controller in Kubernetes that does give you persistent, stable naming independent of the lifecycle of a Pod. I will explore those in an upcoming blog post. The post Persistent Server Name Metadata When Deploying SQL Server in Kubernetes appeared first on Centino Systems Blog. The post Persistent Server Name Metadata When Deploying SQL Server in Kubernetes appeared first on SQLServerCentral.

Intro OSS 2.0 is a release candidate at this time, so this may change once it’s released. It wasn’t quite clear to me how to get up and running quickly with a docker based setup for OSS 2.0 version, so this may save you some time if you are interested. It also should be very similar to the Windows workflow excepting the basic brew commands and service install commands you’ll just want to flip over to choco install telegraf . Docker Compose Grabbed this from a comment and modified the ports as the were flipped from the 9999 range used during first early access. # docker exec -it influxdb /bin/bash version: "3.1" services: influxdb: restart: always # It will always restart on rebooting machine now, no need to manually manage this container_name: influxdb ports: - '8086:8086' image: 'quay.io/influxdb/influxdb:2.0.0-rc' volumes: - influxdb:/var/lib/influxdb2 command: influxd run --bolt-path /var/lib/influxdb2/influxd.bolt --engine-path /var/lib/influxdb2/engine --store bolt volumes: influxdb: The main modifications I made was ensuring it auto started. Access the instance on localhost:8086. Telegraf It’s pretty straight-forward using homebrew. brew install telegraf The configuration file is created by default at: /usr/local/etc/telegraf.conf as well as the telegraf.d directory. I’m still a bit new on macOS, so once I opened Chronograf, I wanted to try the new http based configuration endpoint, so I used the web gui to create a telegraf config for system metrics and then: brew services stop telegraf micro /usr/Local/Cellar/telegraf/1.15.3/homebrew.mxcl.telegraf.plist I updated the configuration (see line 16). <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>KeepAlive</key> <dict> <key>SuccessfulExit</key> <false/> </dict> <key>Label</key> <string>homebrew.mxcl.telegraf</string> <key>ProgramArguments</key> <array> <string>/usr/local/opt/telegraf/bin/telegraf</string> <string>-config</string> <string>http://localhost:8086/api/v2/telegrafs/068ab4d50aa24000</string> <string>-config-directory</string> <string>/usr/local/etc/telegraf.d</string> </array> <key>RunAtLoad</key> <true/> <key>WorkingDirectory</key> <string>/usr/local/var</string> <key>StandardErrorPath</key> <string>/usr/local/var/log/telegraf.log</string> <key>StandardOutPath</key> <string>/usr/local/var/log/telegraf.log</string> </dict> </plist> Start service with brew services start telegraf and it should start sending data. NOTE: I’m still getting the hang of brew and service management on Linux/macOS, so the first time I did this it didn’t work and I ended up starting it using telegraf -config http://localhost:8086/api/v2/telegrafs/068ab4d50aa24000 and just running initially in my console. Any comments on if I did something wrong here would be appreciated ?? I’m pretty sure the culprit is the need for the INFLUX_TOKEN environment variable and I’m not sure if the service load with brew is actually sourcing the .profile I put this in. Maybe I can pass it explicitly? Final Result I like the final result. Dark theme for the win. I’ve had some spikes in Vscode recently, impacting my CPU so I’ve been meaning to do something like this for a while, but finally got it knocked out today once I realized there was a 2.0 docker release I could use to get up and running easily. Next step will be to add some process level detail so I can track the culprit (probably VScode + Docker Codespaces). Wishlist Pretty formatting of date/time like Grafana does, such as converting seconds into hour/minutes. Log viewing api so I could query cloudwatch logs like Grafana offers without needing to ingest. Edit existing telegraf configuration in the load data section. Right now I can’t edit. MSSQL Custom SQL Server query plugin to be released ?? Issue 1894 & PR 3069 Right now I’ve done custom exec based queries using dbatools and locally included PowerShell modules. This sorta defeats the flexibility of having a custom query call so I can minimize external dependencies. #development #influxdb The post Quick Start to Using Influxdb on Macos appeared first on SQLServerCentral.

 Unfortunately a common error in many of our client environments is this: -- Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. -- There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed - a complex query. https://hbr.org/resources/images/article_assets/2018/05/may18_9_170094377.jpg The gotcha of course is finding the complex query, especially if it only occurs intermittently or during varying times of the day. My go-to tool for this and other similar situations is our old friend Extended Events. One of the keys with Extended Events is to only trap the needed information - only the relevant events, and only the relevant fields for those particular events. Here is the code for a session to capture 8623 events: -- CREATE EVENT SESSION [Error8623] ON SERVER ADD EVENT sqlserver.error_reported(ACTION(package0.collect_system_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.username)WHERE ([error_number]=(8623)))ADD TARGET package0.event_file(SET filename=N'Error8623'), ADD TARGET package0.ring_bufferWITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)GO -- This will collect the relevant info but *only* about error 8623's.  Because there is no path built into the event_file target, it will by default write to the SQL Server error log path.  As you can see we are also not collecting a lot of different data fields - just the minimum we need to run down the culprit and where it may be coming from. My preferred method to access data like this is to query the event file via TSQL - a query for this particular file would look like this: -- SELECT  n.value ('(action[@name="server_instance_name"]/value)[1]','nvarchar(500)') AS [server_instance_name] , n.value ('(action[@name="collect_system_time"]/value)[1]', 'datetime') AS [collect_system_time] , n.value ('(data[@name="error_number"]/value)[1]', 'int') AS [error_number] , n.value ('(action[@name="database_id"]/value)[1]', 'int') AS [database_id] , n.value ('(action[@name="database_name"]/value)[1]', 'nvarchar(500)') AS [database_name] , n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(500)') AS [client_app_name] , n.value ('(action[@name="server_principal_name"]/value)[1]','nvarchar(500)') AS [server_principal_name] , n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(500)') AS [client_hostname] , n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(max)') AS [sql_text] FROM  ( SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file( N'Error8623*.xel' , NULL , NULL , NULL) ) AS Event_Data_Table CROSS APPLY event_data.nodes('event') AS q(n) -- Again by not providing an explicit path to the XEL file, the fn_xe_file_target_read_file function looks in the default SQL Server error log path for the file(s) - if you *do* store your event_file output somewhere else, you need to include that explicit path! One other important note relates to the sql_text field - you can see in the script I have it cast as an nvarchar(max) which is especially important here because we are going to be pulling back complex queries, which almost always equates to *long* queries - often even a 4000 or 8000 character field will not hold these queries without chopping them off! https://pixabay.com/photos/man-breaking-technique-pavers-elbow-80638/ Also note that this query returns the collect_system_time value in its default format, which is UTC.  To convert that to the local server time, you need to wrap it in a couple of functions as described in a Stack Overflow post at https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement/57503319: Instead of: n.value ('(action[@name="collect_system_time"]/value)[1]', 'datetime') AS [collect_system_time] Wrap it like this: DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),n.value ('(action[@name="collect_system_time"]/value)[1]', 'datetime')) AS [collect_system_time in local time] -- Hopefully you will find this useful, and this is truly meaningful for any error you are trying to run down - simply change the filter in the XEvents session definition from 8623 to whatever error you are looking for (and probably change the name of the event_file), and voila - you now have a session to run down the source queries for that new error, without any changes to the data return query. At this point you may wonder what a "complex" query looks like - here is one sample I have found recently: -- SELECT Item_ID, PA_Rank, Pj_Hours, Pj_Cnt, Bio_Rank FROM (  SELECT SM.Staff_ID AS Item_ID,    0 AS PA_Rank,    0 AS Pj_Hours,    0 AS Pj_Cnt,    0 AS Bio_Rank  FROM Staff_Main SM WITH (NOLOCK)) TBL INNER JOIN Staff_Main SM WITH (NOLOCK) ON SM.Staff_ID = TBL.Item_ID INNER JOIN Office_Main OM WITH (NOLOCK) ON OM.Office_ID = SM.Office_Topic_ID WHERE SM.Staff_ID > -40   AND SM.Is_Active = 1   AND (SM.Staff_Position_ID IN (106))   AND (SM.Office_Topic_ID IN           (SELECT * FROM dbo.List_SubTopics(2005514)))   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1013)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1014)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1032)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000002)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8597)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1116)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1000)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1001)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1080)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000010)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8589)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8590)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1081)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8588)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1041)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1117)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1003)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8605)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1004)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1005)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8598)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1024)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000009)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1045)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1007)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1008)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1009)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1010)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1127)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1119)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1042)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1120)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1012)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1101)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8599)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000003)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1083)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8592)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1048)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1015)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1043)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1121)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1038)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1039)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1016)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1046)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000012)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1017)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1018)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1019)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1020)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1122)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8603)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1118)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1123)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1021)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8602)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1022)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1124)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1125)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000007)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1023)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1126)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000004)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1103)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8604)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1025)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8591)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1128)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1026)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1107)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1082)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1027)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1028)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1044)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1047)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1029)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1030)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8606)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000008)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000005)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1129)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1031)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000011)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1130)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8601)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 9000000006)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1033)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8600)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1131)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1034)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8585)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1040)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1035)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1132)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1133)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8586)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1036)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1037)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8594)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8587)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 1134)   AND EXISTS     (SELECT Nav_Staff_ID FROM HR_Emp_Language1 SL WITH (NOLOCK)      WHERE SL.Nav_Staff_ID = SM.Staff_ID        AND SL.Language_Rec_ID = 8593)   AND EXISTS    (SELECT TSA.Topic_Staff_ID      FROM Topic_Staff AS TSA WITH (NOLOCK)     WHERE TSA.Staff_ID = SM.Staff_ID       AND TSA.Contact_Role_Ref_ID IN (655))   AND SM.Status = 'Published' ORDER BY PA_Rank DESC, Pj_Hours DESC, Bio_Rank DESC, SM.LastName, SM.FirstName -- https://www.mememaker.net/api/bucket?path=static/img/memes/full/2019/May/15/1/say-what-440.png Complex enough for you?  Maybe you like this one: -- (@dateFilter int, @staffID nvarchar(5))SELECT [PM].[Project_ID],  [PM].[TB_DATE_OPENED],  [PM].[Short_Desc],  [PM].[Project_Type_Code],  [PM].[Case_Number],  [PS].[Responsibility_Desc] FROM [Project_Main] [PM] WITH (NOLOCK) INNER JOIN [Project_Staff] [PS] WITH (NOLOCK) ON [PS].[Project_ID] = [PM].[Project_ID] WHERE [PM].[Status] LIKE 'Published%' AND [PM].[Status] NOT LIKE 'withdrawn' AND [PM].[TB_DATE_OPENED] > (getDate() - @dateFilter) AND [Short_Desc] IS NOT NULL AND [Short_Desc] NOT LIKE 'TB Feed' AND [Short_Desc] NOT LIKE '%UNSOLD PROPOSAL%' AND ([Short_Desc] NOT LIKE '%follow-on%' or datalength([Short_Desc])>50) AND [Short_Desc] NOT LIKE 'confidential%' AND [PS].[Staff_ID]=@staffID AND [Project_Type_Code] = 'c' AND PM.Project_ID IN ('65011625', '65025688', '65032850', '65042668', '65052747', '65058320', '65130746', '65143481', '65144809', '65147726', '65153299', '65161789', '65079545', '65083790', '65088035', '65110849', '65119339', '65123584', '4000218', '65018549', '65027039', '65027254', '65034201', '65052724', '65150620', '65100793', '65110826', '65126478', '65127806', '65140587', '65141915', '65054052', '65058343', '65082485', '65086684', '65093846', '65095174', '65027062', '65027231', '65032804', '65042714', '65047128', '65055449', '65153345', '65157590', '65159087', '65163332', '65120713', '65129372', '65130700', '65137693', '65139021', '65143435', '65096740', '65098068', '65103733', '65107978', '65109475', '65113720', '65071101', '65072429', '65078171', '65079760', '65081088', '65093654', '65027208', '65031499', '65054098', '65055426', '65058512', '65069750', '65157736', '65163355', '65110657', '65119362', '65126432', '65142084', '65143412', '65149031', '65075369', '65081111', '65082439', '65088058', '65093677', '65098091', '65018695', '65041509', '65052578', '65053906', '65059740', '65085210', '65117919', '65129326', '65149223', '65151802', '65086538', '65093700', '65095028', '65097945', '65105107', '65117842', '65005860', '65008854', '65042814', '65048648', '65049976', '65073780', '65129349', '65137716', '65139167', '65140710', '65149200', '65161981', '65075446', '65098045', '65105207', '65106412', '65119193', '65129011', '65017244', '65027400', '65039966', '65051204', '65051373', '65052532', '65159256', '65160338', '65160584', '65161835', '65074095', '65093992', '65095074', '65129034', '65140687', '65150597', '65007234', '65007480', '65018718', '65051227', '65052555', '65073949', '65140418', '65150451', '65160607', '65161689', '65106581', '65117819', '65127975', '65129057', '65129303', '65139213', '65074072', '65083982', '65095097', '65095220', '65095343', '65096425', '65035429', '65043006', '65081211', '65083713', '65084128', '65085041', '65145147', '65146475', '65150720', '65151633', '65085456', '65092618', '65108270', '65133740', '65135068', '65138898', '65025588', '65026916', '65035867', '65042568', '65047228', '65051519', '65154942', '65157444', '65112492', '65118111', '65129472', '65136634', '65143581', '65146083', '65052847', '65055349', '65087022', '65102459', '65104961', '65106289', '65017321', '65032973', '65042960', '65048210', '65068522', '65069850', '65127537', '65143189', '65147849', '65153422', '65154504', '65156508', '65082339', '65086830', '65092572', '65099319', '65102651', '65103979', '65048356', '65048602', '65053975', '65055303', '65066664', '65068668', '65142207', '65152240', '65160146', '65086976', '65100501', '65123899', '65131928', '65135260', '65136588', '65010543', '65014373', '65036013', '65044088', '65045754', '65056823', '65122087', '65136150', '65143727', '65146229', '65155134', '65158626', '65058151', '65066564', '65088204', '65088542', '65089870', '65092034', '65000364', '65003358', '65013145', '65044480', '65046644', '65055595', '65146329', '65101875', '65113574', '65122187', '65132220', '65136050', '65145001', '65057261', '65059425', '65066956', '65068622', '65070786', '65088104', '65002674', '65014327', '65015409', '65024314', '65045708', '65066856', '65133694', '65144855', '65145101', '65145937', '65146183', '65147265', '65102313', '65103395', '65110726', '65122379', '65132366', '65133448', '65067938', '65068184', '65069266', '65079837', '65080919', '65099903', '65025542', '65033165', '65034247', '65035575', '65036657', '65045854', '65146037', '65154988', '65102167', '65103249', '65132512', '65133594', '65143627', '65144709', '65067002', '65067248', '65078363', '65079691', '65099757', '65101921', '65046690', '65048018', '65055887', '65058051', '65058297', '65059379', '12000331', '65000633', '65013368', '65026103', '65042253', '65046498', '65128742', '65131161', '65140979', '65144394', '65151556', '65070640', '65090039', '65102774', '65118426', '65118924', '65123169', '65012063', '65022379', '65048894', '65049392', '65054513', '65066372', '65137255', '65138798', '65148616', '65070165', '65081526', '65088473', '65097676', '65104623', '65121105', '65060776', '65084749', '65085748', '65089163', '65094736', '65104646', '65129618', '65129787', '65137447', '65143020', '65152340', '65035114', '65048725', '65055180', '65056385', '65067746', '65072037', '65149990', '65151035', '65154281', '65154404', '65157982', '65076451', '65097009', '65114112', '65121772', '65126063', '65130969', '65018280', '65039428', '65043750', '65045001', '65051742', '65071639', '65161045', '65161543', '65162794', '65128413', '65131490', '65139980', '65148808', '65152217', '65154381', '65092864', '65098858', '65111593', '65116176', '65120421', '65125496', '65077633', '65082708', '65084374', '65085625', '65087789', '65092787', '65017467', '65018180', '65040779', '65060255', '65081403', '65085725', '65135552', '65140003', '65149661', '65153030', '65159479', '65161520', '65092764', '65104248', '65106873', '65109037', '65114035', '65127062', '65006421', '65027646', '65032389', '65040802', '65050368', '65051450', '65150182', '65158174', '65161420', '65094161', '65097484', '65106312', '65114304', '65115386', '65141354', '65060278', '65071516', '65084082', '65084251', '65086415', '65087497', '65026464', '65050268', '65050391', '65053637', '65072990', '65081695', '65140295', '65141377', '65149369', '65159648', '65162894', '65082777', '65085064', '65118065', '65120106', '65120229', '65140172', '65032097', '65056156', '65056654', '65059986', '65060484', '65068891', '65162625', '65132827', '65133325', '65142230', '65145562', '65151135', '65158795', '65091705', '65097776', '65113843', '65122748', '65126578', '65129495', '65072721', '65079468', '65084543', '65085954', '65088373', '65088871', '65043527', '65049017', '65049515', '65058220', '65068997', '65076528', '65131261', '65154573', '65156731', '65158403', '65093969', '65094467', '65105828', '65113451', '65115609', '65124314', '65009246', '65017988', '65046375', '65049707', '65057530', '65070265', '65144102', '65152509', '65160169', '65103564', '65108639', '65119216', '65124122', '65130869', '65140856', '65073182', '65080252', '65080842', '65090245', '65090331', '65099150', '65047643', '65053016', '65059594', '65070955', '65077902', '65078984', '65126186', '65152117', '65158105', '65159187', '65162433', '65081234', '65082316', '65088181', '65089263', '65095841', '65114825', '65027185', '65046169', '65053408', '65057736', '65070471', '65076720', '65130079', '65078386', '65080550', '65088957', '65117089', '65119508', '65121672', '65024629', '65038154', '65041947', '65044111', '65046275', '65048433', '65155655', '65157321', '65114533', '65121566', '65122064', '65136173', '65151327', '65153491', '65078778', '65080942', '65087391', '65091221', '65096096', '65108539', '65054974', '65055472', '65057138', '65067417', '65067915', '65069581', '65011994', '65016322', '65055864', '65056946', '65067517', '65068599', '65089747', '65099734', '65135781', '65141440', '65010812', '65014058', '65015140', '65025419', '65034616', '65036780', '65148324', '65152701', '65154865', '65157029', '65164062', '65089847', '65093093', '65100716', '65125602', '65133717', '65146160', '65057928', '65068207', '65078486', '65079568', '65080650', '65081732', '65017696', '65024858', '65046415', '65053245', '65057490', '65079047', '65141062', '65161291', '65122754', '65124414', '65131576', '65132572', '65132904', '65136817', '65081632', '65081964', '65086209', '65100272', '65110351', '65115592', '65010772', '65035031', '65043189', '65050351', '65060716', '65066335', '65131599', '65132595', '65142960', '65143956', '65085854', '65087182', '65091805', '65099963', '65107457', '65111324', '65067663', '65068874', '65074493', '65077696', '65079024', '65084858', '65040464', '65046129', '65050374', '65053531', '65056448', '65067686', '65152423', '65155340', '65135775', '65146681', '65147013', '65149930', '65150926', '65151095', '65101483', '65117467', '65118294', '65128373', '65132618', '65134115', '65068682', '65068851', '65071768', '65086163', '65089080', '65090408', '65046106', '65056471', '65057799', '65063418', '65067377', '65067832', '65134301', '65139711', '65140043', '65144002', '65145662', '65157023', '65068828', '65069160', '65085685', '65086017', '65101460', '65122731', '65024689', '65025359', '65054742', '65055993', '65066149', '65072054', '65111178', '65161122', '65078546', '65080467', '65087374', '65088625', '65105937', '65109850', '65000862', '65066126', '65072077', '65078569', '65079239', '65083438', '65141208', '65153651', '65111201', '65114527', '65122685', '65128513', '65129847', '65138546', '65087397', '65090600', '65097092', '65097215', '65103043', '65109873', '65039299', '65060693', '65088410', '65091828', '65097238', '65098489', '65155171', '65157092', '65162502', '65119960', '65121211', '65125957', '65131367', '65152592', '65153843', '65098566', '65098735', '65108645', '65114550', '65115801', '65116047', '65043235', '65043358', '65049186', '65054719', '65060670', '65065957', '65159771', '65141254', '65143787', '65148410', '65153697', '65153820', '65158566', '65097261', '65098712', '65119737', '65125934', '65135844', '65137049', '65070580', '65071908', '65075990', '65077318', '65087351', '65091851', '65048662', '65054911', '65055243', '65056239', '65059073', '65069306', '65141317', '65151550', '65162957', '65091788', '65097693', '65126993', '65134238', '65134570', '65138068', '65072223', '65076053', '65079385', '65083630', '65084211', '65085539', '65027414', '65066212', '65067208', '65087852', '65088848', '65091682', '65127099', '65130929', '65134132', '65152784', '65093471', '65094799', '65098629', '65101629', '65122771', '65123767', '65008333', '65018993', '65040550', '65044878', '65044964', '65077679', '65155094', '65156422', '65125287', '65127208', '65127291', '65128619', '65140189', '65145599', '65083584', '65090162', '65091158', '65094490', '65102565', '65123209', '65007941', '65033663', '65040696', '65041569', '65050975', '65052930', '65133846', '65144457', '65155486', '65155572', '65156814', '65058881', '65070242', '65073242', '65095964', '65122817', '65128227', '65025104', '65044918', '65047580', '65051410', '65057659', '65058987', '65111516', '65115346', '65128828', '65132658', '65148393', '65164039', '65076299', '65080129', '65082791', '65083129', '65092870', '65100441', '65058595', '65069956', '65071622', '65073743', '65083776', '65084065', '65108908', '65109246', '65124351', '65139542', '65088934', '65090268', '65095426', '65096219', '65097885', '65102711', '65038967', '65047626', '65048708', '65055197', '65057361', '65071430', '65093906', '65122625', '65151596', '65152678', '65158088', '65159170', '65043444', '65044772', '65048854', '65049100', '65050182', '65053723', '65152824', '65153906', '65154152', '65159562', '65131430', '65137922', '65142545', '65145791', '65148201', '65151742', '65060461', '65077527', '65078609', '65084019', '65118987', '65130348', '65017115', '65024775', '65025273', '65045502', '65049747', '65050743', '65124829', '65131991', '65135406', '65160378', '65160876', '65082047', '65083873', '65103604', '65104102', '65108015', '65114679', '65053162', '65054158', '65057905', '65058403', '65070972', '65071470', '65000610', '65032910', '65039359', '65047019', '65049724', '65054181', '65153239', '65153737', '65154235', '65154733', '65121818', '65128267', '65139130', '65140126', '65146790', '65147786', '65056886', '65079651', '65082356', '65082854', '65091012', '65110955', '4000109', '65034141', '65038555', '65039551', '65047042', '65056863', '65128290', '65149017', '65150013', '65151012', '65154258', '65092741', '65095658', '65096654', '65110720', '65121626', '65126369', '65067440', '65067769', '65069764', '65075429', '65087998', '65089495', '65025794', '65037155', '65055389', '65066750', '65081357', '65086767', '65147617', '65150322', '65150703', '65155732', '65156654', '65088021', '65089017', '65097132', '65107042', '65111739', '65114985', '65027523', '65048250', '65052993', '65053491', '65078303', '65080882', '65134739', '65138154', '65157630', '65160209', '65162373', '65162871', '65086959', '65089538', '65103773', '65109014', '65112429', '65127577', '65046976', '65050935', '65056929', '65068247', '65068290', '65074241', '65152200', '65155987', '65157481', '65157653', '65160186', '65132011', '65134716', '65140839', '65149323', '65149538', '65150989', '65076405', '65076903', '65110912', '65119568', '65123355', '65131513', '65005585', '65014241', '65035807', '65041048', '65042299', '65059611', '65149346', '65151510', '65151679', '65152761', '65096156', '65097653', '65118211', '65122296', '65128788', '65146269', '65068267', '65069521', '65071685', '65074931', '65090746', '65092243', '65035489', '65054350', '65074908', '65080859', '65083023', '65092220', '65122147', '65124852', '65133508', '65139459', '65158320', '65094384', '65096179', '65098343', '65099425', '65100876', '65115655', '65019279', '65031267', '65048081', '65048579', '65054828', '65059654', '65160123', '65126495', '65130325', '65137072', '65138483', '65138981', '65158712', '65076468', '65080298', '65104855', '65112515', '65113013', '65116843', '65034078', '65037410', '65037908', '65069827', '65074155', '65076860', '65148954', '65156399', '65077985', '65129933', '65141294', '65144626', '65146120', '65147743', '65032890', '65040719', '65043965', '65045047', '65066687', '65069933', '65137856', '65141102', '65148762', '65152263', '65073265', '65088327', '65091659', '65094576', '65115134', '65123876', '65012724', '65027331', '65069541', '65082525', '65083607', '65091267', '65131842', '65098300', '65099296', '65102542', '65103624', '65105247', '65117235', '65047497', '65049661', '65049916', '65053989', '65055910', '65056408', '65156877', '65073720', '65075884', '65079714', '65080212', '65109767', '65117925', '65025465', '65034662', '65043275', '65049269', '65051433', '65054138', '65158649', '65135343', '65139173', '65142376', '65144042', '65150491', '65150534', '65060630', '65066581', '65075237', '65128851', '65133179', '65134845', '65039880', '65067271', '65073763', '65089993', '65092157', '65099488', '65103816', '65121128', '65140604', '65155011', '65156093', '65159339', '65004025', '65072289', '65076076', '65082568', '65085273', '65086355', '65103126', '65105290', '65109077', '65111241', '65114487', '65122602', '65089601', '65091765', '65092847', '65095011', '65099880', '65100962', '65034510', '65040344', '65050162', '65053743', '65058652', '65069395', '65152967', '65155884', '65074304', '65085711', '65102027', '65106936', '65110517', '65139568', '65012598', '65026043', '65027586', '65037404', '65045894', '65058011', '65142462', '65157899', '65159442', '65101125', '65117441', '65123275', '65131765', '65136628', '65141583', '65070036', '65077862', '65082817', '65085688', '65090643', '65091307', '65039465', '65060026', '65071433', '65076342', '65080756', '65092163', '65146020', '65148012', '65157922', '65011765', '65025548', '65026876', '65032495', '65042405', '65043733', '65143080', '65157945', '65088390', '65092140', '65115403', '65131719', '65136674', '65141088', '65050139', '65053766', '65055094', '65060049', '65072074', '65077029', '65024861', '65025187', '65033351', '65047337', '65055240', '65057155', '65148553', '65157381', '65125413', '65132575', '65133826', '65135154', '65145974', '65146561', '65092117', '65100607', '65112014', '65119840', '65121832', '65122419', '65058157', '65060149', '65067975', '65081297', '65081961', '65090789', '65025502', '65033992', '65070577', '65078403', '65079405', '65081397', '65124726', '65146538', '65154364', '65089223', '65090766', '65098592', '65102545', '65113906', '65122396', '65026561', '65033892', '65035884', '65059193', '65068021', '65068516', '65143936', '65145187', '65121214', '65122711', '65133780', '65135277', '65142608', '65143690', '19000089', '65003275', '65013185', '65047268', '65047809', '65056637', '65133262', '65133803', '65112409', '65112532', '65121360', '65121901', '65123770', '65124434', '65068539', '65079359', '65079900', '65088728', '65091802', '65099966', '65002465', '65034848', '65044666', '65047583', '65056322', '65058990', '65136989', '65142147', '65147471', '65149973', '65155961', '65079800', '65080879', '65085788', '65096116', '65122914', '65134321', '65002239', '65015223', '65018758', '65045353', '65047191', '65088682', '65155569', '65161772', '65126269', '65131427', '65134095', '65137630', '65142585', '65147079', '65095180', '65098131', '65102127', '65113488', '65115990', '65118443', '65015661', '65031977', '65050285', '65051367', '65052538', '65058944', '65099651', '65104975', '65121291', '65121540', '65130786', '65140524', '65066601', '65076929', '65082253', '65085834', '65088244', '65098569', '1000571', '65003072', '65026630', '65032372', '65037991', '65054012', '65055635', '65067537', '65075947', '65116485', '65119276', '65004300', '65016448', '65051997', '65054161', '65075801', '65083876', '65149140', '65154049', '65159960', '65127743', '65128330', '65129907', '65130245', '65142642', '65148891', '65085293', '65086621', '65094696', '65095283', '65105854', '65106103', '65006856', '65027463', '65049644', '65052638', '65060464', '65073245', '65141165', '65141706', '65143329', '65149532', '65149870', '65151493', '65117902', '65125728', '65127892', '65130345', '65130886', '65138712', '65073448', '65073786', '65086770', '65096262', '65097590', '65116531', '65018658', '65040298', '65052200', '65083922', '65095575', '65096657', '65141019', '65148350', '65151593', '65152675', '65106395', '65106644', '65128871', '65129953', '65130199', '65137530', '65007689', '65032034', '65060510', '65071871', '65072953', '65083232', '65161480', '65162808', '65139545', '65149824', '65150119', '65150365', '65150906', '65151988', '65083773', '65084314', '65084855', '65108118', '65116282', '65139004', '65032103', '65048419', '65050577', '65053328', '65053494', '65059067', '65139817', '65145556', '65149635', '65075383', '65082379', '65094616', '65123667', '65129240', '65136236', '65007274', '65039193', '65072704', '65089020', '65089684', '65095967', '65130142', '65144749', '65154567', '65010165', '65010832', '65013082', '65013915', '65031390', '65035469', '65121958', '65081171', '65087334', '65089326', '65094404', '65110056', '65114298', '65056199', '65068436', '65071018', '65072515', '65079674', '65080338', '65039050', '65042133', '65047706', '65049203', '65051456', '65055366', '65003195', '65005900', '65018847', '65037819', '65037868', '65042774', '65102419', '65105665', '65114321', '65073033', '65073697', '65077447', '65081689', '65084394', '65093763', '65047188', '65047729', '65057381', '65059090', '65069787', '65070451', '65033428', '65055578', '65060321', '65067560', '65071141', '65079133', '65135403', '65144231', '65148138', '65152887', '65156794', '65094281', '65102937', '65111599', '65122834', '65124998', '65126913', '65002860', '65057593', '65059757', '65060298', '65071118', '65081569', '65152864', '65156817', '65091725', '65112406', '65126349', '65135005', '65135546', '65148161', '65069103', '65077759', '65081005', '65089661', '65110809', '65122293', '65145433', '65147597', '65155420', '65157507', '65125539', '65133531', '65134862', '65137441', '65144351', '65145015', '65001652', '65013554', '65078990', '65087646', '65088187', '65099548', '65154779', '65155443', '65156943', '65133631', '65134221', '65134885', '65143000', '65144082', '65154238', '65113614', '65122983', '65125516', '65126057', '65131516', '65132721', '65034018', '65048247', '65051579', '65067480', '65077298', '65087875', '65131155', '65137404', '65150637', '65089120', '65093199', '65096780', '65109764', '65122499', '65128748', '65002442', '65031705', '65033248', '65034871', '65051187', '65056050', '65148622', '65150245', '65158901', '65161606', '65098795', '65104795', '65109123', '65125808', '65127431', '65129140', '65056511', '65067872', '65075532', '65082479', '65092807', '65097172', '65005751', '65012000', '65041051', '65044297', '65045379', '65049621', '65098154', '65111138', '65113216', '65120876', '65065937', '65067019', '65075924', '65078592', '65080003', '65082920', '65049870', '65050703', '65057281', '65058526', '65059608', '65060198', '65010314', '65017261', '65034659', '65042233', '65044938', '65046020', '65073574', '65074201', '65075283', '65085562', '65100169', '65108825', '65055217', '65056299', '65057885', '65057922', '65070328', '65070869', '65004128', '65010371', '65014456', '65049080', '65051244', '65052661', '65153634', '65162290', '65125751', '65128158', '65136571', '65138735', '65140650', '65149555', '65067809', '65076465', '65082714', '65084878', '65110597', '65117338', '65052810', '65054433', '65087434', '65095549', '65098254', '65099877', '65142665', '65149704', '65155649', '65157819', '65100418', '65108041', '65116697', '65119402', '65127517', '65128058', '65075681', '65084337', '65085419', '65086501', '65097321', '65107059', '65160667', '65160916', '65161749', '65163080', '65142273', '65147932', '65148765', '65149014', '65149847', '65159834', '65120043', '65128456', '65130371', '65136030', '65138194', '65141191', '65102880', '65104503', '65106667', '65108290', '65117487', '65118569', '65153734', '65158652', '65160275', '65160816', '65161898', '65162980', '65137504', '65141291', '65151029', '65151078', '65151619', '65152111', '65119651', '65126684', '65128307', '65128848', '65131012', '65131553', '48000446', '65012455', '65050992', '65057822', '65069229', '65071221', '65155718', '65161623', '65135489', '65137481', '65139402', '65140398', '65144311', '65147228', '65083624', '65086541', '65091450', '65094367', '65102857', '65103853', '65032707', '65041529', '65050019', '65053886', '65057845', '65058841', '65136462', '65140421', '65159608', '65108785', '65109664', '65115615', '65118154', '65134470', '65135466', '65070202', '65075157', '65076153', '65078692', '65090477', '65091473', '65052034', '65102811', '65121048', '65133451', '65135443', '65145353', '65146349', '65149266', '65151759', '65155672', '65163661', '65007523', '65067168', '65083484', '65084480', '65089435', '65090431', '65161646', '65139379', '65144334', '65145330', '65151281', '65156691', '65160650', '65099800', '65102333', '65105751', '65117653', '65122067', '65130010', '65033179', '65055406', '65069724', '65076631', '65077218', '65080544', '65161128', '65094530', '65095864', '65113763', '65117676', '65152300', '65159130', '65011977', '65013311', '65037115', '65038117', '65049478', '65054427', '65164022', '65098420', '65129060', '65139880', '65161145', '65163143', '65163684', '65069747', '65073282', '65074616', '65080567', '65089936', '65097879', '65053368', '65055947', '65067849', '65069770', '65080590', '65087082', '65153179', '65158589', '65100481', '65105891', '65118632', '65125124', '65137026', '65147846', '65038612', '65047440', '65047981', '65048522', '65053932', '65054473', '65093966', '65094507', '65114696', '65115237', '65059883', '65076654', '65082064', '65083146', '65088015', '65088556', '65001635', '65040587', '65043089', '65043504', '65056488', '65060401', '65102270', '65125665', '65126661', '65137819', '65138815', '65067646', '65072804', '65083962', '65090116', '65096946', '65099863', '65000616', '65035240', '65068665', '65069661', '65080026', '65082645', '65158274', '65133929', '65142751', '65145290', '65146286', '65148788', '65149784', '65085977', '65097338', '65107334', '65124646', '65129601', '65130597', '65017324', '65056285', '65057367', '65069269', '65073597', '65074679', '65157255', '65160587', '65115552', '65127540', '65139442', '65143770', '65144271', '65156754', '65002027', '65013388', '65082943', '65084566', '65094931', '65125141', '65128473', '65135506', '65141457', '65145244', '65145785', '65154158', '50000593', '65014871', '65032183', '65038675', '65047918', '65055744', '65155632', '65160541', '65162124', '65162705', '65114759', '65128081', '65136399', '65138320', '65144812', '65151304', '65086627', '65089034', '65090368', '65090955', '65097447', '65103352', '65055987', '65057908', '65062236', '65067394', '65068728', '65079548', '65015309', '65040447', '65042611', '65045605', '65049933', '65050474', '65157647', '65158188', '65142499', '65147165', '65147368', '65153319', '65153657', '65154198', '65052097', '65053972', '65069120', '65082104', '65112203', '65140335', '65025150', '65032724', '65047872', '65054364', '65056033', '65066853', '65126120', '65131530', '65092578', '65095824', '65096906', '65102316', '65109890', '65114218', '65073345', '65076591', '65077673', '65078755', '65083083', '65091496', '15000035', '65011476', '65058887', '65069707', '65070789', '65071330', '65104331', '65110823', '65076740', '65081609', '65092429', '65093511', '65094593', '65100003', '65049581', '65057241', '65058237', '65074553', '65075549', '65082213', '65128410', '65137066', '65143730', '65145722', '65086126', '65090869', '65094782', '65116837', '65119754', '65127414', '65007108', '65013059', '65016760', '65047566', '65056222', '65072538', '65161231', '65082190', '65087145', '65088141', '65089850', '65097510', '65140790', '65074765', '65083421', '65088831', '65092077', '65125705', '65139771', '65163747', '65141025', '65142688', '65143017', '65147431', '65158337', '65160000', '65095472', '65096013', '65104669', '65116030', '65136760', '65139293', '65154613', '65155609', '65159027', '65160023', '65161019', '65164265', '65145957', '65146412', '65147949', '65148662', '65152363', '65154072', '65016454', '65066226', '65068390', '65072060', '65078967', '65080716', '65132824', '65150136', '65150889', '65159545', '65085459', '65094115', '65102771', '65104935', '65106684', '65122004', '65019010', '65027125', '65033617', '65042273', '65050271', '65050812', '65100043', '65100756', '65106707', '65108871', '65124019', '65126724', '65084895', '65089395', '65091387', '65094092', '65096256', '65098051', '65052976', '65056763', '65058927', '65067583', '65068124', '65078944', '15000098', '65032810', '65036471', '65037553', '65040384', '65052286', '65084918', '65090328', '65093574', '65110886', '65070852', '65072844', '65074098', '65076090', '65076262', '65079508', '65055532', '65057029', '65057696', '65058778', '65060275', '65067434', '65005070', '65014267', '65019136', '65022382', '65031579', '65033743', '65083687', '65092884', '65093425', '65057719', '65058801', '65060424', '65066375', '65078277', '65080982', '65046358', '65048350', '65049604', '65050145', '65052137', '65054301', '65036989', '65043112', '65043481', '65044194', '65045645', '65046186', '65055077', '65071393', '65072389', '65080049', '65085373', '65088705', '65142562', '65151218', '65158463', '65162791', '65095950', '65098357', '65100278', '65108934', '65138234', '65141151', '65096342', '65096969', '65099674', '65100670', '65105625', '65113654', '65134298', '65135921', '65115277', '65117982', '65118609', '65119605', '65127265', '65131593', '65098655', '65107311', '65123541', '65127955', '65140939', '65146263', '65160086', '65161168', '65163418', '65148513', '65151430', '65152512', '65155758', '65157169', '65158251', '65121526', '65128559', '65132887', '65135592', '65136133', '65137215', '65156777', '65157773', '65158855', '65163810', '65143166', '65144875', '65147494', '65147580', '65151822', '65155154', '65047503', '65051831', '65056159', '65066979', '65073471', '65077799', '65105688', '65112180', '65133820', '65138148', '65082127', '65084291', '65086455', '65090783', '65092704', '65103524', '65015137', '65025957', '65067128', '65069292', '65073079', '65073620', '65108244', '65075243', '65075784', '65083899', '65090932', '65099588', '65101211', '1000305', '65012581', '65032057', '65033139', '65036385', '65044798', '65091324', '65074012', '65078340', '65080504', '65081586', '65083750', '65084832', '65049369', '65050208', '65053454', '65054536', '65058864', '65066438', '8000429', '65031665', '65034370', '65041403', '65042485', '65050059', '65058174', '65059797', '65069535', '65070617', '65073322', '65050600', '65051141', '65053846', '65054387', '65056010', '65056551', '65032186', '65050494', '65053411', '65054739', '65057656', '65073308', '65118675', '65127165', '65139900', '65144145', '65148390', '65074636', '65077553', '65083126', '65094533', '65107268', '65111513', '65037736', '65057679', '65060550', '65067497', '65069040', '65077530', '65148413', '65149741', '65158231', '65161102', '65084477', '65090311', '65094510', '65111490', '65115781', '65134089', '65015160', '65026398', '65032140', '65039133', '65056113', '65059030', '65142771', '65145688', '65155337', '65158254', '65162668', '65071765', '65074682', '65076179', '65088745', '65091662', '65097404', '9000003', '65032163', '65059176', '65060381', '65060504', '65070414', '65153986', '65159605', '65164019', '65098755', '65108665', '65110116', '65130059', '65142748', '65148367', '65076156', '65077484', '65083103', '65087394', '65093136', '65094341', '65007875', '65018031', '65033345', '65037928', '65050663', '65053242', '65149887', '65159628', '65161294', '65129913', '65131241', '65137152', '65138403', '65139731', '65140069', '65071888', '65073139', '65084623', '65103192', '65107437', '65125668', '65042150', '65049312', '65071788', '65095715', '65097258', '65098586', '65149864', '65151161', '65162522', '65163850', '65107414', '65114576', '65119857', '65128347', '65138503', '65148536', '65006670', '65006839', '65019405', '65040799', '65041881', '65050540', '65138526', '65141105', '65159920', '65104566', '65105894', '65115804', '65117132', '65117301', '65127288', '65053365', '65060696', '65071934', '65075841', '65084500', '65093328', '65053219', '65073162', '65083318', '65085851', '65095761', '65107368', '65149910', '65151115', '65128393', '65128516', '65128639', '65129844', '65138549', '65149787', '65027603', '65044583', '65052160', '65077630', '65080132', '65092867', '65159459', '65128831', '65133989', '65135317', '65138649', '65155629', '65158131', '65097112', '65100444', '65101772', '65114507', '65117424', '65125914', '15000038', '65009272', '65048851', '65056382', '65058884', '65074536', '65159897', '65161440', '65120318', '65125476', '65129767', '65150079', '65151407', '65158569', '65084354', '65094633', '65096176', '65097504', '65103338', '65108957', '65031894', '65038964', '65047961', '65049289', '65052206', '65053534', '65137275', '65154255', '65160833', '65098486', '65101818', '65102900', '65114553', '65119880', '65120126', '65057948', '65059276', '65067351', '65074928', '65082834', '65086335', '65010646', '65053680', '65069209', '65070537', '65071619', '65080570', '65148244', '65149572', '65158523', '65126850', '65136883', '65138211', '65142502', '65143830', '65147162', '65082980', '65087271', '65091931', '65097550', '65101964', '65120272', '65010792', '65034596', '65036262', '65053580', '65054908', '65057902', '65144314', '65145642', '65158377', '65102854', '65111267', '65111759', '65115589', '65121500', '65143976', '65058738', '65067643', '65069309', '65071473', '65078712', '65080378', '65044775', '65046103', '65067251', '65068579', '65070245', '65078612', '65114361', '65124394', '65133007', '65135171', '65146870', '65159313', '65080278', '65081606', '65087563', '65088891', '65098924', '65104082', '65013494', '65026229', '65047377', '65056282', '65056528', '65057364', '65155675', '65099070', '65102562', '65113133', '65135363', '65142940', '65144022', '65057610', '65067689', '65069017', '65070099', '65090411', '65091493', '13000014', '65003361', '65025001', '65033706', '65033952', '65045067', '65154447', '65155529', '65156611', '65157939', '65079986', '65080232', '65099216', '65134135', '65135217', '65146578', '65046395', '65047477', '65056428', '65057510', '65069953', '65078904', '65011874', '65025439', '65032601', '65048751', '65052498', '65058569', '65119588', '65128078', '65140315', '65160212', '65081383', '65086956', '65099193', '65102110', '65114845', '65119090', '65031748', '65038695', '65058048', '65069999', '65072157', '65072372', '65161563', '65162061', '65128101', '65136093', '65138964', '65147952', '65153073', '65155114', '65072870', '65079817', '65086481', '65098340', '65106332', '65112449', '65015575', '65018821', '65031227', '65040046', '65051124', '65054370', '65126704', '65143684', '65151676', '65156419', '65157341', '65159336', '65088330', '65103982', '65111052', '65113302', '65116717', '65120962', '65059445', '65069432', '65072678', '65075097', '65082167', '65085413', '65025462', '65033122', '65048061', '65048184', '65071373', '65079488', '65160687', '65080693', '65119067', '65122436', '65126727', '65153740', '65158646', '65015452', '65026936', '65032930', '65055821', '65058240', '65070975', '65160879', '65116840', '65117593', '65137567', '65143561', '65150302', '65150723', '65082044', '65088453', '65096445', '65096866', '65109678', '65116342', '5000038', '65017639', '65051101', '65085774', '65086266', '65095094', '65148659', '65150325', '65152366', '65095592', '65117693', '65118191', '65127388', '65130849', '65139339', '65018323', '65044291', '65048376', '65072349', '65082336', '65085582', '65162084', '65162253', '65148928', '65151015', '65152097', '65152174', '65158838', '65160089', '65088161', '65095492', '65108227', '65126206', '65142187', '65148851', '65038366', '65050809', '65053096', '65070998', '65082359', '65096843', '65160189', '65160312', '65118114', '65119688', '65127680', '65130926', '65137467', '65150033', '65009295', '65011453', '65015283', '65025860', '65047002', '65056405', '65126329', '65129246', '65139562', '65146309', '65149641', '65154716', '65097527', '65110262', '65113594', '65114092', '65116511', '65122997', '65056903', '65060235', '65079717', '65085705', '65088124', '65091954', '65007231', '65036531', '65045734', '65052183', '65056013', '65059843', '65137796', '65144245', '65149120', '65153448', '65163229', '65097135', '65106953', '65107998', '65115658', '65125937', '65133468', '65062001', '65071204', '65078151', '65081483', '65085313', '65092844', '65035641', '65041214', '65045542', '65053949', '65055031', '65058363', '65129283', '65070514', '65083249', '65089498', '65099983', '65118967', '65127872', '65037905', '65040032', '65054639', '65056889', '65057971', '65066000', '65134842', '65135924', '65143461', '65150494', '65160810', '65161855', '65095300', '65098509', '65104497', '65127809', '65131510', '65132592', '65069332', '65079525', '65079611', '65083939', '65087640', '65090972', '65033683', '65044254', '65057487', '65059651', '65066392', '65072386', '65157962', '65078635', '65080301', '65089704', '65091370', '65113010', '65144729', '65025668', '65046810', '65051138', '65054931', '65057095', '65067958', '65154032', '65156196', '65097719', '65102041', '65110746', '65136714', '65149157', '65151868', '65068456', '65078237', '65082565', '65087932', '65089598', '65090096', '65017155', '65037221', '65056697', '65058861', '65059943', '65067766', '65110554', '65125453', '65126535', '65145519', '65155506', '65071012', '65078927', '65081091', '65086750', '65088914', '65091078', '65034075', '65046518', '65047600', '65054141', '65056305', '65057387', '65120733', '65121815', '65145619', '65146701', '65066584', '65076863', '65081191', '65088224', '65110454', '65113700', '65005625', '65007546', '65045419', '65058486', '65066644', '65069893', '65102193', '65103521', '65116588', '65119505', '65120501', '65078051', '65087869', '65089790', '65091118', '65095363', '65097948', '65058509', '65073829', '65078028', '65092137', '65095340', '65102170', '65148911', '65153110', '65158944', '65159276', '65160272', '65116279', '65117607', '65124769', '65126644', '65140753', '65141749', '65012833', '65048121', '65051038', '65056780', '65069847', '65083670', '65132286', '65133783', '65134779', '65137696', '65152091', '65155008', '65089412', '65112389', '65116302', '65129369', '65130365', '65130697', '65018761', '65034204', '65035741', '65045442', '65047102', '65051061', '65139047', '65144998', '65145994', '65150408', '65161978', '65162974', '65085144', '65102001', '65106415', '65116448', '65116780', '65133305', '65052389', '65066836', '65068164', '65068373', '65074115', '65083693', '65031264', '65040009', '65045250', '65070062', '65076554', '65082459', '65156551', '65119004', '65123919', '65132077', '65147723', '65154630', '65155881', '65090617', '65091287', '65099107', '65099445', '65115091', '65116757', '65048814', '65055306', '65079780', '65079903', '65081231', '65086395', '65153325', '65163020', '65099422', '65111865', '65123226', '65132054', '65136339', '65138005', '65017487', '65027397', '65031479', '65051201', '65052704', '65055114', '65109309', '65115137', '65141772', '65141941', '65160581', '65055283', '65070016', '65071098', '65079926', '65085336', '65086418', '65027374', '65038489', '65039817', '65048645', '65048768', '65049850', '65125147', '65143246', '65146664', '65159230', '65052727', '65082482', '65093843', '65103753', '65119196', '65120524', '65015366', '65027520', '65038927', '65050915', '65056820', '65066899', '65118835', '65153803', '65068227', '65071144', '65089535', '65093697', '65098025', '65100942', '65018260', '65035572', '65043730', '65071536', '65078698', '65097633', '65103836', '65111115', '65122144', '65126306', '65140083', '65024403', '65025731', '65027566', '65042711', '65046043', '65047039', '65140275', '65143438', '65144520', '65157501', '65161915', '65056534', '65073846', '65078174', '65078260', '65105565', '65118881', '65017679', '65024257', '65041237', '65044569', '65046893', '65056926', '65139502', '65140129', '65149535', '65150162', '65157441', '65160441', '65110829', '65122276', '65126604', '65132223', '65133551', '65138174', '65057258', '65058008', '65067291', '65068287', '65068619', '65092177', '65035426', '65043341', '65049833', '65054072', '65067145', '65073637', '65121002', '65146140', '65154885', '65078220', '65085791', '65087955', '65093949', '65099362', '65117753', '65025791', '65078114', '65086727', '65088393', '65091055', '65094885', '65139001', '65162024', '65116525', '65118689', '65122230', '65124892', '65126558', '65134673', '65096760', '65099754', '65101088', '65102416', '65103252', '65115197', '65057118', '65059282', '65060610', '65075758', '65076840', '65080922', '65162170', '65097152', '65109641', '65114805', '65147268', '65154839', '65155921', '65032870', '65037198', '65043985', '65045313', '65048313', '65055592', '65153611', '65157693', '65160103', '65120610', '65126020', '65139840', '65143332', '65147414', '65147660', '65056674', '65059920', '65060756', '65072117', '65082396', '65113577', '65016119', '65026269', '65049249', '65054656', '65058901', '65067059', '65122837', '65148307', '65149801', '65156963', '65070474', '65073391', '65087786', '65095944', '65098363', '65116671', '65047517', '65049226', '65054679', '65055675', '65066040', '65073202', '65130972', '65136923', '65152741', '65162393', '65074696', '65080149', '65087809', '65088307', '65105502', '65123312', '65012160', '65048038', '65073179', '65076425', '65083089', '65101397', '65142356', '65145602', '65145771', '65155423', '65112970', '65127036', '65131779', '65134696', '65135692', '65137942', '65015429', '65031204', '65034450', '65040401', '65043487', '65051762', '65124354', '65141666', '65147998', '65153027', '65153949', '65090726', '65096136', '65097673', '65105333', '65114444', '65115698', '65054848', '65072701', '65080816', '65083066', '65084775', '65088476', '5000061', '65031181', '65033600', '65037845', '65059568', '65074888', '65134241', '65154215', '65082874', '65098194', '65100358', '65117670', '65119419', '65121583', '65006985', '65033451', '65046478', '65050265', '65050978', '65057427', '65160856', '65142333', '65145536', '65147331', '65149495', '65155489', '65160358', '65110414', '65114699', '65125562', '65128224', '65134175', '65134218', '65058921', '65059591', '65068788', '65070454', '65070952', '65082897', '65032392', '65047709', '65053119', '65070431', '65076923', '65081008', '65160166', '65160335', '65124288', '65125370', '65137613', '65142854', '65144105', '65158171', '65084254', '65085751', '65086833', '65088997', '65101566', '65106309', '65005734', '65054891', '65068957', '65075449', '65080318', '65085728', '65106286', '65116196', '65121606', '65130803', '65086810', '65092761', '65101417', '65101589', '65103040', '65104122', '65035097', '65035595', '65046670', '65050500', '65067314', '65068808', '65146641', '65149558', '65150969', '65153388', '65075970', '65097610', '65100029', '65121171', '65125499', '65129827', '65050394', '65054722', '65057839', '65060673', '65072034', '65072532', '65149950', '65150448', '65158108', '65158606', '65128808', '65132140', '65133136', '65139087', '65140298', '65146249', '65049375', '65052621', '65053703', '65056949', '65077593', '65078589', '65135778', '65143607', '65146853', '65154172', '65159837', '65164251', '65081921', '65082917', '65100315', '65104643', '65107889', '65129200', '65002986', '65024626', '65074951', '65082611', '65088562', '65093972', '65095509', '65098841', '65099923', '65104165', '65129137', '65136170', '65015535', '65019365', '65034513', '65035011', '65038841', '65039339', '65152549', '65158798', '65097026', '65098947', '65118423', '65143893', '65146555', '65150385', '65069392', '65071058', '65084540', '65088370', '65091032', '65093196', '65041260', '65043424', '65047752', '65049418', '65066730', '65067228', '65024340', '65026504', '65041652', '65045482', '65047646', '65051931', '65163475', '65145665', '65146163', '65148327', '65151032', '65153196', '65161311', '65069784', '65073614', '65127228', '65131556', '65141337', '65143501', '65054095', '65055761', '65058423', '65060089', '65067122', '65067620', '65043126', '65045290', '65053205', '65055369', '65057533', '65058615', '65128702', '65133030', '65134112', '65139522', '65162585', '65163667', '65075927', '65079173', '65082419', '65101895', '65110308', '65114636', '65017009', '65075535', '65076617', '65077699', '65078781', '65080945', '65098257', '65102585', '65103667', '65105831', '65107454', '65110700', '65082027', '65085814', '65086896', '65089060', '65090142', '65092306', '65024692', '65041672', '65045917', '65046581', '65049498', '65054407', '65153631', '65160129', '65162121', '65119007', '65124580', '65127497', '65133070', '65147394', '65151639', '65079213', '65082130', '65095529', '65098446', '65108264', '65111845', '65024715', '65033205', '65043023', '65051513', '65057347', '65078526', '65091971', '65103332', '65111822', '65124603', '65133093', '65135964', '65038137', '65044543', '65098400', '65121709', '65126618', '65137361', '65158586', '65163664', '65141775', '65142270', '65142934', '65143767', '65157258', '65158091', '3000223', '65026753', '65045061', '65054430', '65077693', '65082107', '65154318', '65158609', '65159273', '65163023', '65163687', '65114075', '65126100', '65130391', '65136010', '65137338', '65148699', '65102714', '65103378', '65108333', '65109120', '65109661', '65109784', '65045084', '65046086', '65055904', '65069226', '65080633', '65086544', '65156717', '65087872', '65091115', '65101858', '65126077', '65136482', '65144308', '65037281', '65047437', '65047978', '65055263', '65055927', '65056468', '65144331', '65144995', '65147866', '65099256', '65111699', '65113242', '65122519', '65124062', '65136505', '65058798', '65059462', '65069249', '65077075', '65079067', '65087895', '65046458', '65047955', '65055950', '65057447', '65057865', '65067188', '65123206', '65135941', '65144354', '65157753', '65080587', '65081251', '65089910', '65110640', '65111304', '65112386', '65067852', '65068685', '65069349', '65076680', '65079090', '65079754', '65013308', '65025874', '65034579', '65046063', '65055973', '65057424', '65146369', '65155738', '65079777', '65080023', '65080564', '65101958', '65102499', '65144377', '65057965', '65058506', '65067334', '65069203', '65078695', '65079236', '65027271', '65036176', '65046504', '65048911', '65054745', '65054994', '65149724', '65151301', '65154882', '65127159', '65132993', '65133408', '65138317', '65138566', '65146143', '65068144', '65089369', '65092535', '65097444', '65114175', '65125582', '65016551', '65032867', '65035535', '65040693', '65041572', '65048519', '65148407', '65149286', '65151739', '65159565', '65122273', '65125144', '65127597', '65128476', '65136302', '65146784', '65055386', '65057009', '65059677', '65070159', '65075993', '65110617', '65048957', '65050039', '65053371', '65066355', '65066850', '65081174', '65131619', '65141852', '65144849', '65157504', '65159173', '65164082', '65090912', '65114221', '65115303', '65119130', '65125536', '65127205', '3000346', '65033700', '65058303', '65059926', '65069664', '65074619', '65158191', '65163146', '65141211', '65146830', '65147912', '65148453', '65151244', '65153113', '65094550', '65098964', '65101632', '65102173', '65134928', '65135469', '65018701', '65031347', '65053325', '65053574', '65072220', '65074722', '65080384', '65083627', '65107769', '65115595', '65150219', '65150806', '65039949', '65060759', '65071579', '65072120', '65081071', '65082399', '65142001', '65152821', '65160647', '65164182', '65084563', '65087557', '65118197', '65128679', '65137384', '65140378', '65018409', '65032721', '65051951', '65054115', '65073099', '65073348', '65149678', '65162167', '65097985', '65107972', '65117218', '65138858', '65139940', '65149183', '65073594', '65074676', '65083919', '65084414', '65085250', '65086083', '65007935', '65018263', '65040985', '65051018', '65053969', '65073199', '65074281', '65075363', '65084560', '65094347', '65095429', '65095970', '17000211', '65049913', '65052830', '65069146', '65070308', '65082545', '65141311', '65143398', '65145390', '65148971', '65155635', '65161706', '65095114', '65103770', '65119422', '65132821', '65135738', '65140481', '17000188', '65016428', '65032744', '65057550', '65066206', '65073866', '65140458', '65144251', '65153903', '65082522', '65086813', '65087477', '65103129', '65115154', '65139296', '65039714', '65071851', '65076093', '65095326', '65099405', '65100069', '65150016', '65153926', '65157009', '65158340', '65160332', '65134029', '65134693', '65139107', '65142685', '65144016', '65147763', '65115131', '65116385', '65118048', '65118377', '65125708', '65130783', '65018466', '65074078', '65090394', '65097341', '65100046', '65101755', '65157650', '65163933', '65143375', '65145748', '65152031', '65155400', '65155941', '65156986', '65109415', '65113116', '65137092', '65137633', '65139084', '65141334', '65002219', '65047663', '65049578', '65054327', '65067062', '65075552', '65108184', '65120919', '65122668', '65133488', '65142144', '65155300', '65082210', '65088951', '65098864', '65099356', '65099528', '65103435', '65016969', '65037158', '65045814', '65060421', '65068911', '65069077', '65144872', '65158317', '65158858', '65100879', '65101420', '65102084', '65112240', '65131347', '65143710', '65071782', '65075575') -- Now *that's* an IN clause! Please note that this is not a contest - DON'T write code that looks like this - ever! https://www.mememaker.net/api/bucket?path=static/img/memes/full/2017/Feb/14/22/please-just-stop-dont-do-that.jpg -- Hope this helps (and good to be back writing after some time!) The post Error: 8623 - The query processor ran out of internal resources and could not produce a query plan appeared first on SQLServerCentral.

This is my report, which continues on from the Aug report. After that report, I made an effort to work a little harder. This is where I am. Reading Goals Here were my goals for the year. 3 technical books 2 non-technical books Books I’ve tackled: Making Work Visible – Complete Pro Power BI Desktop – 60% complete White Fragility – 82% complete The Biggest Bluff – 100% complete Team of Teams – NEW Project to Product – NEW I made an effort during some lunches and breaks to work on Making Work Visible and finished it. The middle of the book was hard, but the end got me thinking again. I’ll write a review soon. No Power BI or White Fragility. I’ve been a little unfocused and struggling to cope for a couple months and trying to let my brain relax when I can. I added the last book after hearing the author talk about poker and how it relates to life and decision making. So far, it’s a mix of poker, fear, and the challenges of imperfect information. Project Goals Here were my project goals, working with software A Power BI report that updates from a database A mobile app reading data from somewhere A website that showcases changes and data from a database. I started compiling some data, grabbing some stats from my life. I’m loading that into a db , as a part of life and some content work, so I’ll write about that. Once I get that loaded, I’m thinking to use that db as a way to kick start some of these items. Work is not slowing this fall,but I did take a bit of time and start to build a PowerBI report that has some data in a database. Early days here, and I really think this is about 10% done, but it’s moving. Nothing on the mobile app, nothing on the website for now. The post Goal Progress– October 2020 appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to ask yourself, will this still matter a year from now? A year is a long time. Next year, as we get ready for Halloween, will this matter? I certainly hope that much of the challenges of COVID will be a memory. While I wouldn’t be surprised if we still wore masks in gatherings and limited some of the heavily crowded areas, I do hope that we have learned some lessons and adjusted our world to cope. The feelings I have today, and discontent, the stress and concerns, much if it won’t matter. The post Daily Coping 30 Oct 2020 appeared first on SQLServerCentral.

Alex is a Data Platform MVP who loves DevOps. He’s has been helping data professionals apply DevOps principles to relational database development and deployment since 2010. He’s most proud of helping Skyscanner develop the ability to deploy 95 times a day. Alex has worked with clients on every continent except Antarctica – so he’s keen to meet anyone who researches penguins. Source The post ASF 034: Alex Yates interview (part 1) appeared first on SQLServerCentral.

Starter Template I saved this as a snippet for vscode to get up and running quickly with something better than the defaults for handling func main isolation. I’ve been working on modifying this a bit as I don’t really like using args, but am trying not to overcomplicate things as a new gopher. I tend to like better flag parsing than using args, but it’s still a better pattern to get functions isolated from main to easily test. The gist that I’ve taken from this and discussions in the community is ensure that main is where program termination is dedicated instead of handling this in your functions. This isolation of logic from main ensures you can more easily setup your tests as well, since func main() isn’t testable. package main // package template from: import ( "errors" "fmt" "io" "os" ) const ( // exitFail is the exit code if the program // fails. exitFail = 1 ) func main() { if err := run(os.Args, os.Stdout); err != nil { fmt.Fprintf(os.Stderr, "%sn", err) os.Exit(exitFail) } } func run(args []string, stdout io.Writer) error { if len(args) == 0 { return errors.New("no arguments") } for _, value := range args[1:] { fmt.Fprintf(stdout, "Running %s", value) } return nil } Puzzles - FizzBuzz I honestly had never done any algorithm or interview puzzles beyond sql-server, so I was really happy to knock this out relatively easily. At least I pass the basic Joel test ?? #development #golang The post go appeared first on SQLServerCentral.

Installing go-task This tool is great for cross-platform shell scripting as it runs all the commands in the Taskfile.yml using a built in go shell library that supports bash syntax (and others). Quickly get up and running using the directions here: Install Task # For Default Installion to ./bin with debug logging sh -c "$(curl -ssL https://taskfile.dev/install.sh)" -- -d # For Installation To /usr/local/bin for userwide access with debug logging # May require sudo sh sh -c "$(curl -ssL https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin #development #shell The post shell appeared first on SQLServerCentral.

The Problem When you use a UDF in .NET for Apache Spark, something like this code: class Program { static void Main(string[] args) { var spark = SparkSession.Builder().GetOrCreate(); _logging.AppendLine("Starting Select"); var udf = Functions.Udf<int, string>(theUdf); spark.Range(100).Select(udf(Functions.Col("id"))).Show(); _logging.AppendLine("Ending Select"); Console.WriteLine(_logging.ToString()); } private static readonly StringBuilder _logging = new StringBuilder(); private static string theUdf(int val) { _logging.AppendLine($"udf passed: {val}"); return $"udf passed {val}"; } } Generally, knowing .NET we would expect the following output: The post Blog .NET for Apache Spark UDFs Missing Shared State appeared first on SQLServerCentral.

A couple of months ago Docker announced that they would be implementing a 6 month retention policy for unused images in the Docker Hub. This was due to kick in on the 1st of November but has now been pushed back until mid 2021. I’ve had multiple Windows SQL Server container images up on the Docker Hub for years now. It’s been a great platform and I’m very thankful to them for hosting my images. That being said, I want to make sure that the images that I’ve built are always going to be available for the community so I have pushed my SQL Server images to the Github Container Registry. In the Docker Hub I have the following public SQL Server images: – dbafromthecold/sqlserver2012express:rtm dbafromthecold/sqlserver2012dev:sp4 dbafromthecold/sqlserver2014express:rtm dbafromthecold/sqlserver2014dev:sp2 dbafromthecold/sqlserver2016dev:sp2 These images won’t be removed (by myself) and I will update this post in the event of them being removed. But they are now available on the Github Container Registry: – ghcr.io/dbafromthecold/sqlserver2012:express ghcr.io/dbafromthecold/sqlserver2012:dev ghcr.io/dbafromthecold/sqlserver2014:express ghcr.io/dbafromthecold/sqlserver2014:dev ghcr.io/dbafromthecold/sqlserver2016:dev Bit of a disclaimer with these images…they’re LARGE! The 2012 dev image is ~20GB which is gigantic for a container image. So if you going to use them (for dev and test only please ) you’ll need to pre-pull them before running any containers. Thanks for reading! The post Migrating SQL Server container images to the Github Container Registry appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to be kind to yourself today. Remember progress takes time. This is something that having and working with kids has taught me. I’ve watched them take advice or coaching from me, or not, but I can see there is a difference across time. Lately I’ve had a lot of deliverables, for various different presentations. Rather than get upset or concerned when I’ve been delayed in getting something done, I’ve stopped and reminded myself that a task may just take time. It’s helpful to remind yourself that despite getting things done today, I may have more to do tomorrow. As along as I’m making progress, that’s OK. The post Daily Coping 29 Oct 2020 appeared first on SQLServerCentral.

I was helping my 16yo write his first college essay the other day. He’s always struggled a bit with writing essays so this isn’t an easy task. As I’m going over what he’s written so far I gave him a piece of advice I’d never thought of before. When writing an essay the first thing you need to ask yourself is What do they want to hear? I told him to read the essay prompt Write about an event in your life. and then expand on that. It’s a college essay so writing about how the event he picked made him want to live life to it’s fullest and go join a circus probably isn’t going to help. As I thought about this I realized just how far reaching this concept is. When I write a blog post, I come up with a concept and then have to think of what do the people reading it want to get out of it. In any form of communication, you need to read the prompt and then figure out what they really want. A blog post (as I mentioned above) has a target audience. And make no mistake, you are always going to be part of your target audience. What does that audience want? Do they want a simple how to? A complex, in depth discussion of a problem? The answer can, and probably will, change post by post. Documentation. Same as a blog post really. Any technical request. It could be anything from a new database, an SSIS package, a stored procedure, view etc. You need to figure out not just what they’ve asked for but what do they want, and what do they need. Know your audience. Make sure your output fits the audience. The post What do they want to hear? appeared first on SQLServerCentral.

A while back I was chatting with Shannon Lindsay on Twitter. She shares lots of useful Power BI tips there. She shared her syntax tip of the & operator being used for concatenation and the && operator being used for boolean AND, which reminded me about implicit conversions and blanks in DAX. Before you read the below tweet, see how many of these you can guess correctly: Blank + 5 = ? Blank * 5 = ?5 / Blank = ?0 / Blank = ? In DAX, Blank is converted to 0 in addition and subtraction. And you can also have fun with blanks in DAX: Blank + 5 = 5Blank * 5 = Blank5/Blank = 8Blank/Blank = Blank0/Blank = NaN — Meagan Longoria (@MMarie) September 4, 2020 What about boolean logic? Do you know the result of the following expressions? AND(True(), Blank()) = ? OR(True(), Blank()) = ? AND(False(), Blank()) = ? AND(Blank(), Blank()) = ? You can see the results as well as a few more permutations in the screenshot below. Read the left table as Number1 [operator] Number2, so 5 + Blank = 5. 5 * Blank = Blank. And 5 / Blank = Infinity. Read the right table as Bool1 [operator] Bool2, so True AND Blank = False and True OR Blank = True. Why does this matter? You need to understand the impact of blanks in your data. Do you really want to divide by zero when you are missing data? If you are performing a boolean AND, and your data is blank, are you ok with showing a result of False? Remember that your expression may produce undesired results rather than an error. First, you need to be aware of where it is possible in your data to get a blank input. When you are writing your DAX measures, you may need to handle blanks. DAX offers the IFERROR() function to check if the result of an expression throws an error. There is also an ISBLANK() function that you can use to check for a blank value and a COALESCE() function to provide an alternate value when a blank value is detected. But adding extra logic in your measures may have a performance impact. For example, the DIVIDE() function can handle divide by zero errors for you. But DIVIDE() may be slower than the / operator. The performance difference is dependent on your data and the expression you are writing. Alternatively, you can use an IF statement to check if an input value is greater than zero using the > operand. This can be quicker than checking for blanks or errors using other functions. At the end of the day, producing the correct result is more important than fast performance, but we strive to achieve both. If you have any tips for handling blanks in DAX, please share them in the comments. The post DAX Logic and Blanks appeared first on SQLServerCentral.

Andy Cotgreave Technical Evangelist Director, Tableau Tanna Solberg October 28, 2020 - 9:19pm October 30, 2020   In the latest episode of Chart Chat, Amanda Makulec, Steve Wexler, Jeff Shaffer and I discussed the lessons data storytellers can learn from 2020 US election poll trackers. Below are my five main takeaways that you can apply in your own analyses. To get the details, watch the full episode of Chart Chat. Embrace uncertainty when making predictions   Figure 1: Visualization from FiveThirtyEight showing potential outcomes of the 2020 US presidential election.Predictions, even based on clean data sets, are uncertain. In 2020, many poll trackers emphasize this, possibly as a result of perceived errors in 2016. They do not want viewers to come away with an expectation that one candidate or another “will” certainly win. Poll trackers are embracing uncertainty in various ways. The New York Times (NYT), CNN, and others, ask you to make decisions on the outcome. Instead of calling toss-up or swing states on your behalf, they force you to interact with the visualization and name discover these swing states for yourself. By engaging the user, they emphasize the fact that these are predictions—not mandated outcomes.  When you open FiveThirtyEight’s forecast pages, you see multiple US maps, each showing a different outcome. This is an arresting sight on first look that is, I think, very powerful. You can’t help but stop and process the meaning of what you’re seeing: multiple possible outcomes based on running multiple simulations. Further down the page, their main predictive plot is a chart with 100 dots showing representative outcomes of 40,000 simulations. You can’t come away from this without knowing you are seeing a range of possible outcomes. Takeaway #1: When you communicate predictions in your organization, do your audiences understand the levels of uncertainty they contain? Slow data adds context and fosters engagement Figure 2: Visualization from the New York Times showing the swing states Trump and Biden need to win to reach 270 electoral votes.   When working with data, we are often too quick to jump to a conclusion and then share an insight, perhaps without really digesting the meaning. A clear trend for this election is how the trackers force a more meaningful engagement. “Slow data” as a concept was first proposed by the New York Times back in 2011, and further refined for the analytics industry by Stephen Few in 2013. More than ever it seems that these ideas are ones we should embrace.  Fiverthirtyeight first shows you an unusual set of maps. As you scroll, you start with a written analysis, not a chart. They are encouraging you to engage in the context of the data. Only after those two sections do you reach the predictive plot. In previous elections, this chart would’ve been at the top of the page, intended for your rapid consumption. The NYT’s drag-and-drop visualization that also forces  you to interact is another example of engaging readers: you can’t reach a result until you have acted. Although we didn’t discuss it in Chart Chat, the Financial Times also adds a lot of commentary among their poll tracker charts: As you look at their tracker page, each chart is interspersed by commentary that is updated regularly. All these examples bring people back from the brink of making conclusions too quickly, and encourage people to engage more deeply with the information. Takeaway #2: What ways do you provide additional context to your data displays? Do you encourage people to consider more than just the height of a bar on a bar chart or the slope of a line chart? Cutesy or dry? Novel or complex?     Figure 3: Visualization from FiveThirtyEight showing a sample of 100 outcomes of the 2020 US presidential election. One of the biggest challenges for any data communication is whether to make it dry and functional, or cute and engaging? Both are valid choices, but you must have reasons for choosing either path. FiveThirtyEight has leaned heavily towards a cutesy and engaging approach. The Fivey Fox mascot pops up next to most charts with call-outs to more further information. Their charts have a playful design, including caricatures of Trump and Biden.  We compared how CNN’s “Road to 270” tracker is essentially the same as the NY Times tracker In both cases, you choose how the states will cast their electoral votes and watch as progress towards one candidate having 270 electoral votes changes. The NY Times chart has bouncy circles that you drag into a cute Voronoi circle chart. The circles pop around and it’s fun to play with. CNN has you click on a map which changes colors of a horizontal stacked bar. Functionally, the two charts are the same, but one is playful while the other is functional. I find the NYT’s draggable bubbles a middle ground between cute and dry. The way they bounce as you let them go is engaging. Most other organizations choose a more austere, dry path. Is dry better than cute? I cannot answer that because it mostly comes down to personal preference. Personally, I’ve stopped noticing Fivey Fox now I’ve seen him many times, but Amanda said she likes him and thinks his inclusion brings a moment of levity to what is a very serious subject. Takeaway #3: Which approach do you take for your internal or external data communications? Do you consider whether to bring levity to your insights, or stay austere and serious? Geospatial data doesn’t have to be a map       Figure 4: CNN showing an electoral map that can be adjusted to simulate changes to who receives a state's electoral votes. There’s another reason to compare the CNN and NYT visualizations. CNN chose to encode their US state data on a map. NYT put them in circles based on whether they will vote Trump or Biden. CNN’s approach means you can readily find any given state, if you know US geography. If you want to look up a specific state, it’s easy. In the NYT chart, it is not easy to find any particular state, requiring you to read through state abbreviations. This doesn’t mean the NYT got it wrong. By putting states beneath a candidate, and making the circle bigger or smaller, you can more easily see which candidate is more likely to win. A map doesn’t afford dynamic changes like that. These are decisions we always have to make when working with geospatial data: a map is always tempting, but sometimes there are options that reveal different insights. Takeaway #4: Before you make a map with your geospatial data, ask yourself if it’s the best way to share your insight. Data geeks love to hunt for historical Easter eggs We talked at length about “The winding path to victory” snake chart on the FiveThirtyEight website. It’s an unusual method of displaying data. Personally, I find it a bit confusing, but during Chart Chat, others pointed out that the novelty is a good way to draw in beginners. I recommend watching that segment because the discussion was fascinating. The snake chart has been a long-standing part of their poll tracking, but Steve called out its similarity to Benjamin Franklin’s Join or Die cartoon from 1754. A conversation on Twitter has, at time of writing, yet to determine if the similarity is by design. Either way, I hope it is. Our field of visualization sits on the shoulders of giants. The 18th century was a period of great experimentation in communicating data, and much that was developed then became part of what we now call “best practice.”  Takeaway #5: Go learn about the history of our field. It is fascinating to dig into! That is the list of our takeaways from the election poll trackers we’ve been reviewing. I’d love to know what other lessons you’ve found. If this is interesting, make sure to follow me on Twitter (@acotgreave) during the night of the US presidential and general election, when I’ll be doing live commentary of the best and worst data visualizations from TV networks: make sure to follow the hashtag #ElectionViz. Also, check out the election projects Tableau has been doing with Survey Monkey and Axios.

I wrote a post about PoSh output recently, noting that in general we ought to use Write-Output or Write-Verbose for messaging. In there, I mentioned Write-Debug as well as a way of allowing the user to control debug information. Since I often find myself fumbling a bit with debugging scripts, I decided to give this a try and see how it works. First, let’s build a simple script. In this case, I’ll write a script that takes two parameters and determines which one is larger. $i = $args[0] $j = $args[1] Write-Debug("First Param:$i") Write-Debug("SecondParam:$j") if ($i -eq #null ) {   $i = 1 Write-Debug("Setting first as a default to 1") } if ($j -eq #null ) {   $j = 1 Write-Debug("Setting second as a default to 1") } if ($a -gt $b) {   Write-Output("The first parameter is larger") } elseif ($i -eq $j ) {   Write-Output("The parameters are equal.") } else {       Write-Output("The second parameter is larger")   } If I run this, I get what I expect. Here are a few executions. Now, what if I’m unsure of what’s happening. For example, I forget the second parameter. How does my program know the first parameter is larger? I have some debug information in there, but it doesn’t appear. However, if I change the value of $DebugPreference, I see something. The variable, $DebugPreference, controls how Write-Debug messages are processed. By default, this is set to SilentlyContinue. However, if I change this to Continue, all the messages appear. If I want, I can also set it to Stop or Inquire, allowing me to control the program differently. You can read more about preference variables here. This is a handy thing to use. I’ve often had a variable I set in programs, sometimes as a parameter, that allows me to show debug messages, but I often then need a series of IF statements inside code to check this and display debug information. Now, I can just include write-debug info in my code, and if the preference isn’t set, I don’t see them. I’ve seen this used in custom cmdlets from vendors, including Redgate, and it is nice to be able to access more information when something isn’t working, and have it suppressed by default. The post Using Write-Debug appeared first on SQLServerCentral.