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:

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.

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

Example of retrieving a remote column and calling compress

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

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
return $arr;
// to exclude file types
add_filter('wt_mgdp_exclude_extensions', 'wt_mgdp_exclude_extensions_fn');
function wt_mgdp_exclude_extensions_fn($arr)
return $arr;