July 8, 2019 at 6:46 pm
Good day everyone,
I'm new to this forum and was hoping I could get some help with what I'm trying to accomplish.
I'm trying to delete old users from each respective database from a list I have.
Now I'm using SQL Server 2012 to do this, but the databases i'm connecting to are in SQL Developer.
I have read that a correlated query would be the best way to do this.
My problem i'm having is that I get the following error:
Msg 512, Level 16, State 1, Procedure DELETE_USER, Line 60
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the current query i'm trying to use:
'DELETE OPENQUERY ([' + @LinkedServer + '], ''SELECT ID_EM FROM MA_HST_PW_EM WHERE ID_EM IN (''''' + (SELECT USER_ID FROM usrs_dlt where DB = @DBNumber) + ''''')'')'
To give a short explanation, I'm looking for ID_EM which is the users ID in their local database. The USER_ID is the same as ID_EM, just labeled different on SQL Server 2012. Then I use the DBNumber to only look for the users at that database in my list.
Sorry if my details and explanation are a bit chaotic as it's the first time I've asked for help on a forum.
Any input/advice you could give me to get this working, or improve my query in general would be greatly appreciated.
Thank you.
July 9, 2019 at 11:12 am
What you're doing is a correlated sub-query in what is effectively the SELECT list and that query is returning more than one value. As the error says, you can't do that. You have to have it return a single value if you want to use it that way. An alternative would be to break down the query and load the data into a temp table or table variable first, then write your query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2019 at 11:27 am
I'm actually a little bit confused as to what you're trying to achieve here. Are you trying to delete rows from the table MA_HST_PW_EM on the linked server, where the name of the linked server is dynamic?
If that is what you're after, seems like a candidate for dynamic SQL. Perhaps something like this?
--Your parameters, I asume
DECLARE @LinkedServer sysname, @DBNumber int;
SET @LinkedServer = N'YourLinkedServer';
SET @DBNumber = 1
--The Solution
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'DELETE FROM ' + QUOTENAME(@LinkedServer) + N' DatabaseName.SchemaName.MA_HST_PW_EM M JOIN ScehmaName.usrs_dlt U WHERE M.ID_EM = U.USER_ID WHERE U.DB = @DBNumber;';
EXEC sp_executesql @SQL, N'@DBNumber int', @DBNumber;
Obviously you'll need to replace the values for DatabaseName and SchemaName, as they were omitted from your sample query.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply