Archive for 2011

TRIGGER_NESTLEVEL TSQL Function

This function is used to determine the current nest level or number of triggers that fired the current one. This could be used to prevent triggers from firing when fired by others. Here’s an example that does that; we have two tables with triggers, that fire AFTER INSERT, and insert into the other table. The […]

The CONNECTIONPROPERTY TSQL Function

The CONNECTIONPROPERTY function can be used to obtain information about the current connection. The information available is similar to the sys.dm_exec_connections system view. ?View Code TSQLSELECT ‘Net Transport’ AS Property, CONNECTIONPROPERTY(’net_transport’) AS Value UNION ALL SELECT ‘Protocol type’, CONNECTIONPROPERTY(’protocol_type’) UNION ALL SELECT ‘Auth scheme’, CONNECTIONPROPERTY(’auth_scheme’) UNION ALL SELECT ‘Local net address’, CONNECTIONPROPERTY(’local_net_address’) UNION ALL SELECT […]

Encryption with TSQL

SQL Server has a bunch of encryption functionality at its disposal. The EncryptByPassphrase allows us to quickly encrypt data using a password. This function uses the Triple DES algorithm to protect data from prying eyes. To encrypt a section of text we supply a password and the text to the function; ?View Code TSQLSELECT ENCRYPTBYPASSPHRASE(’secret’, […]

Check for bad SQL Server login passwords

The PWDCOMPARE function is really handy for further securing your SQL Servers by checking for a range of blank or common passwords. If you google for common password list you’ll probably recognise several if you’ve been working in IT for any reasonable amount of time. Fortunately you can use this function, in conjunction with the […]

SSIS in a Failover Cluster: Failed to retrieve data for this request

I got this error when attempting to expand the msdb ssis package store on my recently built test cluster; "Failed to retrieve data for this request. Additional information: The SQL Server instance specified in SSIS service configuration is not present or available. This might occur when there is no default instance of SQL Server on […]