June 1, 2011 at 4:06 am
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
June 1, 2011 at 4:32 am
Please don't cross post. It just wastes people's time and fragments replies
No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1117940-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply