Create a DSN on the SQL Server box using the Client Access ODBC driver.

This DSN should point to the AS400 libraries you want to use.

Add the AS400 server as a linked server to...

Check to see what name is actually used in the database. Often, DOMAN\username is set as the SQL Server Login, but the database user name may just be the username...

It does not matter what the user authentication is, you can just add the LOGIN to the TargetServerRole in MSDB

Add the user to the TargetServerRole in the MSDB database

Thank you all for your prompt and workable solutions.

That would work for those records I sampled. But what about records which

exists in the sample where the 3rd record is not there.  In that case, the desired record would...

Here is a stored procedure which may help:

CREATE PROCEDURE ChangeAllObjOwner (

  @oldowner sysname,

  @newowner sysname

)

AS

DECLARE @objname sysname

SET NOCOUNT ON

--check that the @oldowner exists in the database

IF USER_ID(@oldowner) IS NULL

  BEGIN

   ...

I think Sergiy's solution will fit the bill. Thank very much!

SQL Server 2000 ships with a scripting utility which can be run from a job.

Here is sample code we run nightly for an "ever-changing" test database:

declare @command varchar(1000)

declare @texttime...

DELETE FROM

 p1

FROM

 tmPunchtimeSummary  p1

INNER JOIN

 (

  SELECT MAX(itmPunchtimeSummaryId) as as itmPunchTimeSummaryID, sCalldate, sEmployeeId, dTotalHrs FROM tmPunchtimeSummary GROUP BY  sCalldate, sEmployeeId, dTotalHrs HAVING COUNT(*) > 1

&nbsp p2

 ON(

  p1.sCalldate =...

sp_MSForEachdb @command1='PRINT''[?]''', @command2='USE [?] EXEC sp_helpfile'

This should loop through all databases and give file locations for each database file and log file for each database.

Using Windows Scheduler.

SELECT     item.[search description], mat.[material__code]

FROM         [Item] item INNER JOIN

DISPENSER.rx_impress.dbo.material mat ON mat.[material__code] LIKE SUBSTRING(ITEM.[SEARCH DESCRIPTION], 1, LEN(MAT.]METERIAL__CODE)

Everyone is correct about the ill-designed table. It is a commercial application design, not our own.

Thanks for everyone's help.

You should have an alias for the remote server set up on your Client Network Utility.  I would use the IP address is the ServerName text box.