So are you saying that your CPU is 80-90%? If...

Here's a decent read on scaling out your application:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

So you've already given it a shot with SUBSTRING? Why don't you post what you tried so we can have a look.

I would say that CPU usage of 95% and connections timing out is not reasonable!! It sounds like you are sharing your development box with multiple development groups? ...

Thanks for posting your final solution. It helps those who may come accross this thread months from now see what ended up working for you.

Thanks for following up with the end solution Diann! Also, now that you have the UNION, you no longer need to include the CASE statement for your Types. ...

Shamsudheen,

I think this was a case where we were not privy to the full scope of the problem domain that Diann was trying to solve. The solution that...

Check out the PIVOT statement in Books Online.

The tables in question have around 100 columns and 900 or so rows. It looks like your query would require writing a line for every column and every row...

From the looks of your code, you are trying to do this procedurally, maybe even with a cursor...correct?? This whole operation can be done with 2 set based queries....

Wow, that was great Jeff...thanks. I'll have to admit, I've been just watching this thread waiting for a solid explanation of the benefits of any of the solutions provided....

I wouldn't quite call it casting. This may help:

DECLARE @Table TABLE (RowID int, RowValue varchar(10))

INSERT INTO @Table

SELECT 1, 'Mister' UNION ALL

SELECT 2, 'Senior'

SELECT REPLACE(RowValue,'Mister','Mr') FROM @Table

It looks like a permissions issue. Make sure that the Peru8\SYSTEM account has permissions to do everything the package needs to do. When you ran the package outside...

You can run SQLDiag.exe to give you detailed locking/blocking/process info.

Ah, I did not see that your tables are not on the same server. Yes, you'll need linked servers and you'll need to watch performance.