2011-12-01

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.

2011-09-27

Mailinator.com

If you ever need to test a system with lots of different unique email addresses, then Mailinator may be just the thing you need.

Whenever you send an email to some random name @mailinator.com, you can go into the mailinator web site, type the email address (without the “@mailinator.com” part), and log on to check the mail. You don’t even need to register.

Some scenarios may be:

  • Register users with unique emails
  • Test confirmation mail functionality
  • Test forgotten password functionality

2011-09-15

Adding a ToXmlString() extension method to all objects so that they may be viewed as Xml when debugging

Working with soap Request and Response objects I have found that it is a nice thing to be able to check the request, and copy it into SoapUI to test what really is going on. So my first thought was to create an extension method and put it on all my request objects, but it takes some work, so I went for the lazy solution: add extension method on System.Object. Now this may not work on all objects, I think they need to be serializable at least, but it seems to work on my request objects. So here’s my code, just use it with caution, as it may have side effects:

using System;
using System.Text;
using System.Xml.Serialization;
using System.IO;
using System.Xml;

public static class ExtensionMethods
{
    public static string ToXmlString(this System.Object obj) { return serializeObject(obj); }
   
    private static string serializeObject(object obj)
    {
        XmlSerializer xs = new XmlSerializer(obj.GetType());
        StringWriter sw = new StringWriter();
        XmlTextWriter w = new XmlTextWriter(sw);
        w.Formatting = Formatting.Indented;
        w.Indentation = 3;

        xs.Serialize(w, obj);
        w.Flush();
        w.Close();

        return sw.ToString();
    }
}

 

2011-09-07

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.

2011-05-23

IIS AppPool and ApplicationPoolIdentity

When you create a new web site in IIS 7.5, an Application Pool is created by default with the same name as the web site.

After Windows Server 2008 SP2, you may select “ApplicationPoolIdentity” as the account to run the app pool with.

ApplicationPoolIdentity is an account on the local machine with the same name as the Application Pool.

If you want to give your web application access to files or other resources outside your web root directory, perform these steps:

  • Right Click the file or folder (or registry key?) you want to give access to, and select Properties from the dropdown.
  • Go to the Security Tab, click the “Edit…” button.
  • Click “Add…”.
  • Click “Locations…” and select the local machine.
  • Under “Enter the object names to select…”, type IIS AppPool\<ApplicationPoolIdentity>, where <ApplicationPoolIdentity> is the same as the Application Pool name.
  • Give rights as needed, click OK as many times as it takes, etc.

I think this should be enough to get you started.

Source: http://learn.iis.net/page.aspx/624/application-pool-identities/

2011-04-15

Setting up Oracle Instantclient Basic

I have been working on setting up an oracle instant client basic connection, using the following article:

http://www.dbatoolz.com/t/installing-oracle-instantclient-basic-and-instantclient-sqlplus-on-win32.html

Ran into one problem which took me a little time to figure out. The article says to add an environment variable as a “User Variable”. Since I am running a web site that has an application pool running on the NETWORK SERVICE account, and it is not possible to log on using that account and set the User Variables, they will not be available to my web app.

The solution is to add all variables as System Variables (using the lower part of the Environment variables window).

Just to be safe, I also added a variable for “ORA_HOME” with the same value as the TNS_ADMIN environment variable, namely the path to the instantclient (in my case “instantclient_11_2”) folder.

2011-04-11

System.Security.Cryptography.CryptographicException: Key not valid for use in specified state

I got this message after I created a new user profile on my virtual machine (VMWare), and tried to compile my ORM project (using Telerik OpenAccess ORM). The error was wrapped as: OpenAccess Error: Key not valid for use in specified state.

This is not a problem for OpenAccess only though. I’ve seen that it also may be a problem for other applications, like SQL Server Reporting Services.

The solution:

  1. Go into the folder (Windows XP) at:
    <drive>:\Documents and Settings\<your windows user account name>\Application Data\Microsoft\Crypto\DSS\<your machine SID>\
  2. Delete the file there. It should have some cryptic name (like a GUID).
  3. Restart IIS (IISRESET).
  4. Recompile. It worked for me.

 

References:

2011-03-29

My experiences from installing EPiServer CMS 5 on Windows XP

Installing EPiServer CMS 5 on a Windows XP developer VM turned out to be a bit of hazzle, maybe because XP is not a “supported” OS any more?

Ok, so first installed the EPiServer CMS5 5.2 R2 application files to the default location. No problem so far.

Since I am working on an existing web site, I created a folder on disk and got latest version from TFS.

I then copied the EPiServer binaries into my bin-folder (XCOPY-deployment), and attempted to browse the website. I got this message:

Could not load type 'EPiServer.UI.WebControls.ControlAdapters.HtmlHeadAdapter'.

This error message originated from the App_Browsers / AdapterMappings.browser file.

I then went into my web.config file and verified that my VPP paths were mapped correctly to files in the Program Files\EPiServer\CMS\5.2.375.236\Application folder.

So my next thought was to open the edit/admin mode. Alas, it looked all messed up, missing both styles and graphics.

This gave me my next clue, because in EPiServer 4.x you had to remap the 404 http error status to /utils/NotFound.aspx to make the admin/edit modes look as they should, as I described in my previous posting: http://stgaup.blogspot.com/2009/10/styles-missing-in-episerver-editadmin.html.

That trick does not seem to work on CMS 5 (only works on EPiServer 4.x). Now in stead you need to do something a bit different:

  1. Open the “Properties” for your web site in the IIS manager.
  2. On the “Home Directory” tab, click the “Configuration…” button.
  3. On the “Mappings” tab, click the “Add” button.
  4. Click the “Browse” button next to the “Executable” text box.
  5. Navigate to the Windows/Microsoft.NET/Framework/v2.0.50727 folder.
  6. Select “Dynamic Link Libraries” in the “Files of type” dropdown.
  7. Select the “aspnet_isapi.dll” file, and click the “Open” button.
  8. Fill in the “Extension” as “.*” (dot-star).
  9. TRICK: There’s a bug in Windows XP that has never been fixed, that causes the “OK” button to remain disabled. Click in the “Executable” text box again and then voila, the “OK” button is enabled.
  10. Click OK as many times as it takes to get back to the “Internet Information Services” application.

Now try to reload your admin/edit mode. It should work.

Some good links:

2011-03-04

Themed CheckBox check mark

Themes for ASP.NET applications came probably in 2005 with .NET 2.0, so it’s been around for a while.

I am creating different themes to adapt my web appliction to different clients.

So the problem I came across with the ASP CheckBox control is that setting the size of the control does not change the size of the check mark. Neither does it have any properties for setting the size.

Also, I want to be able to theme my control, so the control needs to have some kind of property that could be set in the skin file, and there is no such property on the standard asp CheckBox. It does have a method for setting properties on the inner input control, however, but as I said, I need a property.

The CheckBox control is rendered inside a span tag, like this:

<span class="checkbox">
  <input id="ContentPlaceHolder1_chkPersistLogin"
         type="checkbox"
         name="ctl00$ContentPlaceHolder1$chkPersistLogin" />
  <label for="ContentPlaceHolder1_chkPersistLogin">
Remember me</label>
</span>

Now, as you can see, if you set the CssClass property of the control to something, then it will be applied to the span, not to the inner controls directly.

So, as I mentioned, the CheckBox control does have a way of accessing the inner input control. It is by using the InputAttributes propertys methods. For example, the following code will add a “class” attribute to the <input type=”checkbox” /> tag:

MyCheckBox.InputAttributes.Add("class", “MyCheckBoxCssClass”);

If I want to set the class on my inner input control, then I could create my own custom control that inherits from the asp CheckBox control. Here’s my CheckBoxPlus class:

using System;
using System.Web;
using System.Web.UI.WebControls;

namespace MyWebApp.WebUI.Common.CustomControls
{
    public class CheckBoxPlus : CheckBox
    {
        private string _inputCssClass;

        public string InputCssClass
        {
            get { return _inputCssClass; }
            set
            {
                _inputCssClass = value;
                this.InputAttributes.Add("class", _inputCssClass);
            }
        }
    }
}

To be able to use my custom control in a page, I need to add a directive to the web forms where I want to use it:

<%@ Register TagPrefix="custom" Assembly="MyWebApp.WebUI" Namespace="MyWebApp.WebUI.Common.CustomControls" %>

Now I can use the control im my page, and set the new property:

<custom:CheckBoxPlus ID="chkPersistLogin" runat="server" Text="Remember me" CssClass="checkbox" />

The last part is to enable theming/skins for the control. The only thing you need to do is to add the same Register directive at the top of the skin file, and then you can create a template for you control in the usual way. Here’s an example skin file:

<%@ Register TagPrefix="cd" Assembly="MyWebApp.WebUI" Namespace="MyWebApp.WebUI.Common.CustomControls" %>
<asp:Button runat="server" />
<asp:TextBox runat="server" />
<asp:Label runat="server" />
<asp:Panel runat="server" HorizontalAlign="Center" />
<cd:CheckBoxPlus InputCssClass="checkboxplus" runat="server" />

When I load my page now, the rendered HTML looks like this:

<span class="checkbox">
  <input id="ContentPlaceHolder1_chkPersistLogin"
         type="checkbox"
         name="ctl00$ContentPlaceHolder1$chkPersistLogin"
         class="checkboxplus" />
  <label for="ContentPlaceHolder1_chkPersistLogin">Remember me</label>
</span>

As you can see, the theme sets the InputCssClass, and thus I am able to control the size of the check mark of the CheckBox control.

2011-02-11

Installing Windows Phone 7 CTP on Windows XP

Source: http://social.msdn.microsoft.com/Forums/en-US/windowsphone7series/thread/6657c1ff-45a6-466a-b20d-f5640e3f0c1f/

Thanks to Oran Dennison for this solution.

Please be awear that Microsoft does not support running the Windows Phone 7 CTP Tools on Windows XP. I found this solution, and I am using it at my own risk, as should you.

Workaround for installing Windows Phone 7 CTP on Windows XP:

1.Download the Windows Phone Developer Tools CTP Refresh from here: developer.windowsphone.com 
2.Extract the contents of the setup package by running vm_web.exe /x and choosing a path to extract to
3.Go to the folder you extracted to in step 2 and open the file baseline.dat in notepad
4.Look for the section named [gencomp7788]
5.Change the value InstallOnLHS from 1 to 0
6.Change the value InstallOnWinXP from 1 to 0
7.Save and close baseline.dat
8.Run setup.exe /web from the folder you extracted to in step 2

2011-01-14

Windows x64 knowledge

I have learned about Windows 64-bit:

  • On a 64-bit Windows machine, there are two different locations for programs to be installed:
    • Programs
      • For 64-bit programs
    • Programs (x86)
      • For 32-bit programs
  • There are two different ODBC managers:
    • the 64-bit version is accessed from Control Panel / Administrative Tools
    • the 32-bit version may be found under [SysDrive]:\Windows\SysWOW64
  • Oracle 10g had a problem with the parentheses in the “Programs (x86)” folder name, for which there were released a patch.
  • The BI Development Studio (VS2008) which comes with SQL Server 2008 is 32-bit, and does not have easy access to 64-bit drivers. This goes also for the SSIS Package Designer.
    • If you build for “Any CPU” it might still work.
    • Or you could install both, and define ODBC connections with the same names using both of the ODBC managers.
  • (more info coming soon…. maybe)

64-bit Windows 2008 vs Oracle x64

Ok, so finally after exploring several ways to run a query on Oracle x64, I have succeeded.

First step is to install a 64-bit Oracle Client on the W2k8 server. For me it works with the Oracle 11g InstantClient v. 11.2, connecting to an Oracle 10.x.. database.

I am using a 32-bit developer machine, using the System.Data.OracleClient classes, and everything seems to work.

My connection string is like this:
<connectionStrings>
    <add name=”oracle” 
            connectionString=”Data Source=TNSNAME;UserId=uid;Password=pwd;” />
</connectionStrings>

So everything works nicely, until I move my compiled code to the x64 server.
I now get this error:

Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Solution:
Before compiling, go into the project properties and set the “Platform target” property to “Any CPU”.
Compile, then copy your assemblies to the server. Looks like an extra DLL-file has been added to my bin-folder (Oracle.DataAccess.dll).