[RESOLVED] Some kind of caching has got me down

[Shaggy Hiker]

I recently started another thread that got resolved, but this question is closely related:

I have a stored procedure that performs these steps:

1) Create a temporary table.
2) Copy everything from a view into that temporary table.
3) If that works out, clear out a non-temporary table.
4) Copy everything from the temporary table into the non-temporary table.

This sequence of steps is technically performed four times, though one of those times may have nothing to do with this. In each iteration, the temporary table created in step #1 has a different set of fields (because it's working with different data), but otherwise each iteration is identical.

I'm trying to track down a problem in the SQL of the fourth iteration, and it's a tedious process that requires me to run the stored procedure over and over to see whether or not I fixed the problem (one out of many dozen fields is too short an nvarchar, but figuring out which one is a chore). After working on this for several iterations, SQL Server suddenly horfed up a whole series of errors. The meaning of the errors was clear: When step #1 was attempted for the first iteration, rather than creating the temporary table, it was reverting to using the schema of the temporary table used in the fourth iteration (the last one run).

Each iteration begins like this:

BEGIN TRY
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable

CREATE TABLE TestTable

So, it looks for TestTable, and if it is there it drops it. It then creates TestTable with the new set of fields for this iteration. This worked fine for several runs, then it suddenly stopped dropping the table and creating the new table. At first, we thought this was due to having desperately low memory on the SQL Server box (low enough that the whole server should have borked), as the problem vanished once the memory issue was cleared up. However, it showed up again now that we have plenty of memory. When looking at it the first time, my boss did something else, unrelated to the memory. This time, we tried that unrelated solution again, and it fixed the problem again.

The solution was to copy the DROP Table TestTable and run it as a stand alone query. That fixed whatever the problem was, and all went back to normal.

So, the question is: What happened, and how do I keep it from happening again?

It looks like SQL Server may have optimized the execution and optimized away the creation of the test table, because it already existed, or for some other reason. It then moved on to an INSERT statement and bombed out because a whole lot of the fields were wrong. I'm a bit nervous about the fact that the procedure ran correctly (well, mostly correctly, since I still have that string length issue to work out) several times, then suddenly failed. I'd rather not have that happen routinely.

[TysonLPrice]

Are you really using temporary tables or are you using tables temporarily? What I'm wondering is when you are dropping the tables if some kind of transaction logging is getting in the way.

To me temporary tables start with # or ##. Or table variables starting with @.

I don't know the process but could you just create the tables one time and delete or truncate them before using then versus creating them each time? If you don't mind posting the procedure I'd like to see it.

[szlamany]

Do you have any BEGIN TRAN statements in the mix? You could have left a transaction open if so.

Another way to create a table is to do this:

Select Cast('' as int) "Field1", Cast('' as varchar(100)) "Field2" into #XYZ Where 1=2

Or actually use something like that and come FROM YOURACTUALTABLE - that way you do not need to pre-create the table at all

And it doesn't have to be an #XYZ temp table (which goes into the TEMPDB) - it can be a real table in your database

Not sure if this is any help - as I've not seen your sproc...

[Shaggy Hiker]

No transactions in the mix anywhere, but more interesting data:

To track down the problem I was having with the final table, which I mentioned in the original post, I moved a portion of the procedure into a plain old query so that I could run that part again and again while tinkering with the actual columns in that CREATE TABLE line (and a select into insert line that followed it). By doing that, I came across some interesting data:

Taking just this SQL:

IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable

CREATE TABLE TestTable

The If was essential, because I neglected to add a Drop Table at the end of the query, so TestTable remained. If I didn't have the If statement, then the Create Table line would fail because the table already existed. That is exactly what anybody would expect. What I was doing was that I had about fifty columns in the original TestTable, and the subsequent INSERT would fail because I had one of the columns wrong (actually, about four of the columns were wrong, but that's a different matter). Since I wasn't sure which of the columns were wrong for reasons I won't go into, I was taking off about 5-10 columns, then trying it again, to track down the problem fields. Therefore, in each test, the set of columns was getting SMALLER!

Eventually, I tracked down part of the problem and solved it, then replaced the truncated list of columns with the full list to test it again (which means that the list of columns got BIGGER), and I immediately got an error about lots of invalid columns in the Insert. Now, that makes no sense, because the If statement had run and dropped the table, then a new table had been created. This had worked over and over again as I reduced the number of columns. If failed when I increased the number of columns, and the INSERT was saying that the columns didn't exist, but only when I ran the query. At design time, if you had a column in the insert that didn't match a column in the target table, there would be a visible error, but that didn't happen because the columns were the same between the create and the insert statements.

So, as long as the column list stayed the same or shrank, the If statement correctly dropped the existing table, then the create statement correctly built the new table. If the If statement wasn't there, then the Create wouldn't work because the table already existed, which shows that the Drop was essential. However, if the number of columns increased, then it was as if both the If and the Create were skipped entirely.

Technically, it doesn't matter whether the columns shrank or not. Reducing the number of columns in the INSERT would still work even if the Drop and Create had not happened, as it would still be valid SQL. However, once you add columns to the INSERT, then the Drop and Create MUST run, or else things will fail. So, it appears that what is happening may be some kind of optimization. You can't run the Create with the table already in existence, but the SQL compiler may be skipping both the Drop and the Create if the table is already there. That isn't right, but the behavior that I have seen would be explained by such an occurence, and by little else. Saying that the Drop and Create are only skipped in cases where columns are added seems really hard to justify, but saying that the Drop and Create are skipped in cases where the table already exists doesn't seem to far fetched, to me (though it would still be a bug).

[Shaggy Hiker]

By the way, this is a super easy thing to test, since it doesn't harm any existing database. I just ran this query on a totally different DB:

IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable

CREATE TABLE TestTable (RUBaseID uniqueidentifier,HatcheryID uniqueidentifier,RUNamePrefix nvarchar(50))

INSERT INTO TestTable(RUBaseID,HatcheryID,RUNamePrefix)
SELECT RUBaseID,HatcheryID,RUNamePrefix FROM dbo.HATCH_RUBase

Anybody else can try the same thing. You are only creating a temporary table, so you do no harm (as long as you drop it afterwards). Use whatever table you want as the source, take one or more fields for the temporary table, run the query. Run the query a few more times if you like. Then remove one of the columns (it will have to be removed from the Create, the Insert, and the Select, of course). Run it again. Run it a few more times, if you like. It will work fine.

Now add in another column to the Create, the Insert, and the Select. If you don't add to all three, you will get a visible error before you even try to run it. If you add the column to all three, you will get an error because the new field doesn't exist in TestTable, so the Insert will fail.

[TysonLPrice]

"You are only creating a temporary table"...I hate to be so pedantic but these are not temporary tables except in the sense they aren't around that long to you

I makes sense the compiler does not know you are dropping and changing a table. I've been wrong enough times in this thread I should be quiet but if you actually used temporary tables some of this might clear up.

[szlamany]

I always script my DBA tasks like creating and dropping and other various table work - and have always found that a GO placed between the DROP and CREATE statements is helpful.

Granted - you are not doing this in a query window in SSMS...

The database engine is going to "know" some facts about tables - since it needs to "pre-parse" your SQL statements.

btw - why not use 3 different tables?

[TysonLPrice]

I always script my DBA tasks like creating and dropping and other various table work - and have always found that a GO placed between the DROP and CREATE statements is helpful.

Granted - you are not doing this in a query window in SSMS...

The database engine is going to "know" some facts about tables - since it needs to "pre-parse" your SQL statements.

btw - why not use 3 different tables?

That won't work, multiple GOs, in a stored procedure though.

[szlamany]

Granted - you are not doing this in a query window in SSMS...

I realize that - I was making a statement about how the PARSER is "knowing" of certain database object facts.

I was not suggesting putting a GO into the SPROC!!

Although that does make me think of another method that might help shaggy here. How about putting the "create" logic into a different SPROC?

Or - although usually frowned upon - use dynamic SQL - which would not have this kind of problem at all...

[TysonLPrice]

I realize that - I was making a statement about how the PARSER is "knowing" of certain database object facts.

I was not suggesting putting a GO into the SPROC!!

Although that does make me think of another method that might help shaggy here. How about putting the "create" logic into a different SPROC?

Or - although usually frowned upon - use dynamic SQL - which would not have this kind of problem at all...

Gotcha...you may even have been in the thread where that came up in the last few days. I'm striking out all over the place today (been at work since 3:00 AM EST).

pseudo code:

1) Create a temporary table.
2) Copy everything from a view into that temporary table.

select * into #TemporaryTable from view

3) If that works out, clear out a non-temporary table.

truncate non-temporary table

4) Copy everything from the temporary table into the non-temporary table.

Insert into non-temporary table
select * from #TemporaryTable

drop table #TemporaryTable

That's assuming a few things but a basic approach.

[Shaggy Hiker]

Ok, that sound reasonable. I haven't done anything with temporary tables before and didn't realize that a table that was temporary to me was not a temporary table.

Regardless of that, the situation still stands because the behavior is clearly quite strange and entirely reproducible. The way I have it should work, and would work if each line was being executed faithfully each time the query is run. The curiosity is that each line is not being executed in all situations.

[TysonLPrice]

Ok, that sound reasonable. I haven't done anything with temporary tables before and didn't realize that a table that was temporary to me was not a temporary table.

Regardless of that, the situation still stands because the behavior is clearly quite strange and entirely reproducible. The way I have it should work, and would work if each line was being executed faithfully each time the query is run. The curiosity is that each line is not being executed in all situations.

Just for grins, if it is not proprietary, could you post the code? I can't look at it until tomorrow though.

[TysonLPrice]

Ok, that sound reasonable. I haven't done anything with temporary tables before and didn't realize that a table that was temporary to me was not a temporary table.

Regardless of that, the situation still stands because the behavior is clearly quite strange and entirely reproducible. The way I have it should work, and would work if each line was being executed faithfully each time the query is run. The curiosity is that each line is not being executed in all situations.

The thing about temporary tables starting with # is they will delete automatically when the SPROC is done running. Most people have clean up steps just to be sure. If your tables are small look into table variables. The reside in memory and can be much faster. They start with @.

[Pradeep1210]

...

I'm trying to track down a problem in the SQL of the fourth iteration, and it's a tedious process that requires me to run the stored procedure over and over to see whether or not I fixed the problem (one out of many dozen fields is too short an nvarchar, but figuring out which one is a chore). ...
...
So, the question is: What happened, and how do I keep it from happening again?

It looks like SQL Server may have ...

I'm assuming your only problem is debugging SQL Stored Procedures, means stop it at a point and inspect the various variables etc.
Just to let you know, SQL Server Management Studio supports debugging, just like VB or C#. You just need to press F10 and go line by line, instead of pressing F5, as you would do with VB or C# code.

[Shaggy Hiker]

Just for grins, if it is not proprietary, could you post the code? I can't look at it until tomorrow though.

Gads, you don't really want THAT do you? I'm only working with the darn table because I couldn't think of any other way. The view that I was turning into a table had an insane number of columns. Still, the SQL I posted in #5 shows the problem and will work for any table you have handy. For that post, I just popped the code into a query but used a table from a database I have at hand (I don't have access to the actual stored procedure, and won't for a solid week, as I'm going on vacation tomorrow and am not at the office today). All you have to do is choose a table you have handy to act as the source for the columns, put in a few, run it, add some more columns....and watch it crash.

I should have been using true temporary tables. The query is built from an example I found on the interwebs. I now realize that the example was talking about a means of creating a real table using an Insert...Select, so they created the table and dropped it just for demo purposes. Without any experience in stored procedures, I assumed that WAS a temporary table (which I had heard about, but never used), since it was temporary in nature, but it was actually just a demo that was cleaning up after itself. I can deal with that once I get back.

@Pradeep: I did know about that...as of Monday. Before that time....not so much. I neither knew nor cared, since all of my work with SQL Server was either views, one-off queries, or SQL written directly into VB. Now I'm getting to learn more about SQL Server than I had really wanted. It's a good thing, though. Very informative.

[szlamany]

@shaggy - just to clarify...

"Internally" temp tables get made in a database called TEMPDB. It can be on a different disk then your MDB and LOG - obvious benefits to that...

Temp tables named like #XYZ are local to you - named like ##XYZ are available to all users connecting to your database.

You can also make an "in-memory" table like this

Declare @XYZ Table (field1 int, field2 varchar(100),...)

These are really handy for "small number of rows" - as you never have to "DROP" them - they are almost like "variables" in the SPROC.

Keep in mind that they do not scale well - once you get past some magic MS size limit they tank really badly.

[szlamany]

Shaggy - hate to be the bearer of bad news - but that is expected and common behavior.

The error is clearly on the INSERT line - it's validating the VALUES() list to see if all fields exist in the table. Since the table does still exist at that moment it can do the validation - and of course the field DOES NOT EXIST in the table yet.

Adding a DROP TABLE to the bottom - so the table never remains in the database as an object fixes it - changes the behavior - not getting full validation (kind of like late-binding vs early-binding??)

When you "remove" columns the parser does not mind - as you are simply not inserting all columns - no big deal

Use Acctfiles
Go
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
 DROP TABLE TestTable
 
CREATE TABLE TestTable (ConfItem varchar(50), ConfData varchar(50), ConfData2 varchar(50))
 
INSERT INTO TestTable(ConfItem,ConfData,ConfData2)
 SELECT ConfItem,ConfData
,ConfData
 FROM dbo.Acctfiles_T
 
Drop Table TestTable -- adding this line makes this work - as the table not existing does not allow for full validation

[Shaggy Hiker]

Ah, that makes sense. It is validating against the existing table. Had I dropped the table, it would have been unable to perform the validation, so it would just run the SQL without the validation.

While that makes sense, and I can live with it, I would argue that there is still a problem with the the query designer and validation that had me pretty well tripped up: If you make a typo in one of the field names, even one of the ones that isn't in the table, the designer will underline that typo. Technically, this is kind of correct, but also rather misleading. It will validate against the actual table when it runs, but it will validate against the CREATE Table line while in the designer. That had me baffled, because it was clearly validating against the CREATE Table line. If I changed a name in either the CREATE or the INSERT lines, I would get a warning in the other line, so it was validating one against the other. Then, when it ran, it was performing a validation against the existing table rather than against the SQL. Furthermore Intellisense (if it is even called that in SQL Server) will offer up the column names from the CREATE line regardless of whether they are in the existing table.

The behavior is now quite clear to me, but I still can't see that it is 'right'. If the validation is going to be against the existing table, then Intellisense, at the very least, should only show the fields from the existing table, not from the CREATE line. Instead, Intellisense and the designer work with the SQL not with the existing table, then validation occurs against the existing table when it runs, rather than running the SQL as written. It may be expected, and it is doubtless common, but how can anybody argue that it is correct?

In any case, this has been wonderful. Thanks to all who contributed, I learned a lot from this discussion. Please feel free to add more, but don't be expecting me to reply for a while, as I am going dark for about a week (which is an age on here).

[szlamany]

Here it is...

The DESIGNER is a hack-job of validation. Intellisense is a fraud - that is a local experience - based on "object designs" sucked in at the moment of booting the designer...

The Query-parser - which exists in the SQL engine - is the real-deal.

The table the query-parser is validating against is the "table-of-that-real-moment" - the SQL database log is keeping track of the actual table layout at that exact moment. It could be changed by another user - and not even commited (yes you can ROLLBACK an ALTER statement that adds a column to a table).

So the "it was performing a validation" is two different sources - the IDE/intellisense is hoping to help you but is really clueless of the "real late binding facts" of the table layout.

In a simple world most would never discover this.

You decided to re-create a table over and over again with different columns - thus the "server-side-query-parser" doing it's real-time validation rules.

Sometimes I really dislike the way these IDE's hide the reality - the intellisense it not "compiling" the query - it can't as that would be asking the server to do so. And that would never be real as SQL is multi-user and that would be like two people writing code in the same VB sub or function on two diffenent PC's and expecting the syntax validation to be done in tandem.

[TysonLPrice]

I'm assuming your only problem is debugging SQL Stored Procedures, means stop it at a point and inspect the various variables etc.
Just to let you know, SQL Server Management Studio supports debugging, just like VB or C#. You just need to press F10 and go line by line, instead of pressing F5, as you would do with VB or C# code.

That depends on what version you have. 2000 had it, 2005 didn't, I think it's back with 2008. Later versions of Visual Studio have it (2005+ I think). Last time I checked you needed SA access to run it and DBAs are relectant to give that out.

[TysonLPrice]

Ah, that makes sense. It is validating against the existing table. Had I dropped the table, it would have been unable to perform the validation, so it would just run the SQL without the validation.

While that makes sense, and I can live with it, I would argue that there is still a problem with the the query designer and validation that had me pretty well tripped up: If you make a typo in one of the field names, even one of the ones that isn't in the table, the designer will underline that typo. Technically, this is kind of correct, but also rather misleading. It will validate against the actual table when it runs, but it will validate against the CREATE Table line while in the designer. That had me baffled, because it was clearly validating against the CREATE Table line. If I changed a name in either the CREATE or the INSERT lines, I would get a warning in the other line, so it was validating one against the other. Then, when it ran, it was performing a validation against the existing table rather than against the SQL. Furthermore Intellisense (if it is even called that in SQL Server) will offer up the column names from the CREATE line regardless of whether they are in the existing table.

The behavior is now quite clear to me, but I still can't see that it is 'right'. If the validation is going to be against the existing table, then Intellisense, at the very least, should only show the fields from the existing table, not from the CREATE line. Instead, Intellisense and the designer work with the SQL not with the existing table, then validation occurs against the existing table when it runs, rather than running the SQL as written. It may be expected, and it is doubtless common, but how can anybody argue that it is correct?

In any case, this has been wonderful. Thanks to all who contributed, I learned a lot from this discussion. Please feel free to add more, but don't be expecting me to reply for a while, as I am going dark for about a week (which is an age on here).

I've been an application programmer my entire career but always had to interact with databases and for the most part in development did the work myself. Something that helps to keep in mind for esoteric issues similar to this one is just like most languages the SQL has to get turned into machine code. So SPROCs go through a compile stage, build an execution plan, and things like that. So what you are looking at and seeing logically the compiler might "throw out the window".

[Shaggy Hiker]

Here it is...

The DESIGNER is a hack-job of validation. Intellisense is a fraud - that is a local experience - based on "object designs" sucked in at the moment of booting the designer...

Ok, I get it now.

They could have done it better, but it is what it is, and I can live with that.

[FunkyDexter]

If your tables are small look into table variables. The reside in memory and can be much faster. They start with @.

While it's kind of an adjunct to Shaggy's original question I thought it was worth pointing out that this is incorrect. Table Variables can exist in the TempDB and Temp Tables can exist in memory. The principle difference is that temp tables behave as tables and table variables behave as variables. E.g. a table variable cannot be referenced outside the current scope while a temp table can.

I remember this well because I spent about a year telling my team that table vars were based in memory before discovering that I was completely wrong about that

The DESIGNER is a hack-job of validation. Intellisense is a fraud - that is a local experience - based on "object designs" sucked in at the moment of booting the designer...

That's a really nice way of putting it It's also worth mentioning that you can refresh the intellisense cache at any point under the edit menu (although quite why refreshing a cache is under the edit menu is a mystery to me) and it will refresh itself on a regular cycle if left alone. Not sure how long that cycle is but I think it's about once a minute from my somewhat sketchy observations.

[TysonLPrice]

While it's kind of an adjunct to Shaggy's original question I thought it was worth pointing out that this is incorrect. Table Variables can exist in the TempDB and Temp Tables can exist in memory. The principle difference is that temp tables behave as tables and table variables behave as variables. E.g. a table variable cannot be referenced outside the current scope while a temp table can.

I remember this well because I spent about a year telling my team that table vars were based in memory before discovering that I was completely wrong about that

That's a really nice way of putting it It's also worth mentioning that you can refresh the intellisense cache at any point under the edit menu (although quite why refreshing a cache is under the edit menu is a mystery to me) and it will refresh itself on a regular cycle if left alone. Not sure how long that cycle is but I think it's about once a minute from my somewhat sketchy observations.

Actually I think saying "If your tables are small look into table variables. The reside in memory and can be much faster. They start with @." is incorrect - is incorrect. Like many things "it depends". Also you don't mention specifically which part of that statement is wrong. I'm guessing you are addressing where they reside, and that too, depends. I think you'll find most materials refer to them, table variables, as residing in memory. At least starting there if they don't spill over into tempDB .

Here are a couple of links I think are worth looking at regarding them:

http://odetocode.com/articles/365.aspx

http://www.sqlteam.com/article/using-table-variables

There are a lot of differing opinions about performance differences, location, etc. I'd be careful about a flat statement like "If your tables are small look into table variables. The reside in memory and can be much faster. They start with @." is incorrect. For the most part I stand by what I posted as generally true when they are small. However so much of it "just depends".
.

[szlamany]

Actually the difference that stands out in my mind most is that TABLE VARIABLES cannot be part of transactions - they do not rollback. That distinction lives in the part of of my brains that considers TEMPDB vs memory.

Of course the "structure" of the TABLE - whether TEMP or VARIABLE - is stored in TEMPDB.

TEMP TABLES can have that structure altered - and also have that structure rolled back.

TABLE VARIABLES can never have their structure altered.

And TEMPDB is only used to "store" the data of a TABLE VARIABLE that has become too large for memory. That is very different then the fact that a TEMP TABLE has transactional aspects and can be rolled back. And the fact that a TEMP TABLE can be "cached" in memory is a speed-enhancing caching fact - not the same logic that is saying "omg - table variable gone wild - time to store it in TEMPDB".

So, for my personal consideration - if a small amount of data, use a TABLE variable if possible. The no cleanup is the real benefit to that - right? If a large amount of data I usually go traditional TEMP TABLE.

If it's in between then you benchmark it.

I've got a UDF that returns a TABLE VARIABLE that calculates municipal pension amount for a person. It's too slow and has to be re-factored - I hate that. Wish I knew from the start I was going down a "dead-end table variable path". I've probably been burned by TABLE VARIABLES (like in this case) about 3 or 4 times. Payroll calculation was another prime example (although about 10 years ago)...

[FunkyDexter]

Also you don't mention specifically which part of that statement is wrong

Sorry, my clumsiness. The only part I was saying was wrong was the part about them residing in memory. The part about using vars for small table is a pretty good rule of thumb and one I certainly wouldn't want to contradict.

I think you'll find most materials refer to them, table variables, as residing in memory

An awful lot do, yes, but that doesn't stop them being wrong.

The main reason I corrected the statement, though, wasn't to highlight where they reside but rather to bring the (IMO) more important functional differences into the discussion. The scoping one was the one I could remember off the top of my head and the fact that vars don't get managed by a transaction because they're not logged is another important one. The thing that strikes me about alot of material on the net is it gets hung up on where they reside (and often get it wrong) and completely overlook the more important differences. As I said, it certainly led to me giving some bad advice to my team before I got corrected (by Steve Jones from RedGate as it happens).


edit> Just remembered another important difference: Table vars can't be explicitely indexed. They can be implicitely indexed by declaring primary keys and unique constraints on declaration but you explicitely create an index after creation and you can't create a non-unique index. That's another good reason why you'd probably want a temp table for large amounts of data.

[TysonLPrice]

I was really "high" on table variables at first but tend to shy away from them now. It is also amazing how much the "experts" disagree on many aspects of them. I agree with the important differences you mentioned. You can easily get turned around trying to mix and match what they do.

[szlamany]

Back when table variables were first introduced (SQL 2000 was the first - right) the memory aspect was the selling point to developers. To have a down-and-dirty not-logged in-memory quick place for 2 or 3 rows - wow - that's great.

And all the buzz was about how efficient they could be - but what happened if you pushed the number of rows too far? I think a sql-page is like 8000-ish bytes - I remember reading articles about how it used memory and yadda, yadda.

So MS decided to off-store on disk if they get too large - obvious place is TEMPDB since no one cares about the internal structure of TEMPDB. Maybe that is the "bottleneck" moment we all start encountering when TABLE VARIABLES decide to tank on us performance wise.

The relationship between TEMPORARY TABLES and TEMPDB is an ancient one - probably dates back to SYBASE in the 1960's! The DBA-in-me has been burned by TEMPDB out-of-control growth moments.

I only mentioned table variables to Shaggy so that he could see that there were other places then #XXX tables. He was making "real tables" in the production database and thinking they were "temporary" because he did not need them hanging around.

Good conversation though

[Shaggy Hiker]

Table variables are off the table, as what I will be doing won't be small. It won't be huge, by many folks standards, but it won't be small.

[szlamany]

This link seemed to have really good info...

http://www.sqlservercentral.com/Foru....aspx#bm441400