The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

When I got this error, it took me a while to understand just what was wrong, and then I walked away, knowing that I'm smarter than SQL Server. :-)

Check those out:

select

precentage_date from openquery(ORA, 'select precentage_date from PRECENTAGE')

Worked just fine.

select

precentage_date from ORA..ADMIN.PRECENTAGE

Fails with: Error converting data type DBTYPE_DBTIMESTAMP to datetime.

There isn't any values outside the range that SQL Server can handle, but still it gives me those errors. Argh!

This is not a valid performance comparasion by any means, but I noticed something interesting when doing simple data transfers from SQL Server to SQL Server via SSIS & SQL Server.

The situation was copying ~6.7 Millions records from one database to another (on the same machine). I first tried it using the Import/Export wizard, and then using this script:

The performance of both of them was nearly identical (10 seconds difference in a 6.5 minutes operation, which can be explained by different machine loads).

I need to move a couple oftables from an Oracle database to SQL Server, how do I do this?

Well, there is SSMA*, but it has a couple of problems dealing with strange decisions on the Oracle side. It also require a hefty machine to run on, as well as taking quite a bit of time to run.

I used it to generate the schema, and then defined a linked server to Oracle and wrote a very simple script to port the data. It failed, miserably. Let's assume that my script looked like this:

What is there to go wrong? Well, for one thing the range of allowed dates in SQL Server and Oracle is different (1753 - 9999 in SQL, -4712 - 4712 in Oracle). Apperantly some of the customers where registered quite some time ago, about two thousands years ago, to be precise. So I bumped them up to the twentieth century, and tried again.

It failed again. This time it complained about a primary key violation during the copy. That really made me scratch my head. I checked Oracle, and the PK was enabled and doing its job quite well. For the purposed of this conversation, let's assume that the PK is composed of [Name] and [Address].

Can you guess what the problem is now? I couldn't, so I started running the following queries on both SQL Server & Oracle

Well, the Address query returned the same count, but the Name query returned a wildly different result. Looking into it a bit, I discovered that I had set of names that looked like this: Alice, ALICE, alice, bob, Bob, BOB, etc.

But SQL Server's case sensitivity is a server wide option. For a couple of minutes, it looked like I was stuck, but then a friend reminded me about the Collation option in the column spesification, and that does allows you to define case sensitivity in the column level. Once I did that, everything worked just fine.

* Sql Server Migration Assistant

Here is another tip for those who need to explore big SQL databases with a lot of stored procedures and quite a bit of logic. This will gives you a list of all the procedures that has a certain string in them:

I got a stored procedure that was taking an undue amount of time to run, in was about half an hour, and that was really bad. I tried looking at the execution plan and add an index to make it faster, but it wasn't that effective.

The query was an update from a nested select (which contained a rather complex condition and a couple of joins), and I couldn't really figure out how to make it better. I tried running the pieces individually, and they run reasonably fast, so it was the interactions between them that killed the performance. The execution plan showed that the joins were costing very much in terms of performance.

During my attempts to figure it out, I moved the select into a temporary table, and then updated from it, like this:

SELECT To, SUM(p.Amount) TotalAmount
INTO #TempPaymetsAmounts
FROM Payments p, Payments p2, Orders o
WHERE ComplexCondition
GROUP BY [To]

UPDATE PaymentsTotal
SET Total = TotalAmount
FROM #TempPaymetsAmounts
WHERE SimplerCondition

That query run in less than 10 seconds.

The only thing that I did was move the select into a temporary table, but I'm not sure why this should have such a profound effect on the performance of the query.

I recently discovered that a certain class of programmers has a really bad case of Not Invented here. It got so bad that they decided to implement JOIN and GROUP BY by using cursors. Of course, due to the nature of the problem, they were so excited by their new discovery that they immediately rushed to propogate it thorughout the code base, using none else than the tried and true method of copy-paste-change-one-charater-and-then-move-on-never-looking-back.

I won't speak of the performance of this method, but I think that you can guess that there are orders of magnitude difference between this new way of dealing with the database and the standard method of operation in the industry.

I knew that it was bound to happen.

I started running into highly mysterious errors in SQL Server. The strangest among them is the Missing Columns Mystery.

When I run a stored procedure that calls to other SPs, and so on, I get an error about invalid columns somewhere deep in the stack. But when I run the SP directly, it passes perfectly.

Of course that when I check it, the table has the supposedly missing columns. It drives me crazy, and I can't really get the error consistently, if I run it for a couple of time, it seems to fix itself, but I've no idea how.

[Via Mitch Denny's Blog ] [Via Sql Down Under]

It's usualy hard to efficently get hierarchical data using SQL. But apperantely SQL Server can do that easily. The key to this is to create common table expression that recursively points to itself, like this:

Before this I would resort to either getting all of the rows or issuing several queries. Nice.

The sp_helptext stored procedure can give you the text of any non-table object in the database. I find this incredibly useful, as it saves the need to locate the object in the management studio UI.

Here is an example:
sp_helptext Very_Nasty_Trigger

Results:

Text
------------
CREATE TRIGGER Very_Nasty_Trigger
   ON  NASTY INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    Declare @stmt nvarchar(max)
    SET @stmt = (select [text] from INSERTED);
    exec sp_executesql @stmt

END

Now I need to find something that works on the table level as well.