FOR XML works differently between Developer and Enterprise Edition in SQL 2005

If I run the same FOR XML query in a Development edition enviornment and a Enterprise Edition environment, the results are different. The query is exactly the same.

Here is the query:

DECLARE @.MessageBody XML
DECLARE @.AuditTable SYSNAME
DECLARE @.SendTrans BIT
DECLARE @.SendAudit BIT
DECLARE @.RecordCount INT
DECLARE @.OperationType CHAR(1)

SET @.RecordCount = @.@.ROWCOUNT
SET @.OperationType = 'U'
SET @.SendTrans = 1
SET @.SendAudit = 1
SET @.AuditTable = 'States'

SELECT @.MessageBody = (
SELECT * FROM
(
SELECT TOP 10
'INSERTED' AS ActionType, @.SendTrans AS SendTrans, @.SendAudit AS SendAudit,
COLUMNS_UPDATED() AS ColumnsUpdated, GETDATE() AS AuditDate,
@.AuditTable AS AuditTable, 'test' AS UserName, @.RecordCount AS RecordCount, *
FROM l_states
)AuditRecord
FOR XML AUTO, ROOT('AuditTable'), BINARY BASE64)

SELECT @.MessageBody

In my DEV env (Developer Edition), this result is produced:
<AuditTable>
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AK" State="Alaska" />
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AL" State="Alabama" />
</AuditTable>

In my Enterprise Edition evn, this is the result:
<AuditTable>
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:44:48.230" AuditTable="States" UserName="test" RecordCount="1">
<l_states StateAbbreviation="AK" State="Alaska" />
<l_states StateAbbreviation="AL" State="Alabama" />
</AuditRecord>
</AuditTable>

Does anyone have any idea what might be wrong? Any help is greatly appreciated.
Tim

Tim,

What is the results of @.@.VERSION on both servers? (I suspect a difference in versions...)

For XML works different for different sql server 2005 editions

If I run the same FOR XML query in a Development edition enviornment and a Enterprise Edition environment, the results are different. The query is exactly the same.

Here is the query:

DECLARE @.MessageBody XML
DECLARE @.AuditTable SYSNAME
DECLARE @.SendTrans BIT
DECLARE @.SendAudit BIT
DECLARE @.RecordCount INT
DECLARE @.OperationType CHAR(1)

SET @.RecordCount = @.@.ROWCOUNT
SET @.OperationType = 'U'
SET @.SendTrans = 1
SET @.SendAudit = 1
SET @.AuditTable = 'States'

SELECT @.MessageBody = (
SELECT * FROM
(
SELECT TOP 10
'INSERTED' AS ActionType, @.SendTrans AS SendTrans, @.SendAudit AS SendAudit,
COLUMNS_UPDATED() AS ColumnsUpdated, GETDATE() AS AuditDate,
@.AuditTable AS AuditTable, 'test' AS UserName, @.RecordCount AS RecordCount, *
FROM l_states
)AuditRecord
FOR XML AUTO, ROOT('AuditTable'), BINARY BASE64)

SELECT @.MessageBody

In my DEV env (Developer Edition), this result is produced:
<AuditTable>
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AK" State="Alaska" />
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AL" State="Alabama" />
</AuditTable>

In my Enterprise Edition evn, this is the result:
<AuditTable>
<AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:44:48.230" AuditTable="States" UserName="test" RecordCount="1">
<l_states StateAbbreviation="AK" State="Alaska" />
<l_states StateAbbreviation="AL" State="Alabama" />
</AuditRecord>
</AuditTable>

Does anyone have any idea what might be wrong? Any help is greatly appreciated.
TimYou might want to check the compat leve of two databases. The DEV behavior should level=90 (SQL 2005) behavior - using derived table name as row element name. The Ent is SQL 2000 behavior (level=80).

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.