What's the reason behind running an insert statement with EXEC? That's a lot of unnecessary work. I hope you don't code all you procedures this way.

This is not mine. I don't remember where it got it. Could be useful to you though.

I would just look in management studio.

It's placed in memory. Depending on the size of the resultset it can also be dumped to disk.

[edit]

This is an assumption based on what I have observed. Run...

Why did you set lock pages in memory for your user account? Is that the account Sql server service is running under?

It took so long because after the clustered index was built, all the non-clustered indexes had to be rebuilt as well. Can't really say why it's so big not...

If that login doesn't exist on the other server then yes, you need that login on the other server. It will also need appropriate database level permissions assigned to...

Look at the first archived sql log file

The error log is in the install directory. Something like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

I would make it a point to get very familiar with it.

Solution to what?

Is this a new instance?

Look at the windows application log from the server (meaning not from your PC)

How about using profiler.

Do you have a performance problem or are you just moving things for the sake of moving them?