query problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

query problem

Author

Topic

awaisahmad435
Starting Member

11 Posts

Posted - 2011-06-01 : 06:11:33

i have created a stored procedure which runs Perfect in SQL Server 2008 but producing error in SQL Server 2005... The error is

only one expression can be specified in the select list when the subquery is not introduced with exists

the procedure is below. plz help me where i m wrong...

CREATE PROCEDURE Refresh_Lock

AS

DECLARE @AccountID VARCHAR(100)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT TABLENAME
FROM DBO.TMP
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
EXEC('update ' + @AccountID + '
set flag = ''FREE9''
where SYSTEMID in
(select b.systemid
from sys.dm_exec_connections a
right outer join dbo.TMP b on b.ipaddress = a.client_net_address
where a.session_id is null
and b.tablename = ''' + @AccountID + ''')')
EXEC('delete from dbo.tmp
where SYSTEMID in
(select b.systemid
from sys.dm_exec_connections a
right outer join dbo.TMP b on b.ipaddress = a.client_net_address
where a.session_id is null
and b.tablename = ''' + @AccountID + ''')')

FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 06:29:03

Which statement is giving you the error?
Check that the set cursor statement is valid in v2005.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 06:30:35

Duplicate
Is really a 2005 issue

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=161276

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources