I agree with Matt... dynamic, supposedly "smart" triggers whose code is exactly the same for every table is "Death by SQL" and deserves a bucket load of high speed pork...

Perhaps this will help...

http://www.sqlservercentral.com/articles/T-SQL/63681/

AND, if you do happen to need or want to plan on more than 2 billion characters of notes for each row, then I agree with some of the others......

I just wouldn't bother with all of that... we're not talking about in row TEXT columns. We're talking about NVARCHAR(MAX). Just add the Notes column to each table......

I don't see anything in the code that requires dynamic SQL... why the requirement to use it? I won't even ask why you think you need a cursor... 😛

Both 2000 and 2005 do it like Gail said... both make a copy of the table with the new column added, then it moves the data, drops the old table,...

Just use BCP to run it into a temporary staging table with the correct switches set to offload any bad rows into an error file. You should set the...

... and please see the following article for some potential problems you can avoid with some other methods of concatenation...

http://www.sqlservercentral.com/articles/Test+Data/61572/

Jerry's method does not have such problems.

1

... and is TempDB large enough for the task and is auto-shrink on TempDB turned off?

Sorry... not enough info. Please refer to the link in my signature for quicker/better answers.

If the NVARCHAR column has no multi-lingual entries (the main purpose for NVARCHAR), the a simple SELECT CAST(columnname AS VARCHAR(x)) where x is the desired maximum length should work. ...

Probably because it's relatively slower than a Cross-Tab and not backwards compatible to SQL Server 2000 and may not be migratable to other RDBMS's.

So... without any real data to test...

That won't be a recurrsive query... it'll be a simple join between the two tables joined on ID.

Probably not what you want to hear, but I'm thinking that the OCR software, well... sucks and you may have to find some better software.

Guess that's where the term "It Depends" comes from... :hehe: