By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

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

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
  2. set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
  3. DECLARE @exec_statement varchar (100)
  4. select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
  5. exec @exec_statement
the second procedure
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDERE [dbo].[DeleteUsers] AS
  2. DELETE FROM all_accounts
  3. 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?
Mar 26 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
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:
Expand|Select|Wrap|Line Numbers
  1. set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
  2. set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
  3. DECLARE @exec_statement varchar (100)
  4. select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
  5. exec @exec_statement
the second procedure
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDERE [dbo].[DeleteUsers] AS
  2. DELETE FROM all_accounts
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
  2. set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
  3. select @dbname, @server_address
  4.  
Then run this
Expand|Select|Wrap|Line Numbers
  1. DECLARE @exec_statement varchar (100)
  2. select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
  3. 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
Mar 26 '08 #2

P: 3
Try isolation:

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

Expand|Select|Wrap|Line Numbers
  1. set @DB_NAME = (select db_name from all_servers where server_no = @server_no)
  2. set @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
  3. select @dbname, @server_address
  4.  
Then run this
Expand|Select|Wrap|Line Numbers
  1. DECLARE @exec_statement varchar (100)
  2. select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
  3. 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.
Mar 26 '08 #3

deepuv04
Expert 100+
P: 227
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. select db_name from all_servers where server_no = @server_no
  3.  
  4. or
  5.  
  6. select server_address from all_servers where server_no = @server_no
  7.  
  8.  
execute these two statements alone and see how many rows it is returning

thanks
Mar 26 '08 #4

ck9663
Expert 2.5K+
P: 2,878
"Egsakli" what am trying to suggest ;)

-- CK
Mar 26 '08 #5

P: 3
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.
Mar 26 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Then execute this with hard coded values for @server_address and @dbname
Expand|Select|Wrap|Line Numbers
  1.       DECLARE @exec_statement varchar (100)
  2.       select @exec_statement = '[' + @SERVER_ADDRESS + '].' + @DB_NAME + 'dbo.DeleteUsers'
  3.       exec @exec_statement
-- CK
Mar 26 '08 #7

deepuv04
Expert 100+
P: 227
hi,
I think the following statement is returning more than one vlaue

Expand|Select|Wrap|Line Numbers
  1.  
  2. select db_name from all_servers where server_no = @server_no
  3.  
  4. or
  5.  
  6. select server_address from all_servers where server_no = @server_no
  7.  
  8.  
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 )

Expand|Select|Wrap|Line Numbers
  1.  
  2.       select @DB_NAME = (select db_name from all_servers where server_no = @server_no)
  3.  
  4.       select @SERVER_ADDRESS = (select server_address from all_servers where server_no = @server_no)
  5.  
  6.  
and try ...
Mar 27 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.