MySQL - Best long read query solution within a replication based system

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why?
MyISAM
Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is when you have concurrent_insert set to 2 and the MyISAM table experiences no UPDATEs or DELETEs.
Please see my earlier posts on MyISAM behavior.

Dec 04, 2013 : Should we explicitly lock MyISAM tables or is it implicit....?
Nov 25, 2011 : MySQL MyISAM Locking question

AFFECTS ON REPLICATION : You should be a little concerned here because the SQL thread only processes INSERTs, UPDATEs, DELETEs, and various DDL commands. Should any of these command get issue by the SQL thread during MySQL Replication on a MyISAM table you are reading, there will be some latency due to the momentary table lock.
InnoDB
InnoDB can be a little more forgiving in allowing you to read from an InnoDB table that is being written, but it comes at a price. What is the price ???
InnoDB allows for MVCC (Multiversion Concurrency Control) and Transaction Isolation. This means InnoDB will write gobs of housecleaning and point-in-time information so that you can read rows from an InnoDB table without blocking incoming writes, and vice versa.
Here is a pictorial representation of InnoDB's Architecture

All that housecleaning and point-in-time information sits inside the Undo Space and Rollback Segments inside the system tablespace.
AFFECTS ON REPLICATION : When it comes to MySQL Replication, INSERTs, UPDATEs and DELETEs against an InnoDB from an active SQL thread will not block SELECTs from that same InnoDB tables. Of course, any DDL against an InnoDB table will block SELECTs just the same as a MyISAM table would.
MySQL REPLICATION
MySQL Replication can also be affected for the same reasons I mentioned. The shoe would be on the other foot: the SQL thread would get locked if it needs to do an INSERT, UPDATE, or DELETE against a MyISAM table. For InnoDB, MVCC info would be written up to permit SELECTs on the Slave.
EPILOGUE
If the Slave has hundreds of DB Connections, one more DB Connection (the SQL thread) may not be a lot to worry about
If you do know that the Slave may experience an onslaught of INSERTs, UPDATEs, and DELETEs coming from the Slave's  SQL Thread, you could run STOP SLAVE; to disconnect the SQL thread and stop processing SQL commands and also stop downloading Master binlog events. As an alternative, you could run STOP SLAVE SQL_THREAD; to disconnect the SQL thread but allow incoming master binlog event to continue to be downloaded to its realy logs and await processing when the START SLAVE; is issued later. If the number of SQL commands coming from MySQL Replication are few and far between, then MySQL Replication can be left running.


Snapshot Folder Location for Replication with Clustered Instance

As long as you can access that folder from all the nodes of the cluster there shouldn't be any issue.

Just make sure you have a decent nw connection between the cluster nodes and the replication box, otherwise the generation of the snapshots will take a long time.


What are the side effects of changing MySQL `character_set_server`?

You've stumbled onto one of the imponderables of MySQL's default configuration. To quote from the documentation on Character Set Configuration:

  ...when character_set_system differs from character_set_server or
  character_set_client, and you input characters manually (as database
  object identifiers, column values, or both), these may be displayed
  incorrectly in output from the client or the output itself may be
  formatted incorrectly. In such cases, starting the mysql client with
  --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the
  problem.

To distill, not changing the value of character_set_server will almost certainly eventually cause you problems if you use Unicode characters outside the base ISO-latin-1 character set.


How to write stored procedure use SP_HELPTEXT for getting information of objects belong to another database

I would recommend against sp_helptext in general.

SELECT [schema] = s.name, [object] = o.name, m.definition
  FROM otherdatabase.sys.sql_modules AS m
  INNER JOIN otherdatabase.sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN otherdatabase.sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];

If you need the database name to be dynamic, you can do:

DECLARE @db sysname = N'otherdatabase';

DECLARE @exec nvarchar(520) = @db + '.sys.sp_executesql';

DECLARE @sql nvarchar(max) = N'SELECT [schema] = s.name,
    [object] = o.name, m.definition
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];';

EXEC @exec @sql;


Cannot start MySQL Cluster Management Service

Start with

/usr/sbin/ndb_mgmd --ndb-nodeid=123

or set it based on hostname in config.ini

[ndb_mgmd]
hostname=192.168.1.123
NodeId=123

You could try http://www.severalnines.com/cluster-configurator/


How to know who restarted and what caused SQL Server

What about setting up a server audit with this event : "Audit Server Starts and Stops Event Class" > "The Audit Server Starts and Stops event class occurs when the Microsoft SQL Server service state is modified". Never tried it, but you should be able to knwow which "LoginName" modified the SQL Server state. "EventSubClass" 1 is regarding shutdown.

https://msdn.microsoft.com/en-us/library/ms189244(v=sql.100).aspx

https://msdn.microsoft.com/en-us/library/cc280663(v=sql.100).aspx


Get the old version of stored procedure code in sql server

To answer you directly.

You cannot get back the old version of the sp , if it is already executed in production, unless you have a backup or you have a customize trigger that logs all execution of sp.

This happens to my team before, that's why we created a trigger that logs all execution. or you can use SQL source control.

The only thing that you can do now is data fix in production.


Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met.

Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet:

Write Ordering: Writes my happen in the order they are confirmed to the operating system. For example, if the database submits a write request for block A and B to the LUN, you can't have a situation where B is confirmed than A, but where the write happened in the opposite order.

Write through: When the storage says that something is written, it must be on permanent storage that will survive power failure. Unfortunately, a lot of operating systems and storage systems will cache the writes in DRAM unless you are careful. This means that if you lose connectivity to the NAS, you may lose data.

No Torn I/O When you submit a block of I/O and the I/O system confirms the receipt of that block, the entire block must be on stable media (for example, it is not allowed that a 8K page be split into 2 x 4K and the 8K write confirmed before both 4K blocks are written).

Atomic writes: Depending on the media, there is a minimum write size that is required to be atomic (typically 512B or 4K). Writes of this size either happen or they don't. There is no intermediate state that can be represented on the media. Note that this is not (quite) the same as torn pages protection. For example, with a torn page, you could be a situation where 4K of the 8K gets written but the 8K is not confirmed, you then crash and get corruption (which is detectable and typically fixable by the database after restart). With atomics, the write either happens or it does not.

Designing storage that is suitable for a database is no simple matter. Often, people get it wrong - especially if they did not test the storage before deploying. It is little comfort, but your situation is not that uncommon and NAS might have nothing at all to do with this (people get this wrong on direct attached storage and SAN too).

Databases can generally run fine on NAS if the storage is designed correctly and the NAS has the right guarantees.

Saying that the CPU of the NAS is not powerful enough is completely irrelevant to this discussion. So I think you can safely disregard the opinion of that developer.


Move large mysql table data from one server to another

You could use a cron job to connect to the existing database, make a backup of the table, select the rows you need to move (over one week old) disconnect from the sql server,  connect to the new server,  insert the rows you have previously selected using a loop,  disconnect from the second server,  reconnect to first server,  and delete the rows you don't need anymore.

That's one way of doing it,  I don't know if it's optimal,  but it would work,  do not forget to plan the cron job to run Only when the first server is at its lowest usage to make sure you don't impact the performance of the live server.


How do I get a linked list to display more then just the first search item in Access?

One option would be to create a display column fullName that concats your first and last name values into a single column. I believe the following should work in your Row Source:

SELECT [Table1].[ID], [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, [Table1].[Position]
FROM Applicants
ORDER BY [Table1].[lastName] & ", " & [Table1].[firstName];

A couple notes:

Even though we alias [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, we can't order by the alias, so we repeat the expression in the ORDER BY.

Using the above query, your column count will be 3 instead of 4, so you may need to change the column count property of the ComboBox.

I'm not very Access savvy, so I'd suggest checking other properties to make sure I haven't missed anything else.


MySQL: list users which have been granted EXECUTE permission on a given procedure

This script executes and shows all privileges associated for account.

SHOW GRANTS FOR 'antonio'@'antonio@gmail.com';


How to remove duplicates values within particular ID


Postgres plpgsql: Is a view better than a select query in a plpgsql function

There will be no difference in performance between a view and a straight-up query. View is basically a saved query text. If you expect to call this exact query in multiple places in your code, it may make sense to create a view. Otherwise, put a query in your pgplsql code, it will improve readability of it.


How to get the oldest values over each id in a PostgreSQL table efficiently?

There are many possible query styles, most will readily use your PK index on (sensor_id, time) as it fits the task. (Postgres can read indexes backwards practically as fast.) This should be near perfect:

SELECT s.sensor_id, sd.time, sd.value
FROM   unnest ('{1,3}'::int[]) s(sensor_id)
LEFT   JOIN LATERAL (
   SELECT *
   FROM   sensor_data sd
   WHERE  sd.sensor_id = s.sensor_id
   ORDER  BY time DESC
   LIMIT  1
   ) sd ON true;

db<>fiddle here

LEFT JOIN .. ON true keeps sensors without any data entries in the result - with NULL values in place of values.

Related:

Optimize GROUP BY query to retrieve latest row per user
What is the difference between LATERAL and a subquery in PostgreSQL?

Since you are on Postgres 11, a covering index might pay:

... PRIMARY KEY (sensor_id, time) INCLUDE (value)

But it makes the index bigger and writes to the table more expensive, and your names indicate a write-heavy table. And while you only query for few rows at a time, queries don't get much faster anyway. So probably best the way you have it. Related:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?


I think you want multiple aggregations:

First find the max for each ID-Source combination, then sum for every ID:

select ID, sum(MaxNumImages) as SumMaxNumImages
from
  (
    select ID, max(NumImages) as MaxNumImages
    from table
    where Source in ('A','B') and Event = '190'
      and NumImages is not null
    group by ID, Source
  )
  as agg
group by ID ;


Can I construct a trigger that deletes existing rows before inserting new ones in SQLite, which lacks FOR EACH STATEMENT?

Remember that this is a lite version of SQL and does not support all operations. See the following link:

https://www.sqlite.org/lang_createtrigger.html

SQLite supports a BEFORE TRIGGER which can delete rows before further action is taken. The link also provides some cautions about how that works:

  If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
  that was to have been updated or deleted, then the result of the
  subsequent update or delete operation is undefined. Furthermore, if a
  BEFORE trigger modifies or deletes a row, then it is undefined whether
  or not AFTER triggers that would have otherwise run on those rows will
  in fact run.

See the link to learn other details about the SQLite triggers.

Of course you could write your SQLite code to delete the rows that you no longer need in your database before inserting new rows into your database. I would prefer that approach over a trigger since your are just getting rid of unwanted data.


The only risk is that you might drop a user that you still need.
Other than that, PostgreSQL tracks dependencies between users and other objects and won't let you drop users that have permissions or own objects in the database.


I have never seen an IP address like that before. Since 303 is an impossible number for an IP address, you should just save yourself the headache and delete both from mysql.user

DELETE FROM mysql.user WHERE user='asdfasdf_imac4';
FLUSH PRIVILEGES;

Give it a Try !!!


Either the book authors describe some really ancient version of SQLite, or they make a mistake when they say "[t]he header size [...] is encoded as a single byte."
Documentation that "describes and defines the on-disk database file format used by all releases of SQLite since version 3.0.0 (2004-06-18)" states that

The record format makes extensive use of the variable-length integer or varint representation of 64-bit signed integers[...]
A record contains a header and a body, in that order. The header begins with a single varint which determines the total number of bytes in the header. [...] Following the size varint are one or more additional varints, one per column.

while

A variable-length integer or "varint" is a static Huffman encoding of 64-bit twos-complement integers that uses less space for small positive values. A varint is between 1 and 9 bytes in length.

so

The serial type varints for large strings and BLOBs might extend to two or three byte varints

which certainly allows them to represent pretty long values.


User can't stop Oracle listener (TNS-01190)

The description for the message you're seeing is:

  Cause: Most of the listener administrative commands are only intended
  to be issued by privileged users, for example DBAs or system
  administrators. If the listener password is not set, then the listener
  only accepts administrative requests from LSNRCTL running with the
  same OS credentials, or running as a local administrator (also
  referred to as super user).

  Action: If an authorized user is attempting the command, then make
  sure that LSNRCTL is executed with the same OS user credentials as the
  running listener, or as a local administrator.

Unless you want to get into setuid wrappers, you need to have a password set for your listener. There's a good summary of how to set that up here, if you haven't already done so.

Then if you start the listener as normal as oracle, you can shut it down as user1 by going into the interactive mode of the controller, enter command 'set password', and enter the password for the listener:

$ lsnrctl

LSNRCTL for Solaris: Version 11.2.0.2.0 - Production on 12-NOV-2012 14:55:29

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
TNS-01190: The user is not authorized to execute the requested listener command
LSNRCTL> set password
Password: <secret>
The command completed successfully
LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
The command completed successfully
LSNRCTL>

... and it should shut down. I know this is a different O/S and version but the principle is the same. And the reverse is true; if you start it as user1, you need to set password to be able to shut it down as oracle.

However, this is deprecated from 11gR2. The manual mentons using ssh or OEM, but you could also just make user1 switch to the oracle account to start and stop the listener via su. Obviously that gives them full DBA access, it isn't restricted to the listener, which is the case for ssh too.

I'm not sure why you'd want anyone you didn't trust to administer the database to be able to mess with the listener, or why you need to shut it down and start it up often enough for this to be an issue.


SQL Server : cannot add subscriber to publication

As discussed in the comments earlier, the replication couldn't be established is likely due to the instance name, that was being upgraded to a new host, but using back the same instance name as previous.

This issue can be fixed without reinstalling the SQL server, but you will need about a minute or so of downtime.

The following steps can be found in this link here.

(Basically I am copying the steps directly from the link here)

Case 1: For a renamed computer that hosts a default instance of SQL Server

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

Case 2: For a renamed computer that hosts a named instance of SQL Server

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO

  For both cases, you have to restart your SQL server service afterwards.

Once done, then do a verification again by:

SELECT @@SERVERNAME


Varchar(max) field cutting off data after 8000 characters

If you are building the string up via string concatenation make sure that you cast one of the string literals explicitly to varchar(max) if none of the component strings are > 8,000 characters anyway. Otherwise it will be treated as a non max datatype and the concatenation will be truncated at 8,000 bytes.

/*Succeeds. Contains string literal >8,000 chars so treated as max anyway*/
SELECT LEN('b' + 'c' + 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'  + 'B' + 'C')

/*Succeeds. Contains no string literal >8,000 chars but cast to max */
SELECT LEN(CAST('A' AS varchar(MAX)) + 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' + 'B' + 'C')

/*Truncates. Contains no string literal >8,000 chars with no cast*/
SELECT LEN('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'  + 'B' + 'C')

And it would be best to make it the first string literal that is cast to varchar(max) to avoid truncation should the combined length exceed 8,000 before the varchar(max) literal is reached.


Why does linked server need delegation where as file access require no delegation settings?

This happens as the ssis is not involved in a two hops windows authentication process.
As you said you use a proxy so you impersonate an account that is used directly against the CIFS share.
Instead with a linked server you are doing a two hops windows authentication:

Local sql server
target of linked server

To avoid two hops in ssis, don't use linked server. Instead use a direct connection string.


How can I create 3 tables with references On SQL Server

I've re-written your CREATE TABLE statements like this:

CREATE TABLE dbo.uf
(
    uf_id INT NOT NULL
        CONSTRAINT PK_uf
        PRIMARY KEY CLUSTERED
    , uf_name VARCHAR(50) -- you *can* name a column the same as the table
                         -- but try not to, since it will result in confusion
);

CREATE TABLE dbo.carta
(
    carta_id INT NOT NULL
        CONSTRAINT PK_carta
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_carta_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , carta_name VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.cliente
(
    cliente_id INT NOT NULL
        CONSTRAINT PK_cliente
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_cliente_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , cliente_name VARCHAR(50) NOT NULL
);

Always specify the "schema" of the table; in the above example that is dbo - which is the default schema.  Specifying the schema name removes confusion about what schema will contain the table.  I've renamed the uf column in the uf table to uf_name to remove confusion when the uf table is used in queries.  You can name a column the same name as the table it appears in, but I would try not to do that.

The dbo.uf table has a PRIMARY KEY constraint which enforces uniqueness and makes the contents of the referenced column valid for use as a foreign key in other, related tables.

The dbo.carta and dbo.cliente tables have FOREIGN KEY references to the dbo.uf table.  This prevents inserting rows into those tables without a valid entry in the dbo.uf table.  This is commonly referred to as referential integrity and is an important principle in good database design.

I've made all the columns NOT NULL since non-null values are easier to understand, especially when learning.

Below, we're inserting data into the three tables in a way that shows how the foreign keys work.  First, we'll insert two country names into the dbo.uf table:

INSERT INTO dbo.uf(uf_id, uf_name)
VALUES (1, 'Brazil')
    , (2, 'Canada');

SELECT *
FROM dbo.uf;

The results:

The uf_id column is used in the other two tables, instead of the actual name of the country itself:

INSERT INTO dbo.carta (carta_id, uf_id, carta_name)
VALUES (1, 1, 'Natal') --Brazil
    , (2, 1, 'Fortaleza') --Brazil, again
    , (3, 2, 'Winnipeg'); --Canada

SELECT *
FROM dbo.carta;

INSERT INTO dbo.cliente (cliente_id, uf_id, cliente_name)
VALUES (1, 1, 'Paulo') --Brazil
    , (2, 2, 'Nedja') --Canada
    , (3, 2, 'Max'); --Canada, again

SELECT *
FROM dbo.cliente;

To join the three tables together, you use the JOIN clause, such as:

SELECT *
FROM dbo.uf
    LEFT JOIN dbo.carta ON uf.uf_id = carta.uf_id
    LEFT JOIN dbo.cliente ON uf.uf_id = cliente.uf_id

which shows the following results:

Steve Stedman made a fantastic poster about the different kinds of JOIN statements, which is available here.


Disk page reads for Index Nested Loop Join

I never took a class like that but I believe the idea is that it takes additional reads to fetch the data pages when you find matching rows through the index. Let's run a quick test in SQL Server to see if we get numbers close to your formula.

First I'll create tables and insert sample data into them. I want to test three different queries: one with 0 rows returned, one with 5000 rows returned, and one with 10000 rows returned.

-- create tables
CREATE TABLE X_OUTER_TABLE (NUM INT);
CREATE TABLE X_INNER_TABLE_0_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_0_MATCHES ON X_INNER_TABLE_0_MATCHES (NUM);

CREATE TABLE X_INNER_TABLE_5000_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_5000_MATCHES ON X_INNER_TABLE_5000_MATCHES (NUM);

CREATE TABLE X_INNER_TABLE_10000_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_10000_MATCHES ON X_INNER_TABLE_10000_MATCHES (NUM);

-- populate data
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_OUTER_TABLE WITH (TABLOCK)
select N from cteTally where N BETWEEN 1 AND 10000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_0_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 10001 AND 60000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_5000_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 5001 AND 65000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_10000_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 1 AND 50000;

I need to get some information about the index depth and the number of pages in the tables as well as do some additional setup to get cleaner numbers:

-- index depth of 2
SELECT INDEXPROPERTY ( object_ID('X_INNER_TABLE_10000_MATCHES'), 'IX_X_INNER_TABLE_10000_MATCHES' , 'IndexDepth');

 -- 19 used pages, 26 reserved
SELECT OBJECT_NAME(s.object_id) table_name,
        SUM(s.used_page_count) used_pages,
        SUM(s.reserved_page_count) reserved_pages
FROM    sys.dm_db_partition_stats s
WHERE OBJECT_NAME(s.object_id) IN ('X_OUTER_TABLE')
GROUP BY s.object_id;

-- get logical reads after query execution
SET STATISTICS IO ON;

-- Trace flag 8744: Disable pre-fetching for ranges
DBCC TRACEON(8744);

Based on the formula I expect the following results:

Join X_OUTER_TABLE to X_INNER_TABLE_0_MATCHES: 19 + 2 * 10000 + 0 = 20019

Join X_OUTER_TABLE to X_INNER_TABLE_5000_MATCHES: 19 + 2 * 10000 + 5000 = 25019

Join X_OUTER_TABLE to X_INNER_TABLE_10000_MATCHES: 19 + 2 * 10000 + 10000 = 30019

Run the SELECT queries:

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_0_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(0 row(s) affected)
Table 'X_INNER_TABLE_0_MATCHES'. Scan count 10000, logical reads 20000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_5000_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(5000 row(s) affected)
Table 'X_INNER_TABLE_5000_MATCHES'. Scan count 10000, logical reads 25022, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_10000_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(10000 row(s) affected)
Table 'X_INNER_TABLE_10000_MATCHES'. Scan count 10000, logical reads 30044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

Actual results:

Join X_OUTER_TABLE to X_INNER_TABLE_0_MATCHES: 20017 (predicted 20019)

Join X_OUTER_TABLE to X_INNER_TABLE_5000_MATCHES: 25039 (predicted 25019)

Join X_OUTER_TABLE to X_INNER_TABLE_10000_MATCHES: 30061 (predicted  30019)

I think that's pretty close!

Clean up script for completeness:

DBCC TRACEOFF(8744);

DROP TABLE X_OUTER_TABLE;
DROP TABLE X_INNER_TABLE_0_MATCHES;
DROP TABLE X_INNER_TABLE_5000_MATCHES;
DROP TABLE X_INNER_TABLE_10000_MATCHES;


Using SQL Server Snapshot Isolation is it possible to determine a row being read is not the latest?

As far as I know there is no way to do this, you can see how many rows are in tempdb using a query like the below
SELECT  version_ghost_record_count
  FROM  sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.sometable', N'U'),
        1,
        NULL,
        N'DETAILED'
    )
  WHERE index_level = 0

but this will only tell you at a table level (or partition if you are using those) & you want to get down to row level, more info here :-
https://www.red-gate.com/simple-talk/sql/performance/read-committed-snapshot-isolation-high-version_ghost_record_count/


Will update occur even if the join fails?

No, if nothing is found in the join, nothing will be updated.


It'd be easier to just grant execute on Ola's proc to that user. Granted, here they'd be able to call the proc with whatever parameters they want. See Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it if you don't want to go that route. Depending on the route you take, you may duplicate the job and make them the owner of their own job.

While DB_BACKUPOPERATOR would be needed, it isn't enough here. If you read that answer it explains a way, but again it'd be easier to just let them execute the procedure or use a batch script and give them permission to run the batch script. Or use sqlcmd which Ola's scripts support - scsimon

If you make him the owner of that one job only and add him as a member of SQL Server Agent Fixed Database Roles SQLAgentUserRole should be able to execute the job. - sqlworldwide


The only thing I can think of, is to convert the value to a string, remove all trailing zeros, convert it back to a numeric and then use the scale() function:

scale(trim(trailing '0' from the_column::text)::numeric)

The following example:

create table t1 (val numeric);

insert into t1
values
  (1.955000000),
  (1.10),
  (1.1010),
  (1.102030);

select val, scale(trim(trailing '0' from val::text)::numeric)
from t1;

returns:

val         | scale
------------+------
1.955000000 |     3
       1.10 |     1
     1.1010 |     3
   1.102030 |     5


Changing identity column from INT to BIGINT

As there is a primary key defined on identity column you wont be able to directly alter this column.

Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table.

  Drop the PK and alter the column; or

First drop the PK

/****** Object: DROP Index [PK_DatabaseLog_DatabaseLogID]******/

ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName_ID]
GO

Alter Column

ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

Add Primary key

/****** Object: ADD Index [PK_DatabaseLog_DatabaseLogID]******/
ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)

This approach usually does not take much time. In my environment it takes mare seconds on big tables which have more than 5 million rows.

  The copy-drop-rename method, as described

You can use this approach as well. However, for this approach you need more downtime than Approach one as you have to sync the tables.


Is there an equivalent of 'FORMATFILE' in MySQL?

It seems to me this case cannot be handle in the same way used in SQL Server. However, I found another alternative to do that (i.e., importing data based-on the number of character by each column) :

LOAD DATA  INFILE 'xx.txt' INTO TABLE MyTable
 LINES TERMINATED BY '\n' (@L)
 SET
    Field1 = SUBSTR(@L, 1, 3), -- field1 : Position = 1 ,length = 3
    Field2=  SUBSTR(@L, 4, 7),  -- field2 : Position = (1+3) ,length = 7
    Field3=  SUBSTR(@L, 11, 3), -- field3 : Position = ((1+3)+7) ,length = 3
    Field4=  SUBSTR(@L, 14, 2) -- field4 : Position = (((1+3)+7)+3) ,length = 2
 ;


Entity with >150 attributes: One table or many tables with relationships

First off, apply normalization to the table and make sure that it really is one table with 170 attributes and not several tables jammed together.

Then decide whether your table will be sparsely populated and if so, decide whether storage space is a concern that warrants creating sub-tables to hold subsets of nullable columns.

If you do decide to create sub-tables, remember to factor in the extra index space when calculating whether a sub-table saves storage or not.


SQL Server 2008R2 Full Text Catalog Missing Records

Full-Text Catalog Indexing takes time.

The official Microsoft Document states (emphasis by me) in the section Population based on change tracking:

  By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.

Reference: Populate Full-Text Indexes (Microsoft Docs)

You might want to consider using Incremental population based on timestamp which is mentioned in the same document.

  An incremental population is an alternative mechanism for manually populating a full-text index. If a table experiences a high volume of inserts, using incremental population can be more efficient that using manual population.+
  You can run an incremental population for a full-text index that has CHANGE_TRACKING set to MANUAL or OFF.
  The requirement for incremental population is that the indexed table must have a column of the timestamp data type. If a timestamp column does not exist, incremental population cannot be performed.
  SQL Server uses the timestamp column to identify rows that have changed since the last population. The incremental population then updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. At the end of a population, the Full-Text Engine records a new timestamp value. This value is the largest timestamp value that SQL Gatherer has found. This value will be used when the next incremental population starts.


Rather inexplicably, a codeigniter upgrade from 3.0.0 to 3.0.1 solved this!


Changing data directory for postgres on CentOS

SELinux blocking access? Check your /var/log/audit/audit.log.

It might be necessary to fix the SELinux context for the new dir:

semanage fcontext --add --equal /var/lib/pgsql /home/mlu/postgres-data/pgsql
restorecon -rv /home/mlu/postgres-data/pgsql/


Error: CImageHelper::Init () Failed Load of symbol file with path

I resolved this by navigating to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn on the SQL server and modifying two ini files within the Binn folder. The first is "sqlctr" and the second is "sqlagtctr." Upon opening the files you will see that both contain "symbolfile=[SYMBOLINSTALLPATH]\sqlagtctr.h" or "symbolfile=[SYMBOLINSTALLPATH]\qlctr.h" nested under [info].

By replacing [SYMBOLINSTALLPATH] with the fully qualified path of C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn and restarting the SQL service I was able to get the issue resolved. For example, the beginning file content should now look similar to the following:

[info]
drivername=[DRIVERNAME]
symbolfile=C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlagtctr.h


DELETE and INSERT - is this simple query statement sufficient to preserve atomicity?

Yes you can use transactions.

And yes if one of transaction statements fails all of it will rollback. Meaning : Nothing will happen or everything will be done.

  Your code :

BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;

See following link for more information about transactions: Transactions in PostgreSql


How to order by clause on distinct select stament condition value

Since ORDER BY is executed after SELECT you can use the alias that you've defined in your SELECT clause.

ORDER BY ArtHeelNo

Another (less recommended) option would be to just order by the number of the column

ORDER BY 2

It's also possible to just repeat the statements in your ORDER BY, this is less recommended since if your statement would change, you have to change it twice. It's also a lot messier to read.

`ORDER BY (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL) THEN BASE_Art.ArtNo                              ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL)                               THEN BASE_Heel.HeelNo ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END)`


Is columnstore a good option for UPDATES with WHERE clauses?

Modifying a row will cause the old row to be flagged as "deleted" (but its still in the column store index) and the new row to be added to the deltastore (row-based storage which will be compressed when it reaches about 1 million rows). So, as you can imagine, many updates will to some extent degrade your columnstore index over time. You can of course do index maintenance, but a columnstore index on B might not be the best choice...


the error messages you got because you don't have privileges to log in to the server so you need to grant the user the necessary access.

some tips to solve this issue:

1) Mount the remote disk locally (map network drive).

2) Use rman CATALOG command to make the backups available.

3) user has enough privileges on ORACLE level


Postgres slow query with order by, index and limit

I can't confirm with PostgreSQL 9.5. So this may be a regression. Sample data,

CREATE TABLE mapped_transactions(remapped_at)
AS
  SELECT CASE WHEN x::int % 42 = 0 THEN null ELSE x::int END
  FROM generate_series( 1, 40e6 )
    AS gs(x);

CREATE INDEX ON mapped_transactions (remapped_at NULLS FIRST);
VACUUM ANALYZE mapped_transactions;

Here we do NULLS FIRST and it works,

EXPLAIN ANALYZE
SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST LIMIT 10000;
                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.069..4.308 rows=10000 loops=1)
   ->  Index Only Scan using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.067..2.740 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.232 ms
 Execution time: 5.017 ms
(5 rows)

Trying DESC NULLS LAST

EXPLAIN ANALYZE
SELECT * FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST LIMIT 10000;
                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.073..4.429 rows=10000 loops=1)
   ->  Index Only Scan Backward using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.071..2.865 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.114 ms
 Execution time: 5.137 ms
(5 rows)

You may have to work up a test case. It's not evident what you're experiencing.


MySQL Calculate percentage difference (not change) between 2 columns

With some help the solution was to use a subquery something like this:

SELECT
  CONCAT('£ ', t1.n1) AS N1,
  CONCAT('£ ', t1.n2) AS N2,
  CONCAT(((N1 - N2) / ((N1 + N2) / 2)) * 100, ' %') AS 'Difference'
FROM (SELECT
    SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END) AS n1,
    SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END) AS n2
  FROM t2
    INNER JOIN t1
      ON t2.col_id = t1.col_id) t2


i'd go for a different approach:

Post(PostID, PostTitle, PostContent, PostParentID)

the field PostParentID is a reference to PostID on the same table: if that field is null then is a question, if is filled then is an answer to the question with id PostParentID.

with the above structure a single table is required to store votes.

there is the potential issue that a post may have an 'answer' as a parent because there are no easy constraints (a trigger is not a constraint and i don't like triggers anyway ^^) to avoid such association but it should be a very limited issue.

with this approach it can be expensive to make statistics because a count of the votes cast on answers require a join with the Post table; having votes on answers and questions in different tables would make such jobs easier and possibly more performing.

it is a winning solution imho if the major task is to access the single question (get votes/counts for a single question/answer) because the logic is simpler, with fewer objects involved and to maintain (I always suppose to interact using stored procedures) and it is the same for answers and questions.


Converting 3 Master + 1 Slave to 4 Master - Any advice?

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

set the server_id as a different number from M1, M2, M3
set log-slave-updates in my.cnf like you did for M1, M2, M3
enable binary logging on S1 the same way you enabled it on M1, M2, M3
restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.


MS DTC errors on SQL Server 2008 R2 but not SQL Server 2000 running on difference instance

We have run into issues when servers were re-named and we had to set the DTC security properties to "No Authentication Required" on all servers involved in the transaction. This is not a best practice but works for internal network only servers. Directions to the DTC security settings can be found here: https://technet.microsoft.com/en-us/library/cc731495.aspx


Want to clone a role, but am getting "can't login" message after doing so

The answer is simple, but may be very surprising:

  CREATE USER is now an alias for CREATE ROLE. The only difference is
  that when the command is spelled CREATE USER, LOGIN is assumed by
  default, whereas NOLOGIN is assumed when the command is spelled
  CREATE ROLE.

So use CREATE USER instead of CREATE ROLE.

There is one more equally tricky aspect of this. Even if the role you grant to the new role (in your example, rails) has the LOGIN option, myuser won't inherit it.  The reason is that GRANT only gives permissions, while LOGIN is an attribute of the user/role:

CREATE USER rails; -- means it has LOGIN
CREATE ROLE myuser;
GRANT rails TO myuser;

\du rails
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 rails     |            | {}

\du myuser
            List of roles
 Role name |  Attributes  | Member of
-----------+--------------+-----------
 myuser    | Cannot login | {rails}

Note: if you are using one of the trust and peer authentication methods in pg_hba.conf, it's possibly a good idea to rethink that.  If you are using LDAP (the last idea I have, given that you don't specify a password), then ignore this.


Executing sys.dm_fts_parser without sysadmin server role

The problem is with the difference between logins and users.  When you are granting the permissions you are working with a user.  Only a login can have server level permissions such as sysadmin.  I discussed this here if you are interested.

In the mean time I can tell you how to do what you need, however it's not the best idea in the world.

First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database.  Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER.  In this case OWNER is dbo.  Also in this case dbo is SA.  Because you made the database trustworthy it will allow users in it to access server level permissions.  All of this means that your stored procedure will be able to act as a sysadmin.  Next grant the login that you are using for the web application connect permissions to your new database.  Then grant that user execute permissions on the stored procedure.

This is very important.  ONLY grant the user execute permissions on the stored procedure.  You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.


Instead of posting again, you could have updated your previous question.

The issue with the old query is that it wasn't calculating tax *.75 appropriately.

rextester: http://rextester.com/ZGQ51639

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
             -(
               sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
             +(sum(isnull(tax,0))*.75)
              )
             )
            /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc


Distributed database implementation in sql server

This is pretty much a textbook example for Merge Replication.  Here is the Product documentation describing the scenario, and how to implement it.


Is it bad to assign specific processors to SQL Server 2008 R2 cluster instances?

I would not say that its a bad idea, but its always prefer not to touch the CPU affinity on sql server unless you know for sure that the problem is due to excessive context switching (and this too might be not the actual problem, but just a symptom).

  This seems to be working okay, even when failover occurs, but is this a bad idea?

Forcing affinity means that you take away sql server's ability to move processes between schedulers.

Consider below example : (using coreinfo from sysinternals) On a 2 socket, 12 CPU (24 logical cpu with hyper-threading enabled) with 2 NUMA Node machine, we get 12 CPU per NUMA node.

Logical to Physical Processor Map:
**----------------------  Physical Processor 0 (Hyperthreaded)
--**--------------------  Physical Processor 1 (Hyperthreaded)
----**------------------  Physical Processor 2 (Hyperthreaded)
------**----------------  Physical Processor 3 (Hyperthreaded)
--------**--------------  Physical Processor 4 (Hyperthreaded)
----------**------------  Physical Processor 5 (Hyperthreaded)
------------**----------  Physical Processor 6 (Hyperthreaded)
--------------**--------  Physical Processor 7 (Hyperthreaded)
----------------**------  Physical Processor 8 (Hyperthreaded)
------------------**----  Physical Processor 9 (Hyperthreaded)
--------------------**--  Physical Processor 10 (Hyperthreaded)
----------------------**  Physical Processor 11 (Hyperthreaded)

Logical Processor to Socket Map:
************------------  Socket 0
------------************  Socket 1

Logical Processor to NUMA Node Map:
************------------  NUMA Node 0
------------************  NUMA Node 1

Remember that the schedulers are not bound to cores and SQL Server does its own thread scheduling. For some reason, if a non SQL process maxes out CPU 1 which is running a thread on scheduler 1, then SQL server has the ability to put that scheduler onto any available CPU e.g. CPU 4. SQL Server has its own load balancer that will move a thread from one CPU to another.

If you set processor affinity, then you remove the ability from sql server to switch scheduler. So scheduler 1 is bound to CPU 1 and it has to run there only.

Some Excerpts from my book library ...

From SQL Server Professional Internals and Troubleshooting Book :

  One common use for soft NUMA is when a SQL Server is hosting an application that has several different groups of users with very different query requirements. After conﬁguring your theoretical 16-processor server for soft NUMA, assigning 2 × 4 CPU nodes and one 8-CPU node to a third NUMA node, you would next conﬁ gure connection afﬁnity for the three nodes to different ports, and then change the connection settings for each class of workload, so that workload A is “afﬁnitized” to port x, which connects to the ﬁrst NUMA node; workload B is afﬁ nitized to port y, which connects to the second NUMA node, and all other workloads are afﬁ nitized to port z, which is set to connect to the third NUMA node.

From Microsoft SQL Server 2008 Internals book :

  In some situations, you might want to limit the number of CPUs available but not bind a particular scheduler to a single CPU—for example, if you are using a multiple-CPU machine for server consolidation. Suppose that you have a 64-processor machine on which you are running eight SQL Server instances and you want each instance to use eight of the processors. Each instance has a different afﬁ nity mask that speciﬁes a different subset of the 64 processors, so you might have afﬁ nity mask values 255 (0xFF), 65280 (0xFF00), 16711680 (0xFF0000), and 4278190080 (0xFF000000). Because the afﬁ nity mask is set, each instance has hard binding of scheduler to CPU. If you want to limit the number of CPUs but still not constrain a particular scheduler to running on a speciﬁc CPU, you can start SQL Server with trace ﬂag 8002. This lets you have CPUs mapped to an instance, but within the instance, schedulers are not bound to CPUs.

If you want to limit the CPU that a SQL instance uses on a server, use Windows System Resource Manager instead.

As always TEST, TEST and TEST your entire workload to avoid any serious performance issues if you decide to use "CPU Affinity".

Suggestion : Leave it to default - as if you get it wrong, things will be worse and troubleshooting would be more difficult !

  What would happen in failover if the passive server didn't have as many processors?

performance problems can occur for the instance, due to NUMA node scheduler imbalances

References :

affinity mask Server Configuration Option
“Don’t Touch That Button!” Four Dangerous Settings in SQL Server (video archived - so no longer available)
Changes in affinity settings of SQL Server 2008 R2
SQL Server and Soft NUMA
Setting a Processor Affinity in SQL Server – the (unwanted) Side-Effects


Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?

Large objects were restored from a dump with this command:

COPY pg_largeobject (loid, pageno, data) FROM stdin;

The problem is that it's not sufficient, because since PostgreSQL 9.0, which added access privileges to large objects, they are stored into two tables:
pg_largeobject_metadata with one row per object and a unique index on oid, and pg_largeobject, which one row per page of data per object.

So the above way of dumping large objects is obsolete, now it looks like, for each large object:

BEGIN;
SELECT pg_catalog.lo_open('16401', 131072);
SELECT pg_catalog.lowrite(0, '\x23207e2f2e6261736872633a2....')
SELECT pg_catalog.lo_close(0);
COMMIT;

How to fix the problem of the missing entries in pg_largeobject_metadata?

Because oid is a system column, I don't think you can just insert into it to manually to create the missing entries.
A clean way to deal with the problem would be to generate a script looking like the above based on the current contents of pg_largeobject, then truncate pg_largeobject, then play the script to reimport the data the right way.


Only allow one checked row in a Column in SQL Server

No

While strictly speaking it can be achieved, it is always a bad idea. In general thinking at restricting properties of sets is a bad road to travel because set property enforcement usually require inspecting the entire set to validate the constraint. In other words enforcing your 'single row check' requires to scan every other row, on every change. This is not only slow, more importantly is the 100% guaranteed recipe for deadlocks.

It would be much better if you post your actual problem rather than ask how to implement the solution you choose.


Listener does not connect to the correct instance

By default a standalone SQL Server instance (ie non Failover Cluster Instance), will listen on its port on All IP Addresses.  See  TCP/IP Properties (IP Addresses Tab).

And this means that the instance will accept TCP connections on its designated port on all current and future IP addresses.  So when the Cluster brings the AG Listener IP address online, it won't just be the SQL Instance that owns the AG that listens on that IP address.


How do you calculate mysql max_connections variable?

Going to post this as an answer, with the relevant information. The basic formulas are:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM - Global Buffers) / Thread Buffers

To get the list of buffers and their values:
SHOW VARIABLES LIKE '%buffer%';

Here's a list of the buffers and whether they're Global or Thread:
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack


How to make a mysql statement for sent log

In next iteration, you may need to check if there are pending numbers to send sms. If pending count is greater than zero then need to send sms to those pending numbers.

track success_count after resending sms to pending numbers.

Then, need to increment delivered count and decrement pending count with this success_count.

So,

delivered  = delivered + success_count
pending = pending - success_count

then you may need to execute update on make_sent_log table with this updated delivered and pending counts.

update `make_sent_log` set delivered = delivered, pending = pending where transaction_id = 'trans_1';

Continue this iteration cycle until pending count = zero.


Referencing a single table from all tables in the database

You may want to consider a Type 2 slowly changing dimension table and normalize your user name data. This will give you a better picture of whom is changing the data over time and allow you to use a smaller more efficient surrogate key for your *By columns.


Mysql: Problem using GREATEST with IFNULL and join

SELECT
    GREATEST
    (
        IFNULL(tableb.create_time,FROM_UNIXTIME(18000)),
        IFNULL(tablea.create_time,FROM_UNIXTIME(18000))
    );

It just so happens that FROM_UNIXTIME(18000) is '1970-01-01 00:00:00'


Possible career paths for Oracle Certified Associate (OCA)

I imagine you can go anywhere you want to go. But real-world experience counts too -- not just what's on the exam, so don't expect a high-level position from the start. You'll probably have to start as low man on the totem pole, so-to-speak.

Having been a DBA, I can tell you several things:

It's freakin' hard work
When things are going well, life is good. When things are going bad, life is hell.
Unless you happen on a place where you're a pure DBA, chances are good you'll have programming opportunities. Relish them -- this is where you can express your creativity.
When things go really bad, experimentation is sometimes the only way out. (Not reckless abandon, mind you, but oft-times it becomes a matter of trying one solution, finding it doesn't do the trick, and then trying something else.)
There are boring parts: upgrades, backups, maintenance. But sometimes that's a good thing, because otherwise you'll be so busy that your brain can't wait for the boring times.

Hope it helps and good luck with your career.


Federated tables are going to be inherently slow, especially when doing joins.  It's basically copying down an entire copy of the table for each join.

Federated tables should simply be avoided.


How to Perform Two Simple Queries using Select Value from First Query

How about using a join in the unioned SELECTs:

(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id < t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id DESC LIMIT 5)
UNION ALL
(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id >= t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id ASC LIMIT 15)

Note that the order from the parts of UNION ALL might not be preserved in the result.


SQL Server 2016 support for 32-bit systems

These fixes aren't for the database engine.

  Why are there both x64 and x86 packages?
  As above, while there is no x86 engine, fixes can apply to a variety of shared components on the machine (including from the feature pack), and I suppose it's possible that those could be installed in isolation on an x86 machine. It is almost certainly the case that if you have installed SQL Server 2016, you should be applying the x64 CU, and only the x64 CU.

These fixes will continue coming out this way because the updates are cumulative - as soon as a fix is there that targets an x86 binary, that fix has to stay in all future CUs too.


Running scheduled SSIS pkg with SQL Server 2012 Web Edition

When you attempt to run the package from dtexec, you can test this yourself, you're going to get an error that basically says you failed the licensing check and it's not going to run the packages outside the context of Visual Studio (BIDS/SSDT-BI).

Once you have a licensed for a version that supports SSIS, then you'll need to install the Integration Services Service otherwise, you'll get an error in the form of "To run a ssis package outside of sql server data tools you must install {TASK NAME} of Integration Services or higher" See also Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher."


How to write search query to retrevie the rows with biggest DateTime <= 2010102248 in SQL

You need to make sure your subquery is correlated with the main query, so you are getting the max timestamp for each feature:
SELECT
  test.FeatureNbr,
  test.DateTime,
  test.Val
FROM
  testTable test
WHERE
  DateTime =
    (
      SELECT
        MAX(DateTime)
      FROM
        testTable
      WHERE
        FeatureNbr = test.FeatureNbr  --This is what you are missing
          AND DateTime <= '2020-10-06 12:55:00' --Or whatever
    )

See this DB Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a57b5d632f53a973a6225718e2baea3a
A few other points:

Always store date/time values as DATETIME2 in SQL Server.  Otherwise you can end up with invalid date/time combinations and won't be able to use the native date functions without first converting.
You need to make sure your primary key is defined - the right clustered index (in this example (FeatureNbr,Datetime)) is critical - it will allow the query to complete reading the table once and without sorts.
This was addressed in my answer here, but I know you are starting out so it might not have been apparent due to the length.
Should also be mentioned that with the proper clustered index, SQL Server will recognize the queries provided by nbk and Andy Mallon (which are identical since a CTE is just another way of writing the same thing) to be equivalent to the above query - they will produce the exact same query plan and return the exact same result.  It's just more code.

To address your comment:

Yes I begin to understand it a bit better but its so new. I know C#
for 10 years but this is alot of "nesting" like it feels.
After WHERE there should be a condition. You put: WHERE DateTime = (...). Is this  some kind of "temp table" that is happening here, where you later:
FeatureNbr = test.FeatureNbr? I also wonder if it will be correct to
put this condition against DateTime like this? WHERE FeatureNbr =
test.FeatureNbr AND DayTime <= 2010102248 (I know the datetime format
got wrong here but it will work for the example as an integer)

Making the transition from object oriented/traditional programming to database development is a hard one as you're used to specifying exactly HOW things are done.  SQL is a declarative language, we specify what we want and allow the database engine to determine the optimal plan.
So the "nesting" here is just specifying that the Datetime must equal the max Datetime less than or equal to the specified value for each FeatureNbr.  The query engine then looks at the table, the indexes, the column statistics, and decides the best way to fulfill that query.  If you have a good data model, you rarely have to tune queries.
You will probably want to start to familiarize yourself with how to read query/execution plans and the specific operators.  Then you can experiment with how the engine actually fulfills the requests, how your clustered index influences the plan, etc.
If you were to generate query plans for three of the answers (nbk's, Andy's, and my own) they would be identical with the right clustered index.  The answer that uses ROW_NUMBER() forces a sort, which is instructive in its own way.
Additional comment:

If I understand, it doesn't matter in what order you write this(When
you combine columns): CONSTRAINT PK_testTable PRIMARY KEY
(FeatureNbr,DateTime) it could be written like this also: CONSTRAINT
PK_testTable PRIMARY KEY (DateTime,FeatureNbr)

So this brings up a very important concept for the physical implementation of tables in SQL Server, which is the rows are stored (more or less) in the order according to the position of the rows specified during the creation of the clustered index.
So: (FeatureNbr,Datetime) sorts first by FeatureNbr (let's call this option 1) while (Datetime,FeatureNbr) sorts first by Datetime (option 2).
If we do a query that looks for one row and one row only (say, FeatureNbr = 2 AND Datetime = '2020-10-06 10:33:00'), both indexes will perform identically.  But if we do a query that has to look across ranges of Datetime for a given FeatureNbr, option 1 would allow the clustered index to be utilized, option 2 would result in a table scan.  This is because for option 1 the engine knows all values for a FeatureNbr will be on the same page(s) (more or less), but for option 2 the relevant rows could be stored anywhere in the table.
For this reason, a date or datetime column should be the last row in an index as a general rule (in databases there are always exceptions).


SSIS job -- how to create stored procedure that repeats SSIS jobs?

This may be a little less elegant than you are looking for but I would imagine this would work ok for you.

CREATE PROCEDURE dbo.ExecuteSSISPackageOnLoopWithWaitTimer
(
    @PackageName CHAR(100),
    @NumberOfLoops INT,
    @WaitTimer VarChar(20) = '00:05' --five minutes
)
AS
BEGIN
    DECLARE @InstanceCount INT = 0
    IF(@NumberOfLoops < 0 OR @NumberOfLoops IS NULL)
    BEGIN
        SET @NumberOfLoops = 0
    END

    WHILE (@InstanceCount < @NumberOfLoops)
    BEGIN
        exec ssisdb.catalog.create_execution @package_name=@PackageName

        IF(@WaitTimer IS NOT NULL AND @WaitTimer <> '')
        BEGIN
            WAITFOR DELAY @WaitTimer
        END

        SET @InstanceCount = @InstanceCount + 1
    END
END

Taken from a previous Stack Overflow question about SQL Wait Timers

WAITFOR DELAY '02:00' --Two hours
WAITFOR DELAY '00:02' --Two minutes
WAITFOR DELAY '00:00:02' --Two seconds
WAITFOR DELAY '00:00:00.200' --Two tenths of a seconds

Hopefully that give you what you need.


HowTo parsed Stored Procs to get a list of tables and columns referenced in WHERE, GROUP BY, AND JOIN clauses

sysdepends & sys.sql_modules have some known limitations... and you probably don't want to perform string-parsing gymnastics if you want to rely on your results with a high level of confidence. Although neither of these options is really something I would recommend as an answer to your problem.

  The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.

Perhaps you've got a highly targeted need to do exactly this, but my first reaction is that you might want to take a more holistic approach to your stats maintenance than reviewing the text inside stored procedures. You may have an heavy ad-hoc query load that doesn't ever touch a stored procedure. You may have 10 stored procs that reference all the things but are never called and 1 stored proc that touches less but is called all the time. I recommend taking a step back and browsing some literature before taking the path you seem to be steering toward with this question.


This can be VSS based DPM backups. Do you also have info in the Errorlog about "I/O Frozen for database xxx" and "I/O thawed for database xxx" ? That is sign of VSS (Snapshot) backups. If you rely on that kind of backups you should (as always) ensure that you can restore the backups, and you should pay special attention to Log backups.


How do you delete a row that contains a certain value from a specific column across all tables in a database?

I don't think there is a way to do it with a single query but you could query the system tables for any table that has that column name and then use that to create the delete scripts for you.

Select from those tables:

select 'select * from ' + t.name + ' where GtNumber = 2'
from sys.tables t inner join sys.columns c on t.object_id=c.object_id
where c.name='GtNumber'

Delete from those tables:

select 'delete from ' + t.name + ' where GtNumber = 2'
from sys.tables t inner join sys.columns c on t.object_id=c.object_id
where c.name='GtNumber'


How does the spec store JSON on a table?

SQL:2016

This is from my own read of ISO/IEC TR 19075-6:2017: SQL support for JavaScript Object Notation (JSON)

Ignoring both (a) the track record of MySQL with regard to the spec, and (b) the fact that they're now implementing a JSON type. It seems as if they're referring to this,

  A <JSON value expression> may have an optional <JSON input clause>. This indicates that the values expression should be parsed as JSON. The standardized option is FORMAT JSON; implementations may also
  support syntax such as FORMAT AVRO or FORMAT BSON. When using the <JSON input clause>, the <value expression> may be either a character string or a binary string.

Essentially, the spec provides for JSON values to come from strings, or binary blobs. The method of making them JSON is done through a decorator which identifies it and its FORMATs. The spec explicitly mentions "implementation-defined JSON representation option" it gives BSON and Apache AVRO as examples. It would look something like this (my read of the spec)

SELECT str FORMAT JSON BSON;

From there the value construction is accessible with the "SQL/JSON data model" and the rest of the spec applies to it. However there is no binary storage mechanism, or stipulation that one must be implemented for SQL/JSON. Implementing one doesn't seem to contradict the spec. In fact it goes on to say,

  Constructor functions use values of SQL types and produce JSON values
  (JSON objects or JSON arrays) represented in SQL character or binary string types.

So they can be represented as a binary type,

  Query functions evaluate SQL/JSON path language expressions against JSON values, producing values of SQL/JSON types, which are converted to SQL types.

And SQL queries need to return "SQL types", regardless of if there is a named binary type under the hood. Most databases do that though they return character/varchar/text representations of JSON.

The spec's only example of storing JSON on a table uses strings,

CREATE TABLE employees (
emp_id INTEGER,
name CHARACTER VARYING(50),
salary DECIMAL(7,2),
dept_id INTEGER,
json_emp CHARACTER VARYING(5000) )

That essentially means the it's serialized to and parsed according to RFC7159. It's also somewhat amusing that the retrieval of json_emp is missing from the spec.

tldr: I don't think anything about implementing a binary type "contradicts" the spec, as it is explicitly permitted. I think the right word is "complements."


Can 2 different versions of MySQL be run side by side?

The database I wanted to restore was actually MySQL 5.5.29. OH NO!
Based on the suggestion of @mustaccio, I set up a docker container to
run the MySQL 5.5 instance.

bazzani has a MySQL 5.5 docker image builder so I used that
The last step, to use the backed up MySQl instance files with the docker image
was completed with:

docker run --name=mysql55 -d -v ~/mysql:/var/lib/mysql mysql5.5

Find the IPAddress of MySQL in the container:

docker inspect mysql55 | grep -i ipaddress

After that it was easy to access the database and properly dump the data, schema and triggers and grants:

Grants

mysql -u user -ppassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u user -ppassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
Routines

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword  --no-data --no-create-info --routines ${DB} > ${DB}-routines.sql
Schema and Triggers

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword --no-data --triggers ${DB} > ${DB}-schema-triggers.sql
Data

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword --no-create-info --skip-triggers --no-create-db --compact ${DB} > ${DB}-data-only.sql

This whole solution was important as 12 months of research in multiple databases had been poorly backed up using mysqldump this way:

mysqldump --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db

This command dumps everything in a single file, which is not workable, so all current backups mostly useless. Fortunately the old drive is solid though the machine is dead.


What are the Pros and Cons of assigning value for ids from an offset? (Ex: 100,200,1000)

There are a variety of reasons why you may want an offset. Please be aware, some of these reasons are not necessarily good DBA practice.

As dnoeth mentioned, your primary key may be used in some other business process such as an order number. In that case, you don't want your customers thinking they are only the 11th order!  Being order number 1100011 is much better.
You may have a old system that is in read only mode.  It has all your old customers in it with their primary keys, and you want the new system to pick up where it left off.
Same idea, you know you will be importing PKS of a certain value at some point in the future, so you start with ones you know won't cause a collision.

Personally, the only time I offset is to avoid PK collisions. Sure, there was that one time when I was younger that I just though starting at PK ID 10000 was cool, but that was a long time ago.

Really, there are no Cons of doing so.  Except for some nominal space issues between data types and potentially losing the ability to assign PKs up to the data type limit, the database doesn't care if it starts at 1 or 1,000,000. It is going to index it all the same.


PostgreSQL trigger doesn't insert all records

The select from employee table won't work as it is a before trigger and thus the new rows are not yet in the employees table.

You can use the new record directly.

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  SELECT new.empID, new.full_name
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE new.empID = empMerge.empID
                     and  new.full_name = empMerge.full_name);
  return new;
end;
$$ language plpgsql;

You could make that a bit shorter (and faster) if you declare a unique constraint on empmerge(empid, full_name) and then use insert on conflict:

alter table empmerge
   add constraint unique_employee
   unique (empid, full_name);

Then change the trigger function:

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  values (new.empID, new.full_name)
  on conflict on constraint unique_employee do nothing;
  return new;
end;
$$ language plpgsql;

Another option would be to make the existing trigger an AFTER trigger. In that case the new row is already inserted into the employee table and it would be visible for the trigger.