| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-06-03 : 14:21:43
|
I've gota DTS (active x) package (1 step only) that executes a stored procedure to return a recordset of queued email messages. The DTS ActiveX script loops through the recordset and SMTP's 'em away.I can execute it immediately with no problem. If I execute it on a scheduled basis, it fails. Not much information on why. quote:
The job failed. The Job was invoked by Schedule 76 (DLA2003 Scheduled Email Sending Process). The last step to run was step 1 (DLA2003 Scheduled Email Sending Process).
What tools are available to diagnose this problem? Any help appreciated.Sam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 14:31:50
|
| There should be more information in the job history. You have to click on the Show Step Details check box. Then go to the step that failed to get a more descriptive error. If there isn't any good information in there, you're left with checking permissions, checking who owns the jobs, etc... and troubleshooting this problem in DTS designer. Let us know what the error details of the step are after clicking on the check box.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 15:18:33
|
| DTS is a client utility and so runs on the client machine using the users profile.When you schedule the package to run it will run on the server and use (probably) the sql server service account.The problem is probably due to something not being installed on the server which is installed on your client m/c or the account not having the correct permissions.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-03 : 15:23:10
|
| Nigel,You are right. I can schedule the job from my old client PC.Arg !!!!! $# #@# !!!Sam(I'm working on locating my microscope to find the difference in the two clients) |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-06-03 : 15:34:59
|
| This KB has some quite good tips for troubleshooting running DTS in a job[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074[/url]HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-03 : 15:59:14
|
| Thanks for the white paper. I checked the Job owner as directed in the article, and it is identical (doesn't matter which client PC I use to schedule the DTS script).However, one of the client PCs gets successful execution of the scheduled DTS package, and the other client PC gets failure - and this is for the same DTS script object !Arg (again) !!! @#@$@ !!!Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:03:39
|
| Check service pack levels on each of the machines. SQL service packs should be applied to the clients that use DTS when a service pack has been applied to the database server. This was very true in SQL 7.0, although I'm not sure if it still applies to SQL 2k.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-03 : 16:12:45
|
| Good point Tara, and I hadn't checked the server until you mentioned it.Both Clients are on SP3, but the server is SP2.I'll need to have the server upgraded to SP3 (probably Saturday wee hours), but since both clients are running SP3, the difference in scheduled job success/failure doesn't point to the SP revision as the problem.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:15:02
|
| Can't you just create the DTS package on the database server, meaning using the client tools directly on the database server instead of on a user machine?Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-03 : 16:21:37
|
We have a *managed server* contract - at least for now. My access to our server is EM, FTP, QA.It's like fighting with your hands tied.  Maybe I should argue for telnet access.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:23:47
|
| Not sure what telnet is going to give you, but ask for Terminal Services/Remote Desktops/pcAnywhere/etc...Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 00:17:01
|
Believing this is a permissions problem, I've stripped the DTS back to activex saved on my client PC. I've deleted the JOB, and the DTS package.Next, recreating the DTS package from scratch, the problem emerges again, but only when I schedule the job from my new client. quote:
Executed as user: NATIVEINTEL\sql. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Oddly, scheduling this job from my old client executes successfully no errors. Both the old and new client PC have the same username / password pair to register the remote server.Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-04 : 01:17:54
|
| Have you checked the network library?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 07:52:26
|
| Thanks Nigel,I just checked network library in "Client Network Utility". Both clients match.Also TCP/IP and Named Pipes enabled, both machines. Both Alias declarations to the remote server match.Is there another network library setting?Sam |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-06-04 : 08:12:54
|
| In the connection string, in your ActiveX Task, are you using a trusted connection or specifying the username/password explicitly.If the former, you'll need to check verify that the SQLAgent Service startup account, or the proxy account set up for it, can see your server.Jay White{0} |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 08:20:05
|
| Hi Jay,I'm using the username / password pair in the DTS activex (which makes this so much more difficult to understand).Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 08:21:28
|
| Could my windows username password be slipping into this scheduled job somehow? I have the same username / different passwords on both clients.Even if it were, my local windows username is not known on the remote server.SamEdited by - SamC on 06/04/2003 08:23:12 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-06-04 : 08:25:41
|
| Hum....is the error occuring inside the ActiveX Task or is it with loading the DTS Package? If you log the package, does it run the step and fail or does it not even start running the package?Jay White{0} |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 08:28:35
|
It runs and fails. I posted this error from the Log History earlier: quote:
Executed as user: NATIVEINTEL\sql. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
The SQL DB username / password are coded in the activex, and this job runs correctly when scheduled from my older client. Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-04 : 08:36:39
|
| Thanks everyone for your help.I'll be back in about 5 hours.Sam |
|
|
|
|
DTS Package Headache