June 1, 2011 at 4:07 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:48 am
For those who have trouble with SQL in plain text, here it is again with formatting:
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
I'm sure someone here will get rid of the c-u-r-s-o-r for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 1, 2011 at 6:56 am
the problem arises where i use IN clause. 2005 compiler says u cant use IN clause here. use Exists. but Exists clause doestnot solve my problem.....
but 2008 compiler doest'n raise any issue and run it perfectly...
June 1, 2011 at 9:20 am
can any one reconstruct my query with INNER JOIN...
I WANT TO ELIMINATE IN CLASS.... IS THAT POSSIBLE
update TBL_DEGREE_COURE_MASTER
set flag = 'FREE', flagtime = 'Application Crashed'
where SYSTEMID IN
(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')
June 1, 2011 at 9:30 am
awaisahmad435 (6/1/2011)
can any one reconstruct my query with INNER JOIN...I WANT TO ELIMINATE IN CLASS.... IS THAT POSSIBLE
update TBL_DEGREE_COURE_MASTER
set flag = 'FREE', flagtime = 'Application Crashed'
where SYSTEMID IN
(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')
Please DO NOT SHOUT. And explain what you mean by 'class' - is this some sort of educational exercise?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 1, 2011 at 5:39 pm
You can declare a #temp table and insert id's there before the update/del,
or use this syntax for upd/del:
UPDATE a SET a.column1 = 'somevalue'
FROM table1 a
JOIN table2 b on a.id = b.id ...
DELETE a
FROM table1 a
JOIN table2 b on a.id = b.id ...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply