Tuesday, February 21, 2012

reporting on BLOB fields

Hello,
I am hoping that someone can help me out with this.
I am trying to debug a product that I support, the application creates its
own SQL when executing reports. The report I am trying to execute requires
a
field that is a BLOB, see the SQL below, the result is not what I was
expecting... From the application the report comes up with a black for the
PSL_NOTES field but if i execute the the SQL is Query Analyzer this is the
error I get:
"Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator.
"
SQL statement:
SELECT C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES,SUM(PSDETAIL.PSD_MIN) AS PSD_MIN
FROM
PSDETAIL,PSLINES B,TCPROJ C,CHRHIS D WHERE
PSDETAIL.PSD_PSL = B.PSL_KEY AND B.PSL_PRJ *= C.PRJ_KEY AND B.PSL_CHH=
D.CHH_KEY GROUP BY
C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES ORDER BY C.PRJ_NAME ASC,D.CHH_CODE
ASC,B.PSL_NOTES ASC
Thx!
MarcSince you haven't posted DDL, it's impossible to know which column(s) might
be causing the problem. But the error message says it all. If you have
TEXT, NTEXT, or IMAGE columns in your result set, they can't be ordered,
compared, or grouped. This means that your GROUP BY is probably the issue.
One workaround, if you don't mind some data truncation (if your data is over
8000 characters) is to use the SUBSTRING function to truncate the LOB data.
So insead of:
SELECT YourLOBCol
FROM YourTable
GROUP BY YourLobCol
You could do:
SELECT SUBSTRING(YourLobCol, 1, 8000) AS YourCol
FROM YourTable
GROUP BY SUBSTRING(YourLobCol, 1, 8000)
There are some other workarounds, depending on what your actual business
requirement is. Post DDL and sample data (please, keep the LOB data small)
if you need more assistance.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Marc" <Marc@.discussions.microsoft.com> wrote in message
news:CEB1B459-2832-4766-950B-1872C53115F1@.microsoft.com...
> Hello,
> I am hoping that someone can help me out with this.
> I am trying to debug a product that I support, the application creates its
> own SQL when executing reports. The report I am trying to execute
requires a
> field that is a BLOB, see the SQL below, the result is not what I was
> expecting... From the application the report comes up with a black for
the
> PSL_NOTES field but if i execute the the SQL is Query Analyzer this is the
> error I get:
> "Server: Msg 306, Level 16, State 2, Line 1
> The text, ntext, and image data types cannot be compared or sorted, except
> when using IS NULL or LIKE operator.
> "
>
> SQL statement:
> SELECT C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES,SUM(PSDETAIL.PSD_MIN) AS PSD_MIN
> FROM
> PSDETAIL,PSLINES B,TCPROJ C,CHRHIS D WHERE
> PSDETAIL.PSD_PSL = B.PSL_KEY AND B.PSL_PRJ *= C.PRJ_KEY AND B.PSL_CHH=
> D.CHH_KEY GROUP BY
> C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES ORDER BY C.PRJ_NAME ASC,D.CHH_CODE
> ASC,B.PSL_NOTES ASC
> Thx!
> Marc|||Thanks Adam,
That will help alot!!
Marc
"Adam Machanic" wrote:

> Since you haven't posted DDL, it's impossible to know which column(s) migh
t
> be causing the problem. But the error message says it all. If you have
> TEXT, NTEXT, or IMAGE columns in your result set, they can't be ordered,
> compared, or grouped. This means that your GROUP BY is probably the issue
.
> One workaround, if you don't mind some data truncation (if your data is ov
er
> 8000 characters) is to use the SUBSTRING function to truncate the LOB data
.
> So insead of:
> SELECT YourLOBCol
> FROM YourTable
> GROUP BY YourLobCol
> You could do:
> SELECT SUBSTRING(YourLobCol, 1, 8000) AS YourCol
> FROM YourTable
> GROUP BY SUBSTRING(YourLobCol, 1, 8000)
> There are some other workarounds, depending on what your actual business
> requirement is. Post DDL and sample data (please, keep the LOB data small
)
> if you need more assistance.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Marc" <Marc@.discussions.microsoft.com> wrote in message
> news:CEB1B459-2832-4766-950B-1872C53115F1@.microsoft.com...
> requires a
> the
>
>

No comments:

Post a Comment