0

The Easy Way to fix Server Collation in MS SQL Server

by Jon 27. September 2010 21:11

When you have different developers, customers and databases your database collation is bound to get out of sync.  Making sure you have matching collation is important because it can cause problems when it comes to running SQL queries and can impact performance if you are joining text fields.  There isn't an easy way to change the collation across an entire server because the collation is stored against the fields in each table.  I looked at a number of different scripts but found the following script was the easiest way to fix the problem.  Simply set @toCollation to the collation you want in your database, and run the query.  The query will output a List of ALTER COLUMN SQL Queries which you can copy into a query window and execute to fix your database.

declare  @toCollation sysname 

SET    @toCollation = 'Latin1_General_CI_AS' --  Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +

       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +

       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'

            WHEN DATA_TYPE in ('text','ntext') then ''

            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 

             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )

            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END

       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE

                                           WHEN 'YES' THEN 'NULL'

                                           WHEN 'No' THEN 'NOT NULL' 

END

FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES

ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME

AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA

WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')

AND TABLE_TYPE = 'BASE TABLE'

 and COLLATION_NAME <> @toCollation

You will find most of the queries will work, for the queries that dont work due relational joins you can open the table in enterprise manager in design mode and fix the small number of remaining offending fields manually really quickly.  Within moments you have a database with matching collation accross all its tables.

 

Tags: , ,

SQL | MSSQL Server

5

The Asp.Net Vulnerability and DotNetBlogEngine.Net

by Jon 20. September 2010 21:34

A FIX HAS NOW BEEN RELEASED BY MICROSOFT, download from here

Looking at ScottGu's Post and DotNetBlogEngine.Net configuration it looks like DotNetBlogEngine may be one of the Web applications that is vulnerable in the out of the box configuration (I'm not 100% sure and I cant find anyone on the dotnetblogengine forums about it).  Its still not especially clear but it looks like we need to take these steps to take to secure our blogs.  Better safe than sorry, until the underlying problem is fixed?

Replace the Custom Errors in Web.Config

<customErrors mode="RemoteOnly" defaultRedirect="~/error404.aspx" />
   <error statusCode="404" redirect="error404.aspx" />
</customErrors>

With

<customErrors mode="On" redirectMode="ResponseRewrite" defaultRedirect="~/fail.aspx" />

 

Then Add a new File Called fail.aspx to the root folder:

<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Security.Cryptography" %>
<%@ Import Namespace="System.Threading" %>

<script runat="server">
   void Page_Load() {
      byte[] delay = new byte[1];
      RandomNumberGenerator prng = new RNGCryptoServiceProvider();

      prng.GetBytes(delay);
      Thread.Sleep((int)delay[0]);
        
      IDisposable disposable = prng as IDisposable;
      if (disposable != null) { disposable.Dispose(); }
    }
</script>

<html>
<head runat="server">
    <title>Error</title>
</head>
<body>
    <div>
        An error occurred while processing your request.
    </div>
</body>
</html>

For more information on the Problem and details of the fix please look at scott's post

Tags: , ,

asp.net | BlogEngine | scottgu

0

Could not load file or assembly <AssemblyName> or one of its dependencies. An attempt was made to load a program with an incorrect format

by Jon 16. September 2010 22:00

One of the recurring problems I get when installing our ASP.Net system onto customers servers is this cryptic error:

Could not load file or assembly <AssemblyName> or one of its dependencies.  An attempt was made to load a program with an incorrect format

 

The first time I saw it it really stumped me for a couple of hours to find what the problem was, the stack trace, nothing made any sence.  I eventually tracked the only difference down to the server being 64bit.  IIS can run an asp.net application in either 64 or 32bit mode and most people leave both 32bit and 64bit support enabled when they compile there ASP.Net to automatically give them improved performance on a 64bit machine.  Unfortunatly if you are using a 3rd party componant that is older, perhaps an old non managed library it is more likely to be compiled for 32bit exclusivly.  This 3rd party library means your entire application wont be able to run in 32bit mode, and to make matters worse IIS running under 64bit has 32bit support disabled by default and your Compiled code will attempt to run in 64bits, which in turn causes the nasty error message above.

There are one of two solutions:

1. find the offending library and remove it from your Web Application

2. Enable 32bit support in IIS.  

a. Go to Application Pools in IIS
b. Right click on the relevant application pool
c. Press advanced settings
d. Set Enable 32-Bit Applications to True
e. Restart the Application Pool

This quick fix will buy you time so you can remove the offending library at your leisure

Tags: , , ,

asp.net | development | IIS | TechSupport

1

Select Top N using a stored procedure parameter

by Jon 13. September 2010 17:22

If you want to allow your users to select the top N rows to reduce the strain your database, but you want to be able pass N in as a parameter as a stored procedure just wrap the Parameter in () and it wont complain.

Alter Procedure cp_StoredProcedureName

@TopN as integer

as

select top (@TopN) Field1,Field2 from TablleName

where Field3 = "Something"

Tags:

SQL

0

Using MsBuild with TeamCity, (dotnet 3.5). Look Ma no Visual Studio!

by Jon 12. September 2010 01:08

I am currently moving our TeamCity to a different Virtual Server using this post by Mikael Henriksson.  Its is a fresh server install I thought it would be a great excuse to blog the steps you need to take to get MsBuild Working on your TeamCity.  In my previous post I described how to get started with Team City in under 5 Minutes, this short post follows on from that.

MsBuild gives you more flexibility when compiling your projects/solution.  However it isn't especially clear from documentation how you get your server configured to use MSBuild, or even what a basic MsBuild file will look like.  I got there painfully through trial and error, if you want to get up and running with MSBuild in TeamCity without having to install Visual Studio 2008 at all on your server follow the following six steps:

1. Install the MsBuild Community Tasks via the Msi Installer

2. Download an Install Windows SDK for Windows Server 2008 and .NET Framework 3.5 on your server.  During the install you only need to Select the DotNet Development Tools to be installed.

3. Update the Build Targets so you can build the projects you work on your Server.  You need to copy the relevant files from your development machine to your Build Server by updating files in "C:\Program Files (x86)\MSBuild\Microsoft\"

4. Create the SolutionName.msbuild in the root directory of your solution, where SolutionName is the name of your solution.  You will need to update "SolutionName.sln" inside the MsBuild File to point at your solution.

<?xml version="1.0" encoding="utf-8"?> 

<Project DefaultTargets="ReleaseBuild" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> 

<Import Project="$(MSBuildExtensionsPath)\MSBuildCommunityTasks\MSBuild.Community.Tasks.Targets"/> 

    <Target Name="ReleaseBuild"> 

        <Message Text="Building Your Solution"/> 

        <MSBuild Projects="SolutionName.sln" Targets="Rebuild" />

    </Target> 

</Project>

5. Go to your TeamCity and Edit your Build Runner so is is MsBuild, Change your Target to Release Build to Match the Target in the MsBuildFile, and update any command line parameters you may need. I am using an additional commandline parameter /p:OutDir=%teamcity.build.checkoutDir%\target\ which will compile a solution full of separate webapplications to a single target directory.

6. When you are happy you have the MsBuild File and TeamCity Build Runner correctly matching, commit your msbuild file to SVN.  Your Build will be automatically trigggered and MsBuild will be used to compile your solution instead of Visual Studio.  

This should give you alot more flexibility in future.  If this doesn't work for you or if I have made a mistake just leave me a comment and I will improve the post for others in future.

Tags: , ,

ddd | MsBuild | SVN | TeamCity.Net

Powered by BlogEngine.NET 2.0.0.36
Original Design by Laptop Geek, Adapted by onesoft, and finally some tiny tweaks by JonAlb