More fun with DTS Packages

Hi guys,

Well, another day, another adventure in the land of editing DTS packages. Anyone out there run into a package that will run for one sysadmin but not for the other? I have a package that can't initialize it's connections (sql server doesn't exist or login denied) for me but runs fine for my boss from his workstation. Note, permissions have been thoroughly investigated both on the database and the server plus I can run other packages on that server.

Bad or missing files somewhere perhaps? I logged onto the server (where the packages and the database live) and ran some packages. While all my other packages run OK from the server, the package that is giving me fits from my box gets an error message about

'cannot find msdb..sp_log_dtspackage_begin'.

Has this file given anyone else issues? My other packages run from the server without this error.

Any other advice besides 'don't use those evil DTS packages'?

Thanks for your help!DTS is not evil silly girl Microsoft is evil!

who does your boss authenticate to when he/she/it logs in?
who do you athenticate to when you log in?

sp_log_dtspackage_begin should be owned by dbo, is that the case on your server?

What happens if you recompile the sp? Make sure you are logged in as SA!|||Hah! Just another example of absolute power corrupting absolutely?

Well, we all get authenticated (on the network, I'm assuming) through the same box--there are only 15 of us so we have one domain controller. Supposedly we are all processed similarly.

As for the sp_, I cannot find this puppy. I'm assuming that if it exists, lets not rule out a bogus error message, it's not something MS is displaying. Searching for it doesn't turn up anything. It certainly doesn't seem to be anything anyone here wrote.

Ahhhh, to have a local DBA. Currently I'm recreating the whole @.#^$#!$ thing (it's rather robust, to say the least--30 tasks, most with individual field scripting in data transformation objects). I'm on object 4 and so far it runs fine.

Have you ever had one of these become corrupted?

Should I get a bigger stick?|||Bigger stick? Only if it's long enough to reach the the prior developer!

When I was asking about authentication I meant on the SQL box. Are you athenticated to dbo where as your boss isauthenticated to sa?

this sp was shipped from Microsoft. it should be:

CREATE PROCEDURE sp_log_dtspackage_begin
@.name sysname,
@.description NVARCHAR(1000),
@.id UNIQUEIDENTIFIER,
@.versionid UNIQUEIDENTIFIER,
@.lineagefull UNIQUEIDENTIFIER,
@.lineageshort INT,
@.starttime DATETIME,
@.computer sysname,
@.operator sysname
AS
SET NOCOUNT ON

INSERT sysdtspackagelog (
name,
description,
id,
versionid,
lineagefull,
lineageshort,
starttime,
computer,
operator
) VALUES (
@.name,
@.description,
@.id,
@.versionid,
@.lineagefull,
@.lineageshort,
@.starttime,
@.computer,
@.operator
)
RETURN 0 -- SUCCESS

GO

if you can not find it in the list of stored procedures in the msdb database I would say you have a bigger problems. You should be able to copy this from another server.

SQL Server has been a stable product since 7.0. Stable in the sence that things don't mysteriously stop working. Data can and does get corrupt but this sounds like a stored procedure was deleted! Maybe time to check security and change the sa password?

as for corruption, I like to think of Microsoft as a drug dealer, they give you a free taste of their drugs till you get hooked and then WHOMP they have you!|||Thanks for the sp. Adding it to the database's sp's didn't make a difference. However, it is either not displaying or simply not there on any of the databases on this server. Hard to tell what's going on. The packages that run, run with or without. The one that doesn't, similarly, won't run either way.

Sorry if I'm being obtuse, after all I'm just a stray VB/VBA programmer who took a job without realizing that there was no DBA for the back end here. (A situation you do not find when writing to Oracle. Probably MS is making things appear to be way too easy. I bet it's a marketing tactic, 'Use our software and you can fire those expensive DBA's. Any schmoe can ride herd on SQL Server--even applications programmers.') However, under the Server's Security, Server Login Properties I am a system administrator. I'm assuming it doesn't really get any better than that.

Note, the box I'm currently using is considered to be seriously corrupted--at least by the previous user--and when I installed the sp_ my icons for Enterprise Manager blanked out on the desktop and icon bars. Is it a sign? Should I start looking for Holy Water and a disk with SQL Server on it?|||The sp must be add to the msdb database, if it isn't already there. Please verify that the sp exists in the msdb db.

sa = god in SQL Server speak.

<soapbox>I hate companies that think they don't need a dba and that a developer should be able to handle it. IMNSHO this is one of Microsoft's weaknesses. You will rarely see Oracle installed by someone that doesn't know what's going on. This is the #1 reason why Oracle is perceived to out perform SQL Server.</soapbox>

Before you lose your sanity I would verify you are working on a stable box or not. I am wondering if your DTS problems started suddenly or were never working right to begin with? If they just started to fail around the time your predecessor left you may be dealing with another issue.|||Oh dear, I am being a bonehead. Yes, it is there in the msdb.db. Forgot about that whole thing. For this level of hand-holding I may have to fly to Texas and deliver that ride.

About the box, it was my boss's until he managed to get an upgrade so I don't think he's been sabotaging (sp?) it, however, there have been many non-kosher installs. I'll be getting a recycled box whenever someone has time to rebuild a spare.

The only remnant of the previous developer here is the big crack in my monitor case--I hear the keyboard was destroyed. Apparently this occured immediately before his giving 2 minutes notice... Guess he was short on sense of humor.

In any case, I'm slowly hacking my way through rebuilding this thing (still on object 4 though). It's a good learning experience and I obviously have much to learn since I just met up with DTS two weeks ago. The VBScript part feels very comfortable and the two packages I have built so far seem to do a nice job. It's a great idea to have this nifty visual interface. Now if they could only add some decent debugging and flesh out the reference material to actually cover the entire product it would be grand.

I'm sure I'll beat this package into submission eventually.

Thanks for your help!|||Don't worrie about the ride, I needed a good laugh today!

DTS is a VERY cool tool, mainly because it was written to work with almost any provider not just SQL Server. When I worked at Exxon we used it heavely for Oracle and DB2.

Who is the owner of the DTS package? After re-reading your original post I have to believe this is an ownership/permissions issue.|||Well, I hope I made you laugh with me, not at me--but I guess either way is OK.

DTS is a cool tool and I think I'll be pretty good at it in a few months. Looks like I'm going to get lots of practice until they drag me away to do customizations on the front end.

Currently I am shown as the owner. However, it didn't start out that way. We have a wierd (read dysfunctional) setup here in that this package is used on a server in NY. However, development occurs in Boulder. We do not have access to the same databases or servers. They emailed me a copy of the package (which was working) for modifications/bug fixes. Then the fun began because, among other things, our databases have different names. 30 objects all pointing at a connections to nowhere...

I do believe that I have changed all connection strings to point where I can get a connection to a local copy of the NY database. I've also tried adding a new connection and pointing them there. Initially I suspected that the connections changes were the problem, but it is not intuitively obvious why it will run on my boss's box if that is the case.

None of this, of course, bodes well for the idea of sending the package back to NY and having it run in their environment. Right now I have tentative plans (assuming I can manage to test my changes here) of walking someone through just adding the changes on their system so that we change the one that is running rather than replacing it. Far from an ideal scenario either way.|||What happens if your boss logs on your box and runs the package?
Do you run the package from the GUI or DTSRUN.EXE?

Given your current suspect box and diffrent connections string, I think walking a warm body through the changes would be a safe thing to do, for now.

BTW The only person I ever laugh at is myself.|||Hey, I only started programming after they gave us a GUI. I was being an opera singer when that whole DOS promt thing was in vogue. Then almost ten years ago I discovered how easy it is to make money by working on computers... Well, it is easier than trying to get people to pay you decent money (or give you health benefits) to sing.

To answer your question, I click on that there big green arrow thingy or go to Package>Execute.

I'll see if I can get my boss to log onto my box. Might be a while before I can corral him into that.|||Well, I guess they don't pay the boss the big $$$ for nothing.

It wouldn't run for him either, but having received packages for editing from NY before, he knew of one more place to look. He figured out that the path for the package log was set to 'local'. I do have a local copy of SQL Server but it isn't running. Apparently his local copy was running this morning when he tried it on his machine.

Whew! It always seems so easy when it's fixed.

Thanks for all your help--not to mention the entertainment value! File this one in the 'strange but true' category.|||Glad you found a use for your boss. Some times they are useful to have around.

School of hard knocks! Always the best teacher.

Not sure I would have come up with that one but in time all magic is revieled.sql