I don't remember "number" being a data type in SQL Server 2000.  Is that really what you're using?  If it is, please post correct table DDL.

Let's start by removing the spurious first CTE from your code:
WITH rpt_emp as
(SELECT e.EMPLOYEE_NUMBER, e.CLIENT_NUMBER emp_cl_number, -->Part 1 for employees
COUNT(e.CLIENT_NUMBER) OVER() emp_cl, --> Part...

SELECT v.Address, c.City
FROM Vendors v
LEFT JOIN Cities c ON v.Address = '%' + c.City + '%'
Edit:- added c.City to SELECT list.

John

Yes.  The UNION operator eliminates duplicates and hence needs to perform a sort operation.  If you know there are no duplicates (which I think there aren't, in this case) you...

If you want to update 10,000 rows at a time, you can use an UPDATE TOP (10000) ... ORDER BY ID statement in your loop.  You'll probably need an OUTPUT...

Use a string splitter to split each date into its individual elements, and keep those that have four characters and can be converted to a number between, say,...

Have you checked what that error message means?  Sounds like some kind of network issue to me, but don't take my word for that.

John

I would advise you not to grant access to public.  That means that anyone who can get on the server can run the stored procedure.  Grant access only to the...

Then it's your SQL Server Agent service account that needs to have permission to run the stored procedure.

John