Category: Databases

hibernate.cfg.xml not found in ASP.Net application?

Posted onAuthorEmilLeave a comment

In later versions of NHibernate it’s possible to separate NHibernate configuration from normal application configuration (which is normally done in App.config/Web.config) by using a separate file called hibernate.cfg.xml.

Works great, but there is one thing to keep in mind in ASP.Net applications, namely that it should be placed in the bin/ folder, not in the root folder fo the application! Took me quite some time to figure out! So if you have the same problem as me, just move it to that folder 🙂

/Emil

Finding dependencies on a table column

Posted onAuthorEmilLeave a comment

This will list stored procedures, views, etc. that uses a given table column:

SELECT OBJECT_NAME(sd.id) Dependent_Object,
      (SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
INNER JOIN sysdepends sd  ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE so.id = OBJECT_ID('tblcategorypage')
AND sc.name = 'categorytype'

Credits for this tip to SQLServerCentral.

/Emil

Bitwise operators in T-SQL?

Posted onAuthorEmilLeave a comment

You won’t need them very often but it might be good to know that they’re there. Here’s an example of how to use a bitwise AND in a where clause:

SELECT * FROM uppdrag WHERE (applikationer & 1) > 0

In this example, ‘applikationer’ is a column containing several combined flags and we want to check the setting on the flag in the lowest bit.

The bitwise operators are:

  • & – bitwise AND
  • | – bitwise OR
  • ^ – bitwise EXCLUSIVE OR

Easy way to create temporary tables in SQL Server

Posted onAuthorEmilLeave a comment

Have you ever had a need to create temporary database tables in a SQL Server session or procedure? Are you used to creating them by hand like this:

CREATE TABLE #tmp
( 
    id INT, 
    name VARCHAR(32) 
)

Depending on what you need the table for, then there might be an easier way. Have a look at this short session:

SELECT * INTO mytemptable FROM foobar
SELECT * FROM mytemptable
DROP TABLE mytemptable

The first line creates a new temporary table based on the result set of a SELECT query, and stores the result set in the table. The query can be more complex than in the example, with JOINs, WHERE clauses etc. Incredibly useful when mangling and transforming data!

Installing the client tools for SQL Server

Posted onAuthorEmilLeave a comment

I’m back now from my parental leave, so here’s the first posting in a while…

Ever installed SQL Server forgetting to install the client tools such as Management Studio, Profiler, etc? Here’s how to install them the easiest way.

Instead of running the main installation program, which may or may not work, run [drive]:\SQL Server x86\Tools\Setup\SqlRun_Tools.msi to install the tools directly. Much quicker and we’re not depending on updates made to SQL Server since the installation.

Tip found here.

/Emil