How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?
"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:

> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>
|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com
|||in stored procedure

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:

> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order an
d
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure

How is the metadata of the result of a UNION determined?

Hi,

A colleague and I have just found a slightly strange situation that we don't understand.

We had (effectively) the following query:

select cast(1 as decimal(38,10))
union all
select cast(1 as decimal(38,4))

And the result contained 2 rows, each with a a scale of 4. This surprised us, we expected that the metadata of the result would be determined by the topmost query.

So we reversed them and tried this:

select cast(1 as decimal(38,4))
union all
select cast(1 as decimal(38,10))

and got exactly the same result. 2 rows with a scale of 4.

We can't understand why the scale always gets determined to be 4 regardless of the order of the queries.

Any explanation would be much appreciated!

Thanks

Jamie

Doesn't matter. The answer's here: http://msdn2.microsoft.com/en-us/library/ms190476.aspx

-Jamie

|||

In addition to the "Precision, scale and length" topic, the UNION operator topic also documents how the data conversions happens if the various SELECT statements contain columns with different data types. See link below for more details:

http://msdn2.microsoft.com/en-us/library/ms180026(SQL.90).aspx