Manipulatable results from a stored procedure

This is a HACK i.e a tip/trick to help fetch data.
DO NOT use it in a production environment...

Step 1

Add the local server as a linked server use the alias [.] >
(I'm setting the authentication to be the currently logged in user).

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'.', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'.', @locallogin = NULL , @useself = N'True'
GO

Step 2

Use OPENQUERY to return a results set you can manipulate...

SELECT column1, column2, column3, COUNT(*) 
FROM OPENQUERY([.],'EXEC [database].schema.sp_mysproc
  @param1 = 450000
 ,@param2=10000') 
GROUP BY column1, column2, column3
ORDER BY COUNT(*) DESC

Linked Server Connectivity Test Procedure

My latest submission has been published on SQLServerCentral.com as script of the day today :)

SSC : Linked Server Connectivity Test

March 2011 Update : Putting code here after exclusivity period elapsed -

/*
Script  : Linked Server Connectivity Checker
Version : 1.0 (March 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
CREATE SCHEMA [Utils] AUTHORIZATION [dbo]
GO

CREATE PROCEDURE [Utils].[LinkedServerTest] 
 @ServerName SYSNAME
,@emailProfile VARCHAR(255) = ''
,@emailTo VARCHAR(255) = ''
AS 
BEGIN
DECLARE @Test BIT

BEGIN TRY
EXEC @Test= sys.sp_testlinkedserver @servername 

PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))
END TRY

BEGIN CATCH
PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

IF (@emailProfile <> '') AND (@emailTo <> '')
BEGIN
DECLARE @emailSubject VARCHAR(255)
DECLARE @emailBody VARCHAR(8000)

SET @emailSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
SET @emailBody = @emailSubject

EXEC msdb.dbo.sp_send_dbmail 
 @profile_name= @emailProfile
, @recipients=@emailTo
, @body=@emailBody
, @subject=@emailSubject
, @importance='High'
END

RAISERROR ('Linked Server Failure', 16, 1, @emailSubject) WITH LOG

END CATCH

END

GO

Configuring MSDTC (Distributed Transaction Co-ordinator)

MSDTC is required if transactions span multiple servers (i.e. linked servers, ugh!)

Firstly, the SERVICE needs to be running >



Secondly, you need to allow communication (Allow Inbound and Allow Outbound tickboxes below) >

SP : Testing Linked Server Availability

SQL Server comes with a system stored procedure sys.sp_testlinkedserver to test linked server availability.
Here I simply put that inside by own procedure to generate an email too.

CREATE PROCEDURE utils.[LinkedServerTest] @ServerName SYSNAME
AS 
 BEGIN
 DECLARE @Test BIT

 BEGIN TRY
 EXEC @Test= sys.sp_testlinkedserver @servername 

 PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))
 END TRY

 BEGIN CATCH
 PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

 DECLARE @chvFrom VARCHAR(255)
 DECLARE @chvTo VARCHAR(255)
 DECLARE @chvSubject VARCHAR(255)
 DECLARE @chvBody VARCHAR(8000)

 SET @chvFrom = 'sql.admin@domain.co.uk'
 SET @chvTo = 'sql.admin@domain.co.uk'
 SET @chvSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
 SET @chvBody  =  @chvSubject

 EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Mail Profile'
  , @recipients=@chvTo
  , @body=@chvBody
  , @subject=@chvSubject
  , @importance='High'
  
 RAISERROR ('Linked Server Failure', 16, 1, @chvSubject) WITH LOG
 
 END CATCH

 END
GO

Usage :

exec  utils.LinkedServerTest @ServerName = 'my linked server'

Troubleshooting : SQL Compilation Error

A problem encountered by a developer here.

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

1) locate the code calling this script (i already know it is a view) -

select table_name 
  from information_schema.views 
 where view_definition like '%Tbl_Test%' 

view name returned - vw_tblTest

2) attempt to return data from the view -

select * from vw_tblTest

error returned -

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

3) check what the view is doing -

create view [dbo].[vw_tblTest] as
select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

so the view is calling a SYNONYM !

4) check what the synonym is doing -

create synonym [dbo].[SYN_tblTest] for [LINKED_SRVNAME].[DB].[dbo].[Tbl_Test]

5) attempt to run the query from the view -

select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

Success! data is returned!

So the query itself works, but the view does not. The plan being used for the view is no longer valid.

Basicly, Tbl_Test on the remote server had changed.
Even though all the columns being requested in the view were still present (the table had only had 2 columns added), the change to the table occured since the view was compiled.

The solution? Recompile the view.

Either recreate the view (alter view [dbo].[vw_tblTest] .... ) (This is what I did, and it worked)

or

DBCC FREESESSIONCACHE (This clears the cache used by distributed queries i.e. the linked server)

reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2137045&SiteID=1

TSQL : Adding a linked server

Adding a linked server & it's associated login.

EXEC sp_addlinkedserver 'servername'
EXEC sp_addlinkedsrvlogin  'servername', 'false', null, 'username', 'password'

Performance tip :

The local and linked servers should share the same character set and sort order (collation). If this is the case, performance can be boosted by using this command -

EXEC sp_serveroption 'servername', 'collation compatible', 'true'

Sql then assumes local and remote character sets are compatible for all queries.

Linked Server : RPC error

Running a Stored Procedure on a Linked Server.

-- local server

exec sp_executesql N'select @@version'

-- remote server

exec tranasctional2005...sp_executesql N'select @@version'

" (1 row(s) affected) Msg 7411, Level 16, State 1, Line 4 Server 'tranasctional2005' is not configured for RPC. "

Right click, Linked server Name,

Select Properties.

Select Server Options page from the menu on the left.

Set RPC Out to 'True' on the pane on the right.

SQL 2000 > 2005 Linked Server Error

" A severe error occurred on the current command. The results, if any, should be discarded. "

This occurs passing a NVARCHAR to sp_executesql on sql 2000 when linking to a 2005 instance.

Sql 2000 could not cope with the output returned by sql 2005 hence although the steps ran perfectly on their own.

declare @chvDataBaseName varchar(100)
declare @sql nvarchar(2000)
set @chvDataBaseName = 'northwind'

-- backup on local server

set @sql = N' BACKUP DATABASE ' + @chvDataBaseName
set @sql = @sql + ' TO DISK = N''d:\tempmigrationfolder\'
set @sql = @sql + @chvDataBaseName + '.BAK'' WITH NOFORMAT, INIT,'
set @sql = @sql + ' NAME = N''Full Database Backup'','
set @sql = @sql + ' SKIP, NOREWIND, NOUNLOAD, STATS = 10'
print @sql
execute linked2005server.master.sp_executesql @sql

It is confirmed as a bug. I simply reversed my plan and ran the script from sql2005 performing the remote call on the sql 2000 databases.

http://support.microsoft.com/kb/896373