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?
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

1
bibi92Author Commented:
Hello,

I want to return named, create_date on @list

Thank you
0
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
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
0
bibi92Author Commented:
Yes but with specify column name Name and Create_Date
0
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

0

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.
0
bibi92Author Commented:
Thank you very much
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.