Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Hello,

I search to send and email with @list with two columns


DECLARE @PartnerServer sysname
DECLARE @SQL NVARCHAR (max)
DECLARE @subject varchar(100);
DECLARE @list nvarchar(2000) = '''';
DECLARE @mySQL nvarchar(2000);
DECLARE @to varchar(200);
DECLARE @SQLRESULT AS TABLE(NAME sysname, CREATE_DATE datetime);

SET @PartnerServer = (select replica_server_name from sys.availability_replicas where replica_metadata_id IS NULL)
if not exists(select * from sys.servers where name = N'+@PartnerServer+')
EXEC sp_addlinkedserver @PartnerServer, 'SQL Server'

--- Not in Secondary
PRINT 'LOGIN NOT IN SECONDARY'
SET @SQL = ' SELECT name, create_date'  + CHAR(10) + 'From '   + QUOTENAME(@PartnerServer)
         + '.[master].[sys].[server_principals]  WHERE name NOT IN (SELECT name FROM [master].[sys].[server_principals])'
        


INSERT INTO @SQLRESULT
EXEC sp_executesql @SQL;

SELECT *
FROM @SQLRESULT

SET @to = 'dbas95@yahoo.Fr' ;

SET @subject = 'difference logins'

SELECT @list = (select
NAME
from @SQLRESULT )

 IF LEN(@list) > = 1
            BEGIN
                        DECLARE @msg nvarchar(2000) = N'logins not exist ' +@PartnerServer + '  ' +  ' but not in primary'
                              + '  '  +@@SERVERNAME +  ':' +
                              CHAR(13) + CHAR(13) +
                              @LIST

But Only one expression can be specified in the select list when the subquery is not introduced with EXISTS is returned

How can I do?

Thank you

regards
bibi92Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
So, where do you get the error ? And the full error message is ?

Looks like this will error :
if not exists(select * from sys.servers where name = N'+@PartnerServer+')

Open in new window

and should be
if not exists(select NULL from sys.servers where name = @PartnerServer)   -- return a single value if checking "EXISTS"

Open in new window

bibi92Author Commented:
Hello,

I want to return named, create_date on @list

Thank you
NerdsOfTechTechnology ScientistCommented:
Also, shouldn't the semi-colon come after the SET subject line?

SELECT * 
FROM @SQLRESULT
SET @to = 'dbas95@yahoo.Fr' ; /* ; ends section prematurely */
SET @subject = 'difference logins'

Open in new window


SELECT * 
FROM @SQLRESULT
SET @to = 'dbas95@yahoo.Fr' 
SET @subject = 'difference logins';

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

bibi92Author Commented:
Maybe my question is not clear, the following error is returned :
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS when I add a column or try select * on the following query :
SELECT @list = (select
NAME
from @SQLRESULT )

Thank you

Regards
Mark WillsTopic AdvisorCommented:
Ah, yes....

You want to concatenate those values ?  Name and Create_Date - for multiple rows ?

SELECT @list = isnull(@list + ', ','') + (select cast(name as varchar(10)) +' ' + convert(varchar(10), create_date, 112) 
from @SQLRESULT for xml path('') )

Open in new window

and I will check that in a sec
bibi92Author Commented:
Yes but with specify column name Name and Create_Date
Mark WillsTopic AdvisorCommented:
Need to relocate your char(10) into @list and away from @sql

SELECT @list = isnull(@list + char(10),'') + cast(name as varchar(10)) +' ' + convert(varchar(10), create_date, 112)
from @SQLRESULT

Print @list

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Does the above work for you ? heres a quick little example
DECLARE @SQLRESULT AS TABLE(NAME sysname, CREATE_DATE datetime);
insert @SQLRESULT values ('Name1',getdate() - 10)
insert @SQLRESULT values ('Name2',getdate() - 3)

declare @list varchar(max)

SELECT @list = isnull(@list + char(10),'') + cast(name as varchar(10)) +' ' + convert(varchar(10), create_date, 112)
from @SQLRESULT

Print @list

Open in new window

And varchar(10) is a bit arbitrary - needs to suit your requirement.
bibi92Author Commented:
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.