Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpierce
Starting Member
2 Posts |
 Posted - 2010-11-10 : 09:06:51
|
| Hello, We are having an issue where we are running the exact same query on two different servers, running the exact same service pack and update - 9.0.4211 - but receiving two differently formatted XML resultsets. Does anyone have any idea why/how this is happening? Is there a setting/schema involved somehow in the XML generation?Query - SELECT distinct operation.operationid, @zoneId as ZoneID, member.name, IsNull(member.firstname, '') as firstname, IsNull(member.lastname, '') as lastname, IsNull(member.email, '') as email, IsNull(member.memberid, '') as memberid, IsNull(member.type, '') as type, IsNull(member.AppMemberID, '') as AppMemberID FROM IQSECURE_TaskOperations operation join IQSECURE_RoleTasks r on operation.taskid = r.taskid join IQOBASE_Rosters roster on r.roleid = roster.roleid left outer join IQObase_Members member on roster.rosterid = member.Rosterid WHERE operation.operationid = @operationid AND roster.zoneid = @zoneId and member.memberid is not null union SELECT distinct operation.operationid, @zoneID as ZoneID, member.name, IsNull(member.firstname, '') as firstname, IsNull(member.lastname, '') as lastname, IsNull(member.email, '') as email, IsNull(member.memberid, '') as memberid, IsNull(member.type, '') as type, IsNull(member.AppMemberID, '') as AppMemberID FROM IQSECURE_TaskOperations operation join IQSECURE_RoleTasks r on operation.taskid = r.taskid join IQOBASE_Rosters roster on r.roleid = roster.roleid left outer join IQOBASE_Members_Exploded member on roster.rosterid = member.Rosterid WHERE operation.operationid = @operationid AND roster.zoneid = @zoneid and member.memberid is not null ORDER BY IsNull(member.type, ''), IsNull(member.lastname, ''), IsNull(member.firstname, ''), member.name FOR XML AUTO, XMLDATAThanks in advance for any help provided.Jon |
|
|
jpierce
Starting Member
2 Posts |
Posted - 2010-11-12 : 08:25:19
|
| Got my answer - there were two different issues. The first was that one of the servers was set for SQL Server 2000 and one for SQL Server 2005 compatibility mode. The SQL Server 2000 worked ok with this select the 2005 did not. The problem on the server set with SQL Server 2005 compatibilty mode was the "UNION" statement. The select had to be rewritten without the UNION used as it was here. |
 |
|
|
|
|
|
|
|
FOR XML clause