Hi
I have a reporting procedure that fetches a great deal of data
and is currently taking too long to retrieve the results (40 sec).
Looking at the execution plan, I see SQL is not using some of the indexes I added especially for the queries benefit (ie.time column), usually it uses the clustered composite PK.
Does anyone know how I can force SQL to use a particular index on
a query?
advice appreciated...
Thanks
Desmondno worries.., got it...(BOL)
Desmond|||after table name or table alias in SELECT clause and/or in JOIN clause put (INDEX=index_name)|||thanks...still needed the syntax!
des|||Originally posted by DesmondX
thanks...still needed the syntax!
des
Check this:
drop table test13
go
create table test13(id int identity primary key, code varchar(10))
go
create index i_test13 on test13 (code)
go
select * from test13 with(index(i_test13))
select * from test13 (index=i_test13)
Showing posts with label retrieve. Show all posts
Showing posts with label retrieve. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
For XML Explicit
I am trying to retrieve data from a single table as an xml document
and am having difficultes getting it to work...
The data is stored in a single table and each row can relate to
another row in the same table (see example below)...
ID ParentID Description
1 null Test one
2 1 Test two
3 1 Test three
4 2 Test four
Please can someone detail the SQL (FOR XML EXPLICIT) required to
retrieve this data in a hierarchical xml document.
Many Thanks in advance.
Hi,
if i have understand, you need to generate a hierarchical xml from
father->son relationship ?!
the left join help me to build a hierarchical xml with for xml auto, in this
case i now the max level number.
if you have the necessary time, try this approach.
i find a other exemple with for xml explicit and i send it to group
i hope to help you
Boss Hog
--Look, i have choose this methode in replacement of for XML explicit
select 1 as VISIBLE,
LEVEL0.CODE as ULID, LEVEL0.ISSHEET, LEVEL0.[DESCRIPTION] as LABEL,
LEVEL0.CCODE, LEVEL0.CCDESC, LEVEL0.HIERARCHIE, LEVEL0.NBCCODE,
LEVEL0.NBLABEL,
LEVEL1.CODE as ULID, LEVEL1.ISSHEET, LEVEL1.[DESCRIPTION] as LABEL,
LEVEL1.CCODE, LEVEL1.CCDESC, LEVEL1.HIERARCHIE, LEVEL1.NBCCODE,
LEVEL1.NBLABEL,
LEVEL2.CODE as ULID, LEVEL2.ISSHEET, LEVEL2.[CODEDESCRIPTION] as LABEL,
LEVEL2.CCODE, LEVEL2.CCDESC, LEVEL2.HIERARCHIE, LEVEL2.NBCCODE,
LEVEL2.NBLABEL,
LEVEL3.CODE as ULID, LEVEL3.ISSHEET, LEVEL3.[CODEDESCRIPTION] as LABEL,
LEVEL3.CCODE, LEVEL3.CCDESC, LEVEL3.HIERARCHIE, LEVEL3.NBCCODE,
LEVEL3.NBLABEL,
LEVEL4.CODE as ULID, LEVEL4.ISSHEET, LEVEL4.[CODEDESCRIPTION] as LABEL,
LEVEL4.CCODE, LEVEL4.CCDESC, LEVEL4.HIERARCHIE, LEVEL4.NBCCODE,
LEVEL4.NBLABEL,
LEVEL5.CODE as ULID, LEVEL5.ISSHEET, LEVEL5.[CODEDESCRIPTION] as LABEL,
LEVEL5.CCODE, LEVEL5.CCDESC, LEVEL5.HIERARCHIE, LEVEL5.NBCCODE,
LEVEL5.NBLABEL
from #TMPTBL LEVEL0
left join #TMPTBL LEVEL1
on LEVEL1.FATHER_ID=LEVEL0.HCODE
left join #TMPTBL LEVEL2
on LEVEL2.FATHER_ID=LEVEL1.HCODE
left join #TMPTBL LEVEL3
on LEVEL3.FATHER_ID=LEVEL2.HCODE
left join #TMPTBL LEVEL4
on LEVEL4.FATHER_ID=LEVEL3.HCODE
left join #TMPTBL LEVEL5
on LEVEL5.FATHER_ID=LEVEL4.HCODE
where LEVEL0.FATHER_ID is null
order by
LEVEL0.ISSHEET, LEVEL0.CODE,
LEVEL1.ISSHEET, LEVEL1.CODE,
LEVEL2.ISSHEET, LEVEL2.CODE,
LEVEL3.ISSHEET, LEVEL3.CODE,
LEVEL4.ISSHEET, LEVEL4.CODE,
LEVEL5.ISSHEET, LEVEL5.CODE
for xml auto
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||Dan,
FOR XML does not support building recursively nested XML of arbitrary depth
out of a self-referenced table.
Support for this feature is being evaluated for SQL 2005.
Your a few options.
If your hierarchy depth is known you can use:
- FOR XML EXPLICIT
- SQLXML mapping schema.
Otherwise your can extract your data as XML without recursive nesting and
reshape it with XSLT on the client side/middle-tier.
If you want to use FOR XML EXPLICIT you need:
1) Use T-SQL WHILE loop or a query with CTE in SQL 2005 to move your data
into a temp table or table variable adding integer column with level in the
hierarchy;
2) Do as many self-concatenations of the table (UNION ALL) with filtering by
appropriate level each time and using proper ordering.
Here's FOR XML EXPLICIT example for two level hierarchy (using SQL 2005
CommonTableExpression to get Level column, with SQL 2000 you can use WHILE
loop to build a temp table or a table variable):
WITH SelfRefTableWithLevel(ParentID, ID, Description, Level) AS
(
SELECT ParentID, ID, Description, 0 AS Level
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, t1.Description, Level + 1
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevel t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 1
ORDER BY [emp!1!ID], [emp!2!ID]
FOR XML EXPLICIT
GO
Hope it works for you.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||I must mention that I oversimplified ordering problem with my example.
For proper ordering when your hierarchy is more than two level deep you
either need to do multiple self joins in SELECT for each level (number of
self joins would be the level each time) or construct an additional ordering
column.
Since the ordering column trick is more efficient and less obvious I will
give you a 3 level hierarchy example with a path ordering key.
WITH SelfRefTableWithLevelAndPath(ParentID, ID, Description, Level,Path) AS
(
SELECT ParentID, ID, 0 AS Level, convert(varchar(8000),ID) as Path
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, Level + 1,
Path+'/'+convert(varchar(8000),t1.ID)
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevelAndPath t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
Path [emp!1!!hide],
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 1
UNION ALL
SELECT
3 Tag,
2 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
NULL [emp!1!ID],
NULL [emp!1!Description],
NULL [emp!2!ParentID],
ParentID [emp!2!ID],
NULL [emp!2!Description],
ParentID [emp!3!ParentID],
ID [emp!3!ID],
Description [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 2
ORDER BY Path
for xml explicit
GO
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
and am having difficultes getting it to work...
The data is stored in a single table and each row can relate to
another row in the same table (see example below)...
ID ParentID Description
1 null Test one
2 1 Test two
3 1 Test three
4 2 Test four
Please can someone detail the SQL (FOR XML EXPLICIT) required to
retrieve this data in a hierarchical xml document.
Many Thanks in advance.
Hi,
if i have understand, you need to generate a hierarchical xml from
father->son relationship ?!
the left join help me to build a hierarchical xml with for xml auto, in this
case i now the max level number.
if you have the necessary time, try this approach.
i find a other exemple with for xml explicit and i send it to group
i hope to help you
Boss Hog
--Look, i have choose this methode in replacement of for XML explicit
select 1 as VISIBLE,
LEVEL0.CODE as ULID, LEVEL0.ISSHEET, LEVEL0.[DESCRIPTION] as LABEL,
LEVEL0.CCODE, LEVEL0.CCDESC, LEVEL0.HIERARCHIE, LEVEL0.NBCCODE,
LEVEL0.NBLABEL,
LEVEL1.CODE as ULID, LEVEL1.ISSHEET, LEVEL1.[DESCRIPTION] as LABEL,
LEVEL1.CCODE, LEVEL1.CCDESC, LEVEL1.HIERARCHIE, LEVEL1.NBCCODE,
LEVEL1.NBLABEL,
LEVEL2.CODE as ULID, LEVEL2.ISSHEET, LEVEL2.[CODEDESCRIPTION] as LABEL,
LEVEL2.CCODE, LEVEL2.CCDESC, LEVEL2.HIERARCHIE, LEVEL2.NBCCODE,
LEVEL2.NBLABEL,
LEVEL3.CODE as ULID, LEVEL3.ISSHEET, LEVEL3.[CODEDESCRIPTION] as LABEL,
LEVEL3.CCODE, LEVEL3.CCDESC, LEVEL3.HIERARCHIE, LEVEL3.NBCCODE,
LEVEL3.NBLABEL,
LEVEL4.CODE as ULID, LEVEL4.ISSHEET, LEVEL4.[CODEDESCRIPTION] as LABEL,
LEVEL4.CCODE, LEVEL4.CCDESC, LEVEL4.HIERARCHIE, LEVEL4.NBCCODE,
LEVEL4.NBLABEL,
LEVEL5.CODE as ULID, LEVEL5.ISSHEET, LEVEL5.[CODEDESCRIPTION] as LABEL,
LEVEL5.CCODE, LEVEL5.CCDESC, LEVEL5.HIERARCHIE, LEVEL5.NBCCODE,
LEVEL5.NBLABEL
from #TMPTBL LEVEL0
left join #TMPTBL LEVEL1
on LEVEL1.FATHER_ID=LEVEL0.HCODE
left join #TMPTBL LEVEL2
on LEVEL2.FATHER_ID=LEVEL1.HCODE
left join #TMPTBL LEVEL3
on LEVEL3.FATHER_ID=LEVEL2.HCODE
left join #TMPTBL LEVEL4
on LEVEL4.FATHER_ID=LEVEL3.HCODE
left join #TMPTBL LEVEL5
on LEVEL5.FATHER_ID=LEVEL4.HCODE
where LEVEL0.FATHER_ID is null
order by
LEVEL0.ISSHEET, LEVEL0.CODE,
LEVEL1.ISSHEET, LEVEL1.CODE,
LEVEL2.ISSHEET, LEVEL2.CODE,
LEVEL3.ISSHEET, LEVEL3.CODE,
LEVEL4.ISSHEET, LEVEL4.CODE,
LEVEL5.ISSHEET, LEVEL5.CODE
for xml auto
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||Dan,
FOR XML does not support building recursively nested XML of arbitrary depth
out of a self-referenced table.
Support for this feature is being evaluated for SQL 2005.
Your a few options.
If your hierarchy depth is known you can use:
- FOR XML EXPLICIT
- SQLXML mapping schema.
Otherwise your can extract your data as XML without recursive nesting and
reshape it with XSLT on the client side/middle-tier.
If you want to use FOR XML EXPLICIT you need:
1) Use T-SQL WHILE loop or a query with CTE in SQL 2005 to move your data
into a temp table or table variable adding integer column with level in the
hierarchy;
2) Do as many self-concatenations of the table (UNION ALL) with filtering by
appropriate level each time and using proper ordering.
Here's FOR XML EXPLICIT example for two level hierarchy (using SQL 2005
CommonTableExpression to get Level column, with SQL 2000 you can use WHILE
loop to build a temp table or a table variable):
WITH SelfRefTableWithLevel(ParentID, ID, Description, Level) AS
(
SELECT ParentID, ID, Description, 0 AS Level
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, t1.Description, Level + 1
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevel t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 1
ORDER BY [emp!1!ID], [emp!2!ID]
FOR XML EXPLICIT
GO
Hope it works for you.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||I must mention that I oversimplified ordering problem with my example.
For proper ordering when your hierarchy is more than two level deep you
either need to do multiple self joins in SELECT for each level (number of
self joins would be the level each time) or construct an additional ordering
column.
Since the ordering column trick is more efficient and less obvious I will
give you a 3 level hierarchy example with a path ordering key.
WITH SelfRefTableWithLevelAndPath(ParentID, ID, Description, Level,Path) AS
(
SELECT ParentID, ID, 0 AS Level, convert(varchar(8000),ID) as Path
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, Level + 1,
Path+'/'+convert(varchar(8000),t1.ID)
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevelAndPath t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
Path [emp!1!!hide],
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 1
UNION ALL
SELECT
3 Tag,
2 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
NULL [emp!1!ID],
NULL [emp!1!Description],
NULL [emp!2!ParentID],
ParentID [emp!2!ID],
NULL [emp!2!Description],
ParentID [emp!3!ParentID],
ID [emp!3!ID],
Description [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 2
ORDER BY Path
for xml explicit
GO
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
FOR XML AUTO, ELEMENTS
SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
Jens
Are you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens
|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:
> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>
|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...[vbcol=seagreen]
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
Jens
Are you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens
|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:
> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>
|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...[vbcol=seagreen]
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
FOR XML AUTO, ELEMENTS
SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
JensAre you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:
> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
>
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
JensAre you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:
> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
>
Subscribe to:
Posts (Atom)