Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

Incorrect syntax near ')'.

Hello,

I try to compare the jobs between two servers :

DECLARE @server varchar(30);
DECLARE @Text nvarchar(max);
set @server = 'TESTSRV'


SET @Text= N'IF EXISTS (select * from sysjobs where name not in (select name from ['+ @server + '].msdb.dbo.sysjobs))'
EXEC (@Text)
BEGIN

 
RAISERROR('count, level 11', 11, 1)

END

Msg 102, Level 15, State 1, Line 1

Msg 50000, Level 11, State 1, Line 11
count_jobs, level 11

How can I resolve it?

Thanks
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Don't bother with dynamic SQL:
SELECT *
FROM msdb.dbo.sysjobs j1
	LEFT JOIN STATESERVER1.msdb.dbo.sysjobs j2
		ON j1.name = j2.name
WHERE j2.name IS NULL

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jose Torres
Jose Torres
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Thanks a lot regards