Skip to main content

Google Search

Custom Search

Live Search

Yahoo Search


Search the web Search this site

Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.


1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).

Comments

Popular posts from this blog

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If you are getting the above error while trying to connect to a database, the reason is you are using Windows Authentication to login to the SQL Server while being in another untrusted domain. For example if the SQL Server machine is a member of the CompanyDomain and if you are in MyDomain or if you are in a Workgroup the you will face the above issue while trying to connect to the SQL Server.The connection string used in the web.config while the above error is generated is as follows.



<connectionStrings>  <addname="cnnStr"connectionString="Data Source=BI-SVR;Initial Catalog=BBIDatabase;Integrated Security=True"/></connectionStrings>



There are three ways to fix this problem.1. Use the SQL Authentication to login to the SQL Server.You can get this done by changing the connection string to use SQL authentication while connecting. But you need to know the credentials of an account which is having permissions to your required database or the System Admi…

Enabling Hibernate in Windows

Some server versions of Windows are having the hibernate feature disabled by default. So if you need to change the hibernation do the following simple steps.1. Click start and type “cmd” or type command in the search box.2. Then in the search results, right click the Command Prompt and click on Run as Administrator.3. If you are prompted by the user account control click continue.4. Then,To turn hibernation on in your machine type - “powercfg.exe /h on” or “powercfg.exe /hibernate on”To turn hibernation off in your machine type - “powercfg.exe /h off” or “powercfg.exe /hibernate off”5. Lastly you can exit the command prompt by typing “exit”.Sometimes you might get an error similar to the following while trying to enable hibernation.Hibernation failed with the following error: The request is not supported. The following items are preventing hibernation on this system.
        An internal system component has disabled hibernation. This means your machine is running roles or …

Enable Hibernation and Sleep in Windows Server 2008 R2

Normally when you install Hyper-V role in your server, it will disable sleep and hibernate facilities provided by Windows.But you can avoid this by doing some alterations to Windows.Hibernate and seep features are disabled when the Hyper-V service is started, so you can regain hibernate and sleep by changing the start parameter of the Hyper-V service (hvboot).To change,1. Open the registry editor by typing “regedit” in the run window.Locate the key,HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\hvboot and then set the start value of it to 3.Start property can hold the following values.0Boot (Not supported by hvboot.)1System2Auto3On Demand4Disabled Now you will have the hibernate and sleep facilities until you start the Hyper-V services in your machine. 2. Type the following command in a command prompt window with administrative rights. sc config hvboot start= demand Note that there needs to be a space between the = sign and the word demand.