Category: Uncategorized

Had a situation where needed to get an idea of when a database was last used. Created a quick query to generate select statements for all the date columns in all the user tables in the database.

    SELECT C.[name] "table_name"
         , A.[name] "column_name"
         , B.[name] "column_type"
         , 'union select ''' + C.name + '.' + A.name + ''' "column_name", max([' + A.name + ']) "maxdate" from [' + C.name + ']' "cmd"
      FROM sys.columns A
INNER JOIN sys.types B
        ON A.user_type_id = B.user_type_id
INNER JOIN sys.tables C
        ON A.object_id = C.object_id
     WHERE B.name like '%date%'
  ORDER BY C.name
         , A.name

In the process of setting up a new SQL Server 2019 instance and Availability Group, I ran into this error when executing the CREATE AVAILABILITY GROUP command

Error 35220: Could not process the operation. Always On Availability Groups replica manager is waiting for the host computer to start a Windows Server Failover Clustering (WSFC) cluster and join it. Either the local computer is not a cluster node, or the local cluster node is not online. If the computer is a cluster node, wait for it to join the cluster. If the computer is not a cluster node, add the computer to a WSFC cluster. Then, retry the operation.

As it turns out, I had checked the box to “Enable Always On Availability Groups” (AOAG) for the SQL instance after having installed the Windows Server Failover Cluster (WSFC), but before setting up the cluster. I had to disable AOAG, restart the SQL instance, enable AOAG and restart the SQL instance again. Then everything worked splendidly.

• Execute Stored Procedure with Fully Qualified Name (include schema, for example “EXEC dbo.mystoredproc”)
• Execute Stored Procedure with same capitalization (upper/lower case) as it was created
• Do not name stored procedures with “sp_” prefix unless you are specifically intending to create a global stored procedure (preferably in master database)
• Make sure that the Stored Procedure execution query is sent to SQL as an RPC instead of a Language Event. For example, for the .Net command object be sure to explicitly set CommandType to StoredProcedure

Additional information:

https://support.microsoft.com/en-us/help/263889/how-to-troubleshoot-blocking-caused-by-compile-locks

http://c-bit.org/kb/263889/EN-US/

Ran into a situation with a column containing zipped varbinary data in a SQL 2012 instance needed to be unzipped. We wanted a method to decompress the data within TSQL without using CLR or external program. Since we had a SQL 2016 instance available we looked for a way to use the SQL 2016 decompress() function over a linked server. Inelegant as it may be, here is a brief example of syntax involved to get that to work.

Example of using compress over a linked server to compress a varchar string.

SELECT *
FROM openquery([APPS-INST03],'select compress(''test string'')');

Example of retrieving a remote column and calling compress

SELECT *
INTO mytable
FROM openquery([MYLINKEDINST],'select compress(name) "name_c" from [remotedb].[dbo].[remotetable]');

Example of passing a binary string to remote decompress function.

select * from openquery([apps-inst03],'select cast(decompress(0x1F8B08000000000004002B492D2E51282E29CACC4B0700451547130B000000) as varchar(max))');

Example of passing a compressed column value to the remote decompress via linked server using openquery()

DECLARE @tsql nvarchar(max)
, @p1 nvarchar(100) = 'SELECT * FROM openquery([MYLINKEDINST],''SELECT cast(decompress(0x'
, @p2 nvarchar(100) = ') as varchar(max))'');'

SELECT @tsql = @p1 + CONVERT(VARCHAR(MAX),name_c,2) + @p2
FROM mytable

PRINT @tsql

EXEC(@tsql)

Webtoffe WordPress Migration & Duplicator is a great plug-in to backup, restore, and migrate WordPress sites. It can even handle large web sites by compressing them with zip. Sometimes the backup/export function times out with large files or if the total zipped file is over 500MB. If this is the case, one thing to try is adding the following filter to ../wp-content/plugins/wp-migration-duplicator/wp-migration-duplicator.php

// to exclude file/folder

add_filter('wt_mgdp_exclude_files', 'wt_mgdp_exclude_files_fn');

function wt_mgdp_exclude_files_fn($arr)

{

$arr[]='webtoffee_migrations'; // add folder/file path relative to wp-content folder

$arr[]='ai1wm-backups';

return $arr;

}

// to exclude file types

add_filter('wt_mgdp_exclude_extensions', 'wt_mgdp_exclude_extensions_fn');

function wt_mgdp_exclude_extensions_fn($arr)

{

$arr[]='zip';

$arr[]='mp4';

return $arr;

}

DECLARE @startdate smalldatetime
      , @monday date
      , @friday date

SET @startdate = current_timestamp
SET @monday = DATEADD(DAY, (DATEDIFF(DAY, 0, @startdate) / 7) * 7 + 7, 0)
SET @friday = DATEADD(DAY, (DATEDIFF(DAY, 4, @monday) / 7) * 7 + 7, 4)

SELECT @monday "monday", @friday "friday"

PRINT 'Collation = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Collation') as varchar), '' )
PRINT 'Edition = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Edition') as varchar), '' )
PRINT 'ServiceObjective = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'ServiceObjective') as varchar), '' )
PRINT 'ComparisonStyle = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'ComparisonStyle') as varchar), '' )
PRINT 'LastGoodCheckDbTime = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'LastGoodCheckDbTime') as varchar), '' )
PRINT 'LCID = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'LCID') as varchar), '' )
PRINT 'MaxSizeInBytes = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'MaxSizeInBytes') as varchar), '' )
PRINT 'Recovery = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Recovery') as varchar), '' )
PRINT 'ServiceObjective = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'ServiceObjective') as varchar), '' )
PRINT 'ServiceObjectiveId = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'ServiceObjectiveId') as varchar), '' )
PRINT 'SQLSortOrder = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'SQLSortOrder') as varchar), '' )
PRINT 'Status = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Status') as varchar), '' )
PRINT 'Updateability = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Updateability') as varchar), '' )
PRINT 'UserAccess = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'UserAccess') as varchar), '' )
PRINT 'Version = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'Version') as varchar), '' )
PRINT 'IsAnsiNullDefault = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAnsiNullDefault') as varchar), '' )
PRINT 'IsAnsiNullsEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAnsiNullsEnabled') as varchar), '' )
PRINT 'IsAnsiPaddingEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAnsiPaddingEnabled') as varchar), '' )
PRINT 'IsAnsiWarningsEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAnsiWarningsEnabled') as varchar), '' )
PRINT 'IsArithmeticAbortEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsArithmeticAbortEnabled') as varchar), '' )
PRINT 'IsAutoClose = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAutoClose') as varchar), '' )
PRINT 'IsAutoCreateStatistics = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAutoCreateStatistics') as varchar), '' )
PRINT 'IsAutoCreateStatisticsIncremental = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAutoCreateStatisticsIncremental') as varchar), '' )
PRINT 'IsAutoShrink = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAutoShrink') as varchar), '' )
PRINT 'IsAutoUpdateStatistics = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsAutoUpdateStatistics') as varchar), '' )
PRINT 'IsClone = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsClone') as varchar), '' )
PRINT 'IsCloseCursorsOnCommitEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsCloseCursorsOnCommitEnabled') as varchar), '' )
PRINT 'IsFulltextEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsFulltextEnabled') as varchar), '' )
PRINT 'IsInStandBy = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsInStandBy') as varchar), '' )
PRINT 'IsLocalCursorsDefault = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsLocalCursorsDefault') as varchar), '' )
PRINT 'IsMemoryOptimizedElevateToSnapshotEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsMemoryOptimizedElevateToSnapshotEnabled') as varchar), '' )
PRINT 'IsMergePublished = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsMergePublished') as varchar), '' )
PRINT 'IsNullConcat = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsNullConcat') as varchar), '' )
PRINT 'IsNumericRoundAbortEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsNumericRoundAbortEnabled') as varchar), '' )
PRINT 'IsParameterizationForced = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsParameterizationForced') as varchar), '' )
PRINT 'IsQuotedIdentifiersEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsQuotedIdentifiersEnabled') as varchar), '' )
PRINT 'IsPublished = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsPublished') as varchar), '' )
PRINT 'IsRecursiveTriggersEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsRecursiveTriggersEnabled') as varchar), '' )
PRINT 'IsSubscribed = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsSubscribed') as varchar), '' )
PRINT 'IsSyncWithBackup = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsSyncWithBackup') as varchar), '' )
PRINT 'IsTornPageDetectionEnabled = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsTornPageDetectionEnabled') as varchar), '' )
PRINT 'IsVerifiedClone = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsVerifiedClone') as varchar), '' )
PRINT 'IsXTPSupported = ' + isnull(cast(DATABASEPROPERTYEX('mydb', 'IsXTPSupported') as varchar), '' )

In Powershell:

get-aduser -LDAPFilter "(&(objectClass=user)(objectGUID=\02\8B\28\AF\48\4F\32\4A\88\B6\0D\B1\30\57\82\F3))"

In Transact-SQL

SELECT *
FROM OPENQUERY(ADSI,'SELECT cn, ADsPath FROM ''LDAP://DC=myDC,DC=myDC2'' WHERE objectGUID=''\02\8B\28\AF\48\4F\32\4A\88\B6\0D\B1\30\57\82\F3'' ')