asked on

Run a local DTS package

I have set up a DTS package. When I ran the package manually, it went well. But when I schedule it, it had this error. I read some postings and articles about SQL agent account. So I have set SQL agent account as a local system admin in that Win2k3 box and system admin in SQL Server 2k. It does not help as all. By the way, I also tried agent proxy account also.

Please help.

Bin

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Saqib Khan

Which Account Executes DTS (Within DTS)?
when you manually execute DTS, that Account is same as provided within the DTS?

SOLUTION

lozzamoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Navicerts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

lozzamoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Navicerts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_1316035

ASKER

>> Which Account Executes DTS (Within DTS)?
when you manually execute DTS, that Account is same as provided within the DTS? <<

What do you mean by the account "within the DTS"?

>>running the package manually through the GUI<<
Could you be more specific?

Thanks a lot,

Biang

Member_2_1316035

ASKER

It just came to my mind that the package does need to contact other server on domain. I need to give a domain account name for SQL Agent. But the SQL server runs the DTS package is not in domain yet. How should I proceed?

I still appreciate if experts answer two questions I asked.

Thanks

EugeneZ

what is your DTS doing?
if it is usingf connection to anothere sql server - on main server where DTS pack located creat alias in client Network utility for second server (TCP\IP ..)

If not make sure there is last MDAC on server.. (last Sql server sp too)

Member_2_1316035

ASKER

It copies data from other server to intermediarary tables on the local server. Then run a stored procedure to insert or update data to final tables.

I will double check I have lastest MDAC on that Win2k3 box.

EugeneZ

can you create alias in client Network utility for other server (TCP\IP ..) on the local server?

EugeneZ

or try to use IP address in connection of DTS pacjk for other sql server..

EugeneZ

BTW: can you check that the job runs as 'sa' or with sa privlg account..?

Member_2_1316035

ASKER

>>can you create alias in client Network utility for other server (TCP\IP ..) on the local server?<<

I thought I did since the alias name was among the server list under DTS connection properties. But I found out the alias is only on my development platform, not on the server box. So I rename the alias on the server as the one used in DTS connection. The error is still here.

>> BTW: can you check that the job runs as 'sa' or with sa privlg account..?<<
I ran the job as OS local system admin and SQL server system admin. Is it same as 'sa' account?

Member_2_1316035

ASKER

The problem was very likely with the connection to another SQL server, which is in a domain and in Windows authentication only mode. The local server is not in domain but my development platform is.

Does the local SQL Server has to be in that domain?

Member_2_1316035

ASKER

I can run the DTS package successfully only on the development platform, which is on that domain and have the right to access the remote SQL Server.

When I logged in as local account, I can not run the DTS package. The error message is the same.

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

lozzamoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_1316035

ASKER

When I logged into my development PC with a local account, which does not have acccess to the remote SQL server. I could not run the DTS package manually any longer. The error is at the end the posting.

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

EugeneZ

Thus:
or create\use sql server login
or nt\account for trusted connection...

Member_2_1316035

ASKER

On that remote SQL server I guess. That server is in Active Directory domain while the local server is not. I guess I have to move the local server into AD. Right, EugeneZ?

Thanks

Member_2_1316035

ASKER

Thanks for your input. Problem was solved after the box was moved into domain and SQL service runs as a domain account.