Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, December 10, 2019

Seeing Double.

Flash back to Sixteen Candles.

 

Tuesday, December 10, 2019 2:45:42 PM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Sunday, December 08, 2019

https://blog.sqlauthority.com/2015/07/21/sql-server-fix-server-principal-login-name-has-granted-one-or-more-permissions-revoke-the-permissions-before-dropping-the-server-principal/

this will then lead you too:

https://www.mssqltips.com/sqlservertip/5201/drop-login-issues-for-logins-tied-to-sql-server-availability-groups/

SELECT class_desc,*
FROM
sys.server_permissions
WHERE grantor_principal_id =
(
SELECT
principal_id
FROM
sys.server_principals
WHERE NAME = N'Pinal')

SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal'))

Based on output, it means there is an endpoint on which Pinal has been granted permission. Second result shows that I have used my account i.e. Pinal to create endpoint and granted permission to AppUser account using below command:

CREATE ENDPOINT [hadr_endpoint] STATE=STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

As the error message suggested, I revoked permission using the below command:

REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

Definitely the best fix if related to always-on is change endpoint to service account or sa

alter authorization on endpoint::Hadr_endpoint to sa

Let's get a list of Availability Groups owned by the login:

USE [master]
GO
SELECT ag.[name] AS AG_name, ag.group_id, r.replica_id, r.owner_sid, p.[name] as owner_name 
FROM sys.availability_groups ag 
   JOIN sys.availability_replicas r ON ag.group_id = r.group_id
   JOIN sys.server_principals p ON r.owner_sid = p.[sid]
WHERE p.[name] = 'DOMAIN\DBAUser1'
GO   

Now we will re-assign the AG's ownership to another login (preferably service account. 

USE [master]
GO
ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLAG1 TO [DOMAIN\DBAUser1];
GO
Sunday, December 08, 2019 10:23:23 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Friday, November 15, 2019

Msg 33171, Level 16, State 1, Procedure TRUNCATE_TBL, Line 0 [Batch Start Line 11] Only active directory users can impersonate other active directory users.

refer to
https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15

Friday, November 15, 2019 3:04:49 PM (Central Standard Time, UTC-06:00) |  | SQL Server#
Friday, October 11, 2019

Pecan Fest

 

DataView RowFilter Syntax [C#]

This example describes syntax of DataView.RowFil­ter expression. It shows how to correctly build expression string (without „SQL injection“) using methods to escape values.

Column names

If a column name contains any of these special characters ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], you must enclose the column name within square brackets [ ]. If a column name contains right bracket ] or backslash \, escape it with backslash (\] or \\).

[C#]

dataView.RowFilter = "id = 10";      // no special character in column name "id"
dataView.RowFilter = "$id = 10";     // no special character in column name "$id"
dataView.RowFilter = "[#id] = 10";   // special character "#" in column name "#id"
dataView.RowFilter = "[[id\]] = 10"; // special characters in column name "[id]"

Literals

String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.

[C#]

dataView.RowFilter = "Name = 'John'"        // string value
dataView.RowFilter = "Name = 'John ''A'''"  // string with single quotes "John 'A'"

dataView.RowFilter = String.Format("Name = '{0}'", "John 'A'".Replace("'", "''"));

Number values are not enclosed within any characters. The values should be the same as is the result of int.ToString() or float.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Year = 2008"          // integer value
dataView.RowFilter = "Price = 1199.9"       // float value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat,
                     "Price = {0}", 1199.9f);

Date values are enclosed within sharp characters # #. The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Date = #12/31/2008#"          // date value (time is 00:00:00)
dataView.RowFilter = "Date = #2008-12-31#"          // also this format is supported
dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat,
                     "Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));

Alternatively you can enclose all values within single quotes ' '. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value.

[C#]

dataView.RowFilter = "Date = '12/31/2008 16:44:58'" // if current culture is English
dataView.RowFilter = "Date = '31.12.2008 16:44:58'" // if current culture is German

dataView.RowFilter = "Price = '1199.90'"            // if current culture is English
dataView.RowFilter = "Price = '1199,90'"            // if current culture is German

Comparison operators

Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=.

Note: String comparison is culture-sensitive, it uses CultureInfo from DataTable.Locale property of related table (dataView.Table.Locale). If the property is not explicitly set, its default value is DataSet.Locale (and its default value is current system culture Thread.Curren­tThread.Curren­tCulture).

[C#]

dataView.RowFilter = "Num = 10"             // number is equal to 10
dataView.RowFilter = "Date < #1/1/2008#"    // date is less than 1/1/2008
dataView.RowFilter = "Name <> 'John'"       // string is not equal to 'John'
dataView.RowFilter = "Name >= 'Jo'"         // string comparison

Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.

[C#]

dataView.RowFilter = "Id IN (1, 2, 3)"                    // integer values
dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)"          // float values
dataView.RowFilter = "Name IN ('John', 'Jim', 'Tom')"     // string values
dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values

dataView.RowFilter = "Id NOT IN (1, 2, 3)"  // values not from the list

Operator LIKE is used to include only values that match a pattern with wildcards. Wildcard character is * or %, it can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. Wildcard in the middle of a patern 'va*lue' is not allowed.

[C#]

dataView.RowFilter = "Name LIKE 'j*'"       // values that start with 'j'
dataView.RowFilter = "Name LIKE '%jo%'"     // values that contain 'jo'

dataView.RowFilter = "Name NOT LIKE 'j*'"   // values that don't start with 'j'

If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].

[C#]

dataView.RowFilter = "Name LIKE '[*]*'"     // values that starts with '*'
dataView.RowFilter = "Name LIKE '[[]*'"     // values that starts with '['

The following method escapes a text value for usage in a LIKE clause.

[C#]

public static string EscapeLikeValue(string valueWithoutWildcards)
{
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < valueWithoutWildcards.Length; i++)
  {
    char c = valueWithoutWildcards[i];
    if (c == '*' || c == '%' || c == '[' || c == ']')
      sb.Append("[").Append(c).Append("]");
    else if (c == '\'')
      sb.Append("''");
    else
      sb.Append(c);
  }
  return sb.ToString();
}

[C#]

// select all that starts with the value string (in this case with "*")
string value = "*";
// the dataView.RowFilter will be: "Name LIKE '[*]*'"
dataView.RowFilter = String.Format("Name LIKE '{0}*'", EscapeLikeValue(value));

Boolean operators

Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.

[C#]

// operator AND has precedence over OR operator, parenthesis are needed
dataView.RowFilter = "City = 'Tokyo' AND (Age < 20 OR Age > 60)";

// following examples do the same
dataView.RowFilter = "City <> 'Tokyo' AND City <> 'Paris'";
dataView.RowFilter = "NOT City = 'Tokyo' AND NOT City = 'Paris'";
dataView.RowFilter = "NOT (City = 'Tokyo' OR City = 'Paris')";
dataView.RowFilter = "City NOT IN ('Tokyo', 'Paris')";

Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.

[C#]

dataView.RowFilter = "MotherAge - Age < 20";   // people with young mother
dataView.RowFilter = "Age % 10 = 0";           // people with decennial birthday

There is also one string operator concatenation +.

Parent-Child Relation Referencing

parent table can be referenced in an expression using parent column name with Parent. prefix. A column in a child table can be referenced using child column name with Child. prefix.

The reference to the child column must be in an aggregate function because child relationships may return multiple rows. For example expression SUM(Child.Price) returns sum of all prices in child table related to the row in parent table.

If a table has more than one child relation, the prefix must contain relation name. For example expression Child(OrdersToItemsRelation).Price references to column Price in child table using relation named OrdersToItemsRe­lation.

Aggregate Functions

There are supported following aggregate functions SUM, COUNT, MIN, MAX, AVG (average), STDEV (statistical standard deviation) and VAR (statistical variance).

This example shows aggregate function performed on a single table.

[C#]

// select people with above-average salary
dataView.RowFilter = "Salary > AVG(Salary)";

Following example shows aggregate functions performed on two tables which have parent-child relation. Suppose there are tables Orders and Items with the parent-child relation.

[C#]

// select orders which have more than 5 items
dataView.RowFilter = "COUNT(Child.IdOrder) > 5";

// select orders which total price (sum of items prices) is greater or equal $500
dataView.RowFilter = "SUM(Child.Price) >= 500";

Functions

There are also supported following functions. Detailed description can be found here DataColumn.Ex­pression.

  • CONVERT – converts particular expression to a specified .NET Framework type
  • LEN – gets the length of a string
  • ISNULL – checks an expression and either returns the checked expression or a replacement value
  • IIF – gets one of two values depending on the result of a logical expression
  • TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘
  • SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string


 

See also

Friday, October 11, 2019 8:00:54 AM (Central Standard Time, UTC-06:00) |  |  RegEx | Web_Blog#
Sunday, September 01, 2019

Challenge accepted.

Find the oldest date value in database.

This is possibly the worst script I've ever written !  It does everything YOU DON"T WANT, multiple cursors, dynamic SQL, hard coded variable loops, Global Temporay table and a table scan of every table in your database.

BUT the requirement was to search every date,datetimestamp column in the database and find the oldest value.

It does have a filter that can be set, for things like columns of Date of Birth, / DOB.

It also will respect Philip Stanhope, 4th Earl of Chesterfield, The decision to use 1st January 1753 (1753-01-01) as the minimum date value for a datetime.

https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server

Here it is, hate it, love it, it works.

If the format of this is not correct, check my script vault.

/*

DATES to ignore, comma delimited list

*/

Declare @DateTimesToIgnore Varchar(256)

Declare @SmallDateTimesToIgnore varchar(256)

-- set to null to not use this

-- some applications set these dates and we want to ignore them when searching for minimums

Set @DateTimesToIgnore = '(' + char(39) + '1900-01-01 00:00:00.000' + char(39) + ',' + char(39) + '1753-01-01 00:00:00.000' + char(39) + ',' + char(39) + '1753-01-01 12:00:00.000' + char(39) + ')'

-- 1753 is not valid for smalldatetimes, so we have to remove it from the in clause for that data type

Set @SmallDateTimesToIgnore = '(' + char(39) + '1900-01-01 00:00:00.000' + char(39) + ')'

 

print '@DateTimesToIgnore = ' + @DateTimesToIgnore

print '@SmallDateTimesToIgnore = ' + @SmallDateTimesToIgnore

IF OBJECT_ID('tempdb..#Results', 'U') IS NOT NULL

DROP TABLE #Results

/*

-- *************** WATCH GLOBAL TEMPORARY TABLE ************************

*/

IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL

DROP TABLE ##tmp_TempResultsUnknownStructure

-- CREATE TABLE #Results (SchemaName nvarchar(256),TableName nvarchar(256),XMLResults XML)

-- CREATE TABLE #Results (XMLResults XML)

CREATE TABLE #Results (SchemaName nvarchar(256),TableName nvarchar(256),ColumnName varchar(256),MinValue datetime)

SET NOCOUNT ON

DECLARE @SchemaName nvarchar(256),@TableName nvarchar(256), @ColumnName nvarchar(128), @type nvarchar(56)

Declare @SQLStatement varchar(8000)=''

Declare @SQLStatement2 varchar(8000)=''

Declare @ExecuteCounter int

Declare @SQLExecuteLoopCounter int

DECLARE @Previous_SchemaName nvarchar(256),@Previous_TableName nvarchar(256), @Previous_ColumnName nvarchar(128), @Previous_type nvarchar(56)

/*

Variables for inner cursor

*/

declare @temp_table_column_name varchar(256)

declare @column_counter int = 1

declare @schemaname_from_results varchar(256)

declare @tablename_from_results varchar(256)

DECLARE @MinValueTable TABLE (MinValue DateTime)

declare @MinValue DateTime

DECLARE cursor_tables_with_datetimes CURSOR FOR

select s.name as 'SchemaName',

tbl.name as 'Table', c.name as 'ColumnName', t.name as 'Type'

from sys.columns as c

inner join sys.tables as tbl

on tbl.object_id = c.object_id

inner join sys.types as t

on c.system_type_id = t.system_type_id

inner join sys.schemas s

on tbl.schema_id = s.schema_id

where t.name in ('DATETIME', 'DATE','DATETIME2', 'SMALLDATETIME') --, 'DATETIMEOFFSET') -- 'TIME'

--and tbl.name in ( 'archivehistory','bankruptcy','users')

and c.name not like '%DOB%'

order by s.name, tbl.name

OPEN cursor_tables_with_datetimes

FETCH NEXT FROM cursor_tables_with_datetimes

INTO @SchemaName ,@TableName , @ColumnName , @type

-- see starting @previous_{variable} used to break out and know we are changing

-- to a different set of tables with a different set of columns

-- this break means the sql statement is "complete" and ready to execute before

-- starting to build the next iteration

select @Previous_SchemaName = @SchemaName,

@Previous_TableName = @TableName,

@Previous_ColumnName = @ColumnName,

@Previous_Type = @type,

@SQLStatement = ''

WHILE @@FETCH_STATUS = 0

BEGIN

-- just a general debug statement

Print @SchemaName + '.' + @TableName

print ' ' + @columnName

-- test to "see" if we changed to a different set of table/columns,

-- if so, than execute

if @Previous_TableName <> @TableName

begin

print 'Length of @SQLStatement: ' + Convert(varchar(10),LEN(@SQLStatement))

print 'Length of @SQLStatement2: ' + Convert(varchar(10),LEN(@SQLStatement2))

If LEN(@SQLStatement2) <= 0

Set @ExecuteCounter = 1

else

Set @ExecuteCounter = 2

print '@ExecuteCounter: ' + Convert(varchar(10),@ExecuteCounter)

if @ExecuteCounter = 2

print 'Need to execute twice'

set @SQLExecuteLoopCounter = 1

While 1 <= @ExecuteCounter

Begin

Print 'in loop, @ExecuteCounter = ' + Convert(varchar(10),@ExecuteCounter)

if @SQLExecuteLoopCounter = 1

Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '

+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +

' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'

else

begin

set @SQLStatement = @SQLStatement2

Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '

+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +

' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'

end

print '-- *****************************'

print @SQLStatement

print '-- *****************************'

--INSERT INTO #Results

EXEC

(

@SQLStatement

)

-- now cursor through and populate table

-- begin inner cursor

print 'in inner cursor on cursor'

Set @column_counter = 1

DECLARE cursor_temp_table_structure CURSOR FOR

Select Name From tempdb.sys.columns

Where object_id=OBJECT_ID('tempdb.dbo.##tmp_TempResultsUnknownStructure')

order by column_id

OPEN cursor_temp_table_structure

FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name

WHILE @@FETCH_STATUS = 0

BEGIN

-- null @MinValue on everyiteration to ensure it's not accidentally carried over

Set @MinValue = Null

delete from @MinValueTable

Print ' Temp Table Column Name = ' + @temp_table_column_name

-- first two rows are always schema name and tablename, insert those into #results

If @column_counter = 1

begin

set @schemaName_from_results = (Select top 1 schemaname from ##tmp_TempResultsUnknownStructure)

end

if @column_counter = 2

begin

set @tablename_from_results = (Select top 1 tablename from ##tmp_TempResultsUnknownStructure)

end

if @column_counter > 2

begin

INSERT @MinValueTable

exec ('SELECT TOP 1 [' + @temp_table_column_name + '] FROM ##tmp_TempResultsUnknownStructure')

Set @MinValue = ( Select top 1 MinValue from @MinValueTable)

insert into #Results values (@schemaName_from_results,@tablename_from_results,@temp_table_column_name,@MinValue)

end

-- increase loop counter

set @column_counter = @column_counter + 1

FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name

END

CLOSE cursor_temp_table_structure

DEALLOCATE cursor_temp_table_structure

-- now drop the global temporary table

IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL

DROP TABLE ##tmp_TempResultsUnknownStructure

-- end inner cursor

Set @SQLExecuteLoopCounter = @SQLExecuteLoopCounter + 1

Set @ExecuteCounter = @ExecuteCounter - 1

End

-- reset dynamic statements

set @SQLStatement = ''

set @SQLStatement2 = ''

end

 

-- check length of sql statement.

-- it's possible to exceed 8000 characters

-- in which case we take multile passes

If len(@SQLStatement) > 3700

begin

print '******************** TABLE HAS LOTS OF DATE COLUMNS ************************'

print '********* MAX LENGTH of DYNAMIC SQL = 4000 ************************'

print @Previous_TableName

end

if @DateTimesToIgnore is null

begin

if len(@SQLStatement) < 3700

set @SQLStatement = @SQLStatement + ', MIN([' + @ColumnName + ']) as [' + @columnName + ']'

else

set @SQLStatement2 = @SQLStatement2 + ', MIN([' + @ColumnName + ']) as [' + @columnName + ']'

end

else

begin

if @type = 'smalldatetime'

begin

if len(@SQLStatement) < 3700

set @SQLStatement = @SQLStatement + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @SmallDateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'

else

set @SQLStatement2 = @SQLStatement2 + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @SmallDateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'

end

else

begin

if len(@SQLStatement) < 3700

set @SQLStatement = @SQLStatement + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @DateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'

else

set @SQLStatement2 = @SQLStatement2 + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @DateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'

end

end

print '@SQLStatement = ' + @SQLStatement

-- populate previous variable, not sqlstatement not reset

select @Previous_SchemaName = @SchemaName,

@Previous_TableName = @TableName,

@Previous_ColumnName = @ColumnName,

@Previous_Type = @type

FETCH NEXT FROM cursor_tables_with_datetimes

INTO @SchemaName ,@TableName , @ColumnName , @type

END

CLOSE cursor_tables_with_datetimes

DEALLOCATE cursor_tables_with_datetimes

 

-- last statement not executed when breaking out of cursor, so execute it.

PRINT '---------------'

PRINT '-- Dropped out of main cursor '

PRINT '---------------'

print 'Length of @SQLStatement: ' + Convert(varchar(10),LEN(@SQLStatement))

print 'Length of @SQLStatement2: ' + Convert(varchar(10),LEN(@SQLStatement2))

If LEN(@SQLStatement2) <= 0

Set @ExecuteCounter = 1

else

Set @ExecuteCounter = 2

print '@ExecuteCounter: ' + Convert(varchar(10),@ExecuteCounter)

if @ExecuteCounter = 2

print 'Need to execute twice'

set @SQLExecuteLoopCounter = 1

While 1 <= @ExecuteCounter

Begin

Print 'in loop, @ExecuteCounter = ' + Convert(varchar(10),@ExecuteCounter)

if @SQLExecuteLoopCounter = 1

Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '

+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +

' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'

else

begin

set @SQLStatement = @SQLStatement2

Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '

+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +

' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'

end

print '-- *****************************'

print @SQLStatement

print '-- *****************************'

--INSERT INTO #Results

EXEC

(

@SQLStatement

)

-- now cursor through and populate table

-- begin inner cursor

print 'in inner cursor on cursor'

Set @column_counter = 1

DECLARE cursor_temp_table_structure CURSOR FOR

Select Name From tempdb.sys.columns

Where object_id=OBJECT_ID('tempdb.dbo.##tmp_TempResultsUnknownStructure')

order by column_id

OPEN cursor_temp_table_structure

FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name

WHILE @@FETCH_STATUS = 0

BEGIN

-- null @MinValue on everyiteration to ensure it's not accidentally carried over

Set @MinValue = Null

delete from @MinValueTable

Print ' Temp Table Column Name = ' + @temp_table_column_name

-- first two rows are always schema name and tablename, insert those into #results

If @column_counter = 1

begin

set @schemaName_from_results = (Select top 1 schemaname from ##tmp_TempResultsUnknownStructure)

end

if @column_counter = 2

begin

set @tablename_from_results = (Select top 1 tablename from ##tmp_TempResultsUnknownStructure)

end

if @column_counter > 2

begin

INSERT @MinValueTable

exec ('SELECT TOP 1 [' + @temp_table_column_name + '] FROM ##tmp_TempResultsUnknownStructure')

Set @MinValue = ( Select top 1 MinValue from @MinValueTable)

insert into #Results values (@schemaName_from_results,@tablename_from_results,@temp_table_column_name,@MinValue)

end

-- increase loop counter

set @column_counter = @column_counter + 1

FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name

END

CLOSE cursor_temp_table_structure

DEALLOCATE cursor_temp_table_structure

-- now drop the global temporary table

IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL

DROP TABLE ##tmp_TempResultsUnknownStructure

-- end inner cursor

Set @SQLExecuteLoopCounter = @SQLExecuteLoopCounter + 1

Set @ExecuteCounter = @ExecuteCounter - 1

End

-- reset dynamic statements

set @SQLStatement = ''

set @SQLStatement2 = ''

-- oldest

select schemaname,tablename,columnname,minvalue

from #Results

where minvalue =

( select top 1 minvalue from #results where minvalue is not null order by minvalue )

select top 5 schemaname,tablename,columnname,minvalue

from #Results

where minvalue is not null

order by minvalue

SELECT * FROM #Results

 

 

Sunday, September 01, 2019 9:32:27 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Thursday, August 22, 2019
Thursday, May 16, 2019

https://parkwharf.com/blog/database-properties-error-on-sql-server-managed-instance/

SELECT DB_ID(database_name) AS [db_id(database_name)] , database_name, backup_start_date, machine_name FROM msdb..backupset WHERE type = 'D' ORDER BY 1, 2;

SSMS 18.0 GA, pulling up properties of a database for a managed instance.

Thursday, May 16, 2019 9:40:17 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Saturday, April 06, 2019

I've heard of Quince tree's and bushes and finally decided to give some ago.
We'll see how they do, they are difficult to find, Willis Orchards had some and I was luck enough to get a few.  Picked up a Red Baron Peach tree too.  Historically I don't have good luck with Willis Orchards, though the stock they send is great looking, I think it's just Georgia is such a different soil type and zone than Missouri, the tree's often experience shock, and even though they ship FedEx, the tree's often show up with dried up roots, or budded out and the new leaves dried up, which is disappointing.
But they are one of the few places that carry Quince tree's so got to try.
Stark Brothers is supposed to have some this fall and spring 2020, so depending on how these do, I will keep trying.

QTY Description From Picture
3 Smyrna Quince
The Smyrna Quince is the most popular quince used by famous chefs. Widely used for cooking in Europe, the quince fruit trees are finding new attention now in America. The Smyrna Quince is a large apple shaped fruit with lemon yellow skin and excellent flavored tender flesh. This self-fertile fruit is highly perfumed and ripens September to October. (100 Chill Hours) Grows in zones: 5 - 9
Willis Orchards
2 Orange Quince
The Orange Quince is a very adaptable growing well in a range of conditions. This tree is just about as hardy as the peach, surviving temperatures as low as 0º F. Its branches will most likely die back if exposed to temperatures below -15º F, though the root will survive. The Quince can grow up to 20 feet high with unusual, crooked branches. They do best grown in moist, heavy soils and are often found in the wild near creek banks making this fruit a delicacy for wildlife. The orange quince bears heavily with large, bright yellow pear shaped fruits that are flavorful and aromatic. The fruit ripens in October through December and can extend through February in some colder areas. The Orange Quince is a self fertile tree and requires 300 chill hours.
Grows in zones: 5 - 9
Willis Orchards
1 Red Baron Peach
The Red Baron Peach Tree is a patented variety of unsurpassed excellence. Not only is the fruit a large, beautiful red, but the tree itself is prized for its large double red blossoms that cover the tree for several weeks in early spring. These delicious 3" diameter fruits ripen over an extended period of time from mid-July to mid-August, and require a low 250 chill hours.
Willis Orchards
2 Pineapple Quince
The Pineapple Quince is popular for its firm aromatic white flesh with a delicious pineapple-like flavor. This large pear-shaped fruit has smooth, golden yellow skin that is good for fresh eating or for fine jellies. Quince Trees are self-fertile and ripen large crops of fruit in September. (100 Chill Hours)
Grows in zones: 5 - 9
Willis Orchards
2 Jumbo Quince
This large bright-yellow fuzzy cousin of pears and apples is getting a boost from the Agricultural Research Service and making a come back. In Colonial times, many American orchards boasted at least one quince and vendors sold them along with pears and apples. Jumbo Quince fruits are hard and sour when raw. When heated their flesh turns rosy, soft and sweet. An excellent variety for making tarts, pies, butters, marmalades and jellies. The fragrant aroma from the fruit of the jumbo quince has an appealing drawing to large wildlife. The fruit typically ripens in October through December. The Jumbo Quince is disease tolerant and cold hardy growing in zones 5-9. Choose Willis Orchards for top-quality quince fruit trees for sale! Grows in zones: 5 - 9
Willis Orchards

Saturday, April 06, 2019 7:42:28 AM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Saturday, March 30, 2019

Strong Heat # 4

3/30/2019

Saturday, March 30, 2019 1:55:55 PM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Wednesday, February 20, 2019

Was a very cold February for the cows !

 

 

 

Wednesday, February 20, 2019 8:26:20 PM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Thursday, January 24, 2019

Corrupt model database.

Nothing more fun than bug in vm ware causing disk corruption and leaving your model database suspect.

You can run in this state, as long as you don't create a new database and as long as your server doesn't reboot or SQL server service stop and start, as when SQL Starts back up, it will have an issue with model database.

Standard TSQL Statement, Restore database model from disk = 'some location' with recovery.

What this has to do with Record Rater, I have no idea, but I found this cool old record and it even still had the survey card... Big Red Music.

Very Cool...

 

 

 

Thursday, January 24, 2019 8:14:20 PM (Central Standard Time, UTC-06:00) |  | SQL Server | Web_Blog#
Monday, December 31, 2018

Final Tree's for planting in 2018 were delivered and put in the ground the past week.

r.

QTY Description From Picture
2 Pond Cypress
(Taxodium ascendens)
The Pond Cypress Tree is very similar to the Bald Cypress, except that it has finer foliage, thicker bark and is always found growing in non-flowing bodies of water. These trees maintain a desirable straight trunk and develop thick calipers towards the base, even on younger trees. The Pond Cypress is a relatively maintenance free tree requiring minimal pruning only to remove lower dead branches. The cypress will grow 50 to 60 feet tall with a 10 to 15 feet spread and prefers to be planted in wet, poorly drained, acidic soil.
Willis Orchards
2 Indian Blood Peach
This heavy producer of good quality, white streaked with red fleshed peach holds well for canning and preserving. The Indian Blood peach is a zone 4 peach tree of the large red skinned clingstone variety that ripens August to September. The Indian Blood Peach tree does best with a pollinator and needs 900 chill hours. Grows in zones: 4 - 8
Willis Orchards
2 Red Baron Peach
The Red Baron Peach Tree is a patented variety of unsurpassed excellence. Not only is the fruit a large, beautiful red, but the tree itself is prized for its large double red blossoms that cover the tree for several weeks in early spring. These delicious 3" diameter fruits ripen over an extended period of time from mid-July to mid-August, and require a low 250 chill hours.
Willis Orchards
1 20th Century Asian Pear
The 20th Century Asian Pear Tree is an August ripening Japanese variety that is semi-self fertile. It is a medium to large, round yellow-green colored pear of good quality and decent sweet taste. This juicy Asian Pear treat is great eaten fresh and it stores well for canning and preserving. The 20th Century Asian Pear will set a much heavier crop when planted with one of the other Asian pear varieties. Requires 400 chill hours. Will grow in zones 5-9a. Grows in zones: 5 - 9
Willis Orchards
2 Eliza's Choice Crabapple
Eliza's Choice Crabapple Tree is a naturally select cultivar found on a small family farm near McMinnville, TN. This select crabapple tree variety is an excellent pick for wildlife plantings and jelly making. It has beautiful showy white blossoms in Spring and the fruits ripen later than many other varieties. The pictures show Eliza standing by her Choice Crabapple tree loaded with fruit in mid-October. However, the fruits continue to ripen and fall well into November, which makes Eliza's Choice Crabapple a must have for hunting enthusiasts.
Willis Orchards
1 Giant Fuyu Persimmon Tree
This is a very large hybrid of the Fuyu Jiro. The Giant Fuyu Persimmon tree also ripens in November and is non-astringent. The fruit is a dark orange color when ripe and has a delicious smooth texture. (100-200 Chill Hours) Grows in zones: 6 - 10
Willis Orchards
1 Ivory Silk Lilac
Not Your Grandma's Lilac! Looking for something different? The Tree Lilac (Syringa reticulata 'Ivory Silk') is not the flowering shrub you might have seen in grandma’s yard, but a small tree that bears huge (foot- long!) flower panicles in late June, well after other lilacs have given up for the season. The monster flower bunches are creamy white and give off a heady, musky scent that will perfume your entire garden. These trees only get about 25 feet high which makes them perfect for small yards or as a focal point. For an even greater impact, plant several along a fence line – they make a show-stopping tall hedgerow! On top of all this goodness, the Tree Lilac can withstand temperatures as low as 30 degrees below zero and isn’t affected by soil ph like other lilacs. It is the hardiest and most trouble free Lilac available, especially for northern climates. The Tree Lilac blooms more heavily than other Lilac varieties, too. It flowers later in the season, extending your Lilac pleasure. Through the summer your Tree Lilac will be densely covered in beautiful, deep green leaves, a refreshing sight in the height of summer heat. In the fall and winter the attractive, smooth, dark red bark adds an interesting dimension and texture to your landscape. Whether you’re new to lilacs or just want to add something different to your garden, the Tree Lilac is for you. Order yours today! * Huge flowers. * Fragrant blooms. * Cold hardy and trouble free. Don’t Prune Lilacs During Fall or Early Spring
Nature Hills Nursery
1 Shinseki Pear Tree
An early harvest of delicious Asian Pears on a hardy tree. Nothing beats a crisp easy-to-eat pear straight from the tree during the dog-days of summer. Shinseiki Asian Pear Tree is a beautiful tree that covers itself in loads of round, yellow-skinned pears. The tree is easy to grow and the fruit is oh SO delicious!
You will love these pears if you are a fan of Granny Smith apples over Red Delicious. Crisp, juicy texture and sublimely-sweet flavor are the hallmarks of these pretty fruit.
Since this is one of the earliest Asian pears to ripen available on the market today, you will be enjoying bowls of the yummy fruits weeks before your neighbors.
The tree grows naturally to a lovely conical shape and is covered in flowers in the spring. When the fruit is ripening, it looks like Christmas ornaments hung all over the tree. Young trees will produce up to 100 fruit, while 6-7 year old trees produce 500-700 pears each year!
Nature Hills Nursery
1 Falvor Supreme Pluot
Supreme Sweetness in a Gorgeous Fruit Pluots are an all-around great fruit and one of the best fruit tastes in all the world. In a field of amazingly sweet fruits, Flavor Supreme Pluot, Prunus 'Flavor Supreme', raises to the top with award after award for its taste. It’s like picking a winning diamond from a field of diamonds. You can’t go wrong with any pluot, but you will be very happy with the Supreme. It looks different than you’d expect – smooth, mottled-green skin with firm, juicy wine-red flesh. But don’t let the look fool you. These pluots marry the best of both plums and apricots and this brings out the sweetest taste imaginable. The tree itself has a classic shape and beautiful Japanese plum-like leaves. It makes a pretty landscape tree in your yard. A pretty tree with a bonus - you’ll have plenty of fruit to eat and an abundance to share with your friends and neighbors. Flavor Supreme needs a pollinator so order two. Or you can plant it with a Japanese plum like Beauty Plum or another pluot, like Flavor Grenade or Flavor King. Order a couple of these beauties today and enjoy this supremely sweet fruit next year. You’ll thank us! * Winter hardy * Super-sweet taste * Early harvest
Nature Hills Nursery

Monday, December 31, 2018 12:33:26 PM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Sunday, November 18, 2018

Thomas connects with 5 pointer twice !

AR15, with 300 Black out.

Hornady American Gunner Ammunition 300 AAC Blackout 125 Grain Hollow

120lb white tail before being field dressed.

 

Sunday, November 18, 2018 8:23:49 AM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Thursday, November 08, 2018

Eugene Horkay

Academic Letter for 2017 school year, awarded in 2018.

Thursday, November 08, 2018 8:18:33 AM (Central Standard Time, UTC-06:00) |  | Web_Blog#
Saturday, November 03, 2018

Patching SQL Server, generally completes without errors, but sometimes, you do get complications.

 

This was in applying sql 2017 CU 11, 14.0.3814, to SQL Server Failover cluster (FCI), with always-on to another SQL Server Failover cluster (FCI), no listener between the two.  Failed on the passive node.

 

The dreaded error below:

 

Message Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 3933, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

 

Finally figured this out to be related to the check box for MS DTS on the properties for Always-On.

 

Supposedly fixed in CU7, but I think not.

 

https://support.microsoft.com/en-us/help/4092554/fix-cannot-use-save-transaction-within-a-distributed-transaction-when 

 

Unchecked the DTC Support for AG Group.

Re-ran the patch, and it completed successfully.

I Love patches !

Saturday, November 03, 2018 7:06:56 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Instance will not fail back to ...
SQL Agent will not start when a use...
Door Installation
Recent Posts
Archive
December, 2019 (2)
November, 2019 (1)
October, 2019 (1)
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll