October 16, 2002 at 8:22 am
We came this '' close to getting it to work.
In SQL Server 7.0 on machine #1, we created a job that calls a dts. The dts contains an ActiveX Script Task (VBScript) that searches for files in a folder on machine #2, then does some file copies, deletes, etc. Here's the problem: We can get it work when the files are on the same machine as SQL Server (machine #1), but not when the files are on machine #2.
Here's what we've tried so far:
1. Run the dts manually to make sure the path to the files on machine #2 is correct. Works fine.
2. Changed the path in VBScript from mapped drive to unc. Works when dts is run manually, but not when called by job.
3. Created the job and dts with userid who has sys admin rights. Checked to make sure that data connections userid, etc are all correct. Same symptoms as above.
4. Logged into machine #1 (SQL Server) with SQL Agent id. Can see files on machine #2.
5. Separated the VBScript from the dts and put the .vbs file on machine #1. The dts now calls a .bat file which calls the .vbs file. Works when dts run manually, but not when job calls dts.
We're pulling our hair out -- at least what's left of it. Any suggestions would be welcome. A lot is depending on this working, since the final version looks for 'trigger' files, and if not there, reschedules the job to try again in 15 minutes. If 'trigger' files found, it calls another dts and resets the job back to its original schedule. Everything works except for the machine #1 to machine #2 issue.
October 16, 2002 at 8:34 am
The first thing that comes to mind is the user the SQL Agent service is running with. Your DTS package (when scheduled as a job), will run under that NT user. So that user has to have access to the second machines folder where the files are situated.
Go to the Services panel and check the user that SQLAgent runs under. Enter a user that has sufficient rights on the second server's share.
I am not sure, but I think you covered this in bullet nr. 4. Just added this comment to make sure you've checked it.
October 16, 2002 at 8:43 am
Have do done step 4 for the account SQL server is running with?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 8:48 am
Not sure by what you mean by logging in with SQL Server account. In addition to logging in with SQL Agent Id, we also logged in with sys admin id. Saw files on machine #2 both ways.
October 16, 2002 at 9:02 am
Are SQL Server and SQL Agent services running as domain users, its these domain accounts that you need to login in as. Minimum is SQL Agent running as domain account for what you want unless the dts job is being fired from SQL code in which case its the SQL Server service that needs to be running as domain user.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 9:36 am
Simon, SQL Agent is running under the domain account, SQL Server is not. The DTS is being fired by a CmdExec in the job (example: DTSRun /S GGGSVR199 /U sa /P /N dts_testrh_searchForTriggers)
October 16, 2002 at 9:48 am
Slightly confusing, so let me state this simply and let me know if this is correct.
MAchine1 - SQL Server 7 server, has dts pacakge.
Machine2 - Remote Windows server machine containing files (that you want)
Local\MSSQL - Service account for MSSQLServer service on Machine 1. This is a local account? Is it local admin?
Domain\SQLAgent - Service Account for SQLAgent service on Machine1. This is a domain account. Local admin?
Log into Machine1 as Domain\SQLAgent. Open EM/DTS Designer, run package. Works?
Steve Jones
October 16, 2002 at 11:31 am
Thanks to all so far for the input. My responses to Steve are in [CAPS].
MAchine1 - SQL Server 7 server, has dts pacakge. [CORRECT]
Machine2 - Remote Windows server machine containing files (that you want) [CORRECT]
Local\MSSQL - Service account for MSSQLServer service on Machine 1. This is a local account? Is it local admin? [IT'S A LOCAL ACCOUNT WITH POWER USERS PRIVELEGES. WE CAN CHANGE IT TO ADMIN IF NEEDED.]
Domain\SQLAgent - Service Account for SQLAgent service on Machine1. This is a domain account. Local admin? [SQL AGENT IS AN NT DOMAIN ACCOUNT (POWER USERS) -- ALSO TRIED THIS FROM A DIFFERENT SQL SERVER ON MACHINE #3 RUNNING A SQL AGENT WHICH WAS NOT AN NT DOMAIN ACCOUNT, BUT WAS A LOCAL ADMIN -- SAME RESULTS]
Log into Machine1 as Domain\SQLAgent. Open EM/DTS Designer, run package. Works? [YES]
October 16, 2002 at 4:56 pm
is the location being specified as a UNC path. I have known in the past with w2k selecting a UNC path but what got stored is a local path to the share in My network places i.e. F:\Documents and Settings\Administrator\NetHood\share on server.
Go into disconnected edit and check what is set.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 10:36 pm
Had a similar prob. not long ago when trying to run cmd shell from dts pkg when dts is called by a job.
After pulling many hairs out we set the sql agent proxy account using xp_sqlagent_proxy_account to make sure the sql server agent runs under the context of the proxy account configured (e.g. try creating a new user and make this user owner of job, then set this user as proxy account user and try from there). We found that we didn't have any account set up here to begin with, and when we did, it all worked. Hope this is on the right track for you.
October 17, 2002 at 4:26 am
I know you stated you logged in with SQL Agent account and can see the files, but did you run the package to see what was happening. Also, make sure you have no established network shares when you login as SQL Agent as they do not exist when machine is not logged in.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 17, 2002 at 9:14 am
You may have checked on this but I'll put it out there just in case: When you modify a DTS package, you need to becareful that the GUID created by the new version is the one being called in the job. In other words, typically you would "Schedule Package" to create the job that runs the package. Looking at the job, it runs a OS (DOS) command, DTSRun followed by what looks to me to be a GUID. I've run into an issue where I changed the DTS packaged and assumed the job would run the new version when in fact it was still calling the old version, so I was thinking that maybe your changes that work when you run the package from DTS are not working from the job because the job is calling an older version of the package.
October 17, 2002 at 12:57 pm
We had similar problems when we worked on DTS packages on SQL 7 machines using enterprise manager installed using SQL2k. Apparently, a different MDAC version between the two machines would corrupt the job in some way that wouldn't allow it to be scheduled. It would run just fine when we executed the DTS package manually, but when we scheduled the job it would fail. We replicated the job at the SQL server locally (on the server console) then scheduled it there and it worked fine. I have heard reference to a MSDN article about it, but didn't look into it because it was fixed.
October 17, 2002 at 1:19 pm
Just a note to let you know we're still around and trying. Thanks to all for the great suggestions, we finally got it to work twice, and are still in the testing/experimenting phase. Once we get it to where it's robust, I'll report back with our findings.
A couple of little quirks to whet your appetites/make you groan.
1. The server had 2 users with the same id -- one was for SQL Server Authentication (e.g. userid: idiotme / password: yep) -- the other was for SQL Agent which was the network (and box) id (e.g. userid: idiotme / password: nope). When we deleted the SQL Server login, and after doing item 2 below, we no longer got a login failure when running the job.
2. We had created SQL Agent on a development box using my network id and password. Fool that I was, my password was all numeric. When we changed the password to alphanumeric, the job no longer reported a login failure.
Note: The amazing thing is that all the above settings worked when the files for which Job/DTS was searching were on the same box as SQL Server.
Please feel free to post more comments, since we're still at it. Again, many thanks to all.
October 23, 2002 at 2:23 pm
Said I'd get back and here are our findings. Esentially, nothing to add to the previous posting, except that it's imperative that SQL Server Agent have valid id, password and permissions on the network.
We learned (the hard way) a valuable lesson in developing job-fired dts automation. Before fully developing the application, we tested for script execution, rescheduling the job based on success or failure (start the job in 15 minutes if there's a failure -- reschedule to weekly schedule if successful), etc. We figured that those would be the biggest challenges, and relegated connection issues to 'leave it to the last -- we'll fix it -- no problem' bucket. Very wrong.
From now on, when developing jobs that fire events across a network, our first priority will be connectivity. In comparison, the dts, vbscript rescheduling, etc was a piece of cake. Even now, we're still having some issues -- works well across the network to most machines, but one particular ftp server is not letting us in. We know it's a
permissions issue involving SQL Server Agent. But when dealing with a large network with tight security, it takes a little longer to get it working.
Once again, thanks to all for the excellent suggestions. And yes, I heard through the company grapevine that this year's bonus is a case of GrowGain.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply