May 3, 2016 at 9:36 am
I am trying to get data from multiple servers..but I have been getting an error
Msg 7202, Level 11, State 2, Line 38
Could not find server '' + @SN + '' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
DECLARE @tableHTMLA NVARCHAR(MAX) ;
DECLARE @SN VARCHAR(50)
DECLARE C CURSOR
FOR select name From sys.servers OPEN C
FETCH NEXT FROM C INTO @SN;
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @tableHTMLA =
N'<font face="Arial" size="+1" COLOR="#7AA9DD"><u>Failed jobs in the last 10 days</u></font>' +
N'<table border="0" cellpadding="3">' +
N'<tr bgcolor=#D9D9D9><th><font face="Arial" size="-1">Job Name</font></th>' +
N'<th><font face="Arial" size="-1">Run Date</font></th>' +
N'<th><font face="Arial" size="-1">Run Time</font></th><th><font face="Arial" size="-1">Error Message</font></th><th><font face="Arial" size="-1">Server</font></th>' +
CAST ( ( SELECT j.name AS 'td','',h.run_date AS 'td','',h.run_time AS 'td','',
h.message AS 'td','',h.server AS 'td'
FROM [' + @SN + '].msdb.dbo.sysjobhistory h
INNER JOIN [' + @SN + '].msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN [' + @SN + '].msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
-- Formatting of table font, size and colour
FETCH NEXT FROM C INTO @SN;
END
CLOSE C;
DEALLOCATE C;
I am not able to figure out whats wrong in the syntax
May 3, 2016 at 5:39 pm
These wouldn't work in your context:
[' + @SN + '].msdb.dbo.sysjobhistory h
Anything inside brackets it takes as a literal name. Here, it thinks the server you're trying to access is called [' + @SN + ']. I can't think of a way where you can make the server name here dynamic without making the entire query dynamic.
As a warning, every time you write dynamic code, you're opening yourself up for SQL Injection.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply