3

I am trying to run below code in SQL server 2000 SP4 but getting error.

Declare @Body varchar(8000);
Declare @TableHead varchar(8000);
Declare @TableTail varchar(8000);

Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' + 
            '<style>
    td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}
    tr.even {background-color:white;}
    tr.odd {background-color:#eeeeee;}
            </style>' + 
            '</head>' + 
            '<body><table cellpadding=0 cellspacing=0 border=0>' + 
            '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' + 
            '<td align=center><b>Product</b></td>' + 
            '<td align=center><b>Provider</b></td>' + 
            '<td align=center><b>Data Source</b></td>' + 
            '<td align=center><b>Is Linked?</b></td></tr>';

Select @Body = (Select 
        name As [TD],
        product As [TD],
        provider As [TD],
        data_source As [TD align=center],
        is_linked As [TD align=center]
    From sys.servers
    Order By is_linked, name
    For XML Raw('tr'), Elements);

Set @Body = Replace(@Body, '_x003D_', '=');
Set @Body = Replace(@Body, '_x0020_', space(1));

Select @TableHead + @Body + @TableTail;

Error: 

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'For'.

I took this code from here:

and am using it as a reference to build a new report. I have a mix of SQL Server 2000, 2005, 2008, 2012 instances in my environment. This code works on other servers but not in 2000 SP4.

Please help me fix this.

Improve this question

2 Answers 2

Reset to default
7

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO.

Edit:

If you were to run the query to get the list of servers in SQL Server 2000, you'd receive a message like this:

Msg 6825, Level 16, State 1, Line 1
ELEMENTS mode requires FOR XML AUTO.

That would come after you found that sys.servers doesn't exist in SQL Server 2000.

This is the query you'd need to run to get the data you want. I've updated the column names because SQL Server 2000 doesn't like duplicated column names:

SELECT
    srvname AS [TD1]
    ,srvproduct AS [TD2]
    ,providername AS [TD3]
    ,datasource AS [TD4]
    ,isremote AS [TD5]
FROM dbo.sysservers
ORDER BY
    isremote
    ,srvname
FOR XML AUTO, elements

That query works in 2000. From there, I think it's a problem with the nested query not working in 2000 as it's expected to in 2005+. If I have some extra time, I'll play with it some more.

Edit edit:

As I had suspected, but couldn't confirm, FOR XML doesn't work in a subquery in SQL Server 2000. This page has a post from Erland Sommarskog stating as much, and he directs the person who asked a similar question to this Books Online page for SQL Server 2000 where you can see the very first point states

FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other statements (SELECT INTO, assignment).

So, what you're trying to do in SQL Server 2000 can't be done.

Improve this answer
0

You will need to change the syntax to: For XML Auto, Elements); because Row tag name is only allowed with RAW or PATH mode of FOR XML.

Improve this answer
3
  • Do you mean to say just replace For XML Raw('tr'), Elements); with For XML Auto, Elements);? I tried it but it does not work. Getting same error. Commented Apr 5, 2016 at 5:17
  • Interesting. Let me install SQL2000 real quick. Commented Apr 5, 2016 at 5:36
  • I'd follow Stephen's answer below. He's right and faster than me. Commented Apr 5, 2016 at 5:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.