October 2011 Entries

For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference:


Here it is in a nutshell:  If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made using this security context" , the remote login/password combo (usually a service account living in active directory) and (3) set the server options rpc,rpdc out,data access, remote collation to True. That's it!   No need to worry about provider, connection strings,etc.

Here's an example on how to retrieve data from a table using the linked server reference once it is established from an interactive query window in SMS:

select * from [MYSQLSERVERHOSTNAME].[DBInstanceName].[SchemaName].[TableName]



From the terminal type:

lsb_release -a

I've been there! You are at a client site and you know you need certain data elements but aren't certain how many databases on a given server ...or ... which tables the data elements you are interested in might appear.

Here's a single statement when executed from any SMS query connection that will get you there:

EXEC sp_msforeachdb '
DECLARE @pattern nvarchar(100)
SET @pattern = ''%elementName%''
IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern)
    SELECT ''?''
    SELECT table_catalog,table_name, column_name
    FROM [?].information_schema.columns
    WHERE column_name LIKE @pattern