Check for Temp Table Existence in SQL Server

IF OBJECT_ID(‘tempdb..#sometable’) IS NOT NULL
DROP TABLE #sometable

Embedded Resource Queries or How to Manage SQL Code in Your .NET Projects

I developed a technique for managing SQL code in my .NET projects using the Embedded Resource “Build Action” back in the .NET 1.0 days that I still use to this day. I’ve been meaning to share this with the world for several years now and I’m finally getting around to it.

The benefits of this technique are many. Your SQL queries are contained in individually-editable, properly-formatted files in your .NET project instead of constructed as nasty concatenated strings in your code.

However, at run-time, you don’t have to worry about distributing extra files with your application, the SQL text is an embedded resource in your executable or class libary. You get the best of both worlds. You have source control diffs available but no additional distribution burden.

Before I go further, let me address the issue of stored procedures. Stored procedures have their place. In the past, I’ve maintained 2000+ line stored procedures. I’m ashamed to admit that I’ve even written some 2000+ line stored procedures back in the heyday of client server. Stored procedures (not 2000+ line long ones, surely) have their place and can be a great way to minimize network round trips. However, for purely select-oriented SQL, I think stored procedures are an unecessary administrative burden on most .NET projects. By using this technique, you never have to worry about your .net code being out of synch with the stored procedure.

Modern database servers cache query plans equally well for parameterized ad hoc queries as they do for stored procedures. As a result, unless you are doing some strange things, you will not find a measurable performance difference between using a stored procedure and the embedded resource technique I am about to demonstrate for select-oriented SQL.

Finally, let me say that if you work in a change management environment where it is easier to recompile stored procedures than it is to push out DLLs, then this technique may not be for you. Also, if you expect your query to be re-used as is by many different types of non .NET clients (for example, an excel spreadsheet or other stored procedures) then you may want to stick with a stored procedure. Keep in mind that you can easily re-use SQL stored as embedded resources in .NET DLLs among different .NET projects.

Many times I find myself in a query tool such as Query Analyzer, SSMS Query Editor, or Oracle SQL Developer iteratively developing a SQL query that returns a single recordset of results. Once I’ve got the query working reasonably, I create a new text object in under a folder named SqlQueries in Visual Studio in my project, rename it to something.sql and paste in the SQL from the query tool unaltered. I then comment out the section at the top of the query that declares the parameter variables (if I’m using SQL server). Notice that I don’t delete them. This allows me to easily run the query in a query tool if I need to refine or debug the query.

Once the file is in the project, you have to set the build action to embedded resource as shown below:

Setting Build Action to Embedded Resource

We need a class that helps us conveniently access our embedded resources. I’ve always used a class with static methods like this one:

public class EmbeddedResource {
	private EmbeddedResource() {
	}

	public static StreamReader GetStream(System.Reflection.Assembly assembly, string name) {
		foreach (string resName in assembly.GetManifestResourceNames()) {
			if (resName.EndsWith(name)) {
				return new System.IO.StreamReader(assembly.GetManifestResourceStream(resName));
			}
		}
		return null;
	}

	public static string GetString(System.Reflection.Assembly assembly, string name) {
		System.IO.StreamReader sr = EmbeddedResource.GetStream(assembly, name);
		string data = sr.ReadToEnd();
		sr.Close();
		return data;
	}

	public static string GetString(string name)
	{
		return EmbeddedResource.GetString(typeof(EmbeddedResource).Assembly, name);
	}
}

If I wanted to retreive some embedded sql in a file named SelectOrderWithExpensiveItemInfo.sql under a project directory directory named SqlQueries as a string to a variable named sql I would do this:

string sql = EmbeddedResource.GetString("SqlQueries.SelectOrderWithExpensiveItemInfo.sql");
SqlCommand cmd = new SqlCommand(sql);

You may be wondering about the for each loop inside EmbeddedResource.GetStream. Initially, I specified the whole resource name which included what I assumed was the assembly name at the beginning of the resource specifier. Unfortunately, the default namespace from project properties is what is actually used (at least when I first wrote this way back when). I couldn’t find a reliable way to retreive the default namespace so I added the search logic. Also keep in mind that you can only use GetString without passing the assembly in if your code is executing in the same assembly in which the embedded resource is housed.

That’s all there is to it. Here’s a fully-functional demo solution showing this technique in action using the AdventureWorks database. Enjoy. I hope that you find embedded resource SQL queries as useful as I have.

Enterprise Library Logging to Oracle Database

When I first needed to log to an Oracle database from the Enterprise Library, I expected it to be a simple matter of running a script against my Oracle database and adding a few lines to my app.config. Thanks to bad decisions on the part of the Enterprise Library designers and problems with ADO.NET, I had to create my own script to create the necessary tables and stored procedures *and* create two classes that derived from two existing classes in Enterprise Library. This post applies to the Jan ’06 version of the Enterprise library. I have not had a chance to check the Apr ’07 edition to see if they now support Oracle directly.

When I first embarked on this incredibly long journey, I looked at the script included in the EntLib project that creates the tables. Unfortunately, it’s T-SQL specific. So, I converted the script to work with Oracle. That wasn’t so bad. I did have to put “timestamp” in double quotes in my Oracle version of WriteLog since its actually an Oracle datatype. But I was able to invoke my procedure directly using PL/SQL code and I foolishly assumed that EntLib would similarly be able to do so from it’s existing FormattedDatabaseTraceListener.

I fired up my test app and clicked the “Log” button that invoked the EntLib logger. Then I looked at the log table but there were no new entries. I added a flatfile listener and ran the app again. This time I was able to see that the EntLib logging system was trying to write logs out to my oracle database, but was getting the following error (which it kindly logged to the trace file):

PLS-00306: wrong number or types of arguments in call to ‘WRITELOG’

So, I double, triple, quadruple check the order, type, and name of the parameters I am passing and everything looks perfect. Finally, I break down and copy the code that invokes the stored procedure from the EntLib class and add an “Exec Stored Proc” button that will allow me to call the stored procedure directly. I get the same error. Now, I am puzzled. I can invoke this procedure from Pl/SQL but I can call it from ADO.Net. Out of desperation, I change the name of the “timestamp” parameter to TimeOf, recompile the stored proc, and things start working. Apparently, ADO.NET/Oracle cannot handle the parameter with the same name as a reserved word.

Now that I know what the problem is, it’s not very easy to fix. I have to create my own dll that extends the Enterprise Library just to change the name of one parameter in a stored procedure call.

Now that you know the background of why its not as easy as just modifying app.config and running a sql script, you can fully appreciate the attachmdents to this post. I will provide an overview of the importants steps of getting this working in your own project.

First, you will need to add references to the following EntLib dlls in the project in which you want to use the BlueCorner.OracleLogSink dll:

Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Logging
Microsoft.Practices.EnterpriseLibrary.Logging.Database

Second, you will need to modify your app.config file. You can view the attached project for all the details, but the only-non-intuitve/non-standard part is the type reference:

<add databaseInstanceName="LoggingDb" writeLogStoredProcName="pkg_logger.WriteLog"
addCategoryStoredProcName="pkg_logger.AddCategory" formatter="Text Formatter"
listenerDataType
="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListenerData, BlueCorner.OracleLogSink"
traceOutputOptions="None" 
type="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListener, BlueCorner.OracleLogSink"
name="Database Trace Listener" 
/>

Note the “listenerDataType” and “type” attributes. When I first attempted this, I thought I could reuse the FormattedDatabaseTraceListenerData for the listenerDataType because I was not altering the structure of the configuration section, just the implementation of FormattedDatabaseTraceListener. Unfortunately, this did not work. I had to make a copy of FormattedDatabaseTraceListenerData and the only difference was the name of the class and the referenced classes.

Third, just add a reference to BlueCorner.OracleLogSink either as a project or as a built dll.

Fourth, run the attached database script against your database and alter the connectionString section of your app.config appropriately.

I’m not covering any standard connectivity steps like editing tnsnames.ora, etc. If you need help getting ADO.net to talk to your oracle schema you will need to look elsewhere.

Here are the working demo project and the sql script you will need. Enjoy.

Cross Joining a Table Created on the Fly Using Union All

Today I was working on a report to reconcile data between two systems that will be used to validate parallel testing as we work to move the new system into production. I mention this to provide you with some context around the two techniques I employed in one of the SQL queries I wrote that is the basis of this post. Sometimes it’s a very fine line between being clever and being obtuse, especially when you are writing SQL. Since this report will become obsolete once the new system goes to production and I will most likely be the only person that has to maintain it for a short period of time, I look at these techiques as more clever than obtuse in these circumstances.

Creating and dropping temp tables in batches (not stored procs) can be a hassle in SQL Server T-SQL. In Oracle, you only have global temporary tables and you have to deal with the select limitations of PL/SQL. Sometimes, you need a table but you don’t want to explicitly create it or you can’t because you are querying another system over which you have no control. You can create the table “on the fly” using a temporary view and the UNION ALL clause. For example, I frequently need to transform a block of time into hourly blocks and I don’t have a permanent table available and I don’t want to deal with temp tables. I can create the table I need on the fly like this:

(SELECT Hour = 0 UNION ALL SELECT Hour = 1 UNION ALL ... SELECT HOUR = 23) H

In Oracle, you would have to specify the FROM clause (good ol’ DUAL). You also can’t use the = construct but you get the idea. In my experience the performance of this “table on the fly” is pretty good. Obviously, if you are working in a stored proc, you can create a temp table in T-SQL or a TABLE varable in PL/SQL to achieve the same result. This technique is nice when you can’t use a stored procedure. It’s also useful if you only need a few rows once (say four or less). With less than four rows, it’s easier to create the table using a temporary view of UNION ALLs than it is to create the temp table or table variable.

Generally, you want to avoid CROSS JOINs (aka the dreaded Cartesian Product). However, there are many legitimate uses of CROSS JOIN. First, let me state that CROSS JOIN provides one of the most compelling arguments for using the ANSI-92 join syntax. If you see “CROSS JOIN” in a query, you can be fairly certain that the author of the query wanted a Cartesian product. If you see a FROM clause with a table that has no conditions specified in the WHERE clause, how certain can you be that the author intended a Cartesian product?

I digress. In the query I was developing today I was comparing schedules in two systems. In one of the systems, the schedule only included one side of a pair of transactions that offset each other. In the other system, both rows were there. I didn’t want the missing row from the one system to show up as a variance. Luckily, the existing query was already using a UNION to pick up this special case. I used a CROSS JOIN of a “table on the fly” to create the other row for me:

...
UNION (
SELECT
CASE WHEN D.Direction = 1 THEN 'BUY' ELSE 'SELL' END TransactionType,
A.Volume * D.Direction Volume
FROM OneSided A
CROSS JOIN (SELECT Direction = 1 UNION ALL Direction = -1) D
)
...

As you can see, I created the second row that I needed to make this system’s schedule match the other system’s schedule with just one line of SQL and minor modifications to the SELECT clause.

Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs

For the last several months, I have been working primarily with Oracle 8i and 10g databases.  Over the last 10 years, 95% of my work has been with MS SQL server.  The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.  You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.  Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

  1. Bind variables versus PL/SQL variables instead of just variables.  Oversimplifying, use “:” in place of “@” when you want a parameterized query.  This is the most awkward and annoying aspect of Oracle compared to MS SQL Server, IMO.  The bottom line is that you can’t very easily copy code from a stored procedure and run it in Toad or SQL Developer.  Nor can you directly assign values to bind variables — you get prompted for them.
  2. You can’t do a SELECT without an INTO inside of a PL/SQL block.  This is really puzzling.  Especially since you can’t have anything but bind variables outside of a PL/SQL block and you can’t programmatically assign values to bind variables.  All of this adds up to being forced to be prompted for bind variable values every time you run a parameterized query in the SQL Worksheet or in Toad.
  3. There’s no built-in Query Analyzer/SSMS Query tool.  There’s an extremely crippled but free version of Toad.  This what I use if I have to work with a pre-10g instance.  There’s Oracle SQL Developer which is what I use if I am working with a 10g instance.  They both are lacking compared to Query Analyzer/SSMS Query window.  You will press ctrl-E and ctrl-R repeatedly with no effect so many times it will make you sick.
  4. DATEADD doesn’t exist.  You have to add fractional days to dates instead.  DATEADD(hour, 1, D) would be D + 1/24.
  5. INT can’t be used for columns.  You use NUMBER instead.  This can be a real pain in ADO.NET because you have to explicitly convert all those NUMBERs to ints unless you want to treat them as decimals.
  6. No IDENTITY columns.  Instead of typing that one word you have to create a completely separate object called a SEQUENCE and you also have to write a trigger.
  7. You can’t have multiple databases on an Oracle instance.  There are just multiple schemas which are really owners.  Public synonyms are used to make objects from different schemas accessible without explicit schema specification. 
  8. The default date format does not include the time part.  You have to “alter session set NLS_DATE_FORMAT=’MM/DD/YYYY HH24:MI’;” to see the time part.
  9. You concatenate strings with the oh-so-intuitive || instead of the obscure + operation.  Alternatively you can use CONCAT.
  10. You can’t PRINT.  There’s a print-like statement, but it only works in SQL*PLUS.  You don’t ever really want to use SQL*PLUS unless you are a massochistic, command-line-loving freak from the past that still uses VI or EMACS.  Actually, there’s a web-based version of SQL*PLUS that’s usable but I like to stay in the SQL Worksheet.

Those are the top 10 — the things I really struggled to understand coming from the MS SQL side of things.  Here are some more that I found useful:

  • SP_TABLES = SELECT * FROM ALL_TABLES
  • sp_help = DESCRIBE
  • You can’t just SELECT without a FROM clause.  You always have to include FROM DUAL.  I still don’t know what “DUAL” is.
  • GETDATE() = SYSDATE
  • SUSER_SNAME() = USER
  • Getting just the date part of a date value: CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)) = trunc(SYSDATE)
  • Just use VARCHAR2 and don’t ask about VARCHAR.
  • You can’t return values from procedures using the RETURN statement.  Use an OUT param instead.
  • In version 8i and earlier, there’s a “rule-based” optimizer.  The rule-based optimizer can suck badly and you may need to force the join order on some of your queries.  You do that with “SELECT /*+ ordered */” (yes, it really is in the comment).
  • The Oracle compiler doesn’t do a good job of telling what’s wrong with your statement.  It can give obscure messages for missing commas and semi-colons.  Don’t forget that you must terminate each statement with a semi-colon.
  • There’s no built-in support for UUIDs.
  • Oracle does not distingish between an empty string and null (which really sucks)

Here are a couple of non-obvious things I learned about Oracle and .NET 2.0:

  • You can’t send multiple SQL statements in a batch using ADO.NET like you can with MSSQL Server
  • You can’t use the ODP.NET with the Enterprise Library
  • You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP