February 4, 2009 at 5:30 am
Hi,
Could anyone tell what is wrong with below query which is not producing desired result?
Select cast(lastwaittype as varchar(100)) + ' Test String' FROM master.dbo.sysprocesses WHERE spid=51
Any string concatenated before the column works fine but any concatenation operation at the end of the column gets discarded. Confused... Please help!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 4, 2009 at 5:46 am
Your concatenation is actually working, but because you don't trim the trailing blanks you probably don't see the second part. Try using this instead:
SELECT RTRIM(cast(lastwaittype as varchar(100))) + ' Test String'
FROM master.dbo.sysprocesses
WHERE spid=51
[font="Verdana"]Markus Bohse[/font]
February 4, 2009 at 5:57 am
Hi Marcus,
I tried that also marcus. For your reference, I'm attaching the output after running your query. Still no luck.
Also I noticed one thing while experimenting, If we use like convert(varchar(5),lastwaittype) then all the columns get appended with the string but if we keep increasing the varchar length whichever the columns having data less than the length won't concatenate the string and others do.... May be if you try it practically you can understand what I mean....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 4, 2009 at 6:11 am
Sakthivel Chidambaram (2/4/2009)
May be if you try it practically you can understand what I mean....
I've tested it on my system and it works.
But I have noticed that when you use the results to grid option you don't see the last part, while when using results to text you will see the last part. Not sure why grid is showing the wrong result.
WaitTest
----------------------------------
SLEEP Test String
RESOURCE_QUEUE Test String
RESOURCE_QUEUE Test String
PAGEIOLATCH_SH Test String
WRITELOG Test String
...
[font="Verdana"]Markus Bohse[/font]
February 4, 2009 at 6:27 am
Just did some more testing and I noticed that then you run the query against a SQL 2000 server, then the second part is not visible in the grid, but against a 2005 server it works fine.
[font="Verdana"]Markus Bohse[/font]
February 4, 2009 at 6:36 am
Yeah Marcus,
It is like this
Query Analyser -> SQL 2000 DB - It is not showing concatenated string in both Grid and Text
Query Analyser -> SQL 2005 DB - Results are concatenated in both Grid and Text
Mgmt Studio -> SQL 2000 DB - Results are concatenated in Text output but not in Grid
Mgmt Studio -> SQL 2005 DB - Results are concatenated in both Grid and Text
It it a bug or do we need to change any settings in Query Analyser / Enterprise Manager? Thanks in advance for your co-ordination!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 4, 2009 at 6:39 am
I've noticed that if you run this query
SELECT lastwaittype, LEN(lastwaittype) FROM master.dbo.sysprocesses
the number of characters returned by the second column is one more than you would expect. This final character has character code 0. Therefore, it looks like the lastwaittype column is a C-style (null-terminated) string stored in an nchar(32). If you chop off this final null character before appending ' TestString', the query results display as you would expect.
SELECT LEFT(lastwaittype, LEN(lastwaittype) - 1) + ' TestString'
FROM master.dbo.sysprocesses WHERE spid = 51
I actually tested the above in SQL Server 2000 and Query Analyser, so maybe the behaviour is different in SQL Server 2005.
February 4, 2009 at 6:48 am
Hi Andrew,
This query you gave works fine. Could you please tell me how you got ASCII value of the output? I tried to copy the output in QA and then use ASCII for each characeter but unable to find that "0" you have specified....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 4, 2009 at 6:54 am
If you have a Tally table, run this
SELECT P.lastwaittype, T.N, Code = ASCII(SUBSTRING(P.lastwaittype, T.N, 1))
FROM master.dbo.sysprocesses P
CROSS JOIN dbo.Tally T
WHERE P.spid = 51
My query results on a SQL Server 2000 instance are as follows:
[font="Courier New"]
lastwaittype N Code
-------------------------------- ----------- -----------
NETWORKIO 1 78
NETWORKIO 2 69
NETWORKIO 3 84
NETWORKIO 4 87
NETWORKIO 5 79
NETWORKIO 6 82
NETWORKIO 7 75
NETWORKIO 8 73
NETWORKIO 9 79
NETWORKIO 10 0
NETWORKIO 11 32
NETWORKIO 12 32
NETWORKIO 13 32
NETWORKIO 14 32
NETWORKIO 15 32
NETWORKIO 16 32
NETWORKIO 17 32
NETWORKIO 18 32
NETWORKIO 19 32
NETWORKIO 20 32
NETWORKIO 21 32
NETWORKIO 22 32
NETWORKIO 23 32
NETWORKIO 24 32
NETWORKIO 25 32
NETWORKIO 26 32
NETWORKIO 27 32
NETWORKIO 28 32
NETWORKIO 29 32
NETWORKIO 30 32
NETWORKIO 31 32
NETWORKIO 32 32
[/font]
February 4, 2009 at 7:08 am
February 4, 2009 at 12:43 pm
[font="Verdana"]Just a reminder. If one of the column values is null, then "null + 'Test String'" will return null.[/font]
February 4, 2009 at 1:29 pm
Just a reminder. If one of the column values is null, then "null + 'Test String'" will return null.
Quite true, but that's not relevant here.
The concatenation problem doesn't affect SQL Server 2005 because there is no terminating character with character code 0 in the lastwaittype column of the sysprocesses view unlike the case of SQL Server 2000.
However, there is a minor inconsistency in the SSMS query results pane.
Try running the following query with the results in both text format and grid format - 'Second part' is not displayed in the grid.
SELECT N'First part' + CHAR(0) + N'Second part'
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply