Posted on Leave a comment

Optimizing Stored Procedure Calls

  • 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/

Posted on Leave a comment

Compress/Decompress Using Linked Server

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)
Posted on Leave a comment

Webtoffe WordPress Migration & Duplicator

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;
}
Posted on Leave a comment

Print out DATABASEPROPERTYEX properties


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), '' )
Posted on Leave a comment

query AD with Hex ObjectGUID

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'' ')

Posted on Leave a comment

Creating a linked server for SQL Server Always On Listener

Here’s some examples for creating linked server connection to an Always On Availability Group listener

–IF USING INTEGRATED SECURITY

EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’
,@srvproduct = N’SQL’
,@provider = N’SQLNCLI11′
,@datasrc = N’MY_LISTENER’
,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;Integrated Security=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’
,@catalog = N’MY_DBNAME’
GO

EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’
GO

–IF USING SQL LOGIN SECURITY

EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’
,@srvproduct = N’SQL’
,@provider = N’SQLNCLI11′
,@datasrc = N’MY_LISTENER’
,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’
,@catalog = N’MY_DBNAME’
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MY_LINKEDSERVER’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’MY_SQLLOGIN’,@rmtpassword=’MY_SQLPASSWORD’
GO

EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’
GO

Posted on Leave a comment

WordPress Asking for FTP Credentials when Adding/Updating/Deleting Plug-Ins

If WordPress detects that it is running under a different process than the owner of the file, it will prompt for FTP connection information. If you do not have access to chown, or perform other methods of fixing this, here are two that can be implemented if all you have is FTP and write access on your wp-config.php file.

1) Locate your WordPress root diectory and find the wp-config.php file. Edit the file and insert this somewhere in the middle as it’s own block, normally put it after the
mysql username/password block.

/*** FTP login settings ***/
define("FTP_HOST", "localhost");
define("FTP_USER", "yourftpusername");
define("FTP_PASS", "yourftppassword");

2) Another option is to define FS_METHOD in your wp-config.php file. This bypasses WordPress’s recurring prompts, and allows auto-updates of your files to happen.

Open /Wp-Config.Php Paste the following code to your wp-config.php file, preferably just below every other line of code.

define('FS_METHOD','direct');
Posted on Leave a comment

Database Suspect Mode

1. Run a checkdb

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

2. Ensure there is enough free disk space for the data files and transaction log files to grow.

3. Check SQL Server and Windows event logs to see if can determine when corruption started, any errors or events that may have contributed to corruption, when last restart of instance, last online of database, I/O interruptions, power outages, etc. If “under the gun” to get db back online, this step may have to be performed post-mortem.

4. Attempt repair

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CHECKDB('db_name_here', REPAIR_REBUILD)
 ALTER DATABASE [db_name_here] SET MULTI_USER

5. If REPAIR_REBUILD does not fix it and data loss is a concern, then make copies of the *.ldf, *.mdf, and *.ndf files.

You can then attempt to restore the database from last good backups and transaction log backups up to the point of corruption, or attempt a REPAIR_ALLOW_DATA_LOSS

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'
 DBCC CHECKDB('db_name_here', REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE [db_name_here] SET MULTI_USER

6. If possible, can try restarting instance. If not possible to restart instance due to causing other DB outages, then attempt to offline database, then set back online. Sometimes suspect mode is due to a problem during the on-lining of the db and a second attempt to online db clears up suspect issue. Of course, in extreme cases, attempting this may result in the database not being able to online at all…

7. Once you do get it back online run a checkdb and a full backup

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'

NOTE: See the suspect database a lot on EXPRESS editions and databases with AUTO_CLOSE enabled. If this is the case then sometimes the database does not “open” properly and reports as suspect. You can clear the suspect by offline then online the database. When it is back online, recommend turning the AUTO_CLOSE off.