October 2009 - Posts

How to display long text in SSMS
25 October 09 10:15 PM | MartinBell | 7 comment(s)
Displaying text in SQL Server Management Studio has never been great!! Writing large amounts of data to the screen that will often be unread does not help performance and the user experience could be degraded. When you display results to text the default maximum size of characters that you can display in a column is 256 characters. This can be increased to 8192 characters which for most people will be more than enough for everyday use. You can change the size by going to the Tools/Option menu and opening the Query Results branch on the tree control. Then under the Results to Text leaf is the the “Maximum number of characters in a column” value.



If you are displaying large text data, it is quite likely that your data will have line feeds in it. When displaying text results this can be a nuisance as it will mess up the alignment of subsequent columns in your results. To get around this I usually display the results in grid mode. If you do this you can set the maximum number of characters by changing the Maximum Caracters Retrieved for Non XML data on the options screen for “Results to Grid”.




The default value for this is 65535, but you can change this to be lower. Unfortunately whilst writing this post I came across a bug in SSMS. This is that when you cut/paste data from a column in grid mode, only 43679 characters will be transferred. I raised this as a Connect item.

The other issue with “Results to Grid” is that it removes any linefeed characters from the results. For example if you have the following script:

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
            DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX);

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
            SELECT LEN(''' + REPLICATE ( CAST('A' AS varchar(MAX)), 65535) + ''');
END'

EXEC ( @procdefn );

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength'));

The results will not only be truncated they will be a single line like the following:

CREATE PROCEDURE dbo.prc_LongStringLengths  AS  BEGIN   SELECT LEN('AAAAAAAA

When this is the definition of a stored procedure it is not very useful! So what can you do to get the full definition? If you noticed on the Options of “Result to Grid”, you can set the number of characters to be returned for XML data separately. The default is 2MB, but you can choose an unlimited maximum. When using “Result to Grid”, SSMS will display XML data as a clickable link, which when clicked will open a new window.

You can make use of this by casting the text you want to display as XML e.g.

SELECT CAST(OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS XML);


This will allow you to click on the link and view the text. Unfortunately this does have some limitations for instance if this was the procedure definition: 

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX);

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                IF 1 < 2
                                SELECT LEN(''' + REPLICATE ( CAST('A' AS varchar(MAX)), 65535) + ''');
END' ;

EXEC ( @procdefn );

SELECT CAST(OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS XML);


You will get the error:

Msg 9455, Level 16, State 1, Line 13
XML parsing: line 4, character 8, illegal qualified name character

Another way of displaying the data is to use the FOR XML clause e.g.

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX);

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
              SELECT LEN(''' + REPLICATE ( CAST('&<>' AS varchar(MAX)), 3) + ''');
END' ; 

EXEC ( @procdefn );  

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength'))  FOR XML PATH(''), TYPE

This will also produce a clickable link, that will open in a new tab. If you looked at the above example you will see that it will perform entitisation on the XML reserved characters so instead of displaying the ampersand character this is substituted with &amp; and the greater than is substituted with &gt; less than is substituted with &lt; i.e.

CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN('&amp;&lt;&gt;&amp;&lt;&gt;&amp;&lt;&gt;');
END

So how do you overcome this? You can use XML EXPLICIT and specify the text column with a CDATA directive.

SELECT  1 as Tag,
        0 as Parent,
        OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) as [TextData!1!!CDATA]
FOR XML EXPLICIT ;

This will give the following results:

<TextData><![CDATA[CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN('&<>&<>&<>');
END]]></TextData>

This will allow you to see text data including line feeds, but it a bit ugly! A colleague at work Rhys Jones found this interesting feature:

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX); 
SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN(''' + REPLICATE ( CAST('A' AS varchar(MAX)), 65535) + ''');
END' ;

EXEC ( @procdefn ); 

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];


This will create a clickable link, this does not always work if you have multiple columns e.g.


SELECT 1 as Id,
              OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];


it does not work. Also if you have one of the reserved characters you will get an error message when you click on the link, although you can still cut and paste the definition in full into another window and it will not be truncated e.g.


IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX); 

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN(''' + REPLICATE ( CAST('<' AS varchar(MAX)), 65535) + ''');
END' ; 

EXEC ( @procdefn ); 

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];

Clicking the link will give you:



None of these solutions are great so I raised a feature request on Connect so that varchar(MAX) or nvarcar(MAX) data can be displayed in the same way as XML without having to code around it.

Since writing this MVP Adam Mechanic has added the a comment to the connect item pointing out that you can use a process-instruction to avoid entitisation and gain the benefits of a converting to XML

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [processing-instruction(x)] FOR XML PATH('')
Filed under:
Slide Deck - Leeds Area SQL Server User Group Meeting, Leeds - Thursday 1st October
12 October 09 07:12 PM | MartinBell | with no comments
Chris has had some issues loading up his slide decks so here they are!

Getting Dimensional with Data
End to End Report Management and Creation using SQL Server 2008

The next Manchester meeting is on Thurday with more great information on Reporting Services see:
Manchester User Group Meeting 15th October The next meeting in Leeds will be Leeds User Group Meeting 10th December

For a full list of up and coming events see:

The User Group Website http://www.sqlserverfaq.com
SQLBits Website http://www.sqlbits.com
Chris’ website http://www.learnsqlserver.org/
Chris’ Blog http://sqlblogcasts.com/blogs/testas/  

 

Filed under:
Too many passengers!
11 October 09 09:18 PM | MartinBell | with no comments

I’ve just returned from the User Group Meeting in Edinburgh by train. Ok I have a masochistic streak that maybe I should see a shrink about, but when my last connection didn’t turn up, today’s journey ended up being an 8.5 hour nightmare.

My first two trains ran on time, and although they were a bit crowded I managed to get a seat and read my book. I did note that I could have travel to London on my first train in two hours less than my planned journey time.

On the third connection people were standing up the length of the train. This was a two unit train when four would have been a better size. Running trains and computing have a lot of similarities they are both networks and you can learn a lot by not following the example of Network Rail and the train providers.

When we came into the station, the guard announce that he was “Sorry for any inconvenience to passengers who had had to stand for their journey” which was kind of nice although he could have sounded a bit more sincere, “The problem is due to too many customers on the service”.

An issue when enabling protocols with Powershell
09 October 09 07:01 PM | MartinBell | 2 comment(s)

I was looking at the topic “How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)” books online to enable the TCP protocol for a SQL Server instance using Powershell. So I started up SQLPS from object explorer and posted the following script into the window.

$wmi = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol"
$tcp = $wmi.getsmoobject($uri + "[@Name='Tcp']")
$tcp.IsEnabled = $true
$tcp.alter()
$tcp

Unfortunately this gave me an error:

Exception calling "Alter" with "0" argument(s): "Alter failed. "
At line:1 char:11
+ $tcp.alter( <<<< )

I was dumbfounded!!! How could books online be promoting incorrect code? So I trawled the internet and came up with nothing :( Then a flash of inspiration came to me (hopefully not my last!) and that was to run Powershell as an administrator. Et voila it worked.

This raised the problem of how do you start SQLPS from the Object Explorer as an administrator. The solution is to start Management studio as an administrator and the script will work.

So the only question remaining is "Why does this error occur when it's a permissions issue?"

Filed under:
Slide Deck - Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 8th October
09 October 09 10:44 AM | MartinBell | 1 comment(s)

Thanks to everyone who came to the user group meeting in Edinburgh last night. We had a great atmosphere and lots of interaction made a great meeting. Congratulations to the competition winners I hope you enjoy the books and find them useful.

Rob gave a very interesting talk on SQLDiag, SQL Nexus and PAL so I hope you will go off an play with them so when you hit your own performance problems you can resolve them quickly and easily. Rob's going to post useful links on his blog http://blogs.technet.com/rob/ so you can easily find everything.

There was good feedback on Powershell and I hope you are stimulated into finding out more. There are plenty of posts on this blog about scripting and many other articles readily available on the internet, but if you do have a powershell problem let me know and I will try and blog about it.

You can find my slide deck here.

Filed under: ,
The problem with len()?
03 October 09 10:31 PM | MartinBell | 1 comment(s)
I read the blog post by Madhivanan and it reminded me of a documented behaviour of the LEN function which a lot of people tend to forget.

If you had the following script:

SET NOCOUNT ON
SET ANSI_PADDING ON
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' );

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

You get the results:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC            ! 3           15             

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC            ! 3           15             

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC   !          3           6


Which shows that the length function will remove any trailing blanks at the end of the strings. This could be a problem if you had trailing blanks and use MAX(LEN(col1)) to determine the size of a variable used to hold a concatenation. e.g.

SET NOCOUNT ON
SET ANSI_PADDING ON
DECLARE @a char(6)

CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' ); 

SET @a = (SELECT TOP 1 col1 + col2 FROM tab_a) ;

SELECT '!' + @a + '!'  AS [col1andcol2]

DROP TABLE tab_a
GO

The resulting value in @a will be:

col1andcol2
-----------
!ABC   !

Changing the ANSI_PADDING to OFF will cause null-able character columns to truncate trailing blanks so you get the same results from LEN and DATALENGTH for null-able character and for varchar columns

SET NOCOUNT ON
SET ANSI_PADDING OFF
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' );

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]

FROM tab_a

DROP TABLE tab_a
GO


The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC!             3           3

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC            ! 3           15

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC!             3           3

 

The documentation says "trailing blanks" and not "trailing white space" so characters such as tabs are still counted i.e.

SET NOCOUNT ON
SET ANSI_PADDING ON
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   '+CHAR(9), 'ABC   '+CHAR(9),'ABC   ' +CHAR(9));

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC              ! 7           15             

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC              ! 7           15             

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC      !         7           7

 
And:

SET NOCOUNT ON
SET ANSI_PADDING OFF
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   '+CHAR(9), 'ABC   '+CHAR(9),'ABC   ' +CHAR(9));

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC      !         7           7

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC              ! 7           15              

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC      !         7           7

 
Filed under: