Saturday, May 1, 2010

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

No comments: