Translate

Thursday, 10 May 2012

How to see all string characters with their ASCII codes

When i analyzes some databases, i can see differences in stored procedures. SSMS 2012 suggests me to change CR/LF new line type. What would it can be?
To analyze differences, i wrote very simple code to see all SQL module definition characters together with their ASCII code values.
DECLARE @Object_Name VARCHAR(256)= 'Your object'
DECLARE @Definition NVARCHAR(MAX)
SELECT  @Definition = m.definition
       FROM    sys.all_sql_modules M
       WHERE   OBJECT_NAME (M.object_id) = @Object_Name;
;
WITH    H AS ( SELECT   SUBSTRING(@Definition, 1, 1) AS Char ,
                        ASCII(SUBSTRING(@Definition, 1, 1)) AS Code ,
                        1 AS Position
               UNION ALL
               SELECT   SUBSTRING(@Definition, H2.Position + 1, 1) AS Char ,
                        ASCII(SUBSTRING(@Definition, H2.Position + 1, 1)) AS Code ,
                        H2.Position + 1 AS Position
               FROM     H H2
               WHERE    H2.Position < LEN(@Definition)
             )
    SELECT  *
    FROM    H
OPTION  ( MAXRECURSION 0 )
The result should look like this:
You can set @Definition variable as your custom string and see all characters and ASCII codes if you need.

Wednesday, 29 February 2012

Keyword or statement option 'XMLNAMESPACES' is not supported in this version of SQL Server.

When you will prepare database migration from SQL Server standalone edition to SQL Azure, there will be many compatibility issues. One of them is unsupported namespaces inside of XMLs.

You can find a list of unsupported Transact-SQL statements here.

As long as Microsoft does not planning to include namespace support in SQL Azure, we still need to manage this issue.
So assume we have an Excel XML looking like this:
DECLARE @XML xml='


 
       1     SomeText1     SomeText2     SomeText4     SomeText5    
'
One of possible ways is to convert XML to NVARCHAR(MAX) type, then remove namespaces and prefixes using REPLACE function, and then convert it back to XML type.
In this case your code looks like this:
DECLARE @XML_WithNoNamespace_Char XML = 

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(@XML AS NVARCHAR(MAX)),
                                                              'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"',
                                                              ''), 'ss:', ''),
                                                              ' xmlns:o="urn:schemas-microsoft-com:office:office"',
                                                              ''), 'o:', ''),
                                                              'xmlns:html="http://www.w3.org/TR/REC-html40"',
                                                              ''), 'html:', ''),
                                                           'xmlns="urn:schemas-microsoft-com:office:spreadsheet"',
                                                           ''),
                                                   'xmlns:x="urn:schemas-microsoft-com:office:excel"',
                                                   ''), 'x:', '')
Let us see test result:
SELECT 
       @XML.exist('/Workbook/Worksheet/Table/Row/Cell/Data[text()="SomeText1"]') AS [Is_Found_In_Old_XML],
       @XML_WithNoNamespace_Char.exist('/Workbook/Worksheet/Table/Row/Cell/Data[text()="SomeText1"]') AS [Is_Found_In_New_XML]
As expected, in first case row was not found, but in second case required row was found.

Another way is to extract data using XQuery namespace declaration predicate. So we can get all required data - Row number,Cell number and Cell value using numbers table which should be populated from 1 to XML`s row count. I used cross joined sys.objects table for this but definitely you can find some other sequence generating algorithms (or if you have SQL Server 2012 version, you can use built-in sequence generator).

Code looks like this:
DECLARE @NumbersOnTheFly TABLE (Number INT PRIMARY KEY);

WITH Numbers
AS (
 SELECT ROW_NUMBER() OVER (
   ORDER BY O1.[object_id]
    , o2.[object_id]
   ) AS Number
 FROM sys.[objects] O1
 CROSS JOIN sys.[objects] O2
 )
INSERT INTO @NumbersOnTheFly
SELECT Number
FROM Numbers
WHERE Number <= @XMl.query('
  declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
   count(/ss:Workbook/ss:Worksheet/ss:Table/ss:Row)').value('.', 'bigint');

WITH C
AS (
 SELECT N.Number
  , @XMl.query('
     declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
      for $row in /ss:Workbook/ss:Worksheet/ss:Table/ss:Row[position()=sql:column("N.Number")]/ss:Cell
      return  {$row/ss:Data[1]/text()} 
      ') Cell
 FROM @NumbersOnTheFly N

 )
SELECT C.Number AS RowNumber
 , ROW_NUMBER() OVER (
  PARTITION BY C.Number ORDER BY a.b
  ) AS CellNumber
 , b.value('.', 'NVARCHAR(1000)') AS CellValue
FROM C
CROSS APPLY C.Cell.nodes('Cell') a(b)

Which way to choose to process XML with namespaces on SQL Azure? You should check performance to see which one is best for your XMLs.
Anyway, we are still waiting for XML namespaces support in SQL Azure.

Thursday, 28 April 2011

How to preview large string

Often you need to debug dynamic SQL statement. Simplest way to do this is to print statement. Because of 8000 char limit (400 for unicode), it is hard to do in "standard way".

here is an example:
DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

PRINT @Very_big_string 
Last result will be 0800 - 4 chars  for digits and one for end line - char(13) (4+1)*5=4000

There are some methods to print string by UDF etc. I propose simpler method: print XML which will contain your string (xml is not limited by char, by size in SSMS settings only - default is 2MB
So here is an example for this string:
DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

SELECT CAST(''+@Very_big_string+'' as XML)
Here you can preview your string in XML viewer.

Disadvantage of this method is that all standard XML symbols like <,>,! etc are changed to ampersand sequences (eg > is changed to &dt;)

Wednesday, 27 April 2011

How to extract substrings limited with delimiters.

If you need extract from really big string small strings which are limited by delimiters, so for you can be useful this example:

DECLARE @text AS NVARCHAR (MAX) = '***[ddd]***
dfdf
fdfdfdfdfdf
***[fff]***
4545445
45454
***[ahaASSDAD]***

DFDFDF
***[SOME   TEXT
';

DECLARE @PatBegin AS VARCHAR (1000) = '***[';

DECLARE @PatEnd AS VARCHAR (1000) = ']***';

WITH   C1
AS     (SELECT 0 AS Num
        UNION ALL
        SELECT 0),
       C2
AS     (SELECT C1.Num
        FROM   C1 CROSS JOIN C1 AS C2),
       C3
AS     (SELECT C2.Num
        FROM   C2 CROSS JOIN C2 AS C3),
       C4
AS     (SELECT C3.Num
        FROM   C3 CROSS JOIN C3 AS C4),
       C5
AS     (SELECT C4.Num
        FROM   C4 CROSS JOIN C4 AS C5),
       numbers
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY o.Num) AS Number
        FROM   C5 AS o),
       counts
AS     (SELECT COUNT(*) AS CNT
        FROM   numbers
        WHERE  PATINDEX(@PatEnd, @TExt) = 1),
       c
AS     (SELECT N.Number AS CHARBegin
        FROM   numbers AS N
        --CROSS JOIN numbers N2
        WHERE  SUBSTRING(@text, N.Number, LEN(@PatBegin)) = @PatBegin),
       d
AS     (SELECT c.CHARBegin,
               T.CHARBegin AS CharEnd,
               ISNULL(c.CHARBegin + LEN(@PatBegin) + NULLIF (CHARINDEX(@PatEnd, SUBSTRING(@text, c.CHARBegin + 1, ISNULL(T.CHARBegin, 2E7) - C.CHARBegin)), 0), 2e7) AS FactEnd
        FROM   c OUTER APPLY (SELECT TOP 1 C2.CHARBegin
                              FROM   C AS C2
                              WHERE  C2.CHARBegin > C.CharBegin) AS T)
SELECT SUBSTRING(@text, d.CHARBegin + LEN(@PatBegin), d.FactEnd - [d].[CHARBegin] - LEN(@PatBegin) - LEN(@PatEnd)) AS String_OUT,
       [d].CHARBegin
FROM   d;

here you have 2 delimiter - @PatBegin and @PatEnd - begin and end delimiters.
Result here will be:










UPDATE (09-01-2012)
Fixed logic to handle lack of last closing delimiter

Dynamic repeatable character replacement

Sometime you need to eliminate duplicated char in string. Static replacement like
SET @Output_String=REPLACE(@teststring,'__','++')
is good for not big and predictable values. But what if you need to change replaced chars dynamically or its quantity is big enough?
In that case you can use dynamic replacement. Here is an example:


SET @Output_String=REPLACE(@teststring,'__','++')

DECLARE @Duplicate NVARCHAR(100)= '#$'
DECLARE @TestString NVARCHAR(MAX)= 'test_test__f##f2$$g'
DECLARE @Replacement NVARCHAR(MAX)= ''
DECLARE @OutputString NVARCHAR(MAX)= @teststring ;
WITH    numbers
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o.object_id, o2.object_id ) Number
               FROM     sys.objects o
                        CROSS JOIN sys.objects o2
             ),
        chars
          AS ( SELECT   SUBSTRING(@Duplicate, 1, 1) CHAR ,
                        CAST(1 AS INT) [LEVEL]
               UNION ALL
               SELECT   SUBSTRING(@Duplicate, numbers.Number, 1) CHAR ,
                        CAST(numbers.Number AS INT) [LEVEL]
               FROM     numbers
                        JOIN chars ON chars.Level + 1 = numbers.Number
               WHERE    LEN(SUBSTRING(@Duplicate, numbers.Number, 1)) > 0
             ),
        Replicated
          AS ( SELECT   REPLICATE(CHAR, numbers.number) Repl ,
                        numbers.Number
               FROM     chars
                        CROSS JOIN numbers
             )
    SELECT  @OutputString = REPLACE(@OutputString, Repl, @Replacement)
    FROM    replicated
    WHERE   number <= LEN(@TestString)

SELECT  @OutputString
You can modify @Duplicate string which is storage for replaced characters. Also @Replacements is a string which will be inserted instead of duplicated chars

Tuesday, 19 April 2011

Microsoft releases cumulative update #7 for SQL Server 2008 R2

Microsoft releases cumulative update #7 for SQL Server 2008 R2. After this update, your SQL will have version 10.50.1777.0
You can check this by running query:
SELECT  SERVERPROPERTY('ProductVersion') as ServerVersion

You can find article related with this Update on Microsoft page: http://support.microsoft.com/kb/2507770.
Most critical fixes:

Database Engine :


  • Error message when you try to bulk insert data into a table that contains a DATE type column that uses a US date format in SQL Server 2008 or in SQL Server 2008 R2: "Bulk load data conversion error
  • "CPerIndexMetaQS::ErrorAbort - Index corruption" error when you try to update a partitioned view in SQL Server 2008 R2
  • A common table expression-based query that uses the CHANGETABLE function runs very slowly in SQL Server 2008 Service Pack 1 or SQL Server 2008 R2
  • A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2
  • SQL Server Agent job runs incorrectly if it is scheduled to run every hour on the last day of every week or month in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2
  • Error 7359 when you run a query against a user-defined function or a view that uses a synonym on a linked server in SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2
  • Assertion error when you try to recover a database in SQL Server 2008 R2
  • "There is no function '{urn:schemas-microsoft-com:xml-sql}:variable()" error for an XQuery query that updates the value of an XML column in SQL Server 2005 or in SQL Server 2008 R2
  • PowerPivot window does not open when you open an .xla or .xlam file in Excel 2010 if PowerPivot for Excel 2010 is installed
  • SQL Server Browser service periodically does not respond to incoming requests
  • Application performance issue when a query references temporary tables that are created in a session in SQL Server 2008 R2
  • The Intellisense feature in SQL Server Management Studio for SQL Server 2008 R2 may stop working after you install Visual Studio 2010 Service Pack 1


Reporting services:


  • Performance decreases after you move a large report to SQL Server 2008 R2 Reporting Services
  • Report builder 3.0 stops responding after you run a query more than two times against a report model in SSRS 2008 R2
  • Performance decreases after you move a report that contains a large multi-select drop-down parameter list to SQL Server 2008 R2 Reporting Services


SSIS:


  • You can now use Package Installation Wizard (Dtsinstall.exe) to run the deployment manifest file of an SSIS package when SSIS 2008 or SSIS 2008 R2 is not installed
  • Column of a Flat File source is not imported if the next column data is one byte longer than the output column width in SSIS 2005, SSIS 2008 or in SSIS 2008 R2

Friday, 8 April 2011

How to transpose table(change columns to rows).

One of most discussed question about TSQL algorithms is „How to transpose (change columns into rows) a table?” Most solutions based on static code which is adapted to your table. For every table, you need to write different code. Some authors propose using PIVOT statement, but it is not very handy to use this because PIVOT uses rows aggregation so avoiding this is always so easy.
Another question is "What if i need not only data change but gets columns/rows names too?” I you read my previous posts so you know which method we will use: XML-based.
This is how it looks:


DECLARE @xml XML ,
    @RowCount BIGINT
CREATE TABLE #Table
    (
      Column#1 INT ,
      Column2 NVARCHAR(MAX) ,
      Column3 DECIMAL(15, 2)
    )
CREATE TABLE #TempTable
    (
      RowID BIGINT ,
      CellId BIGINT ,
      Value NVARCHAR(MAX) ,
      ColumnName NVARCHAR(MAX)
    )
DECLARE @sSQl NVARCHAR(MAX)= 'SELECT (SELECT DISTINCT ColumnName FROM #TempTable WHERE CellId=Cell.CellId) as ColumnName,'
INSERT  INTO #Table
        SELECT  5 ,
                'Column_1_Test_String' ,
                99.99
INSERT  INTO #Table
        SELECT  9 ,
                'Column_2_Test_String' ,
                NULL


SET @xml = ( SELECT * ,
                    Row_Number() OVER ( ORDER BY ( SELECT   1
                                                 ) ) Rn
             FROM   #Table Row
           FOR
             XML AUTO,
                 ROOT('Root') ,
                 ELEMENTS XSINIL
           ) ;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi),RC AS
   (SELECT COUNT(Row.value('.', 'nvarchar(MAX)')) [RowCount]
   FROM @xml.nodes('Root/Row') AS WTable(Row))
,c AS(
SELECT b.value('local-name(.)','nvarchar(max)') ColumnName,
  b.value('.[not(@xsi:nil = "true")]','nvarchar(max)') Value,
  b.value('../Rn[1]','nvarchar(max)') Rn,
  ROW_NUMBER() OVER (PARTITION BY b.value('../Rn[1]','nvarchar(max)') ORDER BY (SELECT 1)) Cell
FROM
 @xml.nodes('//Root/Row/*[local-name(.)!="Rn"]') a(b)

 ),Cols AS (
 SELECT  DISTINCT c.ColumnName,
  c.Cell
 FROM c
 )
 INSERT INTO #TempTable (CellId,RowID,Value,ColumnName)
 SELECT Cell,Rn,Value,REPLACE(c.ColumnName,'_x0023_','#')
 FROM c
 
SELECT  @sSQL = @sSQl
        + '(SELECT T2.Value FROM #Temptable T2 WHERE T2.CellId=Cell.CellID AND T2.Rowid='
        + CAST(T.RowId AS NVARCHAR) + ') AS Row_' + CAST(T.RowID AS NVARCHAR)
        + ','
FROM    ( SELECT DISTINCT
                    RowId
          FROM      #TempTable
        ) T
SET @sSQl = LEFT(@sSQL, LEN(@sSQL) - 1)
    + ' FROM (SELECT DISTINCT CellId FROM #TempTable) Cell'
EXECUTE sp_Executesql @sSQl
--here you will have your output
DROP TABLE #Table
DROP TABLE #TempTable
Description:
Lines 1-24: declaring variables and filling up source table:

Lines 25-33: writing source table as XML into XML variable










Lines 34-52: filling up ##Temptable (temporary table). It is used for debug purpose only. If you test this solution, you can avoid this and implement it as next CTE in chain of sub-queries.


Lines 54-63: Create dynamic SQL statement to prepare transposed output

Line 64: Executing dynamic SQL. Here we have next output:







Lines 66-67 Dropping temporary tables.

Here I described how you can easily transpose table without knowing nothing about source table structure. Based on code above,you can prepare stored procedure which will get XML generated from table and it will return transposed XML. In such way you can easily transpose any of your tables from any place in your code.