Test to See if Linked Server is Available

The following code snippet shows how you can use the sp_testlinkedserver procedure to determine whether a linked server is available:

BEGIN TRY 
    EXEC sp_testlinkedserver N'linked_server_name'; 
END TRY 
BEGIN CATCH 
    PRINT 'Linked Server not available'; 
    ROLLBACK; 
    RETURN; 
END CATCH 
 
-- continue with usage of linked server

See the following links for further discussion of this issue:

How do I prevent linked server errors?
Test linked server connection settings…

Simple Query to Derive Machine Name from SQL Instance Name

If you have a string containing the name of a SQL server, here’s a simple script to derive the Windows computer name from the instance name using SUBSTRING:

DECLARE @SQLInstance NVARCHAR(128)
DECLARE @SQLMachineName NVARCHAR(128)

SET @SQLInstance = @@SERVERNAME

-- Derive @SQLMachineName from @SQLInstance
IF CHARINDEX('', @SQLInstance) = 0
BEGIN
	SELECT @SQLMachineName = @SQLInstance
END
ELSE
BEGIN
	SELECT @SQLMachineName = SUBSTRING(@SQLInstance, 1, CHARINDEX('', @SQLInstance)-1)
END

References:

SUBSTRING (Transact-SQL)

Parameterizing a SQL ‘IN’ Clause

Marcos Crispino posed the following question over at stackoverlow:

I have a Transact-SQL query that uses the IN operator. Something like this:

select * from myTable where myColumn in (1,2,3,4)

Is there a way to define a variable to hold the entire list “(1,2,3,4)”? How should I define it?

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList

To which LukeHK posted the following brilliantly simple solution using a TABLE variable:

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

References:

Define variable to use with IN operator (T-SQL)

Enabling xp_cmdshell in SQL 2008

On SQL 2008, the only option to enable the xp_cmdshell feature in SQL 2008 is to use sp_configure:

use master
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

Use the following snippet to enable the xp_cmdshell within a SQL script. (Note that GO is a batch separator in the query window, not a T-SQL command, and should be removed before using this code snippet in your SQL script.)

IF (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  master.sys.configurations
WHERE  name = 'xp_cmdshell') = 0
BEGIN
	EXEC master.dbo.sp_configure 'show advanced options',1
	RECONFIGURE
	-- To enable the feature.
	EXEC master.dbo.sp_configure 'xp_cmdshell', 1
	RECONFIGURE
END

And, if you need to enable the xp_cmdshell on a remote SQL server instance, try the following. Note that you’ll need to remotely invoke the RECONFIGURE command using the sp_configure stored procure. The sp_configure procedure expects the parameter @statement of type ntext, nchar, or nvarchar. In this case, I skipped the need to declare an @statement variable and just used the N prefix.

IF (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  [SQLNVT08].master.sys.configurations
WHERE  name = 'xp_cmdshell') = 0
BEGIN
	-- To allow advanced options to be changed.
	EXEC [SQLNVT08].master.dbo.sp_configure 'show advanced options',1
	EXEC [SQLNVT08].master.dbo.sp_executesql N'RECONFIGURE'
	-- To enable the feature.
	EXEC [SQLNVT08].master.dbo.sp_configure 'xp_cmdshell', 1
	EXEC [SQLNVT08].master.dbo.sp_executesql N'RECONFIGURE'
END

See the following article for a discussion on the use the ‘N‘ prefix with the sp_executesql procedure.

Why do some SQL strings have an ‘N’ prefix?

SQL Query to Add a Linked Server

The following SQL query will add the linked server SQLSERVERINSTANCE with the server option RPC Out set to True and specifies that connections to the remote server from logins that are not explicitly mapped will be made using the login’s current security context:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNVT08NP2010', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLNVT08NP2010', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNVT08NP2010', @locallogin = NULL , @useself = N'True'
GO

The sp_addlinkedsrvlogin parameters could also be simplified to just the following:

EXEC master.dbo.sp_addlinkedsrvlogin 'SQLNVT08NP2010', 'True'

SQL Query to Delete a Linked Server

The following stored procedure will remove the linked server instance SQLSERVERINSTANCE along with all associated remote logins:

USE [master]
GO
EXEC master.dbo.sp_dropserver @server=N'SQLNVT08NP2010', @droplogins='droplogins'
GO

Query to Determine if SQL Server Supports Backup Compression

Backup compression is a cool new feature included in SQL Server 2008 Enterprise (or later versions). The following query can allow a script to determine whether a given SQL instance supports backup compression:

SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = 'backup compression default'),-1)

The above query will return 0 or 1 if the SQL server supports backup compression and -1 if it does not. To determine if a linked server supports backup compression, use the following:

DECLARE @statement NVARCHAR(1000)
SELECT @statement = N'SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ''backup compression default''),-1)'
EXEC [LinkedServer].master.dbo.sp_executesql @statement
EXEC [LinkedServerNamedInstance].master.dbo.sp_executesql @statement

This can be enhanced even further to allow for some simple decision making:

DECLARE @statement NVARCHAR(1000)
DECLARE @ReturnStatus INT

SELECT @statement = N'SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ''backup compression default''),-1)'

EXEC @ReturnStatus = [SQLNVT06].master.dbo.sp_executesql @statement
IF @ReturnStatus = -1
BEGIN
PRINT 'Not supported'
END
IF @ReturnStatus = 0
BEGIN
PRINT 'Supported but disabled by default'
END
IF @ReturnStatus = 1
BEGIN
PRINT 'Supported and enabled by default'
END

SQL Query to Determine if Linked Server Entry Exists

Here’s a simple query to determine whether a server has been added to the Linked Servers list:

IF EXISTS (select 1 from [master].[dbo].[sysservers] WHERE srvname = 'LinkedServerName')
BEGIN
PRINT 'Yes'
END
ELSE
BEGIN
PRINT 'No'
END

The above script can be used as the basis for automatically adding a SQL server to the Linked Server list on a given instance.