Posts Tagged “sql”

Follow

Determining SQL Server edition

   , 

Thanks to http://support.microsoft.com/kb/321185 and to Ariel (@Q) who have read it more carefully than I did, I learnt that there is a SERVERPROPERTY that you can query:

SELECT SERVERPROPERTY ('edition')

I expected to find Developer, but found Express instead.

Happy new year, here comes SQL

   ,  , 

One of the things occupying me is my work on my last seminar for the university, so I’d finally get my Bachelor’s degree.

The project is building an application for managing, uhm, a university. You reckon they figured out a way to prototype a new system for free … ?

Anyway, the seminar name is “Database Management Systems Workshop”, so one of the things I have to do is to present a full E/R-D even before I start coding. And UI design sheets. So no agile here :(

My aim is to be able to demonstrate some cool stuff though, like the usage of an advanced O/R mapper (NH), static and dynamic object-based querying (hql and Criteria API), some advanced T-SQL stuff (recursive set-based queries using CTE, and some fancy integrity enforcing triggers), smart caching with SqlCacheDependency, and more.

To make things interesting I decided to create a large-ish mount of fake data. Well, at least in terms of a non-production university-demo app. My DB currently holds 600K students and over 500 possible modules.

I’ll blog a bit on the ways I used to populate that data, and other data as well (such as the ModulePrerequisites table which is an adjacency table).

So, beware, some SQL might follow

Fake data for People table

  

Task Populate People table with fake data for demo purposes.

Assume the relevant tables look like that:

People (Id, LastName, FirstName, CityId)
Cities (Id, Name)

Step 1 - getting names I created a database called Useful, with two tables: FirstNames and LastNames.

Filled each with first and last names, that I found off sites in the internet.

I managed to get 808 first names, and 742 last names.

Step 2 - generating the data I wanted to fill the People table with the Cartesian Product of both name tables.

Out of 808 first names and 742 last names I’d get 599,536 rows using

SELECT
    f.Name AS FirstName,
    l.Name AS LastName
FROM
    FirstNames f,
    LastNames l

But I’d also like to have the CityId populated, with a random city.

The naive approach is

SELECT
    f.Name AS FirstName,
    l.Name AS LastName,
    (SELECT TOP 1 Id FROM City ORDER BY NEWID()) AS CityId
FROM
    FirstNames f,
    LastNames l

But the ORDER BY NEWID() bit is happening once for the whole select, so you end up with the same CityId for all you rows.

My end solution was:


INSERT INTO People (FirstName, LastName, CityId)

SELECT
    FirstName,
    LastName,
    CityId
FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY l.Name, f.Name) AS ROW,
            f.Name AS FirstName,
            l.Name AS LastName
        FROM
            Useful.dbo.LastNames l,
            Useful.dbo.FirstNames f
    ) AllNames,
    (
        SELECT
            Id AS CityId,
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROW
        FROM
            Cities
    ) C
WHERE 
    AllNames.ROW % 66 = C.ROW % 66

Let’s go over it:

  1. I add a ROW column to the “all names” temp-table. Due to the ORDER BY NEWID()) I’ll get a new sort on every select, so the ROW column is random enough.

  2. I add a ROW column to the Cities table. Same kind of randomness as 1

Now I inner join the tables on the ROW column on both tables, mod the number of possible cities (which is less than the names count), getting a random CityId for each row in the “all names” table (which is the Cartesian Product of FirstNames and LastNames)

Unique when not null (or not empty) in SQL Server

  

In SQL Server, you have two main types of Indexes, one that allow duplications, and one that does not.

However, if you want a rule like “I want to forbid duplications, except for null values” you do not have a built in feature for that, as the unique index will treat the NULL value as a real value, thus allowing up to a single row with that NULL.

Apart from refactoring the DB schema, the solution I usually was doing has been to create a trigger to deal with that:

CREATE TRIGGER dbo.People_Unique_NonNull_Email ON dbo.People AFTER INSERTAS BEGIN SET NOCOUNT ON DECLARE @Count INT SELECT @Count=COUNT(p.Id) FROM People p JOIN INSERTED i On i.Email = p.Email WHERE i.Email IS NOT NULL 
 IF @Count > 0  BEGIN RAISERROR ('Cannot put a duplicate email on People table', 16, 1) ROLLBACK TRANSACTION END 
ENDGO

However, Moran Benisty, my T-SQL Ninja pal, has pointed out that the SELECT and IF might bit a wee bit apart, and revised this to

CREATE TRIGGER dbo.People_Unique_NonNull_Email ON dbo.People AFTER INSERTAS BEGIN SET NOCOUNT ON

 IF EXISTS(SELECT 1 FROM People p JOIN INSERTED i On i.Email = p.Email WHERE i.Email IS NOT NULL  )  BEGIN RAISERROR ('Cannot put a duplicate email on People table', 16, 1) ROLLBACK TRANSACTION END 

ENDGO

I’ll leave adding the UPDATE case to the readers

Castle.Tools.* in Castle Contrib's repository has moved a bit

   ,  ,  ,  , 

The tools (various small helper libraries) are now under http://svn.castleproject.org:8080/svn/castlecontrib/Tools/

what’s in there:

Beware of SQL typos

  

I feel so stupid.

Just spent almost two hours trying to figure out why a SQL query wasn’t running.

I kept getting “Incorrect syntax near ‘RowNumber’” error message.

I was under the impression that my SQL syntax was ok, and tried various changes to the way I was invoking the query, but alas, nothing worked.

Then I just copied the raw query into SQL Manager, just to see the next snippet (the colouring, or lack of, was pointing this out for me):

WEHRE RowNumber BETWEEN @First AND @Last

WEHRE the hell was my head?

Wacky JET syntax for UPDATE FROM

  

Today I’ve given a little help to a friend, with a JET 4.0 (Ms-ACCESS) thing.

Situation Given an existing schema:

Customers (Id, Name, …, Email) Ads (CustomerId, …)

the client wanted to add a field name TargetEmail to Ads table.

Adding the field was simple enough:

ALTER TABLE Ads ADD COLUMN TargetEmail TEXT(255)

Now the client wanted to initialise the TargetEmail field for existing ads, based on the Customer’s email.

A Naive and SQL Server jockey as I am, I gave him that little snippet:

UPDATE   Ads SET      Ads.TargetEmail = Customers.EmailFROM     Ads     JOIN Customers ON Ads.CustomerId = Customers.IdWHERE   Ads.TargetEmail IS NULL

Problem JET had refused that syntax.

Or rather, Jet is weird.

Solution Google to the rescue. answer was here.

UPDATE  Ads,         Customers SET     Ads.TargetEmail = Customers.EmailWHERE	Ads.CustomerId = Customers.Id  AND   Ads.TargetEmail IS NULL

SQL Query Generator - First Release

   , 

What is it? A tool that generates a strongly typed representation of a relational database, to be used for generating SQL queries in a type-safe fashion, with the aid of intellisense.

Where to get it?

UPDATE (22/06/2008):The source has slightly moved (to a sub folder):http://svn.castleproject.org:8080/svn/castlecontrib/Tools/Castle.Tools.SQLQueryGenerator/

Limitations:

How to use it?- Generating the classes:Run Castle.Tools.SQLQueryGenerator.exe.Parameters:The mandatory flag is /db:DBNAME where DBNAME is your database name. By default, the server being looked for is (local). you can select another using /server:SERVER.By default, Integrated Security is used. You can supply /userid:USER and /password:PASS to override it.You can alternatively supply a /connectionstring:CONSTR parameter.

Usage sample (from Examples.cs in the test project:

SQLQuery q = SQLQuery .Select(SQL.Blogs.Id, SQL.Blogs.Name) .From(SQL.Blogs);Console.WriteLine(q);

Would print out:

SELECT [dbo].[Blogs].[Id], [dbo].[Blogs].[Name]FROM [dbo].[Blogs]

Not impressed? Well,

dbo_ForumMessages Message = SQL.ForumMessages.As("Message");dbo_ForumMessages Parent = SQL.ForumMessages.As("Parent"); SQLQuery q = SQLQuery .Select(Message.Id, Message.ParentId, Message.Content) .From(Message) .Join(Parent, Message.ParentId == Parent.Id);Console.WriteLine(q);

Will spit out

SELECT [Message].[Id], [Message].[ParentId], [Message].[Content]FROM [dbo].[ForumMessages] AS [Message] JOIN [dbo].[ForumMessages] AS [Parent] ON ([Message].[ParentId] = [Parent].[Id])

Need parameters?

Parameter<int> blogId = new Parameter<int>("BlogId"); SQLQuery q = SQLQuery .Select(SQL.Blogs.Id, SQL.Blogs.Name) .From(SQL.Blogs) .Where(SQL.Blogs.Id == blogId);Console.WriteLine(q);

would echo

SELECT [dbo].[Blogs].[Id], [dbo].[Blogs].[Name]FROM [dbo].[Blogs]WHERE ([dbo].[Blogs].[Id] = @BlogId)

How can YOU help?- Use it. Praise it. Use Paypal.

Already Added Stuff To SQL Query Generator

   , 

the new stuff:

Examples:

Reusing clauses:

FromClause from = new FromClause(SQL.Blogs);WhereClause where = new WhereClause(SQL.Blogs.Id == 2); SQLQuery q1 = SQLQuery .Select(SQL.Blogs.Id) .From(from) .Where(where); SQLQuery q2 = SQLQuery .Select(SQL.Blogs.Name) .From(from) .Where(where); Console.WriteLine(q1);Console.WriteLine(q2);

makes

SELECT [dbo].[Blogs].[Id]FROM [dbo].[Blogs]WHERE ([dbo].[Blogs].[Id] = 2)SELECT [dbo].[Blogs].[Name]FROM [dbo].[Blogs]WHERE ([dbo].[Blogs].[Id] = 2)

Operators:

SQLQuery q1 = SQLQuery .Select(SQL.Blogs.Id) .From(SQL.Blogs) .Where(SQL.Blogs.Id > 2 || SQL.Blogs.Name == "Ken");Console.WriteLine(q1);

makes

SELECT [dbo].[Blogs].[Id]FROM [dbo].[Blogs]WHERE (([dbo].[Blogs].[Id] > 2) OR ([dbo].[Blogs].[Name] = N'Ken'))

OrderBy Clause:

SQLQuery q = SQLQuery .Select(SQL.Blogs.Id) .From(SQL.Blogs) .Where(SQL.Blogs.Id > 2) .OrderBy(Order.By(SQL.Blogs.Id), Order.By(SQL.Blogs.Name).Desc);Console.WriteLine(q);

makes

SELECT [dbo].[Blogs].[Id]FROM [dbo].[Blogs]WHERE ([dbo].[Blogs].[Id] > 2)ORDER BY [dbo].[Blogs].[Id], [dbo].[Blogs].[Name] DESC

Didn’t have time to upload a binary, but you can simply grab the source and build yourself. it has absolutely no dependencies but .NET 2.0

Where from?

http://svn.castleproject.org:8080/svn/castlecontrib/Castle.Tools.SQLQueryGenerator/trunk/

UPDATE (22/06/2008):The source has slightly moved (to a sub folder):http://svn.castleproject.org:8080/svn/castlecontrib/Tools/Castle.Tools.SQLQueryGenerator/

Cannot Recreate Database With ASP.NET Membership Due To Collation Problems

  

Situation:

Problem:

Trying to rebuild the database from script fails due to collation errors.

Explanation:

A bit of lookup into those using my good pal google, have proved my suspicions. Somewhere along the script a StoredProc is being created, which joins to a temp table. It appear that temp tables are being created within the tempdb system database, which in turn has the collation of the server.

The answer usually is to create temp tables with explicit collation. However, those stored proces are generated by the ASP.NET membership thing, so it cannot be setup to use the proper collation on temp tables.

So, Solution 1 (if it’s not happening on generated stored procs):

Explicitly set the collation on temp tables.

Solution 2 (for the other poor people with generated sprocs):

You’d need to rebuild the system databases, either by reinstalling SQL Server, or following these instructions

The list of collation options can be found here

SQL Query Generator

   , 

Imagine you could write that in your IDE:

SQLQuery q = SQLQuery .Select(SQL.Blogs.Id, SQL.Blogs.Name) .From(SQL.Blogs) .Join(SQL.Posts, Join.On(SQL.Blogs.Id == SQL.Posts.BlogId)) .Where(SQL.Blogs.Name != "Ken's blog");
Console.WriteLine(q);

and getting that output :

SELECT [Blogs].[Id], [Blogs].[Name]FROM ([Blogs] JOIN [Posts] ON ([Blogs].[Id]=[Posts].[BlogId]))WHERE ([Blogs].[Name]<>'Ken''s blog')

Soon enough you would be able to to that.

After having fun creating the Static Sitemap Generator, today I’ve had a little free time (as my main machine is being reinstalled), so I came up with a SQL query generator.

It would be a tool to generate classes out of a database, that would make writing typed sql queries a breeze.

I have most of it working, except the part where I retrieve the metadata from the database … No worries, my good friend and SQL guru Moran is about to send me the queries for that real soon.

First release would work with SQL Server 2005, and later on I’ll add extension points to hook up other db engines.

Retrieving All Column Names And Types From SQL Server 2005 For .NET

   , 

Nothing fancy.

With a little help from Moran Benisty, here’s the script I use to get the metadata I need for the SQLQueryGenerator:

SELECT schemas.name AS [Schema], tables.name AS [Table], columns.name AS [Column], CASE  WHEN columns.system_type_id = 34 THEN 'byte[]' WHEN columns.system_type_id = 35 THEN 'string' WHEN columns.system_type_id = 36 THEN 'System.Guid' WHEN columns.system_type_id = 48 THEN 'byte' WHEN columns.system_type_id = 52 THEN 'short' WHEN columns.system_type_id = 56 THEN 'int' WHEN columns.system_type_id = 58 THEN 'System.DateTime' WHEN columns.system_type_id = 59 THEN 'float' WHEN columns.system_type_id = 60 THEN 'decimal' WHEN columns.system_type_id = 61 THEN 'System.DateTime' WHEN columns.system_type_id = 62 THEN 'double' WHEN columns.system_type_id = 98 THEN 'object' WHEN columns.system_type_id = 99 THEN 'string' WHEN columns.system_type_id = 104 THEN 'bool' WHEN columns.system_type_id = 106 THEN 'decimal' WHEN columns.system_type_id = 108 THEN 'decimal' WHEN columns.system_type_id = 122 THEN 'decimal' WHEN columns.system_type_id = 127 THEN 'long' WHEN columns.system_type_id = 165 THEN 'byte[]' WHEN columns.system_type_id = 167 THEN 'string' WHEN columns.system_type_id = 173 THEN 'byte[]' WHEN columns.system_type_id = 175 THEN 'string' WHEN columns.system_type_id = 189 THEN 'long' WHEN columns.system_type_id = 231 THEN 'string' WHEN columns.system_type_id = 239 THEN 'string' WHEN columns.system_type_id = 241 THEN 'string' WHEN columns.system_type_id = 241 THEN 'string' END AS [Type], columns.is_nullable AS [Nullable]FROM sys.tables tables INNER JOIN sys.schemas schemas ON (tables.schema_id = schemas.schema_id ) INNER JOIN sys.columns columns ON (columns.object_id = tables.object_id) WHERE tables.name <> 'sysdiagrams'  AND tables.name <> 'dtproperties' 
ORDER BY [Schema], [Table], [Column], [Type]

Quick, Dirty, Working.

Anyone up to contributing a similar thing for SQL 2000 / MySql / Oracle / Postgres / MS-ACCESS ?

it’s going to be subversion-ed really soon.

SQL Server 2005 - Cannot create Database Diagrams - Database does not have a valid owner

  

Using SQL Server Management Studio (be it the ‘full’ version or the express one), you can sometime encounter the error:

Database has no valid owner

in words:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

It usually happens when you’ve created that DB by a script (which was a bit incomplete) or by restoring a DB created on another machine, with some different settings.

Instead of going to the bottom of it, I’ll just write down the simple solution, for future reference.

open a new Query, and run the next stored procedure:

EXEC sp_changedbowner 'sa'

If you have another default owner on your machine, use it’s login instead of ‘sa’.

That’s it.

Backing up a hosted SQL Server DB

   ,  , 

As you already have probably noticed by now, I did some renovation on my blog.

Among other things, it is now being served froma SQL Server database, rather than form the daBlog xml files.

One caveat of this, is the fact that backuping the blog’s content became much harder. Since I have no access to db backups, I neede to find a way to generate the INSERT scripts that will enable me recreationg the content if it would be needed.

My first try-out was the Microsoft SQL Server Database Publishing Wizard, that I saw at Scott Gu’s blog

This tool is meant to create the script form a local dev db, in order to make it run on the remote one. Actually you can make it run on the remote one, nd save the generated sql file locally, for backup purposes.

I tried it up, but it send some nasty .NET break dialogs.It however managed to create A script, that I’m yet to check for it’s usability.

Nice tool. But I’ll look for something that is pure t-sql so it’d be easier to run (maybe automated every once in a while)

Follow @kenegozi