Synchronizing SQL Server Stored Procedures between databases

In my earlier post Compare Stored Procedures in 2 SQL Server databases, I wrote about how it is possible to select from INFORMATION_SCHEMA to compare stored procedures.

The issue I am dealing with here is that during the development af a new system, the database stored procedures are likely to change between each release to a server (test, staging or production). You have made all the changes in your development database, and you just need to copy the changes to the other database. My old way to do this was to script each SP whenever it changed and link a section in the delivery slip to the script, which was quite tedious.

What I ended up with using in one of my current projects is the following steps:

1. Using SQL Server Management Studio, script all procedures from the source database as ALTER to the clipboard.
   
2. Connect a new query window to the target database and paste the script.
   
3. Run the script.
   
   It will fail for all the SPs that you have added to the source database, which are missing in the target database, but also it will update all existing stored procedures to the latest version.
   
   
4. Replace all “ALTER PROC” with “CREATE PROC” in the script and run it again.
   
   It will fail for all existing SPs in the target database, but more importantly, it will create all the SPs from the source database that are missing in the target database.
   
   
5. And Bob’s your uncle!

This, of course, should be easy to automate. Maybe I’ll come back with an application that does it in an upcoming post.

The routine above works for functions as well, and should/could also work with views, and maybe also tables.

Compare Stored Procedures in 2 SQL Server databases

Using the following script it is possible to get an idea if the stored procedures in two databases are equal.

SELECT A.ROUTINE_NAME, A.ROUTINE_DEFINITION, B.ROUTINE_DEFINITION
FROM sourcedatabase.INFORMATION_SCHEMA.ROUTINES A
LEFT JOIN targetdatabase.INFORMATION_SCHEMA.ROUTINES B ON A.SPECIFIC_NAME = B.SPECIFIC_NAME
WHERE RTRIM(LTRIM(SUBSTRING(A.ROUTINE_DEFINITION,CHARINDEX('CREATE',A.ROUTINE_DEFINITION),999))) <> RTRIM(LTRIM((SUBSTRING(B.ROUTINE_DEFINITION,CHARINDEX('CREATE',B.ROUTINE_DEFINITION,0),999))))
ORDER BY ROUTINE_NAME

Even if the routines have the same functionality, they may have some slight differences, like spaces in front of the CREATE PROCEDURE statement and also trailing spaces at the bottom. I have tried to remedy this by trimming and also comparing substrings where initial spaces have been removed. Still room for more improvement, I’m sure, but just a small idea.

Common Table Expression for Database Structure

When copying data from one database to another, breaking constraints is always a problem. If you do not insert data in a certain sequence, you will get foreign key violations. So I tried to work out a CTE that starts with all tables that have no foreign keys, then the tables referencing them, then the once referencing them again, and so on… So came up with this:

WITH CTE (name,object_id, lvl)
AS
(
    select name, object_id, 0 AS lvl from sys.tables
    where object_id NOT IN (select parent_object_id from sys.foreign_keys)
   
UNION ALL

    select tbls.name, tbls.object_id , lvl + 1 from sys.tables tbls
    join sys.foreign_keys keys on tbls.object_id = keys.parent_object_id
    join CTE on CTE.object_id =  keys.referenced_object_id
)
SELECT * FROM CTE
OPTION (MAXRECURSION 10000)

Note that this will not work if you have self-referencing tables, ie. tables that have a foreign key pointing to its own primary key. Also the same tables may appear many times because they reference the same tables. And also if the same table have many foreign keys it will appear many times.

The conclusion is that this does not solve my problem, but it was fun to create the CTE anyway.

Check if any new properties have been added in EPiServer 4.x

The scenario for this is that you have a Test server and a Production server. You are rolling out all changes that have been tested on the Test server to the Production server. You did, being a pro and all, of course make a note of all the changes that were done to properties on the Test server since the last rollout to Production, but just to make sure you want to check what new properties have been added.

Step 1

Add the Production server as a linked server in SQL Server Management Studio on the Test server, in this example called [LinkedServer].

Step 2

Create a new query on the Test server and paste the following query into it. You will need to substitute the names of the database and linked server with names from your environment.

-- CHECKS IF ANY NEW FIELDS HAVE BEEN ADDED ON THE TEST SERVER
-- THAT DO NOT EXIST ON THE PRODUCTION SERVER
USE EPiServerDb;
GO

IF EXISTS(
    SELECT testTable.pkID, prodTable.pkID
    FROM tblPageDefinition testTable
    LEFT JOIN [LinkedServer].[EPiServerDb].[dbo].[tblPageDefinition] prodTable 
        ON testTable.pkID = prodTable.pkID
    WHERE prodTable.pkID IS NULL)
BEGIN
    PRINT 'NEW PAGE PROPERTIES DETECTED!'
    SELECT pt.Name, pd.Name
    FROM tblPageType pt JOIN tblPageDefinition pd ON pt.pkID = pd.fkPageTypeID
    WHERE pd.pkID IN (
    SELECT testTable.pkID
    FROM tblPageDefinition testTable
    LEFT JOIN [LinkedServer].[EPiServerDb].[dbo].[tblPageDefinition] prodTable 
        ON testTable.pkID = prodTable.pkID
    WHERE prodTable.pkID IS NULL)
END
ELSE BEGIN
    PRINT 'NO NEW PROPERTIES DETECTED.'
END

Step 3

If any new properties have been added, you will see a list of the names of page templates and what properties are new.

This script was tested on EPiServer 4.61/62 only, but may also work for newer versions of EPiServer.

TSQL: Checking if an ID is in a Comma Separated String

Ok, so the scenario is that we have a list of IDs, maybe from a checkboxlist, and we want to get the records that match those IDs from a table. So for the sake of this example, I just assume that the list of IDs is passed to my stored procedure as a varchar(8000) string. Using Northwind as an example database, heres an example of how an SP that gets products could look like:

CREATE PROCEDURE GetProducts
@ListOfProductsAsCSVString varchar(8000)

AS

SET @ListOfProductsAsCSVString = ',' + @ListOfProductsAsCSVString + ',';

SELECT ProductID, ProductName FROM Products
WHERE CHARINDEX(',' + CAST(ProductID as varchar(10)) + ',', @ListOfProductsAsCSVString ) > 0;

So what I do is to first append a comma before and after the CSV-list. This is because I need to search for somthing that starts with a comma and ends with a comma, and usually a CSV-list doesn’t have a comma before the first element or after the last one. Then, in my select, I search using the CHARINDEX function for the ProductID prefixed and postfixed by a comma.

Now, please be aware that this could lead to a possible SQL Injection attack, if you use this procedure uncritically without validating the input before passing it to this stored procedure, so use with caution.

xp_ReadErrorLog (SQL 2005)

The xp_ReadErrorLog extended stored procedure allows you to display the logs for SQL Server 2005 amnd also (as it turns out) the logs for SQl Server Agent.

Usage:
xp_ReadErrorLog - shows the default log for SQL Server.
xp_ReadErrorLog 0,2 - shows the error log for SQL Server Agent (the second parameter means "Agent")

Parameters:
1 (int): Log Number
2 (int): 1 = SQL Server, 2 = SQL Server Agent
3 (string): Search string for searching for a log entry.
4 (string): Another search string.

Source: SQLTeam.com

T-SQL CONVERT datetime to varchar

This script gets current date, and converts it using all existing predefined formats between 1 and 255:

DECLARE @format int;
CREATE TABLE #temp (format int NULL, string varchar(20) NULL, date datetime NULL)
SET @format = 1;
WHILE @format < 256
BEGIN
    BEGIN TRY
        INSERT INTO #temp (format, string, date) VALUES(@format, CONVERT(varchar(20),getdate(),@format), getdate());
    END TRY
    BEGIN CATCH
    END CATCH
    SET @format = @format + 1;
END
SELECT * FROM #temp;
DROP TABLE #temp;

SQL Server 2005 XML - Msg 9402 (unable to switch the encoding)

Had a problem converting XML from a column of type text to type xml. Someone else luckily has had the same problem:
http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/.

The solution was to convert the column from type TEXT to NVARCHAR(max), and get rid of the "encoding" part of the root tag, something like this (slightly altered from the example mentioned above):

SELECT ID, CAST(
REPLACE(CAST(XmlTextColumn AS NVARCHAR(MAX)), 'encoding="UTF-16"', '')
AS XML).query('xpath to nodes') AS Node
FROM TableName

Another way to solve this problem is by adding a computed XML column:

ALTER TABLE [MyTable]
    ADD MyXMLColumn AS CAST(REPLACE(CAST(MyXMLStoredAsText AS NVARCHAR(MAX)), 'encoding="UTF-16"', '') AS XML)
    PERSISTED

If you add the persisted option, the value will be persisted with the table data, which will give better performance for read operations (slightly worse performance for insert/update operations).

Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.

Using SQL Server 2005 XML And Unpivot

I have a table with contracts stored as XML. Using the XML query possibilities in SQL Server 2005 together with the UNPIVOT keyword I can get statistics on different types of contracts.

My table has these columns:
ContractNumber int (Primary Key)
contractXML xml

My XML looks something like like this, and is stored in a column of type XML:

<Contract>
  <Customer>
  ...
  </Customer>
  <Order>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product3</Name>
      <Price>13.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product2</Name>
      <Price>23.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
  </Order>
</Contract>

To get a table with a max of 5 OrderItems as columns, I can use this query:

    SELECT
      ContractNumber,
      contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)') AS Product1,
      contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)') AS Product2,
      contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)') AS Product3,
      contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)') AS Product4,
      contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)') AS Product5
    FROM Contract

 

This is nice, but what if I want to get the number of each product sold? The answer is that I can use the UNPIVOT operator!
Something like this will do it:

SELECT Name, COUNT(*) AS [Count] FROM
(
    SELECT
        ContractNumber, col, Name
    FROM
    (SELECT
    ContractNumber,
    [col1] = contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)'),
    [col2] = contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)'),
    [col3] = contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)'),
    [col4] = contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)'),
    [col5] = contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)')
    FROM Contract) col
    UNPIVOT(
        Name
        FOR col
        IN ([col1],[col2],[col3],[col4],[col5])
    ) AS unpvt
) AS T
GROUP BY PackageName

The UNPIVOT operator gives the values in the 5 columns as 1 column.

If I had only the 1 row in my Contract table from the example above, the result would be:

Name	Count
Product1	3
Product2	1
Product3	1

This is a simple example, and it has a max number of ordered products per contract of 5. Could maybe be extended.

If you want a table with the counts for the different products as columns, then something like this would do the job:

SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract

This would give this result:

Product1	Product2	Product3
3	1	1

And we could of course UNPIVOT this result too:

SELECT
   ProcuctCount
FROM
(SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract) cols
UNPIVOT(
   ProductCount
   FOR cols IN (Product1, Product2, Product3)
) AS unpvt

This should give a table like this:

ProductCount

3

1

1