DTS Package fails when SCHEDULED (Only fails with non-MS dbs / drivers)
-
For several days now I have struggled with a seemingly small issue. Getting a DTS package to run when scheduled in SQL Server Agent. I trawled all the forums and read up about the problems with permissions, configuration etc. but my problem would appear to be different. I have outlined the setup, errors etc. below as comprehensively as possible. Any help / insight would be appreciated.
Objective:
- Copy data from an Interbase 6 db (server and db running locally) to a SQL Server 2000 db (server and db running locally) with a simple DTS package
Setup:
- MS SQL Server 2000 with SP3
- MDAC 2.8 RTM
- Interbase v6 /Firebird 1.03 db Server
- Firebird ODBC Driver
- Connection via ODBC - tried System DSN and File DSN
Only one domain login has been used throughout with Local Admin rights on the machine AND sysadmin priv on the SQL Server
The following have been checked to ensure common user login/password:
- SQL Enterprise Manager (SEM)
- SQL Server Agent (SSA)
- SQL Server Agent Proxy (SSAP)
Problem:
When executed under SEM the pkg operates successfully
When scheduled and executed under SSA it fails - the log below shows the problem (The log above reflects the same error regardless of whether I have an active session (i.e. I am logged in at a terminal) or if I log off.)
----------------------------------------------------------
Package Steps execution information:
Step 'Drop table [LocalHrWizEmployee].[dbo].[MASTER] Step' succeeded
Step Execution Started: 21/04/2004 12:05:01
Step Execution Completed: 21/04/2004 12:05:01
Total Step Execution Time: 0.359 seconds
Progress count in Step: 0
Step 'Create Table [LocalHrWizEmployee].[dbo].[MASTER] Step' succeeded
Step Execution Started: 21/04/2004 12:05:01
Step Execution Completed: 21/04/2004 12:05:01
Total Step Execution Time: 0 seconds
Progress count in Step: 0
Step 'Copy Data from MASTER to [LocalHrWizEmployee].[dbo].[MASTER] Step' failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 21/04/2004 12:05:01
Step Execution Completed: 21/04/2004 12:05:02
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
----------------------------------------------------------
Questions?
- Surely it can't be permissions if I use the same login throughout and I have both local admin rights and sysadmin privileges?
- No network connections are required as all files, services etc. are local
- The really frustrating thing is that when a equivalent script is run using the same steps, same logins, same setup except for the source connection being an MS based local file and using an MS based driver e.g. Access it runs without a hitch
I had thought it could be the Interbase/Firebird aspect BUT it runs successfully under SEM ... Day 4 and I need help ....
-
I'm sure you've rebooted the box since installing the db drivers. If you haven't it's worth ago.
We had a similar problem with oracle drivers and a reboot did the trick.
J
Thanks Jeet -
I have rebooted, re-installed, tried on 3 different systems - same error. I suspect that the problem is something simple, such as rebooting but at this stage I have exhausted everything that I can think of.
Thanks for the input though.
Neil
-
So Interbase 6 is installed on the same server as SQL Server, correct?
-
Yes, Interbase is running as a Service.
-
So it's not an issue of having the correct drivers on the workstation where the DTS runs fine interactively from EM, but not having the correct drivers on the server, where the DTS executes when it is run as a job.
-
Correct. Both the SQL Server and the Interbase Server are running locally as services. The Interbase db and the SQL server db are both located on the local drive so there is no network connections / mapped drives.
I have tried several ODBC drivers as the problem seems to be related to with the SQLServerAgenth making a connection to the Interbase db:
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
I am assuming it is the SQLServerAgent as the Enterprise Manager runs the packages without a problem using the same Drivers.
Permissions? Well I am a local Administrator, have the sysadmin priv on SQLServer and all the packages, connections, 'logins' for Enterprise Manager, SQLServerAgent and event eh SQLServerAgent Proxy are all under my login ... so I am a little confused.
-
Did you create the package from your pc or actually sitting at the server? If you created the package at your pc, you probably won't be able to schedule it. I read somewhere that a DTS package looks to the OS where it was originally created.... Whatever the reason, I have found this to be true many times. If I don't create the package on the server itself, it won't run under a schedule.
Linda
-
I would have to disagree with Linda's post.
I have created DTS packages on an WinNT workstation as well as XP, never on the server. I've been successful in scheduling all packages. The issues with jobs failing always stem from permissions of the agent account that the service is run under or the setup of the server being different than what is on the PC, meaning ODBC drivers set up on the PC but not on the server.
-
I can only go by my experience but in general packages are sensitive to where they are created - that is not to say they are 'locked' to the machine they were created but when moving them there is a checklist of sorts you have to go through to ensure that they will work when deployed on a new system. There are the obvious ones like the DSNs and drivers but also the way the packages reference connections, e.g. (local) or by explicit name.
Anyway back to the original issue ... to ensure that the issue of location was not a factor I duplicated the setup from scratch on a test box making sure that all db files, services (SQL Server and Interbase), DSNs, Drivers etc. etc. were run locally. The packages were created locally and are being run locally. Everything was being done under the same login which had sysadmin priv + local administrator + was a member of a domain (This should address all the normal issues related to having a lack of permissions and not being able to access or execute certain things). The following were all checked to ensure that they were using the same login with the priv. described above
- SQL Server Enterprise Manager
- SQL Server Agent
- SQL Server Agent Proxy (In theory this login shouldn't be used as I have the correct permissions to use the standard SQL Server Agent context but I am just desperate to execute the scheduled package)
I can only imagine that the problem lies in some way with the way SQL Server Agent executes. For the reasons I highlighted above I have tried to completely rule out permissions. Assuming that I have done this it possibly suggests that the Agent is trying in some way to make an unusual type of connection to the db via the System DSN that I have setup via the SQL OLE DB Provider for ODBC. Does anyone know if the Agent tries to exclusively open the db OR can only open a connection under certain conditions (excluding permission related reasons).
I am using MDAC 2.8 RTM and SQL Server 2000 SP3 so hopefully I should have the best mature technical platform MS has to offer.
This of course is just a wild theory born from desperation. Has anyone ever successfully used a scheduled package to connect to an Interbase db?
-
We pull data from DB2 and it seems like I remember running into an issue where we could not use the "IBM OLE DB Provider for DB2 Servers", but instead had to use the "Other (ODBC Data Source)" connection object in order to successfully extract data from DB2. Maybe its the same type of issue with Interbase??? (But I do not personally have any experience with this system.)
-
Unfortunately no improvement. I tried using both an ODBC connection and an OLE DB connection to Interbase, both failed when scheduled but succeeded when run through SEM.
THe OLE DB connection returned the following error:
- Step Error Source: LCPI.IBProvider.2
Step Error Description
atabase connection failed.SQLCODE=-904:
unavailable database
IBCODE=isc_unavailable
- Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Interbase / MS SQL Server / DTS don't seem to be a popular mix based upon a few searches through google and the forums... perhaps this is why.
- Step Error Source: LCPI.IBProvider.2
-
Just for fun... why don't you create a test package (on the server) and put in the piece that is failing and schedule it to run?
Linda
-
Have you checked this site?
http://www.ibdatabase.com/index.html
I'm out of ideas if its not a security issue with agent.
-
A key point on the website I had seen before. DTS can operate multiple threads/processes - and the default is 4 -the majority of Interbase ODBC drivers don't like this at all, to work around the problem I had to set the number of processes to 1. If you don't the SEM will just hang.
As to the security thing - unless I have missed something I think I have addressed it with my local admin/sysadmin/domain login.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply