MS SQL Server XML Query Help

Hi all--

I am trying to execute the following query in MS SQL Server 2000.

SELECT 1 AS Tag, NULL as parent,
'x' AS [A!1!B],
NULL AS [C!48],
NULL AS [RepurchaseDetails!49]

UNION ALL

SELECT 48, 1,
NULL, 'y', NULL

UNION ALL

SELECT 49, 48,
NULL, NULL, 'z'

ORDER BY
TAG ASC

FOR XML EXPLICIT

I get the error:

Server: Msg 6806, Level 16, State 2, Line 1
Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.

However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
the query works just fine. It however still fails for
RepurchaseDetailz, or RepurchaseDetailzz

Any ideas?"Jay" <xml_@.hotmail.com> wrote in message
news:4e7ac2ff.0402260651.7c8b888b@.posting.google.c om...
> Hi all--
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1,
> NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48,
> NULL, NULL, 'z'
> ORDER BY
> TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz
> Any ideas?

You may want to post this in microsoft.public.sqlserver.xml, as you'll
probably get a better response there.

Simon|||Jay (xml_@.hotmail.com) writes:
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1, NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48, NULL, NULL, 'z'
> ORDER BY TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz

Not only that. Shortening it to RepurchaseDetail also works.

I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
bug long way.

The hour is a little late for me now, but I will look into this
again tomorrow. I guess that you need to use a workaround.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I posted this same question on microsoft.public.sqlserver.xml
and they are stumped. I have tried this on different versions of SQL
server 2000 with the same results.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949C6D4ED1DBYazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > I am trying to execute the following query in MS SQL Server 2000.
> > SELECT 1 AS Tag, NULL as parent,
> > 'x' AS [A!1!B],
> > NULL AS [C!48],
> > NULL AS [RepurchaseDetails!49]
> > UNION ALL
> > SELECT 48, 1, NULL, 'y', NULL
> > UNION ALL
> > SELECT 49, 48, NULL, NULL, 'z'
> > ORDER BY TAG ASC
> > FOR XML EXPLICIT
> > I get the error:
> > Server: Msg 6806, Level 16, State 2, Line 1
> > Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> > However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> > the query works just fine. It however still fails for
> > RepurchaseDetailz, or RepurchaseDetailzz
> Not only that. Shortening it to RepurchaseDetail also works.
> I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
> bug long way.
> The hour is a little late for me now, but I will look into this
> again tomorrow. I guess that you need to use a workaround.|||Jay (xml_@.hotmail.com) writes:
> Thanks, I posted this same question on microsoft.public.sqlserver.xml
> and they are stumped. I have tried this on different versions of SQL
> server 2000 with the same results.

I've now tested the query on Yukon Beta 1, where it works as it should.

I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
I get the same error.

I will report this to Microsoft, and I would expect them to file a bug
unless this is a known problem.

However, if you are in need of a fix, and cannot use the workaround of
shortening the element name, or making it longer, then need to open a
case with Microsoft to pursue this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all your help, Microsoft has now confirmed to me that this
is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
and is now being considered for a future service pack for 2000. I'll
just have to work around it for now.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949CEECB08C61Yazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > Thanks, I posted this same question on microsoft.public.sqlserver.xml
> > and they are stumped. I have tried this on different versions of SQL
> > server 2000 with the same results.
> I've now tested the query on Yukon Beta 1, where it works as it should.
> I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
> I get the same error.
> I will report this to Microsoft, and I would expect them to file a bug
> unless this is a known problem.
> However, if you are in need of a fix, and cannot use the workaround of
> shortening the element name, or making it longer, then need to open a
> case with Microsoft to pursue this.|||Jay (xml_@.hotmail.com) writes:
> Thanks for all your help, Microsoft has now confirmed to me that this
> is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
> and is now being considered for a future service pack for 2000. I'll
> just have to work around it for now.

Thanks for reporting back! If this is known bug, then I don't need to
bug (sic!) my contacts at MS with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

MS SQL SERVER 2005 BUG? Cannot insert NULL into column diagram_id

Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this??I had the same issue an just fixed it by turning the "diagram_id" field in SysDiagrams table to "identitity".

I dropped the table and ran the following script.

CREATE TABLE [dbo].[sysdiagrams](
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] identity(1,1),
[version] [int] NULL,
[definition] [varbinary](max) NULL
) ON [PRIMARY]

It worked.

MS SQL 2005 identity lost new database

Hello

I am creating a TABLE

CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]

when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1

now if I want to make a copy of that database, importing datas and tables

I get :
Identity Specification = No
(Is Identity) = No

How can I avoid this problem ? my application is not working anymore

ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]

is not solving that problem

thank youit sounds like you copied your data using SSIS or DTS or whatever you want to call it. This will copy your data but not your schema. You likely lost all of your indexes and constraints as well. What you want to do if you want to copy all of your data and your schema, is to take a backup of the database you want to copy and restore that database to a new location. If it is a new server, you will have to remap you logins as well.|||With SQL Server 2000 Enterprise Manager you could quickly and easily copy both data and schema between servers. With 2005, you can't. This is Microsoft's idea of progress...

A work-around is to generate a script for you schema and execute it on the target server, and then you SSIS to transfer just the data.|||thank you

what is the code line for ALTER COLUMN to create IDENTITY (1,1)
I must do it now with code ?|||there is not one.

I am a little concerned from what you said in first and last post that you are going to mess up all of your primary key and foreign key relationships.

however if you must you need to create a new table and in your create table statement you must define an identity column. copy the data from your current table to the new one. drop the old table, and rename the new one.|||in that way maybe ?:

ALTER TABLE [dbo].[TbTest] ADD
[id1_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED;
ALTER TABLE [dbo].[TbTest] DROP COLUMN [id_TbTest];
EXEC sp_rename '[TbTest].[id1_TbTest]', 'id_TbTest', 'COLUMN';

thank you