Responsive Ads Here
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Friday, May 3, 2013

How to call or execute a Stored Procedure from inside a Select Statement

SQL Server ,
How to call or execute a Stored Procedure from inside a Select Statement
Some times you have a requirement as you need to call a stored procedure in side another stored procedure and you need to use the returned values.That time you need to create a temp table and executing that stored procedure insert that results in to temp table like below. 

INSERT INTO @myTable   
 EXEC sp_myStoredProcedure

But some times you will face some issues like the proc. is nested the following error occurs : An INSERT EXEC statement cannot be nested. To avoid this type of errors we can use openrowset. It is really simple to call a stored procedure from a select statement Using OPENROWSET. Below TSQL Query calls the procedure sp_who from the select statement. 

select * from openrowset('SQLNCLI','DRIVER={SQL Server};Server=****;uid=*****;pwd=****','Exec DBName..SPName params'

For more information you can follow this URL Check this link

If the openrowset is disabled in the system, execute the below commands 
  
 exec sp_configure 'show advanced options', 1  
 RECONFIGURE;  
 exec sp_configure 'Ad Hoc Distributed Queries', 1  
 RECONFIGURE;
Read more »
By at No comments:
Labels:

Monday, May 28, 2012

SQL Server ,

Query to find number of users connected in an MS SQL database


Sometimes you may get an error from your application saying "Connection pool size exceeded".
Here I am going to explain you few queries which can be used to find out the number of open connections in your MS SQL database and also the query to find the maximum connections allowed in SQL server.
First we will check, what is the maximum connections SQL server will allow you to connect.
SELECT @@MAX_CONNECTIONS AS 'Maximum Connections Allowed in the database'
Now you know that how many connections you can have in your database. So we will check how many are already used.
Below query can be used to find the number of connections in each database in your server if you execute this query against master database.
SELECT DB_NAME(dbid) as [Database Name], COUNT(dbid) as [Number of Connections] from master.dbo.sysprocesses with (nolock)
WHERE dbid > 0 GROUP BY dbid
Now we will see who all are the users connected to your database.
Below query will show you the login name and the host name of the user connected to your database. In below query you may change the database name when you execute in your system.
SELECT LOGINAME=RTRIM(LOGINAME),HOSTNAME FROM MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'DataBaseName' AND DBID != 0
Now at the end we will see a very simple query which can be used to show all the sessions that are currently established in the database.
sp_who2

Read more »
By at No comments:
Labels: