Kenneth,

I don't believe this is an issue because the ORDER BY clause is invalid in derived tables, subqueries, etc. unless TOP is also specified, which is what we are doing...

Jeff,

"WOW!"  This is pretty cool. Not only does it work great, but is giving me some more ideas on...

Jeff, haven't read through all of your post so far but am anxious to take dig into it.  Wanted to let you know I'll get to it since you have...

I could add the WHERE to the first INSERT statement if all I had was Approach.  I actually have four other "steps" that I evaluate the median for as well. ...

Thanks everyone.  That was exactly the problem.  There were some records that just had the name of the state and obviously no comma.

Thanks Adrienne.  This will work just fine.

Agson,

Thank you.  Not sure I usderstand why, but that seemed to work.  Wished I didn't have to do the extra work but right now I'll settle for it working.  Thank...

Thanks John.  That got me over that hurdle.  Unfortunately some of the other errors I was getting are not proving to be as easy to resolve as I thought.

That's the road I went down initially and I kept getting an error when running the stored procedure telling me that there was an error converting that string ('SELECT COUNT(*)...

Yes, I am.  Earlier in the procedure I use this to populate the table.

SET @sql = 'CREATE TABLE ##structure _' + @login +

               ...

Thanks for your help PW.  This has me going in the right direction now.  Found out I had to make a few modifications because of the state of our data...

Thanks Andreas.  Your explaination and a few hours of sleep has cleared it up.

I have it working now.  It was a permissions thing in the SQL Server Agent.  Just gave the login for the SQL Server Agent rights to the directory on the...

In the job the owner is SA.  There is only one step in the job because it is running a package and everything I need it to do I am...

Not specifically, but since we started having these issues we did restart the entire server and that didn't make a difference.