Minor incompatibility between 2000 and 2005

Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)Hello Sergey Lipnevich sergey.at.optimaltec.com,

> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).

> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!

> These are both known features. No, really. Features. :)
Known... Yes. Features... I don't know :-).
Sergey.|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.

Minor incompatibility between 2000 and 2005

Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)
Hello Sergey Lipnevich sergey.at.optimaltec.com,

> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).

> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!

> These are both known features. No, really. Features.
Known... Yes. Features... I don't know :-).
Sergey.
|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.
|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.

Miningmodel-Viewer: Parser Error

Parser for XMLA (XML for Analysis): Timeout geting XMLA-Request (XML for Analysis).
Error when executing the administrated stored procedure 'GetItemsets': Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.

I get this Error in Miningmodel-Viewer, what to do? This occurs when I set the MAXIMUM_ITEMSET_COUNT to 1,500,000 .

The same problem was solved in thread "Lift chart - Time out error SSAS data minign 2005 "

Mining XML from file

Right now, when i want to load contents of XLM file, i have to use openXML which looks this way:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc
select *
from openxml (@.idoc, '/root/tag',3 )
( and here is whole select procedure)
insert into (table name)
from openxml (@.idoc, '/root/tag,3 )
(and here it is again)

This is quite "useless" for daily using, unless someone fills the text field with new data.

Is it possible to load content directly from XML file located somewhere on the drive?Is there any other way to open XML file an load its contents into table?|||

What "text field" are you talking about?

Have you looked at the SQL Server Integration Services?

|||Whole content is loaded as text. It has "something" to do with this part:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc

If i wanted an visual studio solution, which i already have, i would put it in visual studio forum.

But still integration package is another file, artificially created. I dont need those.|||You could use bulk load:
1. All data to one field
insert into your_table
select * from openrowset( BULK 'c:\yourfile.txt' SINGLE_BLOB)
2. Split data to few rows
insert into your_table

select * from openrowset( BULK 'c:\yourfile.txt' FORMATFILE='C:\bcpformat.xml')

More info: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Also you could load data into xml column and use xml.nodes function insted of openxml for split xml-data to rows/filed|||

I have the same issue as errpop although i am running SQL 2005 std 1 CPU SP1 on Win 2000 server SP4 (both fully patched clean installs). The errors i am getting in the sql server logs are below and occur immediately after the sql server service starts and the number of cpu's are detected and written to the sql logs.

Error: 8313, Severity: 16, State: 1.

Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.

Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

I have followed the instructions in KB300956 and nothing changed. I noticed executing lodctr sqlctr.ini runs but i dont get any messages on screen. It is like it is not working. The reason i am so interested in the performance counters is i am trying to troubleshoot a local HP SureStore DLT1 tape drive (14 hours to backup 70GB) and i need the SQL backup device counter. I dont know if this is related or not?

Any help is greatly appreciated as this is entirely new territory to me.

Mining XML from file

Right now, when i want to load contents of XLM file, i have to use openXML which looks this way:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc
select *
from openxml (@.idoc, '/root/tag',3 )
( and here is whole select procedure)
insert into (table name)
from openxml (@.idoc, '/root/tag,3 )
(and here it is again)

This is quite "useless" for daily using, unless someone fills the text field with new data.

Is it possible to load content directly from XML file located somewhere on the drive?Is there any other way to open XML file an load its contents into table?|||

What "text field" are you talking about?

Have you looked at the SQL Server Integration Services?

|||Whole content is loaded as text. It has "something" to do with this part:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc

If i wanted an visual studio solution, which i already have, i would put it in visual studio forum.

But still integration package is another file, artificially created. I dont need those.|||You could use bulk load:
1. All data to one field
insert into your_table
select * from openrowset( BULK 'c:\yourfile.txt' SINGLE_BLOB)
2. Split data to few rows
insert into your_table

select * from openrowset( BULK 'c:\yourfile.txt' FORMATFILE='C:\bcpformat.xml')

More info: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Also you could load data into xml column and use xml.nodes function insted of openxml for split xml-data to rows/filed|||

I have the same issue as errpop although i am running SQL 2005 std 1 CPU SP1 on Win 2000 server SP4 (both fully patched clean installs). The errors i am getting in the sql server logs are below and occur immediately after the sql server service starts and the number of cpu's are detected and written to the sql logs.

Error: 8313, Severity: 16, State: 1.

Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.

Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

I have followed the instructions in KB300956 and nothing changed. I noticed executing lodctr sqlctr.ini runs but i dont get any messages on screen. It is like it is not working. The reason i am so interested in the performance counters is i am trying to troubleshoot a local HP SureStore DLT1 tape drive (14 hours to backup 70GB) and i need the SQL backup device counter. I dont know if this is related or not?

Any help is greatly appreciated as this is entirely new territory to me.

Mimic SQL Server Management Studio behavior inside RS Report

I want to know how to mimic the SSMS behavior inside an Reporting Services report when querying an XML field.

In SSMS, a field that is XML shows up as a link, and when the use clicks on the XML, it opens then entire XML document in a new window. I need this exact behavior in RS, but can't accomplish it.

Any smart people out there who can do this?

You would need to use a drillthrough or hyperlink action in the report. You could pass the value of the XML to the target URL.|||

Brian, thats the part that I dont understand. The target isn't in a file- it's in a field in the database. So, with one hyperlink action how do I extract the data, and present it in another IE window, formatted as XML (i.e. indentation, Expand/collapse nodes functionality, etc. like in IE or SSMS)

You say "pass the value of the XML". How do I construnct the hyperlink to do that? Just assign the field to the Drill to URL action? That seems to easy, but I'll give it a shot...

-Kory

|||

I didn't realize you were trying to get an interactive representation of the XML. You can't really accomplish what you want in a report as we don't deal with XML data hierarchically - the XML data extension will flatten it and the binding it into a textbox will simply display the string. (Native XML support is in the long term plans)

So, if you want an interactive view, I would create an ASPX page that took the XML data as a parameter and displayed the HTML then create a URL action to point to the page.