On the access error, you need to look at the linked server set up. It needs to be logging in with rights that can select from those tables.

I'm having trouble visualizing what you're describing. Can you provide a sample table (create table script) and a sample insert command, to create the data you're describing? Just...

Is it running in a different database than the others? I don't mean msdb, I mean the database it does its work in.

Some of these can be...

Good interview. Thanks.

Do a select that joins the two tables on the class_id column, and with a Where clause that eliminated the null rows that you don't want. Does that get...

The left join usually is the best way to handle that. It can also help in some cases to pull the data into a temp table, and then left...

Substring and Charindex can do that pretty well. But first, what do you want to do if the name is "James W Smith", or "Morgan Mac Donald"? Do...

If it's giving multiple result sets, it's got multiple selects.

Go ahead and post the script. That'll definitely help.

The only way to force the sequence is to make them separate queries. That doesn't mean a CTE or derived table, it means a table variable or temp table....

Take a look at Count, and Group By in Books Online. That will do exactly what you need.

Either of those methods will work if written correctly.

If they don't do what you need, you need to clarify what you are trying to do.

Stupidly enough, you will get that error because of the Cast, even with the Where clause limiting it. I've seen that dozens of times. You shouldn't, but you...

Lynn Pettis (4/9/2009)

---

Any help on that thread would be welcome. Based on further information from the OP, I'm thinking something written using SMO may be a better option than...

Yep, a column alias will also be needed. Missed that one.