Package cant find instance when run from a job

Question

Post reply

Package cant find instance when run from a job

juliekenny

SSCommitted

Points: 1558
More actions
July 29, 2004 at 8:37 am

#59964

We have a DTS package that is running OK on a test server but not on the live server. When run from EM it's fine but when run from a job or via a stored procedure in QA (the usual method of delivery) we get the following error :
******************************************
DTSRun OnError: DTSStep_RecreateTables, Error = -2147467259 (80004005)
   Error string: [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 14 (E)
   Error string: [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:     Help context: 0
***************************************
The connection is just to a database on the same server, each server is running a named instance of SQL Server. I've checked logins and authentication methods on each server and see no difference, does anyone have any ideas of what else I can or should check.
Julie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

If you copied it over from dev using export and import to live you have to change the connection properties.

Also check to make sure that the account that sql server agent is running under has the right permissions.

------------------------------
The Users are always right - when I'm not wrong!

juliekenny SSCommitted Points: 1558 More actions · Answer 2

The connections are OK if you run from within EM. The problem also occurs when you run from a stored procedure in QA, does this get affected by the SQL Agent?

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Julie, just an idea... we had similar problem this week (query not running from EM, only from QA) The problem was, that the EM was running under different user. We assumed, that EM is running through Windows authentication (i.e. with the same user as QA), but then we found out that in the registration is both username and password, which makes it behave just like NT authentication (automatic connection, no password required).

Please, check "Edit SQL Server registration properties", whether there isn't a different user specified (in your case, a user with wider range of rights, allowing the job to be run from EM).

HTH, Vladan