April 28, 2009 at 10:10 am
I am in the process of changing over from MSSQL 2000 to MSSQL 2005. As part of this upgrade I have had to convert DTS packages to DTSX packages. I then had to have the following setup in order for my application users to run the DTSX packages ;
1 - Batch file on local PC runs Scheduled Tasks command on Application Server
2 - Scheduled Task on Application Server runs DTSX package
This all worked fine 2 weeks ago and then just last week I put SP3 for MSSQL 2005 on and when I went to run the local batch files nothing happens, the Scheduled Tasks are not executed. The Scheduled Tasks on the Application Server can still be executed directly on the server to run the DTSX packages.
The message I get on the local PC when running the SCHTASKS command is ;
ERROR : Multiple connections to a server or shared resource by the same user, using more than one user name,are not allowed. Disconnect all previous connections to the server or shared resource and try again...
I have checked and SQL Server Surface Area Configuration is setup to allow remote connections.
I have been all round the houses trying to find a fix for this but alas no joy. Does anyone have any ideas, has SP3 messed this up ? Is there a better way for these packages to be setup to be executed as and when required from remote PC's.
April 28, 2009 at 10:55 am
Can you post more information about the batch file? It almost sounds like a windows style error versus a sql error. I have seen those types of messages when you try to map network drives using the net use command more than once without disconnecting first. Can you get the scheduled task call to work with something outside of SQL Server to verify that portion?
April 29, 2009 at 1:33 am
I have done as you suggested and created a Scheduled Task on the Application Server which runs a batch file to put Today's Date into a log file. This Scheduled Task CAN be executed remotely from a user PC using the SCHTASKS command.
I then tried my application batch file and this time it worked fine and correctly executed the Scheduled Task and subsequently the DTSX package. I am not aware of anything that has changed on the server since last night.
It doesn't make sense as to why it wasn't working for afew days then now all of a sudden it is again !?
Thanks for your reply.
Kenny.
April 30, 2009 at 7:01 am
You're not going to beleive this but my paronia took hold of me today so I tested my local batch files to see if they would continue to work as per yesterday but alas they don't anymore !!? Am back to getting the 'multiple connections' error.
Can anyone offer anything to try and resolve this once and for all ?
April 30, 2009 at 7:16 am
Hi there,
It sounds like you've had it work on SP3 since you upgraded. What's the server OS, 2000?
What user were you logged in on the machine as when it worked and when it didn't? Also, what context does the batch, or calls within the batch run under?
Try using NET USE to see what connections you have open...
I'm not really a network guy, but it definitely sounds like a networks issue...
Hope this helps.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
May 5, 2009 at 1:36 am
After much messing about this issue looks to have been resolved. We had to have our login scripts for windows run a scheduled task command on the application server which in turn opened the required connection on the local PC's. This then allowed the users to run the 2 application processes which then ran further scheduled tasks which in turn ran the DTSX packages.
Very long-winded way of doing things but for now it appears to work.
Thanks to all for their replies.
Kenny.
May 5, 2009 at 1:41 am
After much messing about this issue looks to have been resolved. We had to have our login scripts for windows run a scheduled task command on the application server which in turn opened the required connection on the local PC's. This then allowed the users to run the 2 application processes which then ran further scheduled tasks which in turn ran the DTSX packages.
Very long-winded way of doing things but for now it appears to work.
Thanks to all for their replies.
Kenny.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply