How to get output from dynamic queries called by EXEC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to get output from dynamic queries called by EXEC

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:35:39

Avnish writes "I am working on this procedure to get the users connected to a server. I am trying to figureout how to the get output of count(*).

CREATE PROCEDURE PHS_USER_COUNT
@server_name VARCHAR(40)
AS
DECLARE @user_count INT,
@SQLString NVARCHAR(1000),
@SName NVARCHAR(100)

DECLARE C1 CURSOR FOR
SELECT srvname FROM sysservers WHERE srvname != 'repl_distributor'
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[phs_tbl_user_count]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[phs_tbl_user_count]
else
CREATE TABLE phs_tbl_user_count (ServerName VARCHAR(30), CurrentDate Datetime, UserCount INT)

/* SET @backup_subject = 'Backup Status For ' + @server_name */
OPEN C1

FETCH NEXT FROM C1 INTO @server_name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM C1 INTO @server_name
SET @SName = @server_name + '.master.dbo.sysprocesses'
SET @SQLString = 'SELECT count(*) FROM ' + @SName + ' WHERE status != ''background'''
CREATE TABLE usercount (UCount INT)
INSERT INTO usercount EXEC sp_executesql @SQLString,
N'@MyName VARCHAR(100)',
@SName
SELECT @user_count = UCount FROM usercount
INSERT INTO phs_tbl_user_count SELECT @server_name, getdate(), @user_count
DROP TABLE usercount
END
CLOSE C1
END
GO"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-27 : 11:47:38

quote:

SET @SName = @server_name + '.master.dbo.sysprocesses'
SET @SQLString = 'SELECT count(*) FROM ' + @SName + ' WHERE status != ''background'''

CREATE TABLE usercount (UCount INT)
INSERT INTO usercount EXEC sp_executesql @SQLString, N'@MyName VARCHAR(100)', @SName

You're passing two parameters to sp_executesql, but the @SQLString does not take any parameters. Remove the @MyName and @SName parameters and the sp_executesql call will likely run.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources