quote:

---

Originally posted by awaisahmad435


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

---

quote:

---

Originally posted by nigelrivett

You can always use an exist to replace an in clause.
You have two statements with in clasues - do they both fail?
Have you tried executing the query rather than running it in the sp? Try it with just a select statement.

Could be something to do with sys.dm_exec_connections.

==========================================
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.

---

quote:

---

Originally posted by nigelrivett

Neither in nor exists updates a column - they both filter the rows that get updated.
update mytbl
set ...
from mytblt t1
where t1.col in (select t2.col from tbl t2)

is the same as
update mytbl
set ...
from mytblt t1
where exists (select * from tbl t2 where t2.col = t1.col)

>> nothing wrong with sys.dm_exec_connections. im sure
CHeck the way it can be used. There might be nothing wrong with it but the implementation in the two versions might restrict the way it can be used. You don't know unless you check.

==========================================
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.

---

quote:

---

Originally posted by nigelrivett

* just stands for a row - it normally returns all columns but the server recognises that this is an exists clause and just uses whatever indexes are available to run the query most efficiently.

To use a join

update TBL_DEGREE_COURE_MASTER
set flag = 'FREE', flagtime = 'Application Crashed'
from TBL_DEGREE_COURE_MASTER t
join
(select b.systemid
from sys.dm_exec_connections a
right outer join dbo.tbl_locks b on b.ipaddress = a.client_net_address
where a.session_id is null
and b.table_name = 'TBL_DEGREE_COURE_MASTER') t2
on t.SYSTEMID = t2.SYSTEMID



==========================================
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.

---

quote:

---

Originally posted by nigelrivett

It's very similar

delete dbo.tbl_locks
from dbo.tbl_locks t
join
(select b.systemid
from sys.dm_exec_connections a
right outer join dbo.tbl_locks b on b.ipaddress = a.client_net_address
where a.session_id is null
and b.table_name = 'TBL_DEGREE_COURSE_MASTER') t2
on t.SYSTEMID = t2.SYSTEMID



==========================================
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.

---