Varchar(max) field cutting off data after 8000 characters I have a field to store some data, the field is declared as varchar(max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest off.

I have verified that all the data is included in my update statement and the query looks fine everywhere else but when I select the data back out it has been cut off.

The data is truncated when I display it on my website and also when I use SSMS to select content from table.

select DATALENGTH (content) from table comes back as 8000.

I set the data using this: update table set content = 'my long content' where id = 1. The content does have lots of HTML in but I can't see that causing issues. The only thing I can see that I am doing is replacing all " with '' as this is user entered content (can't remember why I did that now).

I did manage to get the content to enter correctly by removing all single quotes in the content so I think something odd is going on with my data rather than the database.

Should I be doing something special with the query to use a varchar(max) field?

Using: SQL Server 2008 (10.50) 64 bit.

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.


Connecting from SSMS to an EC2 SQL Instance I have an EC2 server running SQL2008 R2.

I opened the port 1433 and successfully tested with yougetsignal.com/tools/open-ports.
I have set the SQL Server to Accept Remote Connections.
I have set the Dynamics Port to be 1433 on the TCP/IP dialog under the Configuration Manager.
On Client Protocols I have Shared Memory 1st, TCP 2nd and Named Pipes 3rd.
The server SQL Browser services is running

On the server I normally connect from SSMS using MACHINENAME\SQL2008

QUESTION:
What is the right way to connect from my local machine? Is it "IP ADDRESS\SQL2008"?

I tried various ways and it does NOT work. So I must assume it's both the way I connect and some missing settings on the server. I get the following error:

A network-related or instance-specific error occurred while establishing
a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that the SQL Server is configured
to allow remote connections. (provider: Named Pipes Provide, error: 5-Invalid
parameter(s) found) (Microsoft SQL Server, Errror: 87)

ANSWER
Thanks Mark. I was able to connect just by appending the ",1433" at the end.
So the right answer (at least for my problem) was  MACHINENAME\SQL2008,1433

Thanks again Mark !!!

If the server is installed as the default instance, you should be able to connect with DNS name or IP Address. The "\SQL2008" you've added to the address would be used to connect to a named instance called "SQL2008".

Try specify the port in your connection string e.g. "123.123.123.123,1433". Without the port specified, the client will query port 1434 initially to determine which port to use. Alternatively you could open port 1434.

SQL Server Browser Service

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.

Edit: If you can connect using MACHINENAME\SQL2008 when you are RDP'd to the server, you must be using a named instance. If this is the case, your connection string with the port specified will be "123.123.123.123\SQL2008,1433".


How do I tell what is filling up the transaction log? We have a transaction log on a sql database and for some reason it has filled up to 50GB when our live system only has about 2GB. Is there any way I can profile sql or the transaction log to try and find out why this particular one has grown so much?

Start by investigating what is holding up the log reuse. Read Factors That Can Delay Log Truncation, look at log_reuse_wait_desc value is sys.databases. Based on what you find there, there could be several potential actions.


MySQL: Show list of tables with paging in Terminal I am using MySQL Ver 14.14 Distrib 5.5.44 for debian-linux-gnu.

How can I show my tables page wise in a terminal?

You can use the pager (see docs) command in MySQL console or use the --pager startup option (docs again) to redirect the output of your commands to an external executable.

To use the pager command and list the tables try this:

mysql> pager less
mysql> show tables

You can also start MySQL to send all your output to an external executable like this

mysql --pager=/usr/bin/less


Would forced parametarization help? We have a process that creates a new sequence using dynamic sql.  We then use dynamic sql to get the next value from the sequence.  We have a lot of these dynamic sequences, but don't call any individual one a lot, but we call a lot of them.

In the plan cache it looks something like this:

(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName1]
(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName2]
--etc

These are eating up a lot of our plan cache, and tend to only be called once before they're pushed out.  I have Optimize for Ad Hoc Workload turned on, but that hasn't changed the behavior.  Would turning on forced parametarization help reduce the load on the plan cache?  Is there another setting that might help?

This can be easily checked. Here's code which should imitate your scenario:

ALTER DATABASE [playground] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO
CREATE SEQUENCE dbo.Seq1
    START WITH 5
    INCREMENT BY 5 ;
GO

CREATE SEQUENCE dbo.Seq2
    START WITH 5
    INCREMENT BY 5 ;
GO

DBCC FREEPROCCACHE()
GO

SELECT NEXT VALUE FOR dbo.Seq1;
GO
SELECT NEXT VALUE FOR dbo.Seq2;
GO

Now if you check plan cache:

SELECT text, plan_handle, qs.execution_count FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt

You can clearly see there are two distinct plans for two sequences:

This is what I expected. It works only for parameters and not for object names.


Oracle impdb on windows generates a subdirectory I am importing to a windows oracle, a dump file produced on Linux. But oracle generates a subfolder in the dpdump directory /76E0F91BE49D480CB2633847BD640FC5\  and causes an "ORA-31640: unable to open dump file" error.

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "E:\oracle\admin\orcl\dpdump\/76E0F91BE49D480CB2633847BD640FC5\backup_DB_01.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

I shut down oracle and deleted that long subfolder. But it gets recreated every time I run impdp. Any suggestion?

Ask your System admin to grant read write permission on the directory (where dump is located) and run cmd as admin and then try to run impdp command.

For more details :
http://www.dba-oracle.com/t_ora_31640_unable_to_open_dump_file_for_read.htm


Trivial RowVersion Question Is there a method or syntax that would allow a table that just has a rowversion/timestamp column to be filled with data?

CREATE TABLE [dbo].[TimeStamp_Test]([row_version] [timestamp] NOT NULL)

I'm not sure why you would need this but yes it is possible.

Insert into [dbo].[TimeStamp_Test] default values;

Or

Insert into [dbo].[TimeStamp_Test] values (default);

To do multiple rows you can use merge.


using Change Tracking functions from another database If I turn on change tracking on a database called Production, can I use the functions to get the change data from another database?

For example: CHANGE_TRACKING_CURRENT_VERSION();

I have tried the following, and also tried replacing the schema with sys but I don't think this approach is working:

Use [Analysis]
GO
SELECT Production.dbo.CHANGE_TRACKING_CURRENT_VERSION()

I have worked around it by creating a stored procedure on Production that calls this function. Then I can call that stored procedure from other databases on that SQL Server. But would like to know if there is a more direct way.

I am trying to migrate data from one system to another (over a period of a week or two, then shut down the old system). Change tracking is enabled on the other database. I am limited by the version of SQL Server, CDC requires Enterprise, Developer or Evaluation.

CHANGE_TRACKING_CURRENT_VERSION is a built in function not a UDF so Production.dbo.CHANGE_TRACKING_CURRENT_VERSION() isn't allowed.

It accepts no parameters to specify the database.

I would imagine that either of the following would work.

The first calls Production.sys.sp_executesql and executes the function. The second calls sys.sp_executesql in the current database and adds an explicit USE statement to set the context. I haven't set up change tracking and tested explicitly but both approaches work as desired with DB_NAME()

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC Production.sys.sp_executesql
  N'SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC sys.sp_executesql
  N'USE Production;
    SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION


Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types? Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS geometry type in the destination table:

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry(POINT)
);

INSERT INTO t
VALUES
    ( 1, ST_GeometryFromText('Point(0 0)')      ),
    ( 2, ST_GeometryFromText('Point(1 2)')      ),
    ( 3, ST_GeometryFromText('MultiPoint(2 3)') ),
    ( 4, ST_GeometryFromText('Point(5 23)')     ),
    ( 5, ST_GeometryFromText('Point(42 36)')    );

The error message tell's exactly what's wrong:

ERROR:  Geometry type (MultiPoint) does not match column type (Point)

But it lacks useful context information like:

How many rows of the INSERT have this problem?
What were the exact values?
What values were in the other columns of the offending rows? (In the example above: What were the IDs of the offending rows?)

Can I adapt the INSERT statement so that PostgreSQL would give me this information, e.g. include the complete content of the offending rows in the error message, like it does for violated NOT NULL constraints?

E.g.

CREATE TABLE s (
   i integer NOT NULL, t text
);

INSERT INTO s
VALUES
    (1   , 'foo'),
    (NULL, 'bar'),
    (2   , 'baz');

results in a much more useful message:

ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null, bar).

Motivation / Use-Case

When you can just look at the VALUES listed in the INSERT statement and see the offending rows, this is of course not that relevant. But the same issue arises when the inserted rows are selected from another table or computed dynamically, and then a more informative error message would indeed be useful.

The problem here is that the types aren't mismatched. PostGIS provides very few PostgreSQL types, namely:

box2d — A box composed of x min, ymin, xmax, ymax. Often used to return the 2d enclosing box of a geometry.
box3d — A box composed of x min, ymin, zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry or collection of geometries.
geometry — Planar spatial data type.
geometry_dump — A spatial datatype with two fields - geom (holding a geometry object) and path[] (a 1-d array holding the position of the geometry within the dumped object.)
geography — Ellipsoidal spatial data type.

That said, there is clearly no check on different subtypes of geometry.  From point or multipoint, a violation of subtype causes the transaction to fail.

Create an ETL script that loads into a simple of geometry, then you can select the types that are not of the subtype with ST_GeometryType, or GeometryType

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry
);

INSERT INTO t
VALUES
    ( 1, ST_GeometryFromText('Point(0 0)')      ),
    ( 2, ST_GeometryFromText('Point(1 2)')      ),
    ( 3, ST_GeometryFromText('MultiPoint(2 3)') ),
    ( 4, ST_GeometryFromText('Point(5 23)')     ),
    ( 5, ST_GeometryFromText('Point(42 36)')    );

Now you can run,

SELECT id, ST_AsText(p)
FROM t
WHERE GeometryType(p) <> 'POINT';

And you'll get,

 id |    st_astext
----+-----------------
  3 | MULTIPOINT(2 3)
(1 row)

Alternatively, you can avoid this problem by casting all types to MultiPoint with ST_Multi().


restrict user host settings to socket connection only Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account.

edit: As Abdul Manaf pointed out skip-networking can be used to turn off TCP/IP connectivity altogether. But can it be done on a user account basis?

If you only want to listen MySQL connections only using socket add a line in my.cnf and restart MySQL server.

skip-networking

This is recommended practice when there is no reason for it to listen on TCP.


Can I use unique index in GROUP BY clause instead of column in mysql I have a table shift_wise_traffic with following columns.

toll        date            shift      car      truck      bus
 1          2016-09-01        1        10        20        30
 1          2016-09-01        2        15        25        35
 1          2016-09-01        3        20        30        40

 2          2016-09-01        1        5         10        15
 2          2016-09-01        2        20        25        30
 2          2016-09-01        3        35        40        50

 1          2016-09-02        1        10        20        30
 1          2016-09-02        2        10        20        30
 1          2016-09-02        3        10        20        30

 2          2016-09-02        1        10        20        30
 2          2016-09-02        2        10        20        30
 2          2016-09-02        3        10        20        30

Now I want to produce the output from shift_wise_traffic table group by
toll, date. And I have created a unique-index named shift_unique_index having two columns toll and date.

so instead of writing following query,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic
GROUP BY toll, date;

Can I use this query using shift_unique_index,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic
GROUP BY shift_unique_index;

SELECT toll, date, SUM(car), SUM(truck), SUM(bus)
    FROM shift_wise_traffic
    GROUP BY toll, date;

The GROUP BY must have a list of columns, not an index name.
An INDEX is an optimization, not a requirement.
An INDEX may be useful for queries like this.  (No index is likely to be useful for this particular query.)
Probably this query will simply scan the entire table, sort by toll+date, sum up the values, then deliver the output.  (Or it may build an in-memory hash based on toll+date for summing.)
UNIQUE versus INDEX -- makes no difference in this context.  UNIQUE = INDEX + a constraint.  For INSERT, UNIQUE prevents duplicates.

It feels like your PRIMARY KEY is (date, shift, toll) in some order.  Is it?  If so, then changing the order to PRIMARY KEY(toll, date, shift) might make your GROUP BY run a little faster.  (But it might slow down other operations.)


Practices to keep SQL Server databases easy to port to MySQL? We've developing an ASP.NET Web application that is to be designed for MS SQL Server 2008. I'd like to minimize my future dB admin's headaches should there be a need to migrating over to MySQL.

With that goal, are there any specific design patterns or features we should avoid on MS SQL? Issues that trip over the MS SQL -> MySQL migration tools?

Yeah, plan on using none of the SQL Server features like Geography data types, SQL CLR, filtered indexes, top, CTEs, etc.

I'd say don't bother trying to program for multiple database platforms.  Pick a platform and that'll be the platform which the app runs on.  If the app needs to be moved to another platform later deal with it then.  Otherwise you'll spend so much time trying to figure out if the code can be ported to both platforms that you'll get no where.

If you are going to plan for a MySQL migration, why not also plan for Oracle, Sybase, NoSQL, Progress, etc?


Enabling AWE on SQL SERVER 2008 We have a Server running Windows Server 2008 32-bit (Enterprise Edition) and SQL Server 2008 (SP2) 32-bit (Enterprise Edition) installed.

The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

We have to enable the AWE Option on SQL Server but:

Do we have to enable\configure any options on Windows before enabling the option on SQL Server?
How much memory can be assigned to SQL Server on the Enterprise Windows (Max)?
Is there any other thing we should take care of before applying since its a production server?

The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB

This is because when 32 bit SQL Server is installed on 32 bit Windows Servers it can see maximum Virtual Address Space(VAS) of 2 GB so its memory consumption under default behavior cannot be more than 2 GB no matter how much RAM is present on windows server.

  Do we have to enable\configure any options on Windows before enabling the option on SQL Server?

As I can see you want SQL Server to use more memory and for that you are enabling AWE which is correct but first you have to make sure windows server can see more than 4 G of RAM. Again maximum VAS for 32 bit windows system is 2^32 which is 4 GB so any OS process cannot see more than 4 G of RAM. The good thing is you are having Window server 2008 enterprise and you can enable PAE so that windows server can see upto 64 G memory.

To explicitly enable PAE, use the following BCDEdit /set command to set the pae boot entry option: Take windows team help to make the changes.

bcdedit /set [{ID}] pae ForceEnable

IF DEP is enabled, PAE cannot be disabled. Use the following BCDEdit /set commands to disable both DEP and PAE:

bcdedit /set [{ID}] nx AlwaysOff
bcdedit /set [{ID}] pae ForceDisable

Once PAE is enabled windows server can see more than 4 G and then you can enable AWE so the SQL Server data and index pages can see memory more than 2 GB.

  How much memory can be assigned to SQL Server on the Enterprise Windows (Max)?

For 32 bit, maximum memory like I said is 2GB. But when you enable AWE on windows server which can see more than 4 G, ONLY SQL Server data and index pages can use this extra memory not other caches like proc, plan etc.

  Is there any other thing we should take care of before applying since its a production server?

Just test enabling PAE on UAT, enabling AWE is pretty much straight forward and easy.

I suggest you read

SQL Server memory and troubleshooting
What is difference between VAS and Buffer Pool
Understanding VAS reservation in SQL Server

PS:  Please upgrade to 64 bit ASAP. This will save you lot of hassle moving forward. SQL Server 2016 is now only for 64 bit system so you can see MS is moving away from 32 bit

PPS: SQL Server 2008 is patched to SP2 please patch it to SP4 ASAP.


Mysql Replication Automation Script I am looking for something that will help us to create a lot of mysql slaves connected to the specified master, let's say kind of mass-replication.

If there are any existing solution for this purpose. In simple case it can be just bash script, that locks the master DB, copies table files to the new slave, restore position and start the replication.

From you knowledge if something described above exists?

I would like to suggest something radical. I got this idea from StarTrek : Deep Space 9 (Call to Arms)

  A minefield was set up around a wormhole to prevent the Dominion
  Forces and the Jemhadar from passing through. Each explosive mine was
  armed with a replicator that allowed a mine to recreate another mine
  after it explodes. Hence, the minefield stays up indefinitely.

With that DS9 analogy I bring your an interesting idea.

You set up 2 initial read slaves with MySQL Slave with innodb disabled

[mysqld]
skip-innodb

This is optional. My preference is an all-MyISAM slave to do reads because it is faster for reads than InnoDB for small datasets. Should you choose to go with InnoDB, make sure you relax the ACID compliance with this

[mysqld]
innodb_flush_log_at_trx_commit = 0

In the event of a crash, just destroy the Slave and spin up a new one

We'll call the Slaves S0 and S1

Here is something else: have this in /etc/my.cnf in S0

[mysqld]
innodb_max_dirty_pages_pct = 0;
innodb_fast_shutdown = 0

These will help S0 shutdown fast with completely flushed data.

The following is what you must script in the replicator process

When you need to generate a new slave (we will call it S2), here is what you must do

STEP 01) On S0, run service mysql stop

STEP 02) Install the same version of MySQL that S0 has into S2

STEP 03) On S0, scp /etc/my.cnf S2:/etc/.

STEP 04) On S2, you need to change the server-id of /etc/my.cnf in S2 to a Unique Value (suggestion : use the 2nd,3rd, and 4th octet [without the dots] of the private IP of S2)

STEP 05) On S2, either remove or comment out the innodb_max_dirty_pages_pct = 0 from /etc/my.cnf

STEP 06) On S0, run rsync -av /var/lib/mysql S2:/var/lib/mysql (Note: If you have to spin up 5 Slaves, run the 5 rsyncs at this point)

STEP 07) On S2, service mysql start (MySQL Replication start immediately where S0 had left off)

STEP 08) On S0, run service mysql start

Once you create the replicator script, you can use it spin up MySQL on new Slaves.

Meanwhile, S1 is available for SELECT queries and continues replicating.

S0 is used to recreate a new Slave.

If you are doing this in conjunction with spinning up Amazon EC2 or some other Cloud DB Servers, check with your System Administrators on any Linux commands/API that allows you to spin up a DB Server. Then, you apply the replicator to the newly generated DB Server. Even better, you can incorporate the Db Server creation API in your Replicator Script.

CAVEAT

If you have to spin up dozens or hundreds of Slaves, all you need to do is have 10 Replicator Slave Servers (S0 - S9) and have 10 copies of the replicator script operator on different Replicator Slaves.


Can i control access end point When some table data changed In Mysql Semisynchronous Replication I'm trying to set up One master Multi slave in MySQL 5.7

My architecture is like this.

Master - First slave(sync)
Second slave, Third slave, Fourth slave, Fifth slave(async)

My question is, If aTable data is changed Then application must access First slave. Until all slave is synchronized.

Because another slave data is not realtime sync...

Do you know this function supports library or tools?
If you have an other idea, please help me.

"Semisync" implies

One of the slaves has received the DML and stored it safely (in the relay log).
That slave has not necessarily applied the statement.
If the Master crashes, then at least one slave has every transaction that was COMMITted on the Master.
Once you find that Slave, you could promote it to Master with confidence.


How to speed up query with exists clause? I have requirement to identify customers using multiple emails for the same billing address.  There are 414375 records.  I let the query run for 20 minutes and it still did not return.  How do I speed it up?
select so.customer_email
from sales_order so
inner join sales_order_address soab on soab.parent_id = so.entity_id and soab.address_type ='billing'
where exists
(
    select 1 FROM sales_order so2
    inner join sales_order_address soab2 on soab2.parent_id = so2.entity_id and soab2.address_type ='billing'
    where so2.customer_email <> so.customer_email
    and soab.firstname = soab2.firstname
    and soab.lastname = soab2.lastname
    and soab.street = soab2.street
    and soab.city = soab2.city
    and soab.region = soab2.region
)

See if this gives what you need, but faster:
SET @@group_concat_max_len = 8000;

SELECT GROUP_CONCAT(customer_email SEPARATOR '  ')
    FROM sales_order AS so
    WHERE EXISTS ( SELECT 1 FROM sales_order_address
                       WHERE address_type = 'billing'
                         AND parent_id = so.entity_id )
    GROUP BY first_name, last_name, street, city, region
    HAVING COUNT(*) > 1;

(The output won't be formatted the same.)
Another possibility:
SELECT GROUP_CONCAT(customer_email SEPARATOR '  ')
    FROM ( SELECT so.first_name, so.last_name, so.street, so.city, so.region,
                  so.customer_email
            FROM sales_order AS so
            JOIN sales_order_address AS soa
            WHERE soa.address_type = 'billing'
              AND soa.parent_id = so.entity_id ) AS x
    GROUP BY first_name, last_name, street, city, region
    HAVING COUNT(*) > 1;

Indexes that may help:
sales_order_address:  INDEX(address_type, parent_id)
sales_order:          INDEX(entity_id)   -- unless it is the PRIMARY KEY


Speed-up lookup of a row with prefix pattern Say I have these rows in one UNIQUE column:

202101XXXXX
202101XXXXX
202102XXXXX
202102XXXXX
202103XXXXX
202103XXXXX

The first 6 digits are year and month (YYYYMM), and the XXXXX part are random digits. They are UNIQUE. Let's say there are millions of rows like these.
I need to lookup a certain row e.g., 20210312345, is there a way I could tell MySQL to not look at the whole table and just search on rows with the 202103 prefix? And, if that was possible, would it actually speed up the query?

This is how indexing essentially works. If you create an index on that column, the data will be sorted by the value, which effectively is on the prefix first and then the suffix.
The other thing you can do is either store only the suffix part (the XXXXX) of the value in it's own column too, either from the application that loads the data or using a Stored Generated Column and then create the index on that field. Doing that allows you to do an exact match search on a more unique value.


MySQL varchar(255) limitation & Anomaly I have defined a column in my MySQL database as :

ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 )
    CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

However, when I enter text that is longer than 233 characters from my front end the data is truncated.

Changing the size of the column to varchar(511) has made no difference.

I counted the number of characters using PHP's strlen() function and it revealed 233 characters.

Why is MySQL doing this, and how can I save the full string?

MySQL is not cutting it at 233. The problem is likely in your save method which cuts it to 233 before the data even reaches MySQL.

Also, don't forget that 233 limit is not character limit, and as some character might need more han 1 byte to be stored, you might see less than 233 characters.

Also please make sure that data in MySQL server is really stored as latin1, this can be accomplished with:

show variables like 'char%';


SQL Server AlwaysOn to run updates and schema changes in 24/7 environment First of all i have to say that this question comes from a software engineer-perspective. Unfortunately we have no DBA so we need to administer our own databases.

We have SQL Server 2012 Enterprise installed to run a database which should be available 24/7. The database is about 150 GB and some tables are containing billions of rows. Multiple services are hitting the database thousands of times per minute, to insert realtime measurement data. So when the database is down for half an hour, there is loss of measurement data for half an hour, which we can not afford...

In this situation we have two main issues:

When and how do we install Microsoft Updates?
How do we apply schema changes?

Windows Updates requires no further explaination i think. When new requirements come or existing requirements change, we sometimes need to do some schema changes. For example adding some extra columns, changing data types, resize varchar fields etc. Some of these changes take a very long time to run, or even timeout, because the database is Always under heavy load.

We are thinking to install an extra instance and enable AlwaysOn, to accomplish the following:

For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.
For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.

Can these two things be accomplished with SQL Server AlwaysOn, and is this a common approach? Does the data even gets synchronized aftwerwards? Or am i completely thinking into the wrong direction and are there better solutions?

1.When and how do we install Microsoft Updates?

Using AOAGs could potentially help with this, depending on how much downtime is considered to be "too much". There is still a blip associated with switching the primary to a synchronous secondary as the clients will be disconnected (this may or may not be an issue in your environment) as the move happens. They will need to reconnect using the listener name (so no connection string changes should be required) and be using a library that supports this.

  1.For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.

That's the basics of how it works. Remove automatic failover (synchronous), install the updates/patches, reboot (in this scenario).

  2.How do we apply schema changes?

AOAGs aren't going to help you in this respect. The transaction log is basically replayed from the primary to all of the secondary's. The secondary's could be readable (if you allow them to be) but won't be able to have any write activity (including ddl changes) made to them. A synchronous secondary is a mirror copy of the primary.

Could these changes be made when the application changes are made to minimize downtime? It may make more sense to use a load balanced front end using something such as an F5 and keeping the data in sync through something such as peer to peer replication than use AOAGs. I'm not a replication expert, but it definitely sounds like you will need two distinct systems that have some type of load balanced front end so that changes can be made to a single system at a time to move resources in and out while keeping them in sync.

  2.For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.

This can't be done this way (or any way, really) with AOAGs.


Proof that Inner Join is Commutative I'm trying to prove to myself that the order of inner joins doesn't matter, but, in an abstract sense, I'm coming up with nothing.

How would one go about proving that the order of a set of INNER JOINs that are executed to turn many tables into a single table does not affect the result set (i.e., prove commutativity and associativity of the INNER JOIN operation)?

An inner join is the subset of rows from the cartesian product where a certain condition is true. Although the cartesian product is not commutative (nor associative), it is with regard to relational theory.

It is so because the order of attributes doesn't matter in relational theory. If A and B are tables, then:

A x B = { (a1, a2, .., an, b1, b2, .. bn) | (a1..an) € A and (b1..bn) € B}
      = { (b1, b2, .., bn, a1, a2, .. an) | (a1..an) € A and (b1..bn) € B}  ((1))
      = { (b1, b2, .., bn, a1, a2, .. an) | (b1..bn) € B and (a1..an) € A}  ((2))
      = B x A                                                               ((3))

((1)) because the order of attributes doesn't matter
((2)) because the logical and is commutative
((3)) by definition of B x A

The condition for choosing which elements of the cartesian must be selected will be the same no matter the JOIN order, and as such, doesn't influence this reasoning.

The proof for associativity follows the same line of reasoning.


Master database is corrupt, instance won't start - what are my options? Help! My master database is corrupt, I can't even bring the SQL instance online! What are my options to get my server back up?

I do have a backup of master, but the MSDN page "Restoring the master Database" asks me to start the instance in single-user mode, which I can't do!

(Note: I'm leaving this question unspecified as to SQL version, so as to be a more widely-applicable reference. There are some similar questions on DBA.SE, but none that involve the server unable to start.)

Here are a few avenues I would investigate. Don't do all of these (some of them are different techniques to accomplish the same purpose), but are worth considering:

1. Examine the SQL Error Log directly

Browse directly to the folder containing the SQL error logs and load the most recent ERRORLOG into notepad to get more detail on why the SQL instance will not start. Perhaps you will find the problem isn't with the master database at all.

2. Try to start the instance in single-user mode

Here is a full list of startup options for SQL server, including -m (single user mode) and -f (minimal configuration mode). Other options allow you to specify the path for the master database, if that is the problem.

If you are able to get the instance started, follow the steps in the MSDN article you linked for restoring the master database, or this detailed walkthrough by Thomas LaRock.

If another application always grabs the single user connection before you can, first disable the SQL Agent so it isn't starting. Second, see the ideas on this question for using the -m"Application Name" parameter to specify the application name.

3. Restore master to another instance and copy its files

I've only found one other mention of this undocumented technique, but I used it successfully this past weekend, so it might be worth a try.

If you can't start the instance in single-user mode, but you have another SQL instance running the exact same release and build, try restoring the last known good master database backup from your dead server to the other instance:

Restore as a different name, of course (master_please_god_let_this_work), WITH MOVE so you don't overwrite master on your good server
Restore WITH NORECOVERY. Not sure this is necessary, but made me feel better that I knew the other server wasn't going to alter anything in the restored master
Set it to offline: ALTER DATABASE [master_please_god_let_this_work] SET OFFLINE
Copy the restored MDF and LDF files from the good server to the dead server
Rename the master.mdf and mastlog.ldf files as necessary to replace the bad master files with your restored versions
Cross your fingers and start the instance
Optional: do a fresh restore of master on the revived server. Not sure this is necessary, since we were pretty careful not to change master.

4. Rebuild the system databases

If you don't have another instance running the same version, or if you're not comfortable with using the undocumented procedure listed in #3, or if you don't have backups of master (why do you not have backups??), you can rebuild the SQL system databases from the original install disk:

Setup.exe /ACTION=REBUILDDATABASE /...

When this is complete, you can follow the steps linked previously to restore master from your last good backup. You will also need to restore a recent backup of msdb to keep all your jobs, job schedule, and job history.

5. Restore all USER databases to a new (or existing) SQL instance

If you've got another existing instance already running (proper SQL version, enough disk space), I would probably start database restores from the most recent backups while I'm working on the other troubleshooting steps above, just in case I need them.

If your new (or reinstalled) instance has access to the same disk, it is much faster to simply attach them as new databases:

CREATE DATABASE foo
ON (FILENAME = 'D:\data\foo.mdf'),
   (FILENAME = 'D:\data\foo_log.ldf')
FOR ATTACH;

6. Re-do any changes to master

Once you successfully restore master (via any of the above techniques), you need to investigate any changes that might have been lost, if they were made after the backup you just restored:

Security changes
New databases (the files will still be on disk, just attach them)
Server-wide settings

There is no magic way to find these, you'll have to go back to your own company's documentation trail for these kinds of changes, if you have one.


Is it common practice to mix InnoDB and MyISAM tables on same server? I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's going to be a slow process, focusing on the most write-intensive tables at first).

Is there anything wrong with running a dedicated server where both types of storage engines exist?

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types.

For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be regenerated easily and it's a small table, say for queuing, you might choose Memory. If you have a table that's 90% reads, and the data has got to be there when you look for it, then you'd probably choose a storage engine that supports transactions and configurable atomicity, such as InnoDB. If you want accessibility through the file system w/o damaging data, you might choose CSV.

Nonetheless, you can safely use multiple storage engines within the same schema as well as the physical host.

Let me note though, that your buffers play a role in this whole mess. If you use both MyISAM and InnoDB, you will need to be careful that your key_buffer and innodb_buffer_pool do not contend. This will take careful planning on your part, but that's what we do.


Select rows from table where each row refers to a different schema I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by customer 'a', I can do select * from a.portal.

I want to select all the customers having a particular 'portal name'. But portal table is inside customer schema. So how should I query in this situation?

I have found solution using a PHP script but is there anything that we can do on the DB level?

You could use inheritance for this. You would have a "master" schema with template tables and only the basic set of columns that all inheriting tables share:

CREATE TABLE master.portal(portal_name text);

All other tables in various schemata inherit from it, while possibly adding more local columns:

CREATE TABLE a.portal(portal_id serial PRIMARY KEY, col2 text)
INHERITS (master.portal);

CREATE TABLE b.portal(portal_id serial PRIMARY KEY, col2 text, col3 text)
INHERITS (master.portal);

A SELECT query targeted on the master table would then search all child tables as well:

SELECT portal_name, tableoid::regclass::text AS source
FROM   master.portal
WHERE  portal_name ILIKE '%test%';

More details in this closely related answer on SO:

Retrieve all records from multiple schemas in a PostgreSQL database


Allow users to only view the databases they have access to I wonder if there's any way to allow a user to only view the specific databases they have access to. For example.
Let's say we have a alwaysOn with one local only database and 10 databases that is added in the AO. The user need read rights to one of the 10 databases added in AO. But when giving user rights to that database and he connects to the AO he can view every database but only access the one he got rights to of course. And if the AO is active where the local database is located he will also see that one.

We have tried a few things but nothing successful and from what we've been reading it doesn't seem to be a way unless the user is granted dbowner rights.

Anyone got any ideas?

By default a login can see all the databases on a server.

You can DENY VIEW ANY DATABASE TO [your login or serverrole];, after which a login can only see the databases where he has access.

To verify what databases a login can see, try this when you're connected as an administrative user:

--- This changes the security context to the login "dummy"
EXECUTE AS LOGIN='dummy';

--- Lists the databases visible to that user:
SELECT * FROM sys.databases;

--- Important: changes the security context back again:
REVERT;

The VIEW ANY DATABASE permission is assigned to the server-level principal (the login, i.e. not the user, which is the database-level principal), and therefore, you'll need to apply it to each availability group replica. I would recommend that you apply (deny) this permission on a server role rather than individual users to keep it manageable.

More detailed reading on the subject:

https://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/


mongoexport on 2.0.4 What I'm I missing here? I need data greater that ISODate("2015-01-11T00:39:40.121Z")

I know many forums have different answers for the same issue. But non resolved. Need a different point of view on this.

mongoexport -d central --collection alerts —q '{\"updated_at\":{\$\"gte\": new Date(1420936780121) } }' --csv --out alerts_11_12_Jan_20151.csv

ERROR: too many positional options

When you are exporting to .csv file you should use --field/-f/fieldFile  option.

http://docs.mongodb.org/manual/reference/program/mongoexport/

Try this

mongoexport -d central -c alerts -q "{updated_at:{\$gte:new Date(1420936780121)}}" -f  --csv --out alerts_11_12_Jan_20151.csv


Return result set as string I have a table with SELECT fName, lName FROM person
It returns 3 results
I want to return a concat(fName, ' ', lName) and 2 "and"s in between each concat.

Jon Snow and Margaery Tyrell and Peter Griffin

But I also want it to be dynamic to scale if the result grows. So if it is 6 it will have the names concat with 5 "and"s.

I am trying to output this to a VARCHAR variable.
Using MS SQL Server 2014.

This should get you going - basically what irimias did at this link

Declare @Table table (FirstName varchar(100), LastName varchar(100))
DECLARE @ConcatString varchar(100)

insert into @Table (FirstName,LastName) values('TomFirst','TomLast'), ('DickFirst','DickLast'), ('HarryFirst','HarryLast')

SELECT @ConcatString = isnull(@ConcatString + ' AND ', '') + FirstName + ' ' + LastName
from @Table

print @ConcatString


TSQL DateTime DataType - fractional seconds vs milliseconds Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 00:00:00.000.

So, in testing I run in SSMS:

select cast('2013-03-05 23:59:59.999' as datetime)
select cast('2013-03-05 23:59:59.996' as datetime)
select cast('2013-03-05 23:59:59.994' as datetime)

And get:

2013-03-06 00:00:00.000
2013-03-05 23:59:59.997
2013-03-05 23:59:59.993

The millisecond part comes back off by a little. In the case of the first line it really matters. Because I don't want 2013-03-06 00:00:00.000 - I want a millisecond before midnight.

TSQL states in documentation that it uses fractional seconds as opposed to .Net datetime which uses milliseconds. This would seem to be a syntax difference only but if you really care about millisecond precision in TSQL are you forced to use DateTime2 across the board.  Casting from datetime2 to datetime still mucks things up by a couple milliseconds.

In c# DateTime.Parse("03/5/2013 23:59:59.999").Millisecond still returns 999.

Am I interpreting this all right? If I care about millisecond precision am I stuck with datetime2 only.

Normally that would be an easy fix but in this case we're using a Microsoft system function that receives datetime as parameter.

Instead of specifying 23:59:59.999 you must specify 23:59:59.997 due to rounding.

The far better way would be to :

WHERE MyDate >= '2013-03-05' AND MyDate < '2013-03-06'

Since this captures ALL activity on 2013-03-05


DB2: Reorg and RunStats Is there any frequency at which i should run REORG and RUNSTATS on my master tables?
If i have a daily load of around 1,000,000 records into my table would it be suggestible to run a REORG  and  RUNSTATS everyday after the Load?
Are there any drawbacks to REORG  and RUNSTATS?

I understood what REORG and RUNSTATS does from the links here. But could not conclude  on the Impact it  might have on Overusing or Underusing it.

This is one of those where the correct answer is: it depends. It all depends on your environment, on your data. What you can and should do, is test how frequently you need to run them in lower environments. Play with them under load/stress. Figure out which options you may need to use.

But to give you something to start with, and not knowing the distribution of your data, I would go with RUNSTATS daily. Maybe REORG once a week. Now, depending on when your LOAD occurs, I would consider running it after the LOAD. LOAD bypasses a lot of things. You want to know the number of rows and the distribution of the data in the table(s) in order to have efficiently running queries. REORG is to clean up lost space and improve I/O. Perhaps less important after LOAD (depending on table fragmentation).

Both RUNSTATS and REORG acquire locks of sorts, so again, that is where you want to test in lower environments. If you can, run them after hours for zero impact. If you need to run them during the day, play with the options to see what you can do. The systems I happened to work on allowed for full RUNSTATS and full offline REORGs during the day. But we didn't have the volume of transactions others have.

Hopefully I've given you some thoughts to consider and a place to start with. The (final) answer differs for everyone.


SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0 I have an SQL query that executes perfectly on MariaDB 10.0.35 but fails with a syntax error on MariaDB 10.4.12. I attempted to contact them, but they told me to check forums and compare the changelogs from 10.0 to 10.4. I don't really have the time to scour through dozens of changelog files trying to figure out what is different, especially since their documentation appears to be compatible with what I am doing. Here's the query:

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
FROM testcust
WHERE type = '0'
ORDER BY name
INTO OUTFILE '/tmp/sellers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
);

And I even tried re-arranging it to put the INTO OUTFILE clause before the FROM (as it is in the example):

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
INTO OUTFILE '/tmp/allcustomers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
FROM testcust
ORDER BY name
);

but that didn't matter. (Both versions work on 10.0, by the way.)

And here is the error from MariaDB 10.4.12:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'INTO OUTFILE '/tmp/allcustomers.csv'

And here is MariaDB's Doc for "INTO OUTFILE":
MariaDB Doc page
where they have the following example:

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM customers;

I have disabled secure_file_priv in /etc/my.cnf.d/secure_file_priv.cnf:

[server]
# secure_file_priv = /var/lib/mysql-files
secure_file_priv = ""

I cannot find a way to attach an SQL file for anyone that wants to import it to test this with the above query, but would be glad to send it upon request (unless I am just missing how to attach a file).

I am at a complete loss on this, and cannot seem to find different versions of their documentation to see if there really is a syntax difference -- I can only assume their online docs are for the latest version.

I would really appreciate any enlightenment anyone might be able to bestow on me...

Use

SELECT *
FROM ( SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone',
              'SSN', 'Credit Card', 'County', 'Comment', 'Company'
       UNION ALL
       SELECT name, address1, address2, city, state, zip, phone,
              ssn, credit_card, county, comment, company
       FROM testcust
       WHERE type = '0'
     ) subquery
ORDER BY name = 'Name' DESC, name
INTO OUTFILE '/tmp/sellers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
);


TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers I've got an issue with SQL Server.

The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers.

This happens randomly, once or twice a week, at different times.

Is it an issue with networking drivers ? Virtualization infrastructure? What to monitor to find out? There is no other error/warning log anywhere (windows logs, syslogs, ...).

Full ringbuffer report:

<Record id="33" type="RING_BUFFER_CONNECTIVITY" time="614408705">
  <ConnectivityTraceRecord>
    <RecordType>LoginTimers</RecordType>
    <Spid>0</Spid>
    <SniConnectionId>A4478B00-18A1-4147-A65E-2EB505DCA470</SniConnectionId>
    <SniConsumerError>17830</SniConsumerError>
    <SniProvider>7</SniProvider>
    <State>11</State>
    <RemoteHost>xxxx:xxx:xxxx:xx:xxxx:xxx:xxxx:xxxx</RemoteHost>
    <RemotePort>56201</RemotePort>
    <LocalHost>xxxx:xxxx:x:xxxx::xxxx</LocalHost>
    <LocalPort>1433</LocalPort>
    <RecordTime>1/20/2012 10:12:52.32</RecordTime>
    <TdsBuffersInformation>
      <TdsInputBufferError>10054</TdsInputBufferError>
      <TdsOutputBufferError>0</TdsOutputBufferError>
      <TdsInputBufferBytes>0</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <LoginTimers>
      <TotalLoginTimeInMilliseconds>0</TotalLoginTimeInMilliseconds>
      <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>
      <NetworkWritesInMilliseconds>0</NetworkWritesInMilliseconds>
      <NetworkReadsInMilliseconds>0</NetworkReadsInMilliseconds>
      <SslProcessingInMilliseconds>0</SslProcessingInMilliseconds>
      <SspiProcessingInMilliseconds>0</SspiProcessingInMilliseconds>
      <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>
    </LoginTimers>
  </ConnectivityTraceRecord>
  <Stack>
    <frame id="0">0X00000000019EAC4B</frame>
    <frame id="1">0X00000000019E7A36</frame>
    <frame id="2">0X00000000019EC6BE</frame>
    <frame id="3">0X0000000000CA81BC</frame>
    <frame id="4">0X000000000068B888</frame>
    <frame id="5">0X000000000064EF40</frame>
    <frame id="6">0X000000000064ED12</frame>
    <frame id="7">0X000000000064EA17</frame>
    <frame id="8">0X0000000000AB598A</frame>
    <frame id="9">0X0000000000AB5C25</frame>
    <frame id="10">0X0000000000AB5A56</frame>
    <frame id="11">0X0000000000AB61E2</frame>
    <frame id="12">0X0000000074A737D7</frame>
    <frame id="13">0X0000000074A73894</frame>
    <frame id="14">0X0000000076D2652D</frame>
    <frame id="15">0X00000000772CC521</frame>
  </Stack>
</Record>

I can't give you answer but I can direct you towards it.

You need to download the public symbols for your installed
version of SQL Server
http://www.sqlskills.com/BLOGS/PAUL/post/How-to-download-a-sqlservrpdb-symbol-file.aspx
You need to able the Trace Flag which shows the public function names for
that Stack Trace using DBCC TRACEON(3656) with reference to this blog
Report the public symbol names for those frames back to us or
Microsoft CSS (Customer Support Services)


How to return multiple columns (each a composite type) in plpythonu I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components.

I came across this answer which is A) a little old and B) requires me to separate the components into a table outside of the function.

Here's a simple function that illustrates the problem:

CREATE TYPE foo AS (a INT, b INT);
CREATE OR REPLACE FUNCTION bar () RETURNS SETOF foo AS $$
    return [(1,2),(3,4)]
$$ LANGUAGE plpythonu;

SELECT bar();

This returns two records but only a single column. I want it to return a table with two records and two columns. Is this not possible? That would seem odd but an hour's worth of Googling has gotten me nowhere.

The simple way to do that would be to call your bar() function like this:

postgres@[local]:5432:postgres:=# SELECT * FROM bar();
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Time: 0.622 ms
postgres@[local]:5432:postgres:=#

Which returns as a record type in this context, with two rows and two columns.

Hope that helps. =)


How do you store tabular data in an RDBMS? First: I really haven't got any idea how to google for this. If you got one, leave a comment.

If I want to store arbitrarily large tables in a database, how should I set up my database tables?

Requirements:

A table has an arbitrary number of columns and rows (columns about 1-10k, rows about 0-10M)
There are thousands of tables
A table has a name
The columns of a table have a name
Column-Cell values can be either numbers or strings
A column always has the same datatype

Data I want to store:

"Bob the Table"

| "Name"   | "Size" | "Comment" |
---------------------------------
|  "Homer" |  170   | "A guy"   |
|  "Lisa"  |  120   | "A girl"  |
|  "Bart"  |  130   | "A boy"   |

You get the idea. Table could be much larger.

Data I want to query:

Most of the time I want to query a single table, that is, given a table's name, just display "the table" as depicted above.

How would you implement the RDBMS tables and how would you write queries to select the data for a single table?

Disclaimer: This isn't homework. This here is purely out of curiosity while working on another problem.

Arbitrary data can be put into a RDBMS even if, as the word might suggest, some relation should be applied.

Some points are obvious: every table must have a unique name, any column must have a unique name. For datatype you can use everywhere a VARCHAR. The best way is to analyse the table requirements and use the appropriate datatype for each column, this is useful for space management and query.

For query: in order to speed up the query on your data you have to apply a partitioning (and, if the database technology can support it, subpartitioning). The efficient use of partition and subpartitioning can speed up all DML operations if you can apply to every SELECT statement a WHERE clause based on the partition key. If you want also to use parallel access try to split different partitions across different disks accessed by different disk controllers.

Moreover many RDBMS allow a limited number of columns per table.

Finally, this is a very theoretical answer, too many details are missing. Although you can consider to leave the RDBMS techonology and adopt a NoSQL database. Probably, with the info supplied, a columnar nosql database is the best choice for you: HyperTable or HBase for example. This document is a must read for NoSQL Databases


Checking SQL Server's memory utilization Is there a way to check how much of the memory reserved by SQL Server is actually being used by SQL Server?

Is there a way to look at this information in the past? I.e. how much memory was being used an hour ago?

You can run DBCC MEMORYSTATUS at any time (or probably better to look at the sys.dm_os_memory* DMVs).

As for getting the data from an hour ago, that would require some kind of collection using a monitoring tool, MDW, etc. SQL Server doesn't keep snapshots of memory utilization for all of time - that would get pretty expensive.


Usefulness of a multi-attribute index If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index?

For example, take a table T with an index on attributes a and b. Is the index useful for the query:

select * from T where a='foo'

I ask because the book I'm reading has the following statement which I have trouble understanding:

  If the key for the multiattribute index is really the concatenation of the attributes in some order, then we can even use this index to find all the tuples with a given value in the first of the attributes.

For example, take a table T with an index on attributes a and b. Is the index useful for the query:
select * from T where a='foo'

There are two questions there:

Can an index on (a,b) be used at all for this query?
The answer to that is generally yes. Maybe not all databases have that capacity, but most mainstream ones do AFAIK. Replace the select with select a,b from ... and the engine could not only use the index, but not access the actual table at all to answer the query.

Will the optimizer choose to use the index?
That will depend on the database system and how much information the optimizer has about the data. If it can determine that the first column is "selective enough", then it will likely use it. If not, it likely won't.

Here's an illustration on Oracle XE 11g.
SQL> create table T
  2  as select object_name a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index T_ab on T(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from T where a = 'foo';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | T_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

The all_objects view contains information about all the objects (tables, views, procedures, etc.) present in the database. The object_name fields isn't unique, but there aren't a lot of duplicates (in this database at least), so that leading field itself is very selective.
The cost of doing a range scan on the index, then looking up the estimated one row by rowid is going to be much less than doing a full table scan, so the optimizer takes that route.
This is a fairly usual situation and plan, you'll likely come across it a lot (or something similar on other database engines).
Now here's a different scenario where the optimizer can use a detailed picture of the actual contents of the columns to optimize things differently:
SQL> create table Q
  2  as select 'a' a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index Q_ab on Q(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'Q');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from Q where a = 'a';

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 18085 |   176K|    15   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| Q    | 18085 |   176K|    15   (7)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from Q where a = 'z';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

The table is intentionally completely skewed, the first column has 'a' as its only value. If the optimizer knows that, then it can opt for different paths based on the actual queried key value, as see above.
If 'a' is requested, using the index is a bad move - you'd need to traverse the whole index and the whole table to get all the rows, which is (potentially a lot) more costly than just scanning the table.
If the value requested is not 'a', scanning the index is much more efficient since it will likely return no rows.
Here's something perhaps a bit more surprising: the index on (a,b) can actually be used when the where clause filters on b only.
SQL> select * from Q where b = 1000;

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN       | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

This works because the leading column has few distinct values, but the second column is essentially unique. Think of it as the optimizer partitioning the index by the first column, then doing a binary search on each partition. That's going to be efficient if the number of partitions is small and the other criteria are fairly selective. (i.e. doesn't work for table T in this example.)


How to hide metadata from Postgres users? I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I need to go one step further, and prevent these views-only users from examining the contents of Postgres' metadata tables, which would allow them to reverse-engineer the entire database schema. I managed to do that, too.

Here's my problem: When I connect Crystal Reports to this database, it emits a series of error messages, probably due to its inability to nose around in the database's metadata.

I'd really like to allow Crystal Reports to see only that subset of the metadata which describes the views that Crystal Reports can actually query (or at least get it, and other tools like it, to stop complaining). Is there some "right" way to do this that I'm missing?

Doing that is not supported.  You can try a little bit, and you will succeed a little bit, but everything else around you will fail.  Don't do it, it won't work.

If you need to protection your intellectual property from your clients, create a proper contract or license agreement.


Column types analysis of columns used in joins Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?

I inherited a database that hasn't been consistent in using varchar vs nvarchar columns. And sometimes numeric columns are joined with text fields that contain numbers. Now there are some changes that bring more data in some of the tables in the database as in tens of millions of records and any discrepancies in the joined types can have huge performance impacts. These tables have varchar fields and it is not possible to change them to nvarchar (they are populated by a third party product that doesn't support nvarchar).

The options that I have:

Convert all the varchar columns to nvarchar. Unfortunately, it is not possible. That would have been my preferred option.
1.b Convert all the nvarchar columns to varchar. Hmmm... I feel this is a rabbit hole I probably don't want to get into though all the text data is supposed to be in the English language.
Use the MS Transact SQL parser (DacFx) to analyse the queries, however, I feel this is going to take some significant programming effort. I would need a complete list of all sql server functions and the types they return.

Any other ideas?

Thanks

Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?

It's likely that is possible, but I'd like to recommend a different approach.

  ...and any discrepancies in the joined types can have huge performance impacts.

Rather than trying to comprehensively fix all of these mismatches, I would try and track down the ones that will give you the "biggest bang for your buck."  Use the sp_BlitzCache stored procedure from the SQL Server First Responder Kit.  You can run it like this:

EXEC sp_BlitzCache @DatabaseName = N'YourDatabaseName';

By default, this will give you the 10 most CPU-intensive queries from that database.

In the output, there is a "warnings" column, and you should be able to find queries that have these mismatched predicates and join columns by looking for "implicit conversion:"

For rows that have that warning, you can scroll over to the "Implicit Conversion Info" column for details, grab the execution plan from the "Query Plan" column, or just use the "Query Text" column to guide you to the right spot.

My main point here is that some of the mismatched data types might not be causing any real problems.  You don't want to waste time fixing those if the server is on fire because of a handful of specific columns in specific tables.


Is OLAP cube duplication of data? Is an OLAP cube a duplication of data or is it just references to original tables? Or can I control it?

It depends.

Most products duplicate the data, and require ETL to "refresh" the data, running on a regular basis but implying there is some delay between live data and OLAP data. It also implies that you can keep historical data in the cube, even if the old OLTP data is removed.

Some products will not duplicate all data, and allow to control which data is duplicated or not. That would be somehow like indexes: Some redundant data that allows for better performance, but not all the data is duplicated.

For SQL Server, for example, check the difference between MOLAP, ROLAP and HOLAP models