Prev: splitting of data elements - help in best way forward
Next: Stored procedure timeout
From: pvdm on 15 Jun 2010 19:06
in SQL 2005 if you issue a select with for xml auto from a synonym with an
alias, the returned xml will include the synonym's parent object instead of
the alis name, but in sql 2008 this works fine. for example

create synonym syscolumns_test for msdb.dbo.syscolumns
go
select name from syscolumns_test test for xml auto

on SQL 2005 will return <msdb.dbo.syscolumns name="" />
on SQL 2008 will return <test name="" />

Is there a hotfix for this?

From: John Bell on 17 Jun 2010 17:30
On Tue, 15 Jun 2010 16:06:41 -0700, pvdm
<pvdm(a)discussions.microsoft.com> wrote:

>in SQL 2005 if you issue a select with for xml auto from a synonym with an
>alias, the returned xml will include the synonym's parent object instead of
>the alis name, but in sql 2008 this works fine. for example
>
>create synonym syscolumns_test for msdb.dbo.syscolumns
>go
>select name from syscolumns_test test for xml auto
>
>on SQL 2005 will return <msdb.dbo.syscolumns name="" />
>on SQL 2008 will return <test name="" />
>
>Is there a hotfix for this?

What do you want fixed?

For 2005 have you tried using view instead?

John
From: pvdm on 17 Jun 2010 19:22
Hi

Yes we are actually currently using views, but they make deployment
difficult (we have multiple environments and need to automatically update
views/synonyms with different linked server names for deployment) and also
you have to alter views when source tables change. Synonyms worked perfectly
until I realised the XML gets screwed up.

Obviously this behavior is not by design because this does not happen in SQL
2008 and especially because an alias was specified, hence the request for a
fix.




"John Bell" wrote:

> On Tue, 15 Jun 2010 16:06:41 -0700, pvdm
> <pvdm(a)discussions.microsoft.com> wrote:
>
> >in SQL 2005 if you issue a select with for xml auto from a synonym with an
> >alias, the returned xml will include the synonym's parent object instead of
> >the alis name, but in sql 2008 this works fine. for example
> >
> >create synonym syscolumns_test for msdb.dbo.syscolumns
> >go
> >select name from syscolumns_test test for xml auto
> >
> >on SQL 2005 will return <msdb.dbo.syscolumns name="" />
> >on SQL 2008 will return <test name="" />
> >
> >Is there a hotfix for this?
>
> What do you want fixed?
>
> For 2005 have you tried using view instead?
>
> John
> .
>
From: John Bell on 18 Jun 2010 03:20
On Thu, 17 Jun 2010 16:22:42 -0700, pvdm
<pvdm(a)discussions.microsoft.com> wrote:

>Hi
>
>Yes we are actually currently using views, but they make deployment
>difficult (we have multiple environments and need to automatically update
>views/synonyms with different linked server names for deployment) and also
>you have to alter views when source tables change. Synonyms worked perfectly
>until I realised the XML gets screwed up.
>
>Obviously this behavior is not by design because this does not happen in SQL
>2008 and especially because an alias was specified, hence the request for a
>fix.
>
>
>
>
>"John Bell" wrote:
>
>> On Tue, 15 Jun 2010 16:06:41 -0700, pvdm
>> <pvdm(a)discussions.microsoft.com> wrote:
>>
>> >in SQL 2005 if you issue a select with for xml auto from a synonym with an
>> >alias, the returned xml will include the synonym's parent object instead of
>> >the alis name, but in sql 2008 this works fine. for example
>> >
>> >create synonym syscolumns_test for msdb.dbo.syscolumns
>> >go
>> >select name from syscolumns_test test for xml auto
>> >
>> >on SQL 2005 will return <msdb.dbo.syscolumns name="" />
>> >on SQL 2008 will return <test name="" />
>> >
>> >Is there a hotfix for this?
>>
>> What do you want fixed?
>>
>> For 2005 have you tried using view instead?
>>
>> John
>> .
>>
Hi

I am not sure why you need to change the views for the linked servers,
as I would expect the linked server definition to change but the name
to be the same.

Have you looked at PATH mode?
http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx#forxml2k5_topic6

John
From: Erland Sommarskog on 18 Jun 2010 17:52
pvdm (pvdm(a)discussions.microsoft.com) writes:
> Yes we are actually currently using views, but they make deployment
> difficult (we have multiple environments and need to automatically
> update views/synonyms with different linked server names for deployment)
> and also you have to alter views when source tables change. Synonyms
> worked perfectly until I realised the XML gets screwed up.
>
> Obviously this behavior is not by design because this does not happen in
> SQL 2008 and especially because an alias was specified, hence the
> request for a fix.

You could try to request the most recent Cumultative Update of SQL 2005 SP3
to see if there is a fix included. If not, you could open a case with
Microsoft, and if you can convince them that there are seroius business
demands for this fix, you will get it.

Then again, there appears to be a simple workaround:

select *
from (select name from syscolumns_test test) as test
for xml auto

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: splitting of data elements - help in best way forward
Next: Stored procedure timeout