Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

Storing TSQL Queries in a table without losing the formatting

Dec 17 2010 2:49PM by Jacob Sebastian   

One of the applications we worked on recently had to deal with storing TSQL queries in a table and executing them based on some business logic. It was not a very complicated project, but the development team had a tough time with the formatting of the queries stored in the table. This was a huge problem when the queries were to be modified.

The Problem

The developer writes a well formatted TSQL code and stores it into the table. Later on when a change request comes, he retrieves the query text from the table and finds that all the formatting is lost.

Why does it happen?

Well, the formatting is not really lost. When the developer stores a well formatted query into the VARCHAR(MAX)/NVARCHAR(MAX) column of a table, the formatting is also stored. The problem is the way he retrieves it.

The common way of retrieving the query text is to run a SELECT query which will display the result in a grid view. The grid view does not maintain the special characters (line feed, carriage return). That is the reason why you are loosing the formatting.

An immediate workaround is to change the output to text and run the SELECT query. This works if the TSQL queries stored in the table are small. If the queries are large, then it might truncate the queries.

Workaround

In this post, let us see a workaround that shows how to retrieve the query text without loosing the formatting. To see this in action, let us start by creating a table to store the queries.

USE tempdb
GO

IF OBJECT_ID('BRQueries','U') IS NOT NULL BEGIN
	DROP TABLE BRQueries
END

CREATE TABLE BRQueries(
	QueryID INT, 
	QueryText VARCHAR(MAX)
)

Next, let us write a well formed query.

SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id

Let us now insert this query into the table

INSERT INTO BRQueries (QueryID, QueryText)
SELECT 1, 
'
SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id'

Next, Let us run a SELECT query to retrieve the query text. If we use 'result to grid', we will loose the formatting. If we use 'result to text' we will get the correctly formatted text in this case. However, if the query is longer it might get truncated. If the table has several other columns and rows, the output will be completely messed up.

A workaround is to use to use the XML function processing-instruction(). Run the following query.

SELECT
	QueryID,
	(
		SELECT QueryText AS 'processing-instruction(q)' 
		FROM BRQueries b
		WHERE b.QueryID = a.QueryID
		FOR XML PATH(''), TYPE
	) AS Query
FROM BRQueries a

Take the result of the query into a grid either by pressing CTRL+D or from the toolbar and you will see the result as follows:

query

Click on the desired row and it will open up the query text with the original formatting.

<?q 
SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id
?>

You can strip of the XML tags from the top and bottom of the query text and go ahead with editing. Well, this is not a 'great' method but a quick and dirty trick that might come handy at times.

Tags: XML, FOR XML PATH, TSQL, SQLSERVER, BRH, #XML, #TSQL, #SQL Server,


Jacob Sebastian
1 · 100% · 32235
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

9  Comments  

  • I faced a similar problem, and generally use the Print operator to show the SQL command. Print operator is not suitable for large tables (unless one uses a cursor), but if you want to edit it- you'll probably edit one each time.

    I'll try to use the FOR XML PATH('') too!

    commented on Dec 20 2010 2:09AM
    Geri Reshef
    141 · 1% · 354
  • Really nice trick. It is working fine. Thanks for sharing.

    commented on Dec 24 2010 6:27AM
    Hardik Doshi
    20 · 9% · 2863
  • Glad to know that it helped.

    commented on Dec 26 2010 5:23AM
    JacobSebastian
    47 · 4% · 1215
  • Jacob, can you please share what should "processing-instruction" do?

    commented on Dec 31 2010 4:00AM
    Hardik Doshi
    20 · 9% · 2863
  • I remember having had to implement a bit of a clumsy solution for this kind of problem many years ago because the product had to support other databases (including Oracle 8 - sic) and they implemented queries to reside in multiple rows in a table with the equivalent of varchar(4000). Thus I had to convert backwards and forwards between plain CR and displayed CR/LF in this SQL editor. There was also the problem of not breaking a string contained within single quotes. It was a nightmare. And thus XML and varchar(max) is a welcome relief. But there may be the seed of a TSQL challenge here. Let me sleep on that...

    commented on Jan 3 2011 1:06PM
    dishdy
    16 · 10% · 3269
  • @hardik, "processing-instruction" refers to some header information that is usually present on the header of XML documents: such as style sheet information, which is meant for the application that consumes the XML document. The 'real' use of this function (when used with FOR XML) is to generate additional processing information required for the XML document being generated.

    commented on Jan 3 2011 9:32PM
    JacobSebastian
    47 · 4% · 1215
  • The problem with using the XML features or the PRINT statement is when using SQL Server Management Studio, the output in SSMS is cut off after 8K (8196) characters.

    When I create build scripts, I'll create the object definitions as varchar(max) values, then execute them against the target database using sys.sp_ExecuteSQL. This way the build scripts can be executed against an arbitrary database (makes it very easy to deploy entire systems from dev to UAT to production).

    But it makes debugging the build scripts difficult.

    The solution: spsenddbmail. I'll cast the script to varchar(max) or nvarchar(max), then email it to myself. No 8K limit. I can see the exact code that is going to be run in an email.

    The hard part of all of this is handling the single-quote character. In order to get a script containing a single quote to run:

    SET @myvar = N'foo'

    You have to encode it like this:

    DECLARE @sqlscript nvarchar(max)

    SET @sqlscript = REPLACE(N'SET @myvar = N''foo''', '''', '''''''')

    Then to see the script to be run:

    EXEC msdb.dbo.spsenddbmail @recipient='email@foo.com', @subject='debugging', @body = @sqlscript

    commented on Jan 23 2011 10:10AM
    Marc Jellinek
    95 · 2% · 586
  • @mark, I think there is a little bit of misunderstanding here. Your statement "The problem with using the XML features or the PRINT statement is when using SQL Server Management Studio, the output in SSMS is cut off after 8K (8196) characters." is not entirely true. The XML data type can display values larger than 8K.

    By default, SSMS limits the size of XML to 2 MB but you can change it using the options page to 'unlimited' to support larger values.

    commented on Jan 24 2011 8:32AM
    JacobSebastian
    47 · 4% · 1215
  • Great job..

    commented on Oct 7 2011 11:53PM
    Ashraf
    227 · 1% · 197

Your Comment


Sign Up or Login to post a comment.

"Storing TSQL Queries in a table without losing the formatting" rated 5 out of 5 by 2 readers
Storing TSQL Queries in a table without losing the formatting , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]