Subquery returned more than 1 value. This is not permitted when the subquery...

[blabla2006]

I have a stored procedure that calls another stored procedure on a different server. I get the error i wrote above. The first procedure is:

set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
DECLARE @exec_statement varchar (100)
select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
exec @exec_statement

the second procedure

ALTER PROCEDERE [dbo].[DeleteUsers] AS
DELETE FROM all_accounts
WHERE userid in (SELECT userid FROM dbo.temp_accounts)

im completely sure that the problem doesn't occur because of the first 2 selects (the db_name and server_address) , so it must be the second procedure, but the second procedure *should* return more then one row (for that i use 'in' instead of '=')! how is this possible?

[ck9663]

I have a stored procedure that calls another stored procedure on a different server. I get the error i wrote above. The first procedure is:

set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
DECLARE @exec_statement varchar (100)
select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
exec @exec_statement

the second procedure

ALTER PROCEDERE [dbo].[DeleteUsers] AS
DELETE FROM all_accounts
WHERE userid in (SELECT userid FROM dbo.temp_accounts)

im completely sure that the problem doesn't occur because of the first 2 selects (the db_name and server_address) , so it must be the second procedure, but the second procedure *should* return more then one row (for that i use 'in' instead of '=')! how is this possible?

Try isolation:

Run these first and see if there's an error:

set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
select @dbname, @server_address

Then run this

DECLARE @exec_statement varchar (100)
select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
exec @exec_statement

However, instead of those variables, try and pass the hardcoded value of those variables. You can get it from the select statement you first ran.

It will tell you where the error is.

-- CK

[blabla2006]

Try isolation:

Run these first and see if there's an error:

set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
select @dbname, @server_address

Then run this

DECLARE @exec_statement varchar (100)
select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
exec @exec_statement

However, instead of those variables, try and pass the hardcoded value of those variables. You can get it from the select statement you first ran.

It will tell you where the error is.

-- CK

the problem is not in these variables. even if i use select max(db_name) i get the problem. i realy think its the second procedure.

[deepuv04]

the problem is not in these variables. even if i use select max(db_name) i get the problem. i realy think its the second procedure.

hi,
I think the following statement is returning more than one vlaue

[code=text]

select db_name from all_servers where server_no = @server_no

or

select server_address from all_servers where server_no = @server_no

[/code]

execute these two statements alone and see how many rows it is returning

thanks

[ck9663]

"Egsakli" what am trying to suggest ;)

-- CK

[blabla2006]

I said its not these two selects. I tried to execute the program wih hard-coded values ot use the 'max' aggregate but i still get this error.

[ck9663]

Then execute this with hard coded values for @server_address and @dbname

      DECLARE @exec_statement varchar (100)
      select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
      exec @exec_statement

-- CK

[deepuv04]

hi,
I think the following statement is returning more than one vlaue

[code=text]

select db_name from all_servers where server_no = @server_no

or

select server_address from all_servers where server_no = @server_no

[/code]

execute these two statements alone and see how many rows it is returning

thanks

Hi,
Did you tried the above code...?

okey change the statements in your procedure to: (just replace SET to SELECT and try )

[code=text]

select @DB_NAME = (select db_name from all_servers where server_no = @server_no)

select @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)

[/code]

and try ...