I'm using SQL Server 2000. Suppose I'm in Northwind database and
I execute the following query:
SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
The results of 'Text length' shows 2 X total characters because
Notes is of type ntext (Unicode type).
Q: How to form a query that show the total characters used(in this case
notes/2) provided I'm not sure about the underlying datatype whether
it's Unicode or otherwise?
How to check underlying datatype of a column using T-SQL programmatically?
Regards,
Pedestrian
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1If you know the column type is some string type, you
can try this. I'm guessing that the 0-length substring
calculation will be relatively painless:
SELECT
Notes,
Datalength(Notes)
/CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
THEN 2 ELSE 1 END
FROM Employees
Steve Kass
Drew University
pedestrian via webservertalk.com wrote:
>I'm using SQL Server 2000. Suppose I'm in Northwind database and
>I execute the following query:
>SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
>The results of 'Text length' shows 2 X total characters because
>Notes is of type ntext (Unicode type).
>Q: How to form a query that show the total characters used(in this case
>notes/2) provided I'm not sure about the underlying datatype whether
>it's Unicode or otherwise?
>How to check underlying datatype of a column using T-SQL programmatically?
>Regards,
>Pedestrian
>
>|||If you just want to count number of characters, use len instead of
datalength. This works for both Unicode and non-Unicode strings.
You can query column information in T-SQL by using the
INFORMATION_SCHEMA.COLUMNS view,
HTH
- Baileys
pedestrian via webservertalk.com wrote:
> I'm using SQL Server 2000. Suppose I'm in Northwind database and
> I execute the following query:
> SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
> The results of 'Text length' shows 2 X total characters because
> Notes is of type ntext (Unicode type).
> Q: How to form a query that show the total characters used(in this case
> notes/2) provided I'm not sure about the underlying datatype whether
> it's Unicode or otherwise?
> How to check underlying datatype of a column using T-SQL programmatically?
> Regards,
> Pedestrian
>|||Baileys wrote:
> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
However, you need to keep in mind that LEN() excludes the trailing
blanks when counting the number of characters (but DATALENGTH() does
not).
Razvan|||Steve Kass wrote:
> If you know the column type is some string type, you
> can try this. I'm guessing that the 0-length substring
> calculation will be relatively painless:
> SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
> FROM Employees
Hello, Steve
That's a brilliant trick.
However, I'm not sure why you have used the CASE expression ?
Consider this query:
SELECT
Notes,
Datalength(Notes)
/ DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
FROM Employees
Wouldn't this be the same as your query ?
Razvan|||
Razvan Socol wrote:
>Steve Kass wrote:
>
>Hello, Steve
>That's a brilliant trick.
>However, I'm not sure why you have used the CASE expression ?
>Consider this query:
>SELECT
> Notes,
> Datalength(Notes)
> / DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
>FROM Employees
>Wouldn't this be the same as your query ?
>Razvan
>
>
Yup. Good catch.
SK|||
Baileys wrote:
> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
But LEN does not accept types ntext and text, which the user required.
SK
> You can query column information in T-SQL by using the
> INFORMATION_SCHEMA.COLUMNS view,
> HTH
> - Baileys
> pedestrian via webservertalk.com wrote:
>|||oops, I missed that part of the question...
- Baileys
Steve Kass wrote:
>
> Baileys wrote:
>
>
> But LEN does not accept types ntext and text, which the user required.
> SK
>|||Thanks for quick replies.... particularly to Steve Kass 'n Razvan Socol ...
.
Best Regards,
Pedestrian
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Confused over the following query which return the result 2s:
SELECT Datalength(space(1)+SUBSTRING(Notes,1,0)
) as myCol from employees
Why not the above query return 1s instead of 2s ... ?
I suppose space(1) returns 1 and SUBSTRING(Notes,1,0) as below return 0 as
below...
hence space(1)+SUBSTRING(Notes,1,0) should only returns 1.
This query return me 1... Ok
SELECT Datalength(SPACE(1)) As Slength
This query return me 0s... No problem...
SELECT Datalength(SUBSTRING(Notes,1,0) ) As Length1 FROM Employees
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1
jwelch wrote:
|||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.